Dynamically Updating Elements and Suppressing Zeroes in Perspectives

[DavidS]

Hello All,

I am currently working with Perspectives for the first time, and am attempting to set up a report that has dynamic dimension elements in the middle of some data. I need the user to be able to select members of other dimensions at the top, and have the account dimension refresh to only show accounts that contain data for that member. For example, if they select HR, the accounts shown will be different than if they select Sales.

I am able to do this with an active form if there is only data on the right of the dimension, but am not able to do so with data on both sides. From what I understand, I could suppress zeros in Excel with a macro, but since this is going to be a websheet I cannot use macros.

To clarify, there are really two issues here:

• (1) Dynamically pulling a dimension (without an active form) so that any new elements appear
  (2) Suppressing Zeros

Any information on either is appreciated, but I need a solution that will work for both together.

I have attached an image of the layout needed.

I have a fair amount of experience with TM1, but again I am new to Perpectives.

Any help would be greatly appreciated, thanks!

[declanr]

You can still show data to the left of the active form.
Just insert columns and populate the top row with formulas (DBRW etc.) and then rebuild and they will be there... you may need to reference a separate view statement instead of the active form view though.

[tomok]

I'm assuming what you want is for the accounts to zero-suppress so that it only shows those accounts when the selected Department has data in either the month or YTD for either Actual or Budget, correct? The issue is not a left or right thing. An Active Form will only function correctly when you don't split the DBRW columns by having a TM1RPTROW column(s) in between. Your layout isn't how it came out of the cube anyway because a view cannot be configured like this. Simply use the view to create the active form then either to the left or right of that add your own columns in the order you want them to appear and point the formulas in those columns to the appropriate columns from the view. Once done hide the columns from the view so that only your new columns are visible. Piece of cake.

[DavidS]

Thanks guys. I have set it up as you guys suggested and it is working great for me. The issue I am coming across now has to do with the formatting. Since the formatting of the active form does not stretch out to where all of the data and accounts are once inserting the cells, they are not formatted dynamically based on the format area at the top. Since the position of the accounts will be moving based on department, project, etc. I cannot just format the cells statically. Is there any information on how this formatting is applied from the format area, and how I can expand on that?

[declanr]

The formatting area is defined by a named range so you can just change the named range to include extra columns. Then within the formatting area you end up with you can do pretty much anything with conditional formatting and a bit of imagination.