Forcing all values to appear

[BryanLane]

I am creating a report that groups based off a formula, not a field.

We have a Product Category field (two letters, such as AA) that every item is assigned. I am grouping off the first letter, derived by using Left(ProdCat,1), and then I use another formula to assign a description to this first letter. Twenty-four of the letters are used (no U or V at this time).

When I create the report, the user wants to see all the Groups, even if there is nothing present for them in the selected data. So if there are no Product Categories that start with the letter "I", the report would have a group for "I" with no data, just the letter and description.

Since this single digit code does not exist in a table in the database I assume I will have to use "Add Command" to create a table that I can then do an Outer Join on, but I am not sure how to create the command.



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

 

[lbass]

The issue is how you are joining this table to others. If you use the product category table as your lead table, and then connect others to it with left outer joins and without making any selection on those added tables, then all groups based on your formula would appear. As soon as you use criteria, though, groups would potentially disappear.

There is a way of handling this in a command so that you can add criteria to outer tables by adding the criteria in the From clause rather than the Where clause of the command. So the question is what record selection criteria you have added and on what table.fields.

-LB

 

[briangriffin]

Your command will be:

select
'AA' as GroupLetter
union select
'BB'
union select
'CC'
etc. etc.

No from clause is needed. Then do an outer join from the command to the product category field.

Your primary grouping will still be on a formula - extract the first letter of the GroupLetter field from your command.

 

[BryanLane]

I rearranged my report so that PCAMAS (Product Category table) is the lead table and connected via left outer joins and I still get the same results.

I linked PCAMAS to INVMAS (Inventory Master -- where the product categories are assigned to each part number) by ProdCat as a left outer join.

To INVMAS I linked LINPRM (sales detail history table)by Sup and Part. To LINPRM I linked CUSMAS (Customer Master Table) by Cusno (Customer Number). This table provides the Customer Name for the report.

My Selection criteria is:

{LINPRM.CUSNO} = {?Customer#} and
{@Invoice Date} in {?Start Date} to {?End Date} and
{LINPRM.PRODCAT} < "ZA"

The report is being run calendar year to date for a specified customer and we are ignoring all the "Z" product categories that are for special situations.

(I had to whip this out for the big boss in a hurry, so I exported what I had to Excel, added the missing groups and inserted zero totals for them across the board, but I know this will be coming up again real soon, so I need to continue to work on this.)

I forgot to state in the initial posting that I am on version 11.5.8.826.

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

 

[lbass]

You cannot select on the sales history table--this is what is undoing the left outer join. Try the following command and use it as your sole datasource:

select
PCAMAS.PRODCAT,
CUSMAS.CUSNO,
LINPRM.SALESDATE
from
(((PCAMAS equal join INVMAS on
PCAMAS.PRODCAT = INVMAS.PRODCAT and
PCAMAS.PRODCAT < 'ZA'
)
left outer join LINPRM on
INVMAS.PART = LINPRM.PART and
INVMAS.SUP = LINPRM.SUP
)
left outer join CUSMAS on
LINPRM.CUSNO = CUSMAS.CUSNO AND
CUSMAS.CUS = {?CustomerNo} and
LINPRM.SALESDATE >= {?Start} and
LINPRM.SALESDATE < {?End}
)

If CUSMAS.CUS is a string, enclose {?CustomerNo} in single quotes: '{?CustomerNo}'. You must create the parameters within the command on the right. You should enclose the table and field names in the punctuation appropriate for your database (check the Show SQL Query on your current report to see what the convention is, if you are not sure). Correct the date field name--I just guessed, and add other necessary field names to the list of fields in the Select clause.

After the command compiles, you should insert a group on the PCAMAS.PRODCAT field.

-LB