Form Template Issues

aricgresko
Contributor III

Has anyone had issues with cell reference links breaking in Input Forms after clicking Submit Sheet?

Background:
We plan to base level accounts, with our operating expense accounts planned between four departments in UD2. All other accounts are planned at the “None” department level.

Example:
611000-Base Salary account can be planned more granularly to Dept 100 (Customer Support), Dept 200 (Prod Dev), Dept 300 (S&M) and Dept 400 (G&A).

A#611000:U2#Dept_100 = 5,000
A#611000:U2#Dept_200 = 2,000 

In this example, we would have a total of 7,000 planned to the 611000 account.

Issue:
The cell links are only breaking on the 6xxxxx accounts where we are specifying UD2 as a specific department vs. all other accounts having U2#None. Additionally, the values input in the form aren’t going to the correct department. The 2,000 example above from Dept 200 would go to Dept 100 for the same 611000 account.

Any advice would be greatly appreciated!

11 REPLIES 11

AndyR
New Contributor III

Hi Aric,
I think it would be helpful to see the row and column definition that applies to those cells. Also, if you right-click on the cell in question and look at the POV, what does it say?
Thanks,
Andy

aricgresko
Contributor III

Hi Andy,

Thank you for the reply! Here would be the rows for the 6xxxxx accounts that get planned at the UD2 department level.

A#600000.base:U2#Dept_100:U1#None:U5#None:U6#None
A#600000.base:U2#Dept_200:U1#None:U5#None:U6#None
A#600000.base:U2#Dept_300:U1#None:U5#None:U6#None
A#600000.base:U2#Dept_400:U1#None:U5#None:U6#None

Here are the columns.
T#WFYear.Base,T#WFYear

There are no row or column overrides on the cube view. UD2 is defined specifically on each row. For all accounts that aren’t a 6xxxxx account, we use U2#Dept_Tot, which is the parent of the departments listed above.

Also, there is some Data Cell Conditional Input restrictions placed on some accounts, some of which fall under the 6xxxxx accounts. I’ll attach a screen shot of what that looks like. Basically, that just restricts some of the accounts that our FP&A team doesn’t want planned to.

DataCellConditionalInput
 

AndyR
New Contributor III

Based on your slice security it looks like Dept_100 is a parent level. Are you using extensibility? At a minimum you’re applying that security across all scenarios even if it should only apply to a limited number of scenarios. I’m hoping you’re going to tell me FP&A has a cube all to themselves even if that still doesn’t fully explain what’s going on.

In your cube view for the input form, are those individual rows or do you have all of those on the same row? Are you only using Member Expansion 1 or are there nested expansions? When you run the cube view and you type in the 2,000 from your example, then right-click on the cell and select Cell POV Information, what does it tell you? When you look at your results does A#611000:U2#Dept_100 have 2,000 or 5,000 or 7,000? I think we’re just not seeing the complete picture here and we need more details.
Thanks,
Andy

I’ve attached a few screen shots to provide you some more information. To be completely transparent, I’m new to this company and wasn’t involved with any of the forecast/plan build out or design.

We do use extensibility. Dept_100, Dept_200, Dept_300, Dept_400 are all siblings that roll-up to Dept_Tot. That last screen shot of the data cell conditional input was showing an example of Dept_100 which is Customer Service. We have the exact same Member filter as shown above except the only change is Dept_200, Dept_300, and Dept_400 are used instead of Dept_100 (I’ve attached a screen shot of this).

Basically, a 6xxxxx account can be planned at one of those four department levels, while all other 4xxxxx and 5xxxxx accounts are planned to U2#None. Each row in the cube hardcodes which UD2 member to plan to and we are only using Member Expansion 1, no nested members.

So I retested again and the values are mapping correctly from the excel input form into OneStream. I corrected the parent in the POV of the cube view. Whoever built the form had that wrong. So now it seems the only issue remaining is the excel links breaking. I’ve attached screen shots of before I clicked Submit Sheet and after. On the after, you can see Dept_100 is good to go (it retains its link to another workbook, however the other three departments links break and the cells become hardcoded with the plan values. I’ve hidden a few columns to not show actual data.

dept%20200%20300%20400%20bad Dept%20100%20good

InputForm_Before
 
 
Those posted in reverse order. Bottom picture I’m showing which department the 611000 base salary account I am planning and this is prior to submitting sheet. The picture with “100” shows that Dept_100 loaded correctly and retained the cell link. The picture with the “X” shows the cells became hardcoded for Dept_200, Dept_300, Dept_400

 

 
 
 

AndyR
New Contributor III

Hi Aric,
I think I get it now. This is about Excel behavior combined with the add-in rather than the form itself. I’d suggest opening a ticket with OneStream. Maybe it’s a bug or maybe there is an underlying cause they can help identify.
Thanks,
Andy

Thanks Andy. I’ll follow back up on here once we get this issue resolved. Maybe it’ll help someone else out in the future!

This is a linked Cube View in Excel, yeah?  If so, check the Cube View settings as well.  There is an option to "Retain Formulas in Cube View Content"

db_pdx_0-1639165466387.png

 

StephD
New Contributor II

Did you get to a resolution with OneStream on this?  I'm seeing the same behavior, but for my specific process we have 7 CubeViews involved.  6 of the 7 keep the formulas as expected, but 1 of them lose the formulas on refresh/submit.  I have not found what makes this one different then the others.  

@StephD we ended up creating a template using XFSet.  Our users wanted to be able to link their forecasting models to the OneStream upload template using SUMIFS formulas.  All of the XFSet logic is hidden/locked from them.  All they have to do is work in their models and when ready to submit, login to the excel add-in and click submit sheet.  Another reason we went with XFSet is the issue when rows shift in a cube view, such as a new GL account added during the year, it can easily break an end user's links to other tabs in their workbook.  

StephD
New Contributor II

@aricgresko - thanks for the reply.  I also have a ticket open with OneStream so will see if a cause is found.  We are using the XFSet for other processes and were thinking of converting this one over too. Most of our users will use vLookups to get around the rows shifting, but I'll keep in mind the thoughts about hiding/locking cells as that is a good tip.