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
gibneytAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
mlmccCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

gibneytAuthor Commented:
@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.
0
mlmccCommented:
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
0
James0628Commented:
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
0
gibneytAuthor Commented:
Deep in other task and hope to implement tomorrow or early next week.  Sorry for the delay.

Tim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.