I use CR XI under Windows XP Pro.
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