Forcing report to list all possible group values even if no activity

[BryanLane]

I am using Crystal 11.5.8.826

How can you force all possible Groups to appear on a report even if their value totals zero?

I am working on a spreadsheet that will be populated by an export from Crystal Reports that will show by location the sales for each product category group for each month. The data is currently hand entered from an existig report.

I would like to automate it at least a little. The information will have to be cut and pasted into the existing Excel spreadsheet that I can not alter (too much).

We have a Product Category field that is two character alpha-numeric, that always starts with a letter and can end in either a letter or a number. The Product Category Group that I am concerned with is used in the spreadsheet but does not exist as a field in the database. It is the first character (always a letter) of the Product Category, so it is limited to 26 possible values (we currently don't use U, V or W; but may in the future, so I would not be opposed to forcing it to all 26 letters instead of the 23 currently being used).

I have a formula that takes the first character of the Product Category to create the Group value, but I only get a listing for the Product Category Groups that were sold during the month, I want all 26 possible totals.

Of course, there has to be some kind of complications. Not sure if these will affect any possible solution, but I figure it is best to get them in the open up front:

1.) We have a location that we merged with another store, but its records exist up through the time the store closed, but for comparison purpose we report them as a single location (loc 3 and 5 combined as location 3). I do this via a formula that converts loc 5 to loc 3 and then group on the formula.

2. All of our delivery and freight charges do not have a location assigned to them (I back into the location by looking up the customer's home location and use it for several transaction types.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[LinPro]

Could you show a sample of your data?

If you are grouping by location, are you doing a left outer join, with Location on the left? This will display all location.

L

 

[BryanLane]

I am grouping by Location and then by Product Group (my formula that takes the first character of Product Category).

I am using three tables LINPRM which is my sales table, InvMas which provides the Product Category (linked to LINPRM via Supplier and Part fields using a left outer join LINPRM > INVMAS) and CusMas which is linked to LINPRM via the CusNo (equal join) field and is used to provide the Location for delivery charges that do not exist in LINPRM.

I end up with data such as:
1-Z 182.50
3-A 8546.27
3-B 45.40
3-C 1779.50
...
3-T 1311.00
3-X 1010.01
7-A 1245.78
7-D 65234.00
7-K 1478.40

I want to see
1-A
1-B
1-C
1-D
1-E
and continue on through location 1 and then start with location 3, etc. If there are no sales for that category it could be zero or blank, either is acceptable.

My goal is to be consistent so I can quickly cut and paste without having to look at each value and slide it into the correct cell in the Excel spreadsheet. Right now the spreadsheet lists the 23 used letters for each location. I could insert the other three if necessary.

Hope that helps.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[LinPro]

Do you have all the letters in the alpha in your product category? You will need to have all the letters in order for Crystal to group and sort on it first.

L

 

[BryanLane]

We have A through T plus X, Y & Z...no U V W (at this time, but that could change). But they are in there as the first letter of a 2 character field.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[IanWaterman]

YOu need to add in a table which holds all your product codes, make that the lead table and join your existing data with a left join from the product group table.

Change your grouping to use product code from the new table.

Ian

 

[Madawc]

I think you'll have to do it the hard way, create a 'Mock-Crosstab'. This is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.

Each running total will count the record if it was within the criteria - in your case, Location followed by Product Group.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[BryanLane]

Thank you Ian and Madawc,

I started out with the table idea and then tried the crosstab. The crosstab worked quite well, for the last two and half years of full months I get every possible product category group listed each month. Thank you everyone for your help.

I really appreciate all the effort that goes into this forum. I scan it frequently and have learned so much from the various posters here.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein