Dynamic captions

Hi all, 

I have three calculated columns in a table that I would like to name dynamically. Each of the column corresponds to a specific month of the quarter. The calculated fields are derived using data from a specific quarter.

Using the dynamic captions editor I have the option to select month from our calendar dimension. However, I only have the options choose between first/last/all. First gives January, last March, how do I get February for the second column?

Any alternative suggestions on how to do this, short of manually typing the names?






  • Hi Kenneth

    I gave it a try :-)

    Here's a setup similar to your - except I added an extra element to the user dimension:

    Now I add the calculations as measures (not calculated columns). This will initially look chaotic because there will be calculation in every month - like shown below:

    Now working with properties, visibility I will hide a range of these calculations:

    Month1 (from 1 from the first - and click Define range end);

    to 6 from the first and click apply:

    One more range needs to be hidden - so click hide a range again - this time from 8 to 9 from the first

    When you're done you should only have the 7th occurence of the caluculation left - it's shown like this in the dialogye:

    2nd calculation should be hidden like this:

    And last one like this:

    That leaves me with this crosstab:

    Now I just have to hide my initial measure with this setup:

    With a final touch by setting up dynamic captions as space for the calculations - my final crosstab will look like this:

    You should of course name the 3rd element of the user dimension better (and also the calculations) - but with a lot of hiding - the calculations are now shown under the months where they belong.

    Hope it makes sense



  • If it is similar to your screenshot, you can try something like:

    if allcount(d1,d1:0,m1)= 1 then a else
    if allcount(d1,d1:0,m1)= 2 then b else
    if allcount(d1,d1:0,m1)= 3 then c else
    if allcount(d1,d1:0,m1)= 4 then d else
    if allcount(d1,d1:0,m1)= 5 then e else
    if allcount(d1,d1:0,m1)= 6 then f else
    if allcount(d1,d1:0,m1)= 7 then g else
    if allcount(d1,d1:0,m1)= 8 then h else
    if allcount(d1,d1:0,m1)= 9 then i else
    if allcount(d1,d1:0,m1)= 10 then j else
    if allcount(d1,d1:0,m1)= 11 then k else l

    Of course replace the letters with your expressions. 

    For this to be a robust solution - you must be sure that there will always be 12 rows in that particular sequence.

    Will that work for you?


  • Hi Kenneth

    Could you perhaps thrown in a screenshot (with hidden numbers) to explain exactly what you want to do?
    I think there could be a solution involving a user dimension - but I would need to understand the details a little better to help out.


  • Hi Niels

    Hopefully the screenshot makes sense.


    It is the "måned 1, 2 & 3" values im trying to replace with actual month values. The remaining naming, "2020 - 4. kvartal" is already done using dynamic captions.


  • Hi Niels

    It makes sense :)

    Your solution works just not for my specific case, at least not yet.

    The reason why I went with calculated fields instead of a calculated member, is that I haven't quite found a way to make a calculated member do what I need it to do. 

    I essence this is what I am trying to build (need to):

    As far as I can tell, I cannot make a calculated member differentiate the calculation based of value in UD2 or by line. Is that possible? in that case your solution would work perfectly.



  • Hi Niels

    Based on some initial testing, it should. The report has 60 or so lines. Writing that nested if is going to take ages. I hope there is not a limit on the length of a calculation expression...

    Thank you for your brilliant suggestions :-)



  • ok - one improvement is possible - if (like in your screenshot) there are 6 calculations repeated, you can use the mod function to repeat the same behaviour every 6th time.


    The allcount part counts the rows 1.2.3..... - however because the allcount is used in a mod, every result is divide by 6 and only the remainder is kept.

    1/6 - result 0 remainder 1
    2/6 - result 0 remainder 2
    3/6 - result 0 remainder 3
    4/6 - result 0 remainder 4
    5/6 - result 0 remainder 5
    6/6 - result 1 remainder 0
    7/6 - result 1 remainder 1
    8/6 - result 1 remainder 2
    9/6 - result 1 remainder 3
    10/6 - result 1 remainder 4
    11/6 - result 1 remainder 5
    12/6 - result  2 remainder 0

    So by using the mod in your if sentence, you can limit the outcomes to 6.

    if mod(allcount(d1,d1:0,m1);6)=1 then a else 
    if mod(allcount(d1,d1:0,m1);6)=2 then b else 
    if mod(allcount(d1,d1:0,m1);6)=3 then c else 
    if mod(allcount(d1,d1:0,m1);6)=4 then d else 
    if mod(allcount(d1,d1:0,m1);6)=5 then e else f



Please sign in to leave a comment.

Didn't find what you were looking for?

New post