Display data item in summary row of Cross Tab reports.

[vincydza@gmail.com]

Hi Cognos Guru's,

I have created below cross tab report in Report Studio and  its based on an DMR cube. The format of the report is as follows.

Revenue                                                         2014
Salesman Name |  Product Name |     
Adrian                      Shoes                        1,000.00
                      Shirts                        2,000.00    
              Total                                         3,000.00   

Revenue is the default measure of the report.

The requirement is to add the SalesMan name to the 'Total' row –   i.e.  'Total – Adrian    3,000.00
The dataitem is automatically suffixed  to Total column in List report. Can this be achieved in Cross tab report?             

Regards

Vince

[MFGF]

Hi Cognos Guru's,

I have created below cross tab report in Report Studio and  its based on an DMR cube. The format of the report is as follows.

Revenue                                                         2014
Salesman Name |  Product Name |     
Adrian                      Shoes                        1,000.00
                      Shirts                        2,000.00    
              Total                                         3,000.00   

Revenue is the default measure of the report.

The requirement is to add the SalesMan name to the 'Total' row –   i.e.  'Total – Adrian    3,000.00
The dataitem is automatically suffixed  to Total column in List report. Can this be achieved in Cross tab report?             

Regards

Vince

Yes - quite easily. You need to locate the lock/unlock button on the toolbar (looks like a padlock) and click it so that the structure is unlocked (the padlock appears open). Drag a text item from the toolbox into your group footer after the "Total" text and enter a space, a dash and a space (ie " - ") as the text. Then go to the Data Items tab and drag the SalesMan Name item into the footer after the text item.

Press the padlock button again to relock the structure. Run the report. Smile and enjoy your success :)

MF.

[vincydza@gmail.com]

Hi MF,

Tried that and it didn't work. What could be the reason.?

Regards

Vince

[MFGF]

Hi MF,

Tried that and it didn't work. What could be the reason.?

Regards

Vince

What does "it didn't work" mean? Can you explain?

MF.

[vincydza@gmail.com]

Hi MF,

Followed your step by step instructions and when I executed the report the Sales Man name was not appearing in the Total column.  Looks like the sales man name was replaced with NULL value on that row.

Please advise.

Vince

[MDXpressor]

Hi Vince,
I use crosstab space with fact cells to create total lines.  I then set it's source type to "Data Item Value" and the set the Data Item Value to the column I'm trying to total.

So, in your case

• drop a Crosstab Space (with fact cells) to the right of Salesman, and below your Product Name.  It will be your total row.
• select the crosstab space
• set the Source Type property to Data Item Value
• set the Data Item Value property to Salesman

[MFGF]

Hi Vince,
I use crosstab space with fact cells to create total lines.  I then set it's source type to "Data Item Value" and the set the Data Item Value to the column I'm trying to total.

So, in your case

• drop a Crosstab Space (with fact cells) to the left of Salesman, and below your Product Name.  It will be your total row.
• select the crosstab space
• set the Source Type property to Data Item Value
• set the Data Item Value property to Salesman

Yowzer!! It's great to see you back on Cognoise, my friend!! You just made my day! :)

MF.

[MDXpressor]

It's a slow day.  ;)

I came on looking for some answers for a different topic and couldn't find them...  Alas I created my own...  Then I decided to answer some fan mail.  lol

Good to see you again MF.

[vincydza@gmail.com]

Hi MDXpressor,

Sweet !!!!, your solution worked.  Thanks a ton

Vince

[MFGF]

It's a slow day.  ;)

I came on looking for some answers for a different topic and couldn't find them...  Alas I created my own...  Then I decided to answer some fan mail.  lol

Good to see you again MF.

Made me smile to see you here - welcome back! :)

[JuanGonzalezT]

Yes - quite easily. You need to locate the lock/unlock button on the toolbar (looks like a padlock) and click it so that the structure is unlocked (the padlock appears open). Drag a text item from the toolbox into your group footer after the "Total" text and enter a space, a dash and a space (ie " - ") as the text. Then go to the Data Items tab and drag the SalesMan Name item into the footer after the text item.

Press the padlock button again to relock the structure. Run the report. Smile and enjoy your success :)

MF.

It worked like a charm in cognos 11, thanks!

[MFGF]

It worked like a charm in cognos 11, thanks!

Glad it helped! :)

MF.