Crosstab Query to show null values. 1

[Swathi37]

Hi,

I have a question. I have crosstab query that looks something like this

Service E I O D T
ABC 45 8 8 6 9
PQR 34 3 2 3
XYZ 12 2 3 4


Services are ABC, PQR and XYZ
InternalTest = E,I,O,D,T
Cell Values are = Count of Customer IDs for the criteria RegID is not missing (RegID is a column that has a value and also blank)

My question is

Sometimes there are no customers with InternalTest = D - then the column for D does not show up in the crosstab query. How do I get the column on the crosstab even if there are no values for that. Something like this
i.e though I dont have customers with InternalTest=D for that particular week I need show 0 under that column.

Service E I O D T
ABC 45 8 8 0 9
PQR 34 3 2 0 3
XYZ 12 2 0 4

I don't want to use a hotfile in this case. Can this be done through calculation?

I appreciate your help.

 

[nagrajm]

A crosstab template is not a solution. Do a simple list report with the following columns;

1) Service

2) E = Total ( if ( InternalTest = "E" and CustomerID is not missing ) then ( 1 ) else ( 0 ) )

3) I = Total ( if ( InternalTest = "I" and CustomerID is not missing ) then ( 1 ) else ( 0 ) )

4) O = Total ( if ( InternalTest = "O" and CustomerID is not missing ) then ( 1 ) else ( 0 ) )

5) D = Total ( if ( InternalTest = "D" and CustomerID is not missing ) then ( 1 ) else ( 0 ) )

6) T = Total ( if ( InternalTest = "T" and CustomerID is not missing ) then ( 1 ) else ( 0 ) )

Enter a filter condition that says "RegID is not missing".

Group the report on Services.

Strictly speaking "CustomerID is not missing" is just a dummy statement in the above calculation to force the query to bring in the table containing CustomerID. I only hope that the concerned tables do not return multiple instances of CustomerIDs.

You will be surprised to see that this query produces a crosstab report with zeros for entire blank columns.

Hope this helps!!

 

[Swathi37]

Hi Nagraj

Thank you so much for your response. Actually E,I,O,D,T belong to one column TestInternal. How can I split it up into so many calculation. I am not clear on this. Could you please give me the steps. Thank you so much.

Swathi

 

[nagrajm]

Apart from 1 column for "Service" which is a catalog column, you will have to create 5 separate "calculated columns" in the "Simple List Report" and name them "E", "I", "O", "D", "T".

In the "Calculation Definition" dialog box of the column that you would name "E", insert the following calculation

Total ( if ( InternalTest = "E" and CustomerID is not missing ) then ( 1 ) else ( 0 ) )

Do likewise for all other calculated columns "I", "O", "D" and "T".

If InternalTest is likely to hold other values apart from E, I, O, D, T, (say X and Y) then you will have to create calculated columns for X and Y as well.

Do not forget to group the report on "Service".

I hope I've been clear enough. We are using the likely values from InternalTest itself as "Column Names" to accomplish this report. Do it practically as I have spelled above and you will understand.

Let me know.

 

[Swathi37]

Hi Nagraj,

Thanks a lot. It works very well. But I have a question, this solution is great if it is grouped by just one column.
How to do the same if I have something like this. i.e grouping 3 columns. Is this possible?


Service Decision Status E I O D T
ABC Edit Confirm 45 8 8 6 9
Pay Rejected 0 0 0 0 0
PQR Edit Confirm 34 3 2 0 3
Pay Rejected 0 0 0 1 0
XYZ Edit Confirm 12 2 3 4
Pay Rejected 0 0 0 0 0

In this example... though
ABC Pay Rejected has null values it should still show up as zero.

Please let me know
Thank you so much once again for all your help.

Swathi

 

[nagrajm]

The method will certainly work for multiple groupings as well. Try it out!!!!

 

[Swathi37]

Hi Nagraj,

I tried it out but when it comes to rows it doesnot show all the rows if there are no values for that particular Service, Decision and Status. Please help.
Thank you so much for all your help.

 

[nagrajm]

You are right. Rows with all Zeros will not show up. What you ask for could be accomplished with a Cube though. Would you be interested? I am not sure if it can be done in Impromptu. I've got to think it over.

 

[Swathi37]

Hi Nagraj,

Thank you so much for your response. Infact I use only Impromptu. I dont have powerplay. Well please let me know if I can do it in Impromptu. I am happy to know if it is possible to do it using a cube. Thank you so much once again.
Swathi

 

[shwetabh]

What if the number of columns to be displayed is not in my control, will your list and calculation approach work?