Displaying groups containing no records

[agorjest]

Using CR2008. Have a report listing workorders grouped under 3 different sites, WI, IA, and TX. However, the TX site is small and often does not have any workorders to display. Therefore, only the WI and IA group names appear on the report. How can I get the TX site to display it's group header and total # of workorders even if it has no workorders to display?

Any suggestions appreciated...

 

[hilfy]

Do you have a table that contains site information without any workorder information that can be joined to the workorder info? Without that, there is no way to do this.

What you'll do is make the "site" table the master for the report. Create a left join FROM the site table TO the workorder table. Group on the Site from the Site table instead of the site information from the workorder table. This should get you what you're looking for.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[agorjest]

The "Site" field is actually a customized field in the workorder table, so I couldn't do a join of 2 different tables.

Has anyone done anything with formatting to make this work, like a text box with the group name if there are no records in that group, and conditionally suppressing it if there are records for that group?

 

[hilfy]

There is another way to do this. How are your SQL skills?

You'll need to use a Command to pull all of the data for your report. A Command is a SQL select statement. It will look something like this:

Select
  site.Site,
  <all of the other fields you need for the report>
from ( --get the distinct site names
  select Site
  from Workorders
  group by Site) as sites

  left outer join Workorders
    on sites.Site = Workorders.Site

  <joins to any other tables you need>
where Workorders.ID is null 
   or (<other filter criteria for report>)

If there are any parameters for your report, delete them from the main report then recreate and use them in the Command Editor. If you don't do this, Crystal will pull ALL of the data from the command and then filter it in memory instead of pushing the filter to the database.

Because of the left outer join, you should get all of the sites regardless of whether there are workorders for a give site.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[agorjest]

Thanks for your responses, hilfy.

I have done SQL commands before, so I'd be willing to take a crack at it. However, if I'm interpreting your above SQL code correctly, your reference to the field "site.site" assumes that there is a separate table for the sites, which there is not (it is a custom field in the workorder table called workorder.wolo1).
Let me know if I'm interpreting your code wrong, as I am by no means a SQL expert.

 

[hilfy]

It should have been "sites.site" - that's a typo on my part. "Sites" is the name of the subquery that gets the list of sites in your workorder table.

Sorry about that!

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[ShortyA]

It may also be worth considering using a universe with a derived table inside it showing the above SQL. Then it can be re-used if required and also you can keep your SQL snippets in one place. I have found the performance using queries in universes better than commands in the report also.
ShortyA

 

[kenhamady]

If it is only one site that you are concerned about you could hard code a 'fake' section. Create a section that looks like the TX section and fake totals of zero. Then suppressed it based on a condition. To create the condition you will first need a formula Called TXCheck that looks like this:

If {site} = "TX" then 1 else 0

Then you could suppress the fake "TX" section with a formula that says:

Sum ({@TXCheck}) > 0

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[agorjest]

Ken,
Thanks for the response. 2 follow up questions:

1. By creating a "section", do you mean using the "Insert Section Below" to create a new group section below the Site groups?
Currently, my report is grouped by the "site" field in the workorder table.

2.There are 1 of 3 possible sites contained in the "site" field, and usually it's only the one TX site that doesn't display any records. But depending on the date range of data, there may also be no records for the IA site. What would I do to hard code more than 1 group?

 

[kenhamady]

1. Depends on where it has to appear. I would split the report footer and put the subsection on the top so that it is like the last "group".

2. You could do all three using the same technique. You would need three formulas and three subsections.

The following articles explain several other options depending on the situation:

http://www.kenhamady.com/news0208.shtml

http://www.kenhamady.com/news0903.shtml

http://www.kenhamady.com/news0905.shtml

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/