Row Total of a Calculated Member

[Former Member]

How should I calculate the row total of a calculated member "Sunday Diff"?

It should just be a sub-total by itself and should not add to the grand total.

[abhilash_kumar · Accepted solution]

Hi Binit,

Here's what you need to do:

1) Right-click the 'Total' Column and select Calculated Member > 'Insert Child'

A blank column with zero values is added to the left of 'Total'

2) Right-click the blank header cell of the new column > Select Calculated Member > Edit Header formula and use this text:

"Total"

3) Right-click one of the zero values on the new column > Calculated Member > Edit Calculation Formula and use this code:

local numbervar i;

for i := 0 to CurrentColumnIndex - 1 do

(

    local numbervar summ := tonumber(summ + gridvalueat(CurrentRowIndex, i , CurrentSummaryIndex))

);

summ

4) You can then right-click the header for 'Total' column to the extreme right > select Column Grand Totals > Check Suppress Grand Totals.

-Abhilash

[Former Member · Accepted solution]

Abhilash -

I am stuck on step 1. Inserting a child does not show a blank column with zeros to the left of 'Total'.

After that when I check > Advanced Calculations > Calculated member.. I see the following    

[abhilash_kumar · Accepted solution]

Sometimes, CR doesn't show the Calculated Member even when it is really there!

As you can see in the 'Advanced Options' screen above, the calculated member column exists.

Would you be able to send the report with saved data so that I can try on my machine?

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash -

I have emailed you the report on your gmail. Let me know if you haven't received it.

Thanks,

Binit

[abhilash_kumar · Accepted solution]

Unfortunately, the calculated member doesn't seem to show up on my machine too.

I don't know why this happens though.

I would request to see if this shows up on his machine.

-Abhilash

[JWiseman · Accepted solution]

i don't get the extra total column either. it works when using the same steps but with a summary, just not with the Total.

[abhilash_kumar · Accepted solution]

Would it be possible for the PG to take a look at this, Jamie?

I keep having this issue quite often (and many users here as well) with absolutely no explanation of why.

-Abhilash

[JWiseman · Accepted solution]

this issue has been sent to Ludek.

-jamie

[abhilash_kumar · Accepted solution]

Thanks, Jamie!

-Abhilash

[patrick_genest · Accepted solution]

Hi Binit,

The reason there is no blank columns added to the cross-tab when inserting a child, it is because your cross-tab is formatted to suppress the subtotal. A child calculated member is added as a subtotal, since it is suppressed, then it does not display on the cross-tab.

To view the column you added, go to the cross-tab expert, and under the tab  "Customize Style", select the second column, and uncheck the Group Options: "Suppress Subtotal"

Once this is done, then the column you added will display on the cross-tab.

Patrick

[Former Member · Accepted solution]

Patrick - Thanks for reviewing. To make it simple, I removed the extra fields to avoid subtotals.

Suppress sub total is now grayed out and unchecked. Note, suppress empty columns and rows have been unchecked.

Here is snapshot without child row and child column.

After adding the child row and child column, here is the calculated members expert box.

The child row was added ("Sun")  but child column before total did not get added. 

So the issue is still there.

On another note, I have found the solution to my requirement as shown below.