Skip to Content

Crosstab Calculated Member - Show row even if no values

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!

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 02, 2016 at 06:24 PM

    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.


    Add comment
    10|10000 characters needed characters exceeded

    • 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} = '')


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

      Let me know if this worked.

  • avatar image
    Former Member
    Nov 02, 2016 at 06:48 PM

    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


    Add comment
    10|10000 characters needed characters exceeded