# 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?

Regards,

Kenneth

1

## Comments

Niels ThomsenHi 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.

br/Niels

Kenneth ThomsenHi 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.

BR/Kenneth

Niels ThomsenHi 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

br/Niels

Kenneth ThomsenHi 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.

BR/Kenneth

Niels ThomsenIf 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?

Kenneth ThomsenHi 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 :-)

BR/Kenneth

Niels Thomsenok - 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.

mod(allcount(d1,d1:0,m1);6)

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.