Adding a calculated member in a cross tab report

[JerushaLala]

Hi All.

I created a cross-tab report with 2 row fields and date for the columns.
(Cross-tab.png)
I need to add a calculated based on the total of row one.

I right clicked and added a calculated member, but it only does it for the 1st row.

Does this need to be added for each row??

Thank you.
Jerusha

[abhilash_kumar · Accepted solution]

Hi Jerusha,

Right-click one of the summary cells in the newly added Row > Calculated Member > Edit Calculation Formula. Use this code:

GetTotalValueFor("Row 2") * x.xx

Replace "Row 2" with the actual field used as the second row in the crosstab. The field needs to be enclosed in double-quotes just like in the example above.

-Abhilash

[JerushaLala · Accepted solution]

Hi Abhilash,

Thank you very much. It worked..I just need to add an if statement as the percentage for each section is different..But this has helped me alot. Huge help on this forum.

Regards,

[JerushaLala · Accepted solution]

Hi Abhilash

The formula is working, however its pulling the same value from the 1st group down to all the groups. I tried using an if statement. but it doesnt seem to resolve it.
Any ideas on that?
Regards,

[abhilash_kumar · Accepted solution]

Hi Jerusha,

Would you be able to attach the report with saved data or attach it to google drive and send the URL?

-Abhilash

[JerushaLala · Accepted solution]

Hi Abhilash

Thanks for the speedy response.
Could I inbox you the google drive URL?

Jerusha

[abhilash_kumar · Accepted solution]

Hi Jerusha,

The forum rules don't allow sharing e-mail addresses in replies.

You could post the URL to drive here and I'll delete it from the reply once I download.

-Abhilash

[JerushaLala · Accepted solution]

Hi Abhilash

No problem.
this is the link:

<Deleted Link>

Thank you

[abhilash_kumar · Accepted solution]

The report doesn't have saved data.

Please go to File > Check Save data with report > Save.

-Abhilash

[abhilash_kumar · Accepted solution]

It looks like you've added the Calculated Member as a "Row" and not a "Child".

Could you add as a "Child" and try again?

-Abhilash

[abhilash_kumar · Accepted solution]

Jerusha,

The Calculated Member's "Calculation Formula" should be:

If GridLabelAt("JE.UMA-FSBClass", CurrentRowIndex) = "" Then 
GetTotalValueFor("JE.AcctName") * 0.5 else 
GetTotalValueFor("JE.AcctName") * 2

-Abhilash

[JerushaLala · Accepted solution]

Its doing the same thing, except the new "row" is on the top of the group instead the bottom.

Am I on the right track though? In the report there is net premium..that is the total of #Row 2 grouping.
I need a new field to be calculated based on that total.

[JerushaLala · Accepted solution]

Apologies..I did not do the one step (2. Right-click the blank space to the top left of the crosstab > Select Advanced Calculations > Calculated Member > Under "Properties", look for a radio button with the label "Insert Evaluation". Check "After" for this label.)

..But the calculations are doing the same thing.

[JerushaLala · Accepted solution]

Hi

I missed the post with the calculation. I will give that ago. Thank you very much

[abhilash_kumar]

Hi Jerusha,

1. Right-click the new blank row that you inserted > Select Calculated Member > Edit Insertion Formula > Use this code:

GetRowGroupIndexOf(CurrentRowIndex) = 1

2. Right-click the blank space to the top left of the crosstab > Select Advanced Calculations > Calculated Member > Under "Properties", look for a radio button with the label "Insert Evaluation". Check "After" for this label.

-Abhilash

[JerushaLala]

Hi Abhilash.

Thanks so much..That worked (one of the simplest solutions ever 😄 ).
The reason I want to add the calculated member, is to also show a calculation based on the total for that group (highlighted in green). The calculation is the total * x.xx%.

Is this possible through using the calculated member, is the calculated member the right thing to use. I seen some of your previous solutions..But cant seem to get it right.

Thanks again.
Jerusha