Skip to Content
0

Adding a calculated member in a cross tab report

Feb 02 at 02:56 PM

63

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Abhilash Kumar
Feb 05 at 11:22 AM
0

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

Show 12 Share
10 |10000 characters needed characters left characters exceeded

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,

0

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,

0

Hi Jerusha,

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

-Abhilash

0

Hi Abhilash

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

Jerusha

0

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

0

Hi Abhilash

No problem.
this is the link:

<Deleted Link>

Thank you

0

The report doesn't have saved data.

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

-Abhilash

0

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

0

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

0

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.

0

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.

0

Hi

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

0
Abhilash Kumar
Feb 02 at 03:22 PM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Abhilash.

Thanks so much..That worked (one of the simplest solutions ever :D ).
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

0