Solved

Crystal 2008 SQL command parameter

Posted on 2011-03-16
13
835 Views
Last Modified: 2012-06-27
I have a Crystal financial odbc report based of a SQL database.  The report uses a complex SQL command comprised of several union queries.  The report contains a Crystal selection formula which determines if the financial report will summarize at the corporate summary level or print records at the detailed general ledger department level.  

The report contains a Crystal parameter for the user to select 1 if they want to run the report with option #1 summary mode or option 2 at the GL department level.  Corporate general ledger department postings are in departments starting with 7xxx to 9xxx.  Non corporate departments start with 1xxx and end with 6999.

When the user selects 1 (summary) the report pulls all data from 1xxx through 9xxx.  When the user selects 2 (detail) the report pulls data from 1xxx to 6999 and excludes data from 7xxx to 9999.  

Due to a limitation with a 3rd party reporting tool I can not use Crystal selection formulas.  Additionally I can not use stored procedures or views on the server.  I’m looking for a way to pass the user selection into a SQL command parameter.

The current Crystal selection formula is
If {?Report Level} = 1
Then true
Else if command.acct_unit <> “7000000” to  
Then true
Else false

Any input on how to create a SQL command parameter to choose which departments to pull from the database?
0
Comment
Question by:jrbledsoe001
  • 8
  • 4
13 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35152972
When you say SQL Command are you referring to the Crystal command option?

mlmcc
0
 

Author Comment

by:jrbledsoe001
ID: 35153557
Hello mlmcc,

Thanks for responding so quickly to my post.  The report uses a SQL command entered into the data source window; example Crystal Data base Expert edit or view command.
0
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 500 total points
ID: 35153752
The report uses a complex SQL command comprised of several union queries.  The report contains a Crystal selection formula which determines if the financial report will summarize at the corporate summary level or print records at the detailed general ledger department level.  

Actually, you're not supposed to use Crystal selection formula and parameters with a Command at all.  Best practices would be building the parameter directly into the SQL Command.  That's because Crystal selection formulas and parameter aren't used by the command.  Instead, the command pulls back every possible record, which is then filtered out on the client after the command has already been run - horribly inefficient and causes increased database and network traffic.

You will need to open the command and add the parameter directly into the command (there's a button for it) and then incorporate it into the SQL.  Without seeing your entire command, however, it's tough to tell you how to incorporate the parameter into the WHERE clause (or clauses, since you're using multiple unions) of the command.  Are you allowed to post the SQL from the command?

~Kurt
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 100

Expert Comment

by:mlmcc
ID: 35153859
If you can't use Crystal selection formulas can you use Crystal pararmeters?

mlmcc
0
 

Author Comment

by:jrbledsoe001
ID: 35156215
Kurt,

Your post is very helpful!!  We are running this report from a server.  The report does take some time to refresh to screen and the server performance takes a hit when the report runs.  It's good to know that we shouldn't try to use Crystal selection formulas with SQL command parameters.  
0
 

Author Comment

by:jrbledsoe001
ID: 35156794
Here is a mock up of the SQL statement for reference.  I stripped out unnecessary fields.  The report uses general ledger posted amounts, budgeted amounts, general ledger statistics, budgeted statistics; hence the need for multiple union queries.  This example only pulls posted amounts and budgeted stats.  

The report has a SQL command parameter which prompts the user to enter the fiscal year.  The name of the SQL command parameter is Fiscal Year.

Can you help me understand how to create another SQL command parameter to include all  accounting units (1xxx-9999) when a user selects the summary option and to exclude accounting units (7xxx-9999) when a user selects detail option?

(select
GAM.COMPANY,
GAM.ACCT_UNIT,
GAM.ACCOUNT,
GAM.FISCAL_YEAR,
0 BUDGET_NBR,
'AAM' EXTRA,
GAM.CR_BEG_BAL + GAM.DB_BEG_BAL NET_BEG_BAL_AMOUNT,
GAM.CR_AMOUNT_01 + GAM.DB_AMOUNT_01  NET_AMOUNT_01,
0 NET_BEG_BAL_UNITS,
0 NET_UNITS_01
from GLAMOUNTS GAM

where GAM.FISCAL_YEAR in ( {?Fiscal Year} , {?Fiscal Year}-1 )
and     GAM.COMPANY = 1
and     GAM.ACCOUNT <> 800000
 )

UNION

(select
FBD.COMPANY,
FBD.ACCT_UNIT,
FBD.ACCOUNT,
FBD.FISCAL_YEAR,
0 BUDGET_NBR,
'BST' EXTRA,
0 NET_BEG_BAL_AMOUNT,
0 NET_AMOUNT_01,
FBD.CR_BEG_BAL + FBD.DB_BEG_BAL NET_BEG_BAL_UNITS,
FBD.CR_UNITS_01 + FBD.DB_UNITS_01   NET_UNITS_01,
FBDETAIL FBD

where FBD.FISCAL_YEAR in ( {?Fiscal Year} , {?Fiscal Year}-1 )
 
and     FBD.COMPANY = 1
and     FBD.ACCOUNT <> 800000
)
0
 

Author Comment

by:jrbledsoe001
ID: 35173165
hi mlmcc

I didn't see your question about using Crystal parameters instead of Crystal selection formulas.  We are trying to find an alternative to using both Crystal parameters and Crystal seleciton formulas by moving all the code logic into the SQL statement.  The user will enter values into SQL command parameters which will be passed to the SQL statement.  We encountered a limitation in another 3rd party tool which has problems with Crystal SQL command reports with complex crystal selection formulas.  

joanna
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35173263
Are the parameters Crystal command parameters?

mlmcc
0
 

Author Comment

by:jrbledsoe001
ID: 35173909
Hello mlmcc,

The report needs to be revised so that only Crystal command parameters are used.  
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35173928
Do you need help creating them?

mlmcc
0
 

Accepted Solution

by:
jrbledsoe001 earned 0 total points
ID: 35173964
I think I solved my own problem.

This syntax seems to be working

If {?Report_Level} = 1
     
begin
(select... )
UNION (select...)
end
      
else
      
begin
(select ...)
UNION (select...)
end
0
 

Author Comment

by:jrbledsoe001
ID: 35173973
thank you
0
 

Author Closing Comment

by:jrbledsoe001
ID: 35205141
Solved the problem with a begin and else if statement
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…

685 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