johntmcii
asked on
Joins on tables
Continuing to work on the Reports, ran into another issue not usre if it is a coding problem or data exception. I am using four SAP tables in this CR; ODLN is the main table used and joins OCRD, DLN1, and OSHP. I oiginally had code all to use left outer joins, enforced outer join, =.
The changed to Inner joins as shown in the file below (see Crystal Report joins file)
In testing, I ran a query in SAP to select records from the tables so I could compare the output. SAP created the following join statements in the query it created.
From ODLN T0
Inner Join OCRD T1 on T0.cardcode = T1.cardcode
Inner Join OSHP T2 on T0.Trnspcode = T2.trnscode
inner Join DLN1 T3 on T0.Docenry = T3.Docentry
From the test, the Crystal report produced correct reports until dates 10/01/08 encountered (This is probably going to confuse the issue but the information on the query appeared to be correct regardless of the date) No matter how I had the joins coded any report containing a shipment number prior to 10/01 appeared to be correct however any shipment number after 10/01 did not show any data on the report. It appeared that there was no data in the DLN1, OSHP or OCRD. No detail lines appeared, no address lines appeared, only static data on the report, but on the query I could see the information.
My question is simply there were 4500 lines on the query that I could verify the same data on the crystal report so I know the logic worked in the CR. By that I mean the detail lines were appearing fine, the addresses formated properly, the totals were correct, etc. Is there a debug or some other tool I can turn on in CR to see why the report stopped working after 10/01?
SKMBT-C45108102210040.pdf
The changed to Inner joins as shown in the file below (see Crystal Report joins file)
In testing, I ran a query in SAP to select records from the tables so I could compare the output. SAP created the following join statements in the query it created.
From ODLN T0
Inner Join OCRD T1 on T0.cardcode = T1.cardcode
Inner Join OSHP T2 on T0.Trnspcode = T2.trnscode
inner Join DLN1 T3 on T0.Docenry = T3.Docentry
From the test, the Crystal report produced correct reports until dates 10/01/08 encountered (This is probably going to confuse the issue but the information on the query appeared to be correct regardless of the date) No matter how I had the joins coded any report containing a shipment number prior to 10/01 appeared to be correct however any shipment number after 10/01 did not show any data on the report. It appeared that there was no data in the DLN1, OSHP or OCRD. No detail lines appeared, no address lines appeared, only static data on the report, but on the query I could see the information.
My question is simply there were 4500 lines on the query that I could verify the same data on the crystal report so I know the logic worked in the CR. By that I mean the detail lines were appearing fine, the addresses formated properly, the totals were correct, etc. Is there a debug or some other tool I can turn on in CR to see why the report stopped working after 10/01?
SKMBT-C45108102210040.pdf
ASKER
On the databse option I listed the SQL (below) This report is one that I had pointed to a test database initially then changed to production, this query appears to be pointing to Test, (look at the dsn. The dsn appears to be pointing to production. when could I be picking up the test_sap_test_2008-10-01) Could this be pointing to the test databse even though I have the live database selected in the "set datasource location" This would explain the problem?
Something I am not understanding about how to create set the dso/dsn.
SELECT "ODLN"."DocNum", "DLN1"."ItemCode", "DLN1"."Dscription", "ODLN"."DocDate", "DLN1"."ShipDate", "DLN1"."Quantity", "DLN1"."LineTotal", "DLN1"."Price", "ODLN"."CardCode", "OSHP"."TrnspName", "OCRD"."CardName", "OCRD"."Address", "OCRD"."City", "OCRD"."State1", "OCRD"."ZipCode"
FROM (("TEST_SAP_TEST_2008-10-0 1"."dbo"." ODLN" "ODLN" LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01" ."dbo"."DL N1" "DLN1" ON "ODLN"."DocEntry"="DLN1"." DocEntry") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01" ."dbo"."OC RD" "OCRD" ON "ODLN"."CardCode"="OCRD"." CardCode") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01" ."dbo"."OS HP" "OSHP" ON "ODLN"."TrnspCode"="OSHP". "TrnspCode "
WHERE "ODLN"."DocNum"=275763
ORDER BY "ODLN"."DocNum", "DLN1"."ItemCode"
Code in the file below is the current dsn
2008-10-22-130058.jpg
Something I am not understanding about how to create set the dso/dsn.
SELECT "ODLN"."DocNum", "DLN1"."ItemCode", "DLN1"."Dscription", "ODLN"."DocDate", "DLN1"."ShipDate", "DLN1"."Quantity", "DLN1"."LineTotal", "DLN1"."Price", "ODLN"."CardCode", "OSHP"."TrnspName", "OCRD"."CardName", "OCRD"."Address", "OCRD"."City", "OCRD"."State1", "OCRD"."ZipCode"
FROM (("TEST_SAP_TEST_2008-10-0
WHERE "ODLN"."DocNum"=275763
ORDER BY "ODLN"."DocNum", "DLN1"."ItemCode"
Code in the file below is the current dsn
2008-10-22-130058.jpg
As a guess the database TEST_SAP_TEST_2008-10-01 is data up to 2008-10-01
mlmcc
mlmcc
ASKER
Yep, I just wrote anotehr CR (no copy), included same fields, all worked fine. so my question is (how, where) do I change the qualified name on the DSO? Maybe I am missing something but I don't recall seeing where this is required. I changed the location under the database, I thought that would make the necessary changes?
ASKER
I need a spell check , can't get my fingers to work!
It should be in the SET DATASOURCE LOCATION that you show.
Just navigate to the new connection
Select the one to change in the top box
Click UPDATE
mlmcc
Just navigate to the new connection
Select the one to change in the top box
Click UPDATE
mlmcc
ASKER
Notl not sure where I go from here other than starting over with a new CR. I performed those exact steps and as you can see the dso is still pointing to Test. The SQL was after I had changed the Set Datasource location. I will try one more time then let you know the results.
ASKER
I ran through the entire process again, created new DSN, set datasource location, same results
SQL STILL SHOWING TEST_SAP_TEST_2008-10-01". "dbo
SELECT "ODLN"."DocNum", "DLN1"."ItemCode", "DLN1"."Dscription", "ODLN"."DocDate", "DLN1"."ShipDate", "DLN1"."Quantity", "DLN1"."LineTotal", "DLN1"."Price", "ODLN"."CardCode", "OSHP"."TrnspName", "OCRD"."CardName", "OCRD"."Address", "OCRD"."City", "OCRD"."State1", "OCRD"."ZipCode"
FROM (("TEST_SAP_TEST_2008-10-0 1"."dbo"." ODLN" "ODLN" LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01" ."dbo"."DL N1" "DLN1" ON "ODLN"."DocEntry"="DLN1"." DocEntry") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01" ."dbo"."OC RD" "OCRD" ON "ODLN"."CardCode"="OCRD"." CardCode") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01" ."dbo"."OS HP" "OSHP" ON "ODLN"."TrnspCode"="OSHP". "TrnspCode "
WHERE "ODLN"."DocNum"=272626
ORDER BY "ODLN"."DocNum", "DLN1"."ItemCode"
2008-10-23-090324.jpg
SQL STILL SHOWING TEST_SAP_TEST_2008-10-01".
SELECT "ODLN"."DocNum", "DLN1"."ItemCode", "DLN1"."Dscription", "ODLN"."DocDate", "DLN1"."ShipDate", "DLN1"."Quantity", "DLN1"."LineTotal", "DLN1"."Price", "ODLN"."CardCode", "OSHP"."TrnspName", "OCRD"."CardName", "OCRD"."Address", "OCRD"."City", "OCRD"."State1", "OCRD"."ZipCode"
FROM (("TEST_SAP_TEST_2008-10-0
WHERE "ODLN"."DocNum"=272626
ORDER BY "ODLN"."DocNum", "DLN1"."ItemCode"
2008-10-23-090324.jpg
It looks like it changed it to me.
Do you get the expected data?
mlmcc
Do you get the expected data?
mlmcc
ASKER
No sir, thats what I am saying the DSN changed, however the ressults are the same. This is exactly what I saw previously. It says it changes but is still pointing to "Test" when I Show SQL Query (after change) as shown above.
Is the report saved with data?
mlmcc
mlmcc
ASKER
No the option to save data with the report is not selected.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Strange,I have created a new dsn in the same report but had the same results; the fully qualified name is still incorrect and the information still did not appear on the report. I wrote a quick report with same dsn used above but did not copy and change report as I did in the instance above, in this case the information was correct and fully qualified name was correct.
I am at a point I am going scrub the report and start over with a new one, I believe it will work.
Thanks for you help.
I am at a point I am going scrub the report and start over with a new one, I believe it will work.
Thanks for you help.
ASKER
I recreated report and that part worked fine. Nothing different that I would tell from the previous report.
Is there data in the database for dates after 10/1
Are you filtering the data through the select exprt?
Since it is 10/1 is that the end of an FY and the new data is m a new table for the FY?
mlmcc