How to print in a report movements from two tables organized by date ? 1

[johnwolf]

Hello

Working with CR8 and Oracle DB

I am trying to show the movements of goods we buy and sell.

I have two tables organized in the same way.

Table 1: PURCHASE ORDERS

PUR_ORDER# ITEM# PUR_QUANTITY PUR_PRICE PUR_DATE
1 DA019039 30 12.50€ 2009/01/02
1 DA025003 12 4.00€ 2009/01/02
36 DA019039 20 12.80€ 2009/03/25

Table 2: SALES ORDERS

SAL_ORDER# ITEM# SAL_QUANTITY SAL_PRICE SAL_DATE
6 DA019039 3 21.50€ 2009/02/12
14 DA025006 1 7.00€ 2009/02/24
25 DA019039 4 21.50€ 2009/04/01

I would like to get a report like

(GROUP)ITEM# DA019039
MOVE_DATE MOVE_TYPE QUANTITY PRICE
2009/01/02 PUR 30 12.50€
2009/02/12 SAL 3 21.50€
2009/03/25 PUR 20 12.80€
2009/04/01 SAL 4 21.50€

(GROUP)ITEM# DA025003
MOVE_DATE MOVE_TYPE QUANTITY PRICE
2009/01/02 PUR 12 4.00€
....

I do not know how to organize the links between tables to get to that result. If I only link on ITEM#, I get duplicated lines for every movement, and I do not know how to sort by date as dates come from two distinct tables.

Any help welcome.
Thanks in advance.

 

[Madawc]

You can get round duplicate lines by grouping and suppressing the detail line, showing what you want in the group header or footer.

To use two alternative dates, have a formula field that chooses one or the other. You will have to have this as a higher-level group, since groups are always the highest level of any sorting.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[johnwolf]

Thanks for your answer.

I do not understand exactly what you mean.

I am used to group/hide details in many reports, but my first logical problem is the link beetween the 2 tables: should it be only on the item#. From the result I get it seems it should be otherwise.

I thought I could use a new table with all the dates from a given period and create a link beetween this new table and the purchase orders table and the sales orders table on date fields. But I do not know how to create a date table that does not exist

I feel like there must have a simple logical link but I do not know how to figure it out.

If you have any idea please try to be specific, based on the data samples so that I can understand better.

Thanks in advance,

 

[Madawc]

If the problem is with your data, I suggest you do test Crystal reports to see if it is what you think it is.

You can't create your own tables in Crystals. You can do SQL Commands with SQL databases: I have no idea if anything similar is possible in Oracle.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[johnwolf]

Thanks for your answer.

I have access to the data only through Crystal and know almost nothing of SQL and less of Oracle.

From what I understand, I do no think I have problems with the data itself as both tables are simple as described above.

To create a report I use the table relations menu. And there appear both tables. If I make a link on item# the number of detail lines I get per item in the report is (number of records of purchase orders X number of records of sales orders) when what I would like to get is (number of records of purchase orders + number of records of sales orders).

This makes me think my post is not in the correct forum, maybe it should be in Data Access.

I thought what I would like to get could be achieved through CR but maybe it is not the case, it has to be on the database level.

Thanks anyway.

 

[lbass]

What you really need to do is merge the date fields into one field. Please go into database->show SQL query and copy your current query and paste it here. If it is not too complex, I'll show you how you can modify it to do this.

-LB

 

[johnwolf]

Thanks for your answer

Here is SQL query

D01_DET_ALBARANES_CLIE es SALES_ORDERS
D01_DET_ALBARANES_PROV es PURCHASE_ORDERS
FECHA es DATE
PRECIO es PRICE
CANTIDAD es QUANTITY


SELECT
"D01DET_ALBARANES_CLIE"."FECHA", "D01DET_ALBARANES_CLIE"."CANTIDAD",
"D01DET_ALBARANES_CLIE"."PRECIO", "D01DET_ALBARANES_PROV_TBL"."FECHA",
"D01DET_ALBARANES_PROV_TBL"."CANTIDAD",
"D01DET_ALBARANES_PROV_TBL"."PRECIO"
FROM
"MIC2000"."D01DET_ALBARANES_CLIE" "D01DET_ALBARANES_CLIE",
"MIC2000"."D01DET_ALBARANES_PROV_TBL" "D01DET_ALBARANES_PROV_TBL"
WHERE
"D01DET_ALBARANES_CLIE"."CODART" = "D01DET_ALBARANES_PROV_TBL"."CODART"

Hope it is clear.

Thanks in advance for your help

 

[Madawc]

If all of the items appear in one table, do a 'left outer' link from that table to the other. (That's an option in the report linking.)

If neither are complete, see if there is another table that has everything and do left-outer links for both.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

First create a report using only the "D01DET_ALBARANES_CLIE" table, where you place the three fields plus the item # field in the detail section. Also add an additional string field from the same table--it doesn't matter what it is--it will just be acting as a place holder. Then go to database->show SQL query and you should see something like this:

SELECT
"D01DET_ALBARANES_CLIE"."FECHA",
"D01DET_ALBARANES_CLIE"."CANTIDAD",
"D01DET_ALBARANES_CLIE"."PRECIO",
"D01DET_ALBARANES_CLIE"."YourItem#Field",
"D01DET_ALBARANES_CLIE"."SomeStringField"
FROM
"MIC2000"."D01DET_ALBARANES_CLIE" "D01DET_ALBARANES_CLIE"

At the end of the query, add the following:

WHERE 1 = 0

After this Where clause (which eliminates all data from the original query), paste the following into the query:

Union All
SELECT
"D01DET_ALBARANES_CLIE"."FECHA",
"D01DET_ALBARANES_CLIE"."CANTIDAD",
"D01DET_ALBARANES_CLIE"."PRECIO",
"D01DET_ALBARANES_CLIE"."YourItem#Field",
'Sales Orders' "Type"
FROM
"MIC2000"."D01DET_ALBARANES_CLIE" "D01DET_ALBARANES_CLIE"
Union All
SELECT
"D01DET_ALBARANES_PROV_TBL"."FECHA",
"D01DET_ALBARANES_PROV_TBL"."CANTIDAD",
"D01DET_ALBARANES_PROV_TBL"."PRECIO",
"D01DET_ALBARANES_PROV_TBL"."YourItem#Field",
'Purchase Orders' "Type"
FROM
"MIC2000"."D01DET_ALBARANES_PROV_TBL" "D01DET_ALBARANES_PROV_TBL"

Now all dates will appear in the "D01DET_ALBARANES_CLIE"."FECHA" field--same for the other fields. Insert a group on the item field and then you can add the new "Type" field into the detail section in order to tell which fields are coming from which table. Note that you should do any record selection in the where clause on each side of the union all rather than using the main report select expert.

Note that this method (modifying the "show SQL query") is specific to v.8 or 8.5. In later versions, you could simply have used a command for this purpose.

-LB

 

[johnwolf]

Thank you very much LB.

That is clear and accurate. Works perfectly.

One question to make sure I understood well:
To make a selection, I have to put the same WHERE clause at the end of each of the UNION ALL blocks, correct?

 

[lbass]

It is not necessary to have a where clause in each section of the union all--unless the logic dictates it. So, if you wanted to limit the report to certain purchases, but you wanted to show all sales, you could add a where clause to the purchases segment without a corresponding clause in the sales side, but if you wanted to limit all dates, you would add a date criterion to each side of the union all. You do not need to add a where clause to the section preceding the "where 1 = 0" though.

-LB