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 Logged
