Solved

Crystal 2008 SQL command parameter

Posted on 2011-03-16
13
823 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
Comment Utility
When you say SQL Command are you referring to the Crystal command option?

mlmcc
0
 

Author Comment

by:jrbledsoe001
Comment Utility
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
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
If you can't use Crystal selection formulas can you use Crystal pararmeters?

mlmcc
0
 

Author Comment

by:jrbledsoe001
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:jrbledsoe001
Comment Utility
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
Comment Utility
Are the parameters Crystal command parameters?

mlmcc
0
 

Author Comment

by:jrbledsoe001
Comment Utility
Hello mlmcc,

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

Expert Comment

by:mlmcc
Comment Utility
Do you need help creating them?

mlmcc
0
 

Accepted Solution

by:
jrbledsoe001 earned 0 total points
Comment Utility
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
Comment Utility
thank you
0
 

Author Closing Comment

by:jrbledsoe001
Comment Utility
Solved the problem with a begin and else if statement
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

11 Experts available now in Live!

Get 1:1 Help Now