Chart and DataView with dynamic Offset per Month
Dear Community,
I'm trying to solve a reporting-requirement where a screen contains a pager defined by month and "current period" and the screen objects should dynamically show this current month and and the previous 12 months as a progression.
Here is an example of what i mean:
My first thought was to define a Layout with 13 blocks, setting the period offset function accordingly. But in this case, the description of month axis in charts or colum headers in dataview will not work as needed.
Do you have any ideas on that?
In a second step, the screen will also contain another area which only shows a current month´s KPI - so for a solution which may dynamically select all months via screen selection, im considering using an additional "apply selection (current month) to layout" - procedure.
Answers
-
Hi Florian Lübbing,
to have on one hand a month pager and on the other hand a chart with more than one month shown (e.g. to see the development of the current year), we usually use an artifical month-dimension.
You can use a mapping cube with structure like this
to calculate your new chart cube (same dimensions + "artificial" month). So if you choose e.g. month 5 in the pager, the chart will show you "artificial" month 1-5.
A calculation might look like this:
Cube (A) = Base cube from your report with month dimension
Cube (B) = Calculated.chart cube with same dimensions as A + artificial month
MAP (A) = 1:1 Mapping between real month and artificial month
MAP (B) = as seen for example in the screenshot
- DF: Cube (A)* MAP (A) = Cube (B)
- DF: Cube (B) = TMP Cube w/o month
- DF: TMP Cube w/o month * MAP (B) = Cube (B)
In that case you don't need any calculations within the frontend so your screen performance should not be affected
Hope this gives you some inspiration!
Best regards,
Bettina
4 -
Hi Florian Lübbing, we've achieved similar with a hard select of all time periods on the chart layout. We then bring through a mapping/display cube into the chart layout. This cube just has a 1 entered against the 13 months you wish to display, multiply it by the cube you wish to chart, and then chart the resultant algorithm. You then just need a small procedure to clear and populate the mapping/display cube for the required months. Bettina's solution might be better depending on the size of the cube your displaying and how the screen performs.
1 -
Hi Bettina Clausen,
thanks for sharing your solutions with me! Time now has come to start buildung up the screens.
If I understood you correctly, in each case I need an additional cube for getting the desired result ?!
(Whichs means if our customer wants this style of report as the "standard" view, we kind of "double up" the database (you don´t want to know how many cubes we already have here ...)?
I will try different approaches and will let you know the result.In the meantime, we all may vote up for this one :
best regards
0 -
Hi Florian Lübbing,
yes, you will definitely need at least one additional cube. If I understand Brendan Broughton's solution correctly, one cube for filtering/multiplication is sufficient in his approach. In my example you'll need more cubes (TMP, Mapping, new Targetcube) to achieve a report like mentioned.
So it depends on how many objects and how big your relevant cubes are and how much performance you are willing to sacrifice as Brendan has already mentioned (frontend solution vs pre-calculated solution).
If you have some free cubes left in your database I'd prefer the pre-calculated way
Best regards
Bettina
0 -
Hi Florian Lübbing, Bettina Clausen, Brendan Broughton
One way of achieving this is to use a procedure which dynamically selects the 12 previous months based on the month selected in the pager.
The screen setup below should hopefully explain what I have done:
I have explained further below how the Reset Months and Run report capsule procedures work.
DYNAMICALLY UPDATING MONTHS
For example, if the pager had April-19 selected, the dataview below shows how the preceding 12 months can be computed. The first three blocks are all the same cube dimensioned by two entities - Month and Dummy (single member):
The entity select (Month) can be used in a procedure based on the 12 month offset cube and the selection can be passed to the screen (see procedure below).
Run report procedure
This procedure updates the dummy month cube with a 1 based on the Month pager selection of the screen from where it is run and then computes the preceding 12 months and applies it to the screen:
Procedure Step 7
b - Last Value Function
c - Last Value Function and Period Offset set to 12
Reset Months procedure
When Run report procedure is run, the months pager will have 13 months. If the user now wants to select a month which is not included in the pager, a Reset Months procedure can be used:
I hope this helps.
Regards,
Aamir.
0 -
HI Florian Lübbing, in reading Aamir Ahsan's reply and in re-reading the original post again after a month, depending on how many objects you have that you want to display 1 month and 13 months, you might be better applying the 13 month selection to the screen as Aamir Ahsan mentions, rather than in the algorithm of the Chart object as I mention. You would then use the algorithm on your KPI's to display on the current month, rather than on your objects requiring 13 months.
In this case your 'mapping cube' used in the algorithm would have only the current month populated with a 1 (rather than the 13 months as per my original suggestion).
One benefit is that it would remove the need to put a 'hard select' of months on the dataview as I originally advised, as the 'current month' will always be included in the 13 months applied to the screen selection. However you might need to check the screen performance as it would most likely depend on how many objects of each (1 month vs 13 months) you have.
Cheers
Brendan
0 -
Hi all,
After reading this thread I decided to share my "shorter" solution:
I used a CUBE with a time dimension - month, flagged current month with "1" using data entry and then applied CUBE functions: Last Value, Period offset 11, Cycle 12 (image below). This added "1" in trailing 12 months from current month. Then, added procedure to write this function into another CUBE with month dimension to store the values. This CUBE can then be used for Dynamic screen selection.
Assuming, you have a CUBE with current month flag already, this will require only one extra CUBE, and very simple dataflow procedure to store values in CUBE.
0
