Report Design
 Crystal Reports Forum : Crystal Reports 9, X, XI, 2008, 2011 : Report Design
Message Icon Topic: IF...THEN in Command WIndow Post Reply Post New Topic
Page  of 2 Next >>
Author Message
tigerlilly
Newbie
Newbie


Joined: 08 Feb 2011
Online Status: Offline
Posts: 9
Quote tigerlilly Replybullet Topic: IF...THEN in Command WIndow
    Posted: 08 Feb 2011 at 4:30am
I have a report that prompts the user to determine which report type it is (No Settlement Data, Settlement Only, ALL).  I want to set the SQL in the modify command window using IF...THEN...Else
 
DECLARE @RT as varchar(18)
SET @RT = {?ReportType}  -- Crystal Prompt
 
IF @RT = 'No Settlement Data' THEN
  BEGIN
     SQL1
  END
ELSE IF @RT = 'Settlement Only Data' THEN
  BEGIN
     SQL2
  END
ELSE
  BEGIN
    SQL3
  END
END IF
 
 
I can do this in access in 5 minutes.  I have wasted 8+ hours trying to determine if this can even be done.  Any assistance would be greatly appreciated.
 
Also, I forgot to add that I can run this SQL on the server side and it runs fine...  It just doesn't run in the command window in crystal.
 


Edited by tigerlilly - 08 Feb 2011 at 5:23am
IP IP Logged
c16271
Groupie
Groupie
Avatar

Joined: 24 Aug 2010
Location: United States
Online Status: Offline
Posts: 48
Quote c16271 Replybullet Posted: 08 Feb 2011 at 4:56am
Ha, welcome to the Crystal Reporting world. If you're just starting, let me tell you that you'll face these issues constantly, so don't get discouraged.

This is what I would do:

1. Create a view or command that brings back all the data.
2. Create a parameter against a field in that view, ie, recType

This means that you can write a decode statement, which tags each record with a type, meaning, if you have 4 records:

name    recType
---------------------------------------
bank1   nosettlement
bank2   settlement
bank3   settlement

3. Create a parameter handling formula

This formula will contain your conditional blocks.

4. Add this formula to the Record Selection
IP IP Logged
tigerlilly
Newbie
Newbie


Joined: 08 Feb 2011
Online Status: Offline
Posts: 9
Quote tigerlilly Replybullet Posted: 08 Feb 2011 at 5:30am
Thanks for responding so quickly.  I tried your suggesion originally, but the dataset pulls from a massive database (millions of claim records) and just destroys the performance of this report without the promts at the beginning.  The SQL for each report type is complex and completely different, but uses the same fields in the report.
 
These reports are for users that I support and performance is crucial.   
 
Can I even use an IF...THEN statement or SELECT CASE in the command window?   I keep getting syntax errors near 'IF' or near 'Then' or near 'ELSE'  It's as though crystal cannot evalute whether the statement is boolean.
 
I just find it very hard to believe that Crystal cannot handle a simple program function (IF...THEN...ELSE) in the command window.    Is that the case?
 
Also, I need to point out that I don't use the THEN keyword in the If then.  I just initiate the SQL if it is true.


Edited by tigerlilly - 08 Feb 2011 at 5:35am
IP IP Logged
c16271
Groupie
Groupie
Avatar

Joined: 24 Aug 2010
Location: United States
Online Status: Offline
Posts: 48
Quote c16271 Replybullet Posted: 08 Feb 2011 at 5:37am
In terms of performance, you shouldn't be experiencing a huge delay, if your filtering you data based on your parameters.

I understand, performance is crucial when it comes to reporting. Another thing, are you performing any SQL Optimization rules, also, what kind of database is it? 
IP IP Logged
tigerlilly
Newbie
Newbie


Joined: 08 Feb 2011
Online Status: Offline
Posts: 9
Quote tigerlilly Replybullet Posted: 08 Feb 2011 at 6:54am
Thanks for taking the time out to answer my questions.   Database is SQL Server 2005 with optimization (proper indexing, etc).   The problem is not the SQL; it runs extremely fast.  The problem is that the report cannot determine which SQL to run because the IF....THEN cannot be evaluated for whatever reason.  I find that impossible to believe that Crystal cannot handle this simple function in the modify command window.
 
When dropping the prompts at the command window, it takes over an hour for crystal just to refresh after I modify the command so I would am tied up for an hour waiting for the data to refresh (every time I modify the command).   That was becoming too costly in development time.   That's why I decided to use prompts in the command window vs. in the Selection Expert; now it takes less than 2 minutes to refresh the data.  It takes less than a minute to run the IF...THEN command from within SQL Server 2005; very fast considering how massive the database is.  WHen I run a stand alone report for a specific report type (No Settlement Data) which the command uses static filters and DOES not have to determine which SQL to use, it runs in 2-3 minutes.  I just want to be able to use this same report but tell Crystal to use this other SQL if the user enters a report type prompt for say NO SETTLEMENT DATA.  
 
I do appreciate you trying to assist me, but you didn't answer the KEY question about the IF...THEN function.   Nobody can seem to answer that question; I've asked a 1/2 dozen "IT professionals."  
 
Can I use an IF...THEN....  or SELECT CASE Function statement in the command window in Crystal as I would on SQL Server?  Not in an expression included in a filter, of select case to select a value using SQL for a specific field value, but as pogramming function to determine which SQL to send as a command as indicated by the IF...THEN... statement in the original post.
 
I don't want to have to create 50 formula fields and write an if then formula in every one of them to determine which command.fields to use in this report when the user enters which report type it is.  That seems like a very hard way to go; plus 50 formulas run for every record that gets pulled will kill the performance of this report as well.  It may time out and the user will not get their data.
 
As a developer, I have to say that Crystal appears to be extremely limited in how to select datasources on the fly based on a prompt.   In Access I can write a simple IF...THEN and set the recordsource property on the OnOpen event of the report which is very developmentally friendly.   Crystal does not allow you to do that which is killing my efforts to use one report with one command using a Select Case or IF...THEN function to set the SQL.  As it is now, I have 9 reports that are the same in format (same fields, set up, etcs) but using different SQL.  In an effort to achieve production efficiency, I would like to manage 1 report vs. 9.  I know in the future we will do this with many shared reports (with different datasources) so I need to figure out how to use different a single command with a SELECT CASE or IF function based on report type prompts entered by the user.  There has to be a way.
 
Unfortunately, I have no choice but to do these reports in Crystal or I just would have done them in Access.  They are published to Business Objects and run by the users using a schedule or adhoc schedule.  Any report running longer than 1/2 hour in that environment will time out and the user will not get their data.
 
 
 
 
 
 
 
 
 
IP IP Logged
DBlank
Senior Member
Senior Member


Joined: 19 Dec 2008
Online Status: Offline
Posts: 7897
Quote DBlank Replybullet Posted: 08 Feb 2011 at 7:01am

I do believe there is a way to use a dynamic data source but not exactly as you are trying to do it. I am not sure what that process is as I have not had to deal with it but there are many posts re: dynamic data sources in this site usually under the data connection portion.

Another possible soution would be to create one stored proc using your parameter to determine which "source" you want to use and have it run your sp as desired so rather than trying to use a dynamic source you use one source that is dynamic.


Edited by DBlank - 08 Feb 2011 at 7:02am
IP IP Logged
tigerlilly
Newbie
Newbie


Joined: 08 Feb 2011
Online Status: Offline
Posts: 9
Quote tigerlilly Replybullet Posted: 08 Feb 2011 at 7:12am
Thanks for responding.  The stored proc idea is an excellent one which I thought of already, but us developers here on my team are so restricted that we do not have write privileges to create them; let alone have permission to put them out there in production environment.  I know, that's no way to run a development shop, but it's not my call.  It's just how it is here.  I'm hangin from a noose that tightens every day with the restrictions they have here; it's very hard to do my job effeciently.  Work-arounds always take more time.
 
Dynamic datas source is something I haven't heard until now.  I will look into it.  Thank you for the suggestion.
 
My question still has not been officially answered.   Can you use IF...THEN....ELSE or SELECT CASE as a programmatic function (not expression)  in the command window of Crystal reports?  YES or NO?
 
Really, that's all I want to know so I can stop wasting my time believing I can manipulate Crystal into being able to.
 
 
 
IP IP Logged
DBlank
Senior Member
Senior Member


Joined: 19 Dec 2008
Online Status: Offline
Posts: 7897
Quote DBlank Replybullet Posted: 08 Feb 2011 at 7:23am

in a command object (as a data source) you can use SQL code and a case statement in it so maybe you could try to replicate the process here but your struggle is more about limiting the data and defining the source at call time rather than at display time. I don't think you can use a declared variable in a command object but you might try that...

IP IP Logged
DBlank
Senior Member
Senior Member


Joined: 19 Dec 2008
Online Status: Offline
Posts: 7897
Quote DBlank Replybullet Posted: 08 Feb 2011 at 7:33am
another possible work around would be to create 3 sub reports thata re conditionally suppressed based on a param.
Suppressed subreports do not run so they would not kill performance, however sub reports do not allow for additional sub reports and act a little different on drill down.
IP IP Logged
tigerlilly
Newbie
Newbie


Joined: 08 Feb 2011
Online Status: Offline
Posts: 9
Quote tigerlilly Replybullet Posted: 08 Feb 2011 at 7:35am
I've tried using a variable in a command object, and don't get any errors...however, the command doesn't run because of syntax errors so it may be blowing up before it returns the declared object error.   I don't really know..there are so many possibilities in the world of development.
 
I tried to run the same process not using a Declared variable where I just look at the {?ReportType} prompt using the IF...THEN function...but that just returns syntax error 42000 syntax error next to 'Settlement' and ELSE...
 
I'm really starting to hate Crystal.  She is not a model of developmental efficiency.
 
 
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.