Calculated Members based on data not in cross tab?

[julie_jamieson2]

Hi,

I am doing some financial reporting for a customer.

They want a P & L showing the actual values for each month, and a grand total showing the total for the year.

After the grand total, they want to show the annual budget, and the Last Year values.

Can I draw the values into a calculated member with out having to add the monthly columns, then make them white & as narrow as possible, leading to a report that goes over two pages?

[abhilash_kumar · Accepted solution]

Hi Julie,

You can using an array and a 'display string formula'.

Here's the idea:

1) Create an array variable that inserts the row value as a delimited string along with this summary value. e.g:

RowValue1 > 160

'>' is the delimiter

2) Insert a calculated member in the crosstab

3) Use a display string formula to compare each Crosstab row with the array values using the instr function. If a match is found, extract the value

-Abhilash

[julie_jamieson2 · Accepted solution]

Hi Abhilash,

I am working on this but having some issues. Is there a way to insert the calculated members after the Grand Total, so I can show Total This Year, Total Last Year then Total Budget after each other?

If I insert all 3 as calculated members then I finish up with blank cells when I add my display strings and would have to finish up using display strings to calculate all values which makes my report really slow, as I have a crosstab in a group level to display results by 30 branches...

[abhilash_kumar · Accepted solution]

Hi Julie,

The only to way to insert a calculated member 'after' the Total column is to:

1) Disable the Total column

2) Insert the Total as a calculated member

This way you can control which calculated member column displays where.

And yes, you'd need displays strings for all columns.

-Abhilash