Cross tab report in Impromptu

[arrowhouse]

As a relative newcomer to impromptu can any of you gurus tell me if (& how) it is possible to do a report like this:

[tt] week 1 week 2 week 3[/tt]
[tt]PR Bk 12 34 23[/tt]
[tt] Sh 10 34 24[/tt]
[tt]NPPR Bk 3 4 6[/tt]
[tt] Sh 3 3 6[/tt]

So in the query popup crosstab tab
rows: are Location (PR/NPPR) & the name of measures (Bk, Sh)cells: measures (values of Bk and Sh)
columns: Week

When I apply the cross-tab template to my list report the best I have been able to achieve is

[tt] week 1 week 2 week 3[/tt]
[tt] Bk Sh Bk Sh Bk Sh[/tt]
[tt]PR 12 10 34 34 23 24[/tt]
[tt]NPPR 3 3 4 3 6 6[/tt]

So in the query popup crosstab tab
rows: are Location (PR/NPPR)
cells: measures (values of Bk and Sh)
columns: Week (the names of the measures appear automatically in each column)

I'm hoping this just a setting somewhere...

Hope the layout doesn't get lost in the posting.

Thanks in advance for your advice.

 

[christenhusz]

Hi Arrowhouse,

When you open impromptu jou can start with a crosstab report. Click File-New and you see (if default is installed) 8 templates. You can choose a crosstab template and a grouped crosstab template.

Good luck

Christenhusz

 

[arrowhouse]

Thanks for your advice Christenhusz,

I have tried directly creating a cross tab & grouped cross tab as you describe but neither of these give me the layout I'm after. If you group the Bk or Sh measures you get the report grouped by the values of Bk or Sh. Am I missing out a vital step?

I just want a straight forward cross tab report but the labels for bk and sh to appear in the rows rather than the columns.

The advice I have had from a friendly cognos consultant is to use power play... but if anyone knows how to achieve the above in Impromptu I'd like to know.

Thanks.

 

[griffindm]

arrowhouse,

This report should not be too tough to get to. In your existing report, try to pull the dimension (I know that's a PowerPlay term, but it still applies) represented by the "Bk" and "Sh" data down from it's current position (which should be in the Column area under the Weeks data element. Place it instead in the Row area, below the current dimension there. If you are restarting from a list frame report, the grouping order should be:

Dimension 1 ("PR" and "NPPR&quot
Dimension 2 ("Bk" and "Sh&quot
Dimension 3 ("week1" and "week2&quot

with the aggregated summary measure beneath that.

Then just pull the "week" grouped element to the top of the list frame to convert in to a crosstab.

Hope this helps,

Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[arrowhouse]

Thanks for your suggestion Dave,

The issue I have is that Bk and Sh are the names of the measures in the cells section of the cross-tab. There is no dimension 2 (bk, sh). I do not have anything appearing under the weeks data element in the columns section.

The name/lable of each measure in the cells area of the cross tab automatically appears under the dimensions that are in the columns section of the cross tab. Aparently there is no option to let you say you'd rather have it beside the rows instead.

Cognos have advised me to use PowerPlay instead - but I'd prefer to use impromptu if possible.

I guess one option is to artificially create a dimension 2 wich is a bit of a fiddle, but I'll give it a go. Any other suggestions?

Thanks for your advice,