Impromptu Crosstab Question. 1

[Swathi37]

Hi All,

I have a problem in Impromptu. I have table with 2000 rows. When I group, this data comes upto 300 rows. Is it possible to create a crosstab in a single shot without first creating the list report and then saving it as an .imr file and using this imr file in a crosstab query?
Infact when I tried creating the crosstab query it gives me the total for 2000 rows and not for 300 rows. (though I grouped). I need the crosstab query for 300 rows(for grouped data). Please help me out. Thanks in advance

 

[griffindm]

Swathi,

Something is not right in your report. In order to do a crosstab, every column in your report is either a part of the grouping or a summary aggregate value. It sounds like you have detail in your report that is not part of any grouping.

In your list report, before you create the crosstab, check to see if the checkbox at the top of the Group tab in Report | Query | is checked. Then check the resulting SQL in the Report | Query | Profile by selecting the SQL radio button and look for a group by clause in the statement. If it is not there, you should modify your report so that a true group by expression results. Once you have this you should be able to convert it to a crosstab without problems.

Regards,

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

 

[Swathi37]

Griffin,
Thank you for your reply. I checked with the SQL and the data is grouped. But still I can't figure out the problem. Please help me.
I ran the query in Access. Let me explain this in ACCESS
1) Table consists of 2000 rows
2) I group the data and the output comes to 300.
3) I created the crosstab with the grouped data (i.e 300)and the total count is 300.

If I do not group the data and create a crosstab in Access the output of crosstab comes to 2000.
In the above case the data is grouped twice.

How can I do this process in COGNOS?

 

[griffindm]

Swathi,

If you are getting the group by statement in your SQL and the grouped list report returns 300 rows, the only other place to look is in your definition of the crosstab.

Typically a crosstab report report is grouped on two or more columns. You usually move the lowest level of grouping into the 'X' dimension across the top of the crosstab. Impromptu version 5 and beyond allow nesting of dimensions in both the 'Y' and 'X' axes (yes - that's the plural of axis. I looked it up ).

If you are not already doing so, try migrating the list report by just moving the lowest level of the grouping to the 'X' axis and let me know the results. You will not get 300 rows. When the crosstab is generated it reduces the displayed row count to just the number of distinct groupings for the 'Y' axis.

Regards,

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

 

[Swathi37]

Dave,
Thanks a lot for your response. Please take a look at the sample data and the crosstab I expect. Please explain as to how I can go about this process. Since this is really important to me.
External_Internal_Test Account Number


Test Customer Account Service
Code Number Center
--------------------------------------------
External A0123 123 Dallas
External A0123 123 Dallas
External A0123 123 Dallas
External A0123 567 Denver
External A0123 567 Denver
External B4536 489 Houston
External B4537 489 Houston
External B4538 489 Houston
External B4539 679 Denver
External B9999 489 Houston
External B9999 489 Houston
Internal C2586 777 Houston
Internal C2586 777 Houston
Internal C2586 777 Houston
Internal D6987 888 Dallas
Internal D6987 888 Dallas
Internal D7777 888 Dallas
Internal D7777 888 Dallas
Internal D8888 888 Dallas
Internal D8888 888 Dallas
Test E5826 999 Denver
Test E5826 999 Denver
Test E5826 999 Denver
Test F5555 222 Houston
Test F5555 222 Houston
Test F9854 222 Houston
Test F9859 321 Houston


Want to calculate in Cognos, using one query (cross-tab?), the unique number of Customer Codes and Account Numbers by External_Internal_Test
and Service Center. The results should be as follows:

External
Internal_Test Dallas Houston Denver Total
-----------------------------------------------
External 1 4 2 7
Internal 3 1 4
Test 3 1 4
------------------------------------------------
Total 4 8 3 15

Thanks in advance.

 

[griffindm]

Swathi,

Setup a list report with the following characteristics:

Columns:

External_Internal_Test
Service_Center
Count (Distinct Customer_Code)
Count (Distinct Account_Number)

Check that AutoGrouping is checked. This should give you a 'group by' clause in you SQL (Note: you do NOT have to have a group by clause in order to produce a crosstab, but it is more efficient). The resultant list report should look like:

Test_Type Srvc_Ctr  Cnt(CustCd)  Cnt(AcctNo)

External  Dallas        1             1
External  Denver        2             3
External  Houston       4             1
Internal  Dallas        3             1
Internal  Houston       1             1
Test      Denver        1             1
Test      Houston       3             1

Then save this report first, and then pull the service center column to the top of the list frame until it highlights the horizontal area for the 'X' axis of the Crosstab. This should give you what you are looking for. Go into the Query |Data menu tab and click on the sigma sign at the bottom to get Y dimension sub-totals.

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

 

[Swathi37]

Dave,

Thanks a million. You solved my problem. Actually I was not using distinct function in my query. It works!!!!
Thank you so much for all your help.

Swathi.