Forum Discussion

aricgresko's avatar
aricgresko
Contributor III
4 years ago

Form Template Issues

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!

Cube View
  • AndyR's avatar
    AndyR
    New Contributor III
    4 years ago

    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's avatar
    aricgresko
    Contributor III
    4 years ago

    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's avatar
      AndyR
      New Contributor III
      4 years ago

      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

      • aricgresko's avatar
        aricgresko
        Contributor III
        4 years ago

        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.