asked on

Show empty groups in Crystal Report

I have a Crystal Report that displays by group. That means that the groups that do not display have zero productivity. The report should display every group. I need to see that zero productivity - the report should show the non-productive group with zeros for qty and associated costs. This is a Crystal 8.5 report because it is used in Crystal Enterprise 8.0 web reporting- I know it is old but it works and is licensed. I am reporting on data from an MSSQL 2005 MRP database using a 32 bit ODBC DSN called MAXRM.

TIA
Tim
charge-back-report2.rpt

Mike McCracken

You have to get a record for each group into the record set

One way to do this is to

Create a table in the database that has ALL the groups in it.
Use that table as the master and left join your query to it.
Use the group name from that table as the group field.

If the list of groups is static, It can be done through formulas.
How many groups are we looking at?

mlmcc

Jim Horn

Can't give you a Crystal-specific answer, but it sounds like your source of data needs a JOIN to change to a LEFT JOIN, specifically groups LEFT JOIN productivity, so that the set shows groups that have zero productivity.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gibneyt

ASKER

@mlmcc,

I am not allowed to create tables in the db. I would have to look into the possibility but at this time not capable.

In the Formula Editor for @NAme the Group Name is related to Planner ID with the following list:

if {Part_Master.PLANID_01} = "01" then "01 – DSP 4017" else
if {Part_Master.PLANID_01} = "50" then "50 - ISSO 7142" else
if {Part_Master.PLANID_01} = "51" then "51 - MEMS 7266" else
if {Part_Master.PLANID_01} = "52" then "52 - Pro AV 7446" else
if {Part_Master.PLANID_01} = "53" then "53 - CFTL 7168" else
if {Part_Master.PLANID_01} = "54" then "54 - Chirag Patel 4903" else
if {Part_Master.PLANID_01} = "55" then "55 - SDP 7168" else
if {Part_Master.PLANID_01} = "56" then "56 - DAU 7205" else
if {Part_Master.PLANID_01} = "57" then "57 - METER 7206" else
if {Part_Master.PLANID_01} = "58" then "58 - AUTO 7130" else
if {Part_Master.PLANID_01} = "59" then "59 - HSN 4123" else
if {Part_Master.PLANID_01} = "60" then "60 - ASC-WEST 7103" else
if {Part_Master.PLANID_01} = "61" then "61 – APPSTECH 7183" else
if {Part_Master.PLANID_01} = "62" then "62 - APPS MKT 7229" else
if {Part_Master.PLANID_01} = "63" then "63 - MARKETING 7247" else
if {Part_Master.PLANID_01} = "91" then "91 – APPSTECH 7183" else
if {Part_Master.PLANID_01} = "92" then "92 - APPS MKT 7229" else
if {Part_Master.PLANID_01} = "93" then "93 - MARKETING 7247"

If formulas could be added for null returns that would be great.

@jimhorn,
I am not at liberty to change the joins in the db and the joins in the report are as they should be I think, unless you have other reasons or are talking of other joins that I might be unaware of.

Mike McCracken

Look at the sample report in the linked question. The method used should work for you. If you need/want help implementing it in your report let me know.

mlmcc

James0628

FWIW, using a generic example, what jimhorn was suggesting was that if you were reading something like an order table and joining that with a customer master table, to get the customer name, then if you made that an outer join, the data would include the customers from the master table that had no orders.

That might work, although an added wrinkle with CR is that under some circumstances, it will change an outer join to inner, so even if you are reading tables like that, an outer join might not work (because CR might change it).

James

gibneyt

ASKER

Deep in other task and hope to implement tomorrow or early next week. Sorry for the delay.

Tim