Skip to Content

Calculated Members based on data not in cross tab?

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Oct 23, 2015 at 11:23 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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