Crosstab Calculated Member - Show row even if no values

[cwarner]

For a monthly Crystal Report which contains a crosstab, I need to include rows of charge amounts for every doctor, even if their charges = $0.00.

Crosstabs normally don't show any rows, unless there is a value, so some doctors are left off the report for that month.

To compensate, I tried adding a calculated member for each doctor's name with a value of $0.00. This works great if, indeed, they had $0 charges. However, if they actually had charges, then the Crosstab generates 2 rows: 1 row of $0 (calculated member) and 1 row showing an actual value.

Is there another way I can design this report, so I don't have to edit the exported report every month to add or subtract rows that should or should not be there?

Thanks for any help!

[abhilash_kumar · Accepted solution]

Hi Cheryl,

Use a SQL Query as the report's datasource.

Create a static temp table that lists ALL doctors and LEFT JOIN TO the existing table on this doctor field.

Then, use the doctor name field from the static temp table in the Crosstab row.

IF you've used the Doctor Name field anywhere else in the report - selection formula etc, make sure it is sourced from the temp table.

-Abhilash

[cwarner · Accepted solution]

Thanks Abhilash.

I am still not getting it to work, although I was struggling with creating a SQL Query.

I emailed you a sample report, if you would be willing to take a look at it.

Thanks again!

[cwarner · Accepted solution]

Thanks Abhilash, your selection formula change worked!

Appreciate your help!

Hi Cheryl,

Could you modify the selection formula to:

(isnull({vwGenProdAnalysis.Actual_Dr_Name}) OR {vwGenProdAnalysis.Actual_Dr_Name} = '')

OR

{vwGenProdAnalysis.Service_Date_From} > DateTime (2016, 10, 10, 00, 00, 00)

Let me know if this worked.

[Former Member]

Create an additional query in the report and just add the 'Doctors' dimension in the report objects. Refresh report, merge both 'Doctor' fields at report level. Pull in merged dimension in crosstab. Basically, in the additional query, your telling the report to return all 'Doctor' values from the DIM table in the universe.

Kind Regards

Wardie