Skip to Content
0

Crosstab Calculated Member - Show row even if no values

Nov 02, 2016 at 05:23 PM

90

avatar image

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!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Abhilash Kumar
Nov 02, 2016 at 06:24 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

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!

0

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.

0
Gavin Ward Nov 02, 2016 at 06:48 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded