List next to Crosstab so metrics from 2 dift queries display side by side?

[psrpsrpsr]

Hello all, I have a requirement to replicate an Excel spreadsheet in 10.2.2, see below.

1.) The primary metric is displayed in a crosstab (blue in attached picture).
2.) There is a summary of the prior 12 months for the metrics contained in the crosstab (orange).
3.) There are two unrelated metrics that are not from the same namespace as the crosstab that are displayed next to the crosstab (green).

Is it possible to display a List next to a crosstab, such that the Attribute values (like 'location name') in the crosstab and table can be sorted to display next to each other?

How would you approach this development?

[psrpsrpsr]

Anyone? Is this achievable in Cognos?

[BigChris]

First things first, are you able to get all of the data onto the same page in your report? Once you've done that it should be possible to manipulate it so that the rows line up...

[psrpsrpsr]

BigChris, yes, I can get all of the data onto one page, but my question is more about WHAT types of containers (Crosstab, List) in which the data should be configured, and HOW they should be configured. Thanks

[hespora]

(assuming all this is relational)

My approach to build all this would be a single crosstab. Basically, you'd have three queries, corresponding to what you grouped as "Crosstab", "Summarized Crosstab Metrics", and "Metrics not contained in crosstab". These would share some common dimensions (otherwise, you wouldn't be able to line them up in XL either), across which you could join the data into a single query, from which you could feed the crosstab.

[psrpsrpsr]

Hi Hespora, the trouble I'm having is combining the columnar values so that they display properly.

For example, I have a 'YearMonth' field that should display the month rate for a certain KPI, and then fields that are summarized. (KPI1-3, Unrelated KPI4). Dragging these fields next to the YearMonth is yielding wacky results.

How can I position the fields like this? (there does not have to be a merged header above them, that's just there to show how they relate.)

+-------+--------+--------+--------+------+------+------+----------------+
|       | Detail                   | LTM                |                |
+-------+--------+--------+--------+------+------+------+----------------+
| LocID | 201709 | 201710 | 201711 | KPI1 | KPI2 | KPI3 | Unrelated KPI4 |
+-------+--------+--------+--------+------+------+------+----------------+
| Loc1  | 1.2%   | 1.3%   | 1.4%   | 20%  | 60%  | 70%  | 75%            |
+-------+--------+--------+--------+------+------+------+----------------+
| Loc2  | 1.3%   | 1.4%   | 1.5%   | 30%  | 70%  | 80%  | 85%            |
+-------+--------+--------+--------+------+------+------+----------------+
| Loc3  | 1.4%   | 1.5%   | 1.6%   | 40%  | 80%  | 90%  | 95%            |
+-------+--------+--------+--------+------+------+------+----------------+

[hespora]

Hi Psr,


please consider the image attached, where I combined 3 screenshots.

The first one is a plain crosstab. I'm starting out with a list containing the dimensions [Material Number] and [Fiscal Period], and the measures [Net Sales LC] and [Budget Revenue LC]. Select [Fiscal Period] and click "Pivot list to crosstab", and that first screengrab is what you're getting.
Now pick up [Budget Revenue LC] and Drag and Drop it where indicated in the second screengrab. The result would be the third screengrab. In that one, the measure [Net Sales LC] will be broken down by [Fiscal Period], whereas the measure [Budget Revenue LC] will not be broken down, it will show as a single column.

If I understood your question correctly, that UI part was exactly what you were having trouble with. If I misunderstood, can you please elaborate?

[psrpsrpsr]

Hi Hespora, that helped, thank you! The problem was that the field I was including was a static figure from a base query, with the aggregation set to None (because it was static across the entire time frame). It was displaying the crosstab like this:

+-------+--------+--------+--------+-----+-----+-----+
| LocID | 201709 | 201710 | 201711 | .20 | .30 | .40 |
+-------+--------+--------+--------+-----+-----+-----+
| Loc1  | 1.2%   | 1.3%   | 1.4%   |     |     |     |
+-------+--------+--------+--------+-----+-----+-----+
| Loc2  | 1.3%   | 1.4%   | 1.5%   |     |     |     |
+-------+--------+--------+--------+-----+-----+-----+
| Loc3  | 1.4%   | 1.5%   | 1.6%   |     |     |     |
+-------+--------+--------+--------+-----+-----+-----+

...but by
1.) fixing the measure to be calculated in the final query and
2.) ungrouping the measure from the YearMonth dimension, it is looking 95% better!

Thanks again

[hespora]

wonderful, glad I could help.

So what's missing for the final 5%?