Hello, I’m hoping to be able to get some help with calculated fields in Report Studio 8.4.
Basically I need to write some crosstab reports showing profit & loss categories (eg Income, Staff Costs, Expenditure etc) as rows, and departments as schools. I then also need to have various calculated rows showing for example, Staff Costs as a % of Income, which would need to sit between other rows. Here’s a very simplified example:
Rows School A School B etc , there might be 10+ columns like this
------- ------------ ------------
Income 50000 75000
Staff Costs 10000 5000
Staff Costs as % of Income 20.0% 6.67%
Expenditure 30000 25000
I need this in one single crosstab report.
The data items that make up the row headers are in a relational data model and they have a ragged hierarchy so I need to create a defined data item to standardise that hierarchy (eg income might be at level 2 and staff costs might be at level 4).
I want one single visible crosstab report with all the information as above in it. The workaround I have at the moment is to have one crosstab with rows up to the calculated row, one crosstab with the calculated row, another crosstab with rows from the next non calculated row to the next calculated row etc etc and whilst it works ok it is messy in terms of presentation and fiddly to maintain. Changing the published model in any way is not an option, I have to be able to do this with what I have already. I know there must be a way to get all this stuff onto one crosstab report, I just don’t know how to do it! Any help would be really great.
Basically I need to write some crosstab reports showing profit & loss categories (eg Income, Staff Costs, Expenditure etc) as rows, and departments as schools. I then also need to have various calculated rows showing for example, Staff Costs as a % of Income, which would need to sit between other rows. Here’s a very simplified example:
Rows School A School B etc , there might be 10+ columns like this
------- ------------ ------------
Income 50000 75000
Staff Costs 10000 5000
Staff Costs as % of Income 20.0% 6.67%
Expenditure 30000 25000
I need this in one single crosstab report.
The data items that make up the row headers are in a relational data model and they have a ragged hierarchy so I need to create a defined data item to standardise that hierarchy (eg income might be at level 2 and staff costs might be at level 4).
I want one single visible crosstab report with all the information as above in it. The workaround I have at the moment is to have one crosstab with rows up to the calculated row, one crosstab with the calculated row, another crosstab with rows from the next non calculated row to the next calculated row etc etc and whilst it works ok it is messy in terms of presentation and fiddly to maintain. Changing the published model in any way is not an option, I have to be able to do this with what I have already. I know there must be a way to get all this stuff onto one crosstab report, I just don’t know how to do it! Any help would be really great.