Pivot a List to a Crosstab

[Cognos_Jan2017]

On a List, using a timestamp and number of month, we were able to utilize code to calculate
values for Current and Previous Fiscal Years.

We placed them in a List, and learned that while the values are correct, we cannot get
One Row for each Business Group to display FY17 and FY16 UNLESS there are 2 rows
in the List for each Fiscal Year.

Just converted that List to a Crosstab which presented a new question ...

There are 4 measures, from the Crosstab, that did pivot into a Crosstab.
We have 2 query calculations needed to be displayed in the Crosstab.

How does one drag into those two query calculations (were written as Data Items)
into the Crosstab Measures are all seen as a flashing black area.  Note:  An
attempt to do that does display in the upper left corner of the Crosstab.

TIA, Bob

[tjohnson3050]

When you want two numeric measures in a cross tab, you don't place them in the upper left corner, you drag them (nest) under your column data items.  (or to the right of the data items in your rows).

[Cognos_Jan2017]

Thank you tjohnson3050.

I took the List and pivoted it to a Crosstab.

Cognos automatically placed the 4 Measures in the Crosstab, but
the 2 Calculated Data Items stayed in the rows of the new Crosstab.

Trying to drag those 2 calculated Data Items into the Crosstab "moves" them
(2nd overwrites the 1st) to the upper left corner which I know is bad.

Trying to Nest them under the column data items doesn't keep the
calculations displayed next to the appropriate Measures.

How can I ensure something like ...
Measure1   Measure2  Calc1  Measure3   Measure4  Calc2
are next to each other in the Crosstab Columns?

[tjohnson3050]

Couple things - I didn't realize you already had measures in the columns (didn't read closely enough).  Take the data items and drag them to the right of the existing column measures.  Next, make sure you have the aggregate and roll up aggregate properties set on the two data items (make sure they are set to something beside none, or automatic).

[Cognos_Jan2017]

 Thank you tjohnson3050.

I have read previously (non-Cognoise) how important it is to set aggregate properties.  So far, setting as Total has worked.

Will do at work tomorrow am, and reply here how it works.

[Cognos_Jan2017]

Thank you tjohnson3050

Couple things - I didn't realize you already had measures in the columns (didn't read closely enough).  Take the data items and drag them to the right of the existing column measures.  Next, make sure you have the aggregate and roll up aggregate properties set on the two data items (make sure they are set to something beside none, or automatic).

Trying to drag one of them to the right of the existing column measures "forces" it to the "upper left corner" of the Crosstab.

When trying to drag into the existing measure, the "block of measures" blinks all black.

[Cognos_Jan2017]

I have been able to drag in the 4 Measures and the 2 calculated Data Items into the Crosstab.

However, how do I "move" the order of the Columns?  Trying to move by dragging isn't working.

TIA, Bob

[Cognos_Jan2017]

I have been able to drag in the 4 Measures and the 2 calculated Data Items into the Crosstab.

However, how do I "move" the order of the Columns?  Trying to move by dragging isn't working.

Of the two calculated Data Items, even though they are supposed to be
two separate Data Items, trying to assign the Correct Data Item Name
in their separate Properties sheets makes both Data Items calculations the same.

Is there a way to ensure each Data Item is assigned in the Crosstab?

TIA, Bob

[tjohnson3050]

One thing that may be throwing you, if you don't have the aggregate property set correctly on the data item, it will look like the data item is displayed twice.  This is to let you know it will create a separate item for each value in the data, instead of aggregating that item to the appropriate level based on the other items on the cross tab edges.

[Cognos_Jan2017]

Learned that setting the 2 aggregate properties as ...
Calculated
Default
... gives the correct calculations.

Now if I can separate the two "conjoined" calculated Data Items, and see if columns can be ordered.  If so, my Boss (and I) will be VERY happy.

Do you know of a good source to learn all the nuts-and-bolts of a Crosstab?

Interesting if I check the Query Expression of each Column ... while the correct values appear (we know from previous data presentation in a List), all the Query Expressions are of one of the calculated Data Items.

[Cognos_Jan2017]

tjohnson3050 ...

One thing that may be throwing you, if you don't have the aggregate property set correctly on the data item, it will look like the data item is displayed twice.  This is to let you know it will create a separate item for each value in the data, instead of aggregating that item to the appropriate level based on the other items on the cross tab edges.

This is fairly long, but we stumbled onto a solution.  With your experience, we welcome your thoughts on this.

First ...
Our First (Top) Crosstab ... these columns are all Correct ...
Hours    = a measure
Restricted Days    = a measure
DAW Cases    = a measure
DAW Incident Rate    = a calculated Data Item
DAW    = a measure
... the last Column of "DAW Severity Rate" (a calculated Data Item)
displays data for 'DAW Incident Rate'.  We are unable to separate that
from a "conjoined" 'DAW Incident Rate'.  Changing one changes the other.

Of the 6 Column Headers, checking the "Query Expression" ALL display
([IIMS - Business Layer].[Inj/Ill No. Of Lost Time Cases]
*200000)/[IIMS - Business Layer].[Employee and Supervised Contractor Hours]
As "DAW Incident Rate" ALTHOUGH ...
Hours
Restricted Days
DAW Cases
DAW Incident Rate
DAW ..... ALL display the correct data.

Second ...
We added a second (bottom) the first one,
and used the same Query.  In that Crosstab, we
added the Business Group as a Row, and dragged
'DAW Severity Rate' into the Crosstab.  We set
"Calculated" as Detail Aggregation, and "Default"
as Summary Aggregation.

After doing that, the second Crosstab displayed the
Correct 'DAW Severity Rate' AND ALSO changed the
incorrect value in the first (top) Crosstab replacing them
with the Correct values.

We will learn the new Analytics Dashboard, upload an Excel
file and drag in only the first (top) Crosstab.

IF we keep the Report in the old Cognos 10.2.2 Workspace
format, we will see if the second Crosstab (while still there)
can be hidden.

tjohnson3050 ... at your convenience, those this all make sense?

TIA, Bob