Crosstab - how to add calculated row beneath a total? (e.g. total per location)

[psrpsrpsr]

My reporting requirement is to display metrics by Total and by per-location, like this:

|              | Last 7 Days | Month to Date | Program to Date | % Female | % Male |
|--------------|-------------|---------------|-----------------|----------|--------|
| Total        | 200         | 4,000         | 10,000          | 45%      | 55%    |
| Per Location | 10.2        | 12.5          | 11.4            |          |        | <<<How do you get this?

I'm able to get the 'Total' easily by doing the following:
1.) Create crosstab, Ctrl+click the 5 fields and drag to Columns
2.) Create a Query Calculation called 'Total', which simply has the value of 'Total', and drag to Rows.

I'm not able to get the Per Location calculation. Please advise the best practice to achieve this, thanks!

I have tried:
1.) Dragging a Query Calculation underneath the aforementioned 'Total' row in the crosstab.
2.) Dragging a Crosstab Space with Fact Cells object underneath 'Total'.

In both instances, I can't figure out how to get the desired data to display.

Thanks!

[psrpsrpsr]

Bump!

[rockytopmark]

"Per Location" is a quotient of Total by number of Locations, correct?

So drag a query calculation below "Total" row, and set it's expression to:
[Total] / <number of locations>

[psrpsrpsr]

That solution didn't work:
1.) The metrics from the 'Total' row use aggregate functions. The 'Total' field is just a dummy field with a value of 'Total', for the purposes of labeling that row in the crosstab. So it is not a value that can be calculated on (e.g.) ( can't calculate 'Total' / 2 )
2.) I need there to be blanks in the % Female and % Male columns for the bottom calculation, because that calculation doesn't apply. I unlock the report and click on the text items (which I recall being able to do in C10, but I am in Cognos Analytics), but I'm unable to select the aforementioned text items to delete.

[CognosPaul]

You should still be able to remove text items from intersections in CA. Same procedure as in C10, override the cells or unlock the report.

Is this a relational or a dimensional source?

[rockytopmark]

That solution didn't work:
1.) The metrics from the 'Total' row use aggregate functions. The 'Total' field is just a dummy field with a value of 'Total', for the purposes of labeling that row in the crosstab. So it is not a value that can be calculated on (e.g.) ( can't calculate 'Total' / 2 )

OK, then replace the dummy 'Total' row's node with the "All" member of another dimension (if there is one available) and then do the quotient.  This worked perfectly in my quick test yesterday, so...  And to echo Paul, the ability to define the contents of any intersection of a crosstab persists in Cognos Analytics

[psrpsrpsr]

So I'm getting an error message when I try to reply with an [IMG] tag and explanation. I'll try again tomorrow.

[psrpsrpsr]

test

[psrpsrpsr]

[CognosPaul]

The text items you're clicking on in that image are the default values that appear. You can click on an intersection and set "define contents" to Yes. The text item will disappear.

[psrpsrpsr]

Hi Paul, Define Contents = Yes and then dragging a Query Calculation is not displaying any output:

[psrpsrpsr]

Bump

[CognosPaul]

I'm a little confused here. The red label you wrote is on a crosstab spacer. Intersections are the fact cells.

Set up the crosstab the way you want, with the calculation appearing for male/female. Select the intersection between the total row and the sex node and set defined contents to yes.

[psrpsrpsr]

Sorry I had a brain fart there... in my image where I wrote 'crosstab intersection', that should be 'crosstab space'.

So setting 'Define Contents' = yes on the Male/Female makes sense to not display a Data Item makes sense, but I'm still confused about how I get the per-location figures underneath the Total row. What I tried to show in that last picture is that I tried 2 things that did not work:
1.) Dragging query calculation to the Crosstab Space row
2.) For kicks, nesting a query calculation to the Rows space.

So to clarify: let's say there are 19 locations - I need to show 10,12,11, etc as the per-location calculations underneath the total row.


|              | Last 7 Days | Month to Date | Program to Date | % Female | % Male |
|--------------|-------------|---------------|-----------------|----------|--------|
| Total        | 200         | 4,000         | 10,000          | 45%      | 55%    |
| Per Location | 10.2        | 12.5          | 11.4            |          |        | <<<How do you get this?

Thanks for your help!

[CognosPaul]

can you attach the report xml?

[psrpsrpsr]

I will create a new report in Cognos Analytics from dummy data to avoid security concerns. That may take me a few days to do. When it's complete I'll attach the Excel data and XML.