How to suppress NULL value in a crosstab 1

[tctran]

Hello everyone,

I have a cross tab that I would like to exclude the null value of a particular field. However, I don't want to exclude the null value from a report. I had the group sort to only display the top 10 medications dispensed in the crosstab but the "null" value is displayed as the top of the list and I'd like to suppress it. I've tried many different methods but can't seem to figure this out. I'd really appreciate any advice anyone can provide.

Thank you in advance for the assistance!

Best regards,
TC

 

[pmax9999]

One approach would be to put the cross tab in a Sub-Report that replicates the Record Selection of the main report, but with the additional code to remove the Nulls.

This is not efficient as it requires 2 passes of the database, but it is relatively quick and simple to achieve the result you are looking for.

An alternative, depending on the structure of the report, might be to use groups and Formulas/Running Totals/Summaries to make it look like a cross tab (sometimes referred to as a "mock-tab"), and then use conditional suppression (and/or Group Selection formula) to exclude the Nulls. Certainly more efficient, but could add significant overhead to the development process and may not be possible depending on the nature/look/purpose of the report.

Hope this helps.

Cheers
Pete

 

[tctran]

Pete,

Thank you for the tip! I thought there may be an easier way to exclude the null value from the crosstab other than the first approach you've recommended. For now, it's working perfectly with sub-report method. Will try to venture the second approach when I have some free time to play around with the report.

Thank you so much for your time and the advice. I really appreciate you!

Best regards,
TC