Dataview formatting with Multiple cubes

Previous Member

Hi, I have a complex/tricky requirement and need help on how I can achieve the required output.

 

We need to have a data view similar as represented in the screenshot below.

Currently I have 4 cubes as shown above, data is being updated through Data reader or from other source cubes through data flow. For Cube 4, I get actual data as of prior year (in above e.g. till 2018) and 2019 onwards is my planned data.

My requirement is that in data view, Planned CY and 2019 needs to be highlighted as both are my current year planned data.

Also I would need 2019 column to have a drill down to screen (which will be detailed monthly data entry screen), while 2020 onwards to drill to a different screen (yearly at higher level data entry screen).

I tried having three different cubes to show cube 4 data, i.e. one to show actuals another for Current year planned and another for future planned. But in this case I need to uncheck show all in my column axes which results in my future planned cubes with no data in certain year to be hidden which is not right as I would like to know and may be update if any year is not planned and has zero value.

 

Thanks

Apurv

Brendan Broughton

Hey Apurv Pednekar

 

Regarding your cube 4 and the need to uncheck 'show all', I would look to use a mapping cube(s), dimensioned by FY, and then a multiplication algorithm on the 'source' cube, and show the algorithm rather than source cube.  That will let you control which years to show with SHOW ALL still checked.

 

For example, if you had 3 mapping cubes (PY, CY, FY) with a '1' against the relevant FY's in the relevant cubes you could use a single sumproduct type algorithm to multiply and combine your CY/PY/FY into a single algorithm which should allow you to keep 'show all' checked so that empty/blank columns still show.  Algorithm = PYMap*Actual + CYMap*planned + FYMap*planned.  That could obviously be either done as an algorithm on screen on dataflown to a separate cube which is then displayed, which ever is most appropriate for the case.

 

You could also possible use the CY cube as the basis for an alert to highlight the CY column. Although you won't be able to highlight the heading within the dataview itsefl.  We've previously overlaid dataviews with one set to 'transparent' when we've needed to do formatting that wasn't possible within a single dataview

 

Those mapping cubes could also then be used by a drill down procedure to determine which screen to go to.

 

Hope that help and gives some other options to explore.