Returning values not pulled by the Query in a report

[saran26]

Hi All,

We have query which is pulling the state field, which has values like open, assign, closed and resolved.

After constructing a Cross tab, I am seeing there are 5 in open state , 2 in assign state and 3 in resolved state. with a total of 10. But in my report it is not showing that there are 0 in resolved state. They query is not returning any thing in resolved state because actually there is none, But I just want to display in the report with a values 0 against it, how to do it.

Please help me out.

Thanks
Saran

 

[rosemaryl]

Dear Saran26,

One way you could solve this is with a manual crosstab, where you create the columns yourself. You would create a formula for each possible state. With your example data, you would create 4 formulas.

//Example formula
If {Table.State} = 'Open'
then 1 else 0
//end example

Place each formula in the detail section of your report, under an appropriately named column header.

Then group your report by whatever you want to see at the row level and insert a summary on each "column" formula. If the sum is 0 for a column, it will show 0. You should hide the detail section.

The cons to this approach is that if a new state is added, you must modify the report.

Regards,
ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbass]

An inserted crosstab will not return data that isn't there, so you will have to create a manual crosstab. Insert a group on your row field, and then create conditional formulas for your column field. If you column field is {table.state}, then a conditional formula would look like this:

if {table.state} = "Resolved" then 1

Then insert a sum (NOT a count) on this at the group level, and then suppress the detail section. This assumes you do not have row inflation. If you do, you would have to use a set of running totals instead of conditional formulas.

-LB