I am working on reports for an annealing shop.  A charge consists of a stack of coils of steel sheet or wire.  It is stacked on a base, a furnace is lowered onto it, and it is heated at a controlled rate for a couple of days. 

 

I am trying to come up with a report that will summarize information for all charges that have been annealed on each base in a given month.  It is quite possible that no charges will be annealed on a base during the month, but charges may have been annealed on it in some other month.

 

As an example, suppose I develop a report to list the number of charges that have been annealed on a base.  I start my report with the bases table and the charges table, linked on the base name field.  I set up a group on the base name, and use a running total that is reset when the group changes to report the count of charges for the base.  My report shows me the six bases out of 124 that have any charges. 

 

But I want all bases listed.  So, I change the link from an inner join to a left outer join.  Now, I've got all 124 bases, with 118 of them showing 0 charges.  Fine. 

 

But what happens if I add a record selection formula?  Let's say, all charges annealed since the start of this year.  (That means all of them.)  The record selection formula is:

{charge.fire_date} > DateTime (2009, 01, 01, 00, 00, 00)

Now I'm back to the six bases that had charges!  How can I get this report, using this selection formula, to include all 124 bases, including the 118 for which the count will be zero?

 

 

One way I can think of to do it is to move the selection criterion from the record selection formula to the evaluation formula of the running total that contains the charge count.  But the report I'm working on has a fairly set of columns, with totals to be maintained for each base and for the plant as a whole, and putting an evaluation formula in for each running total field I use.  But that is really cumbersome, and it will be even more cumbersome if I ever have to change the evaluation formula for each of the 20 or so running totals in this report. 

 

Another thing I thought of trying was basing the report on an SQL Command, since I can put parameters in the comand that will limit the number of records returned.  I could do that if the starting date and ending date of the range of interest were specified explicitly as parameters.  However, the report is designed to show data for a given month.  The report has two parameters, a month number and a year number, and the user has requested a default value of the previous month.  I am implementing that by using a month number of 0 to mean the previous month.  An SQL statement that will select for a given month and year is easy to put together, but I have no idea how to write an SQL statement that would do that and also would translate 0 into "Give me anything regardless of date". 

 

I could easily write a stored procedure in the database that would do what I need it to do, but I've been modifying the databae too much as it is, and I really don't think I should have to.

 

What I want to do is very simple in concept, and I have no idea why it's so hard in reality.  How can I get empty groups to be included in my report?

 

Thank you very much!

 

RobR

Think about what happens with a left outer join when nothing on the right side matches anything on the left.  You end up with a record with all the fields from both the left and right sides, but all the fields on the right contain NULL.  Any logical operator on a NULL field returns FALSE.  So, a record selection on a NULL field will always return FALSE, and there won't be any records for the base's group, and the base won't be included on the report.

 

I actually tried checking for NULL in the record selection formula in the original report.  Any record with a NULL charge number would be accepted.  That actually appeared to work, until I looked closely.  I found that I didn't have bases listed on which charges had been annealed at some time, just not in the target month.  Again, I need those bases to appear on the report with a count of 0.  That was when I started trying to come up with ways to have the date range included in the original SQL statement instead of merely in a selection formula.

 

RobR

do a left join on the tables, gives you lots of bases, in record selection select dates are null or in a range.

 

it is the same as what you do in a stored proc,

select * from base left join charge on charge.base=base.base where charge.date between x and y

 

you will still get the null dates, how would build the report?  group by base.  Every base has an entry, every one will display.  The rest is converting NULLs to 0.

 

Regardless, in Crystal or SQL, it is going to start with an outer join.  If you applied the same logic to the sql, you get the same result

select * into #temp from base left join charge on base.base=charge.base

select * from #temp where date between x and y

this will get rid of all the unused bases which is not the intent.  you need to craft your selection formula so that it includes what you want, not just exclude everything, because, as you said, look at what happens in an outer join...lots of nulls, and you want the nulls.

 

Not saying that I am necessarily right, the data could be completely different, just that no matter what you do in Crystal or SQL it is going to start with an outer join, or you are going to get really creative with inner joins that in the end simulate an outer join, because your requirements require that you perform the outer join.

 

Think what you would do in SQL and apply it to Crystal, since Crystal basically thinks the same way.

 

I've also learned the basics of using SQL Command objects with parameters.  Good grief, is that buggy!  I ended up with two parameters that didn't show up my Parameters list, both of which were named "area", which were displayed when I previewed the report instead of the parameters named "Month" and "Year" that should have been there. 

 

And is there really no way to connect an SQL Command object to previously defined parameters?  The only way I could get it to work was if I was creating the parameters along with the command object.

 

RobR

 

P.S.  Thanks very much for the time you have taken to try to help me!