Question about calculated fields in rows in Report Studio 8.4

[ekart]

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.

 

[blom0344]

I may be missing the point, but this should be fairly easy with dataitems that store values for each row of the crosstab seperately.
I mean Income is defined through its own dataitem.

You can then nest the measures below one another , which means building a crosstab with just one dimension (School)

Staff Costs as % of Income would then be a calculated item like : ([Staff costs] / [Income]) with aggregate set to calculated and datatype in properties set to percentage.

I'm sure you have other issues, otherwise this would be too simple

Ties Blom

 

[ekart]

I tried to do this, there are 2 issues: 1) every row I define has the row with the row name as defined and fact data, and then a null row name with more fact data. 2) when I create the calculated row and insert into the crosstab and run the report I get the error "The context for the arithmetic operator '/' is invalid. A numeric value is required"

Are these 2 issues related?

 

[blom0344]

Yes, I think so. your model is probably set up to return null values as well. Did you try to use coalesce to return zeroes?

Ties Blom

 

[ekart]

Thanks blom0344, your suggestion got me thinking along the right lines and although I couldn't initially get the query to work with individual rows defined, after a bit of messing about it worked perfectly!