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
Please sign in to leave a comment.