The column titles of our cross tab are simply the names that we chose for the User dimension members.
From Smartpad Properties we can make these titles dynamic, so that they will show the actual months and period represented by the individual columns.
We want to apply dynamic captions for the columns of the Period Comparison.
Initially you will see a small editable field containing {cellvalue}. This variable, if kept, will insert the original title of the comparison element as part of the column titles. Often you will want to delete {cellvalue} and instead create a new title that is based completely on dynamic content.
Having deleted {cellvalue}, we can now start building up the dynamic caption. We do this by selecting dynamic references from the Time dimension. From the hierarchy in the Time dimension we select Month and Year.
The selections are inserted as {Month:first} and {Year:first}. Also, remember to add a space between the two references.
Important: When building the dynamic caption it is important to add references from the same dimension (same attribute or same hierarchy) as the one that were used for creating the dynamic criteria.
When you click ”Apply”, you will see the dynamic captions in the crosstab. Furthermore, you can see that all four columns are now using the same common syntax. This is why we see “January” in the last two columns – because the current syntax is relatively simple: {Month:first} {Year:first}.
We will need to modify the dynamic captions for the last two columns to display not only in which month they are stating, but also in which month they are ending. To edit the dynamic caption for a single column, you must right-click the column header and the select Table / Dynamic captions / Edit…
When we insert Month and Year again as dynamic references, we must this time change the reference type to ”Last”.
When this has been done for the last two columns, our crosstab, with dynamic captions, should look something like this:
Comments
Hi there,
I have an problem doing when more than 2 diferent years are chosen.
For example, I have monthly sales for 4 years. where the colums are years and the months are lines.
Then I do 4 more colums with calculations to get the cummulated sales in each month.
When i try to do dynamic captions for those colums to be shown the year that they refer I have no problem with the first year ({year:first}) and the last({year:last}). But i cant do it for the middle years.
I was wondering if i can input calculations on those formulas or an alternative way to show the correct year?
Thank you
Hi Carlos,
Instead of doing 4 calculated columns, you might try to do it as one calculation: A calculation "as a new measure". The syntax should now probably be changed to something like:
In this way, I think there is a better chance of getting the dynamic column headers correct.
BR / Ole
Thanks! works perfect
For whatever reason I have an installation in German.
The caption is set to
{Monat:first} {Jahr:first} - {Datum:last}31st of December, 2023 was a Sunday and no turnover for this day.
The Caption for the column says
How can I change the caption to tell about the filter range instead of the data range?
Many thanks in andvance
Jimmy
Hi Jimmy,
Unfortunately, the dynamic captions can only show the data range.
You may try the 'Include empty members' option - available from the crosstab's right click menu (Data).
Please note that an unwanted side effect from the 'Include empty members' is that it will potentially include empty members from all other dimensions in the crosstab as well. Depending on the nature of your crosstab, you may then hide unwanted empty members by use of a visibility agent.
BR / Ole
Hi Ole
Thank you very much for this hint.
Unfortunately, this doesn't add the missing day into the dynamic caption.
I think I'll have to adjust the database to show this date together with a dummy record which does not show or count any turnover...
BR Jimmy
All right Jimmy,
That would have been my next suggestion. :-)
BR / Ole
Please sign in to leave a comment.