Solved

Joins on tables

Posted on 2008-10-22
15
506 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:johntmcii
  • 9
  • 6
15 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 22779363
Not that I know of.

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
0
 

Author Comment

by:johntmcii
ID: 22779533
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-01"."dbo"."ODLN" "ODLN" LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."DLN1" "DLN1" ON "ODLN"."DocEntry"="DLN1"."DocEntry") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."OCRD" "OCRD" ON "ODLN"."CardCode"="OCRD"."CardCode") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."OSHP" "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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22780376
As a guess the database  TEST_SAP_TEST_2008-10-01 is data up to 2008-10-01

mlmcc
0
 

Author Comment

by:johntmcii
ID: 22780631
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?
0
 

Author Comment

by:johntmcii
ID: 22780725
I need a spell check , can't get my fingers to work!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22781976
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
0
 

Author Comment

by:johntmcii
ID: 22785852
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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:johntmcii
ID: 22786241
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-01"."dbo"."ODLN" "ODLN" LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."DLN1" "DLN1" ON "ODLN"."DocEntry"="DLN1"."DocEntry") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."OCRD" "OCRD" ON "ODLN"."CardCode"="OCRD"."CardCode") LEFT OUTER JOIN "TEST_SAP_TEST_2008-10-01"."dbo"."OSHP" "OSHP" ON "ODLN"."TrnspCode"="OSHP"."TrnspCode"
 WHERE  "ODLN"."DocNum"=272626
 ORDER BY "ODLN"."DocNum", "DLN1"."ItemCode"

2008-10-23-090324.jpg
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22786617
It looks like it changed it to me.

Do you get the expected data?

mlmcc
0
 

Author Comment

by:johntmcii
ID: 22786908
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.
 
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22790022
Is the report saved with data?

mlmcc
0
 

Author Comment

by:johntmcii
ID: 22791402
No the option to save data with the report is not selected.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 22792093
I know in previous versions of Crystal the SQL didn't change when you changed the location but the data was pulled from the correct location.

mlmcc
0
 

Author Comment

by:johntmcii
ID: 22795942
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.
 
0
 

Author Closing Comment

by:johntmcii
ID: 31508787
I recreated report and that part worked fine. Nothing different that I would tell from the previous report.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now