[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Report from Query

Posted on 2011-10-11
8
Medium Priority
?
305 Views
Last Modified: 2012-05-12
It seems like this should be easy, but I can't fnd it.
I'm trying to create a report with Crystal Reports drivvel by an SQL Query.
The query Joins several tables, One of them twice withe different aliases.
Some of the joins are on different dats types ( int and char )
Crystal will not let me create the joins in the 'Database Expert', ther is no way to retrict the Join specifying contants. I.E.
LEFT JOIN GLINTCO J
      ON N.COMPANY = J.COMPANY_A
      AND N.LEVEL_DETAIL_01 = J.ZONE_B
      AND J.RECORD_TYPE='L'
      AND J.SYSTEM='ZC'
Seems like ther should be an easy way to specify the query string that drive the report an treat it much like a table in Crystal, but I can't find it.


Select
     P.COMPANY , P.VENDOR, P.INVOICE, P.TRANS_NBR
     ,D.ORIG_TRAN_AMT,D.DIST_COMPANY, D.DIS_ACCT_UNIT, D.DIS_ACCOUNT, D.DIS_SUB_ACCT
     ,D.ORIG_TRAN_AMT
     ,N.LEVEL_DETAIL_01
   
     ,I.AR_ACCOUNT_A CRACCT1,I.AR_ACCTUNIT_A CRUNIT1,I.AR_SUB_ACCT_A CRSUB1
     ,I.AP_ACCOUNT_B DBACCT1,I.AP_ACCTUNIT_B DBUNIT1,I.AP_SUB_ACCT_B DBSUB1
   
     ,J.AR_ACCOUNT_B CRACCT2,J.AR_ACCTUNIT_B CRUNIT2,J.AR_SUB_ACCT_B CRSUB2
     ,J.AR_ACCOUNT_A DBACCT2,J.AR_ACCTUNIT_A DBUNIT2,J.AR_SUB_ACCT_A DBSUB2
    
     from APPAYMENT P
     left join APDISTRIB D
      ON  P.COMPANY = D.COMPANY
      AND P.VENDOR = D.VENDOR
      AND P.INVOICE = D.INVOICE
      AND P.SUFFIX = D.SUFFIX
      AND P.CANCEL_SEQ = D.CANCEL_SEQ
     LEFT JOIN GLNAMES N
      ON D.DIST_COMPANY = N.COMPANY AND D.DIS_ACCT_UNIT = N.ACCT_UNIT

     LEFT JOIN GLINTCO I
      ON N.COMPANY = I.COMPANY_A
      AND N.LEVEL_DETAIL_01 = I.ZONE_B
      AND I.RECORD_TYPE='L'
      AND I.SYSTEM='AP'

     LEFT JOIN GLINTCO J
      ON N.COMPANY = J.COMPANY_A
      AND N.LEVEL_DETAIL_01 = J.ZONE_B
      AND J.RECORD_TYPE='L'
      AND J.SYSTEM='ZC'
     where P.COMPANY=' & Company & ' AND TRANS_NBR in ('1500','1501','1502','1503')
     and I.AP_ACCOUNT_A is not null
     order by P.TRANS_NBR,P.INVOICE

Open in new window

0
Comment
Question by:kinsey
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Expert Comment

by:almander
ID: 36951792
The best option here is to create a View in the database. Then reference the view in the report.
0
 
LVL 3

Author Comment

by:kinsey
ID: 36951816
Can't touch the database! Just query, read only.
0
 
LVL 5

Expert Comment

by:almander
ID: 36952047
There is no way that I know of, to specify the actual Query text in Crystal.

However, I have gotten around this type of limitation by specifying the Join criteria in the Query. But, this is:
1. Not Officially Supported, or recommended.
2. Takes longer, and takes more resources to run the report.

For example, you could add the table in question (GLINTCO), and remove the links in the database expert (usually crystal warns you that this is not supported in some way).

Then in the Record Selection criteria, specify your join criteria.

  {N.COMPANY} = {GLINTCO.COMPANY_A}
  AND {N.LEVEL_DETAIL_01} = {GLINTCO.ZONE_B}
  AND {GLINTCO.RECORD_TYPE}='L'
  AND {GLINTCO.SYSTEM}='ZC'

This should effectively create the same result as the join would have.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 3

Accepted Solution

by:
kinsey earned 0 total points
ID: 36952201
Found the option,
Under data Sources clikd Add Command.
0
 
LVL 5

Expert Comment

by:almander
ID: 36952244
Very Nice
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36952381
That is the method.

Writ ethe command in the syntax of the database.  It will be passed to the database before the report is opened.

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37144299
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

834 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