holdsworthbros
asked on
Using field summary from another report
Hi Everyone,
I have two databases, one with sales records and one with stock records.
I can create a report that summaries sales records (grouped by a category) and
I can create a report that summarises stock holding (grouped by category)
But I cant seem to create a report that shows both.
The report will only extract inventory records that happend to be in the sales database. Where I would like the sales report to summarise all sales (per category), and summarise all inventory items (per category) regardless of whether there was any sales activity for an individual item.
I dont quite know where to start with this problem
I have two databases, one with sales records and one with stock records.
I can create a report that summaries sales records (grouped by a category) and
I can create a report that summarises stock holding (grouped by category)
But I cant seem to create a report that shows both.
The report will only extract inventory records that happend to be in the sales database. Where I would like the sales report to summarise all sales (per category), and summarise all inventory items (per category) regardless of whether there was any sales activity for an individual item.
I dont quite know where to start with this problem
ASKER
Hi James,
Thanks for your solution. It does work, kinda sorta. Although I still run into trouble. Its slightly more complicated than I indicated. The sales are stored in two tables. One 'sales header table' that stores generic information, like the date of sale, salesperson etc. It links through one field to the 'sales detail table' that stores the inventory items that are sold. (the linking field is the sale invoice number).
If I left outer join the inventory file and the sales detail file then the left outer join works. But if I then add the sales header table (joined by the sales invoice number) and select according to a field in the sales header table, then the left outer join 'fails' and I only get records from each table that are an exact match.
Is it my linking thats stuffed or does Crystal just not like it?
Thanks for your solution. It does work, kinda sorta. Although I still run into trouble. Its slightly more complicated than I indicated. The sales are stored in two tables. One 'sales header table' that stores generic information, like the date of sale, salesperson etc. It links through one field to the 'sales detail table' that stores the inventory items that are sold. (the linking field is the sale invoice number).
If I left outer join the inventory file and the sales detail file then the left outer join works. But if I then add the sales header table (joined by the sales invoice number) and select according to a field in the sales header table, then the left outer join 'fails' and I only get records from each table that are an exact match.
Is it my linking thats stuffed or does Crystal just not like it?
> ... and select according to a field in the sales header table ...
What, exactly, does your record selection look like?
I've read that if you use a LEFT OUTER join in CR, but then try to check a field in the table that you're joining with (as you're doing), then CR changes the join back to an INNER join. That could very well be what's happening to you.
However, the few times that I've looked at situations like this, that did not happen to me. I've been able to LEFT OUTER join to a table, check a field in that table, and I still had the LEFT OUTER join, and got the records I wanted. So, while the join may be changed in some cases, it doesn't seem to happen all of the time.
The problem may be in your record selection, which is why I asked what yours looks like. In particular, if you're testing fields in the sales header or detail table, you have to check for nulls first. CR does not like seeing a null value when evaluating a formula and will just quit evaluating the formula if it runs into one. So, for example, if you're looking for a specific order number that was entered in a parameter, but you also want to see every item that was not sold at all (doesn't make much sense, but it will do as an example :-), your record selection would look something like:
IsNull ({sales header table.order number}) or
{sales header table.order number} = {?order number parameter}
James
What, exactly, does your record selection look like?
I've read that if you use a LEFT OUTER join in CR, but then try to check a field in the table that you're joining with (as you're doing), then CR changes the join back to an INNER join. That could very well be what's happening to you.
However, the few times that I've looked at situations like this, that did not happen to me. I've been able to LEFT OUTER join to a table, check a field in that table, and I still had the LEFT OUTER join, and got the records I wanted. So, while the join may be changed in some cases, it doesn't seem to happen all of the time.
The problem may be in your record selection, which is why I asked what yours looks like. In particular, if you're testing fields in the sales header or detail table, you have to check for nulls first. CR does not like seeing a null value when evaluating a formula and will just quit evaluating the formula if it runs into one. So, for example, if you're looking for a specific order number that was entered in a parameter, but you also want to see every item that was not sold at all (doesn't make much sense, but it will do as an example :-), your record selection would look something like:
IsNull ({sales header table.order number}) or
{sales header table.order number} = {?order number parameter}
James
ASKER
Hi James,
Thanks again.
The joins stay as left outer join. So thats not the problem.
The database tables look like this...
inmaster = inventory table
orderdtl = inventory items that sold under a sales header (effectively this table is a comprehensive list of every line item that has sold on an invoice)
orderhea = Sales header table (contains generic data of the sale and has a sub table (orerdtl) that contains the inventory items that sold in the invoice batch.
inmaster.CODE --LOJ--> orderdtl.PART_CODE (the partcode is unique to an inventory item)
orderdtl.ORDER_NUMBER---in nerjoin--- >orderhea. ORDER_NUMB ER (a sales invoice number is unique to the sale)
Record selection...
orderhea.STATUS is one of "Z", "C", "X" (Dont include any sales that are flaged as Z, C, X)
I so hope you're not confused!
Thanks again.
The joins stay as left outer join. So thats not the problem.
The database tables look like this...
inmaster = inventory table
orderdtl = inventory items that sold under a sales header (effectively this table is a comprehensive list of every line item that has sold on an invoice)
orderhea = Sales header table (contains generic data of the sale and has a sub table (orerdtl) that contains the inventory items that sold in the invoice batch.
inmaster.CODE --LOJ--> orderdtl.PART_CODE (the partcode is unique to an inventory item)
orderdtl.ORDER_NUMBER---in
Record selection...
orderhea.STATUS is one of "Z", "C", "X" (Dont include any sales that are flaged as Z, C, X)
I so hope you're not confused!
ASKER
Sorry... STATUS is NOT one of "Z", "X", "C" (Dont include any sales that are flaged as Z, C, X)
OK, you need to check STATUS to see if it's null first. Something like:
not IsNull ({orderhea.STATUS}) and
not ({orderhea.STATUS} in [ "Z", "C", "X" ]
James
not IsNull ({orderhea.STATUS}) and
not ({orderhea.STATUS} in [ "Z", "C", "X" ]
James
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!!!!!!
You're welcome. Glad I could help.
James
James
Like I said, that's the basic idea. Let me know if you need more help.
James