• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 849
  • Last Modified:

Crystal 2008 SQL command parameter

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
jrbledsoe001
Asked:
jrbledsoe001
  • 8
  • 4
2 Solutions
 
mlmccCommented:
When you say SQL Command are you referring to the Crystal command option?

mlmcc
0
 
jrbledsoe001Author Commented:
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mlmccCommented:
If you can't use Crystal selection formulas can you use Crystal pararmeters?

mlmcc
0
 
jrbledsoe001Author Commented:
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
 
jrbledsoe001Author Commented:
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
 
jrbledsoe001Author Commented:
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
 
mlmccCommented:
Are the parameters Crystal command parameters?

mlmcc
0
 
jrbledsoe001Author Commented:
Hello mlmcc,

The report needs to be revised so that only Crystal command parameters are used.  
0
 
mlmccCommented:
Do you need help creating them?

mlmcc
0
 
jrbledsoe001Author Commented:
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
 
jrbledsoe001Author Commented:
thank you
0
 
jrbledsoe001Author Commented:
Solved the problem with a begin and else if statement
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now