Skip to content

Crystal Reports XI: SQL Commands, Parameters and Prompts

January 21, 2010

One of my favorite things about Crystal Reports XI is the SQL Command. This is lamented by co-workers who don’t know SQL but sometimes have to deal with my reports, but I effectively decrease the network traffic (though all reports are run internal so it’s not a huge concern) and significantly offload the process onto the server. That said, they have a few stinging caveats.

First of all, let’s discuss Commands. They give you some more flexibility and often times for me they completely replace highly inefficient and potentially confusing subreports.

Let’s say you have a report that lists departments and has a parameter called @Company which is a Number type and you use in your selection criteria and maybe in some formulas to make sure you’re dealing with departments from the company the user enters only. Then you decide you want to pull information out of a few other tables and decide to do that with a Command object. You can have your report @Company parameter flow into your SQL Command object by simply ‘Add’ing a parameter in the Edit Command dialog with identical name and type and then inserting that parameter for use within. Let’s do a simple example:

SELECT Employees.Name, Employees.Department, Sales.Amount
FROM Employees LEFT OUTER JOIN Sales
ON Employees.Company = Sales.Company AND Employees.ID = Sales.EmployeeID
WHERE Company = {@Company}

Okay so now you have a command object listing Employees Names and Departments and their Sales amounts for the requested company. You could visually link that to the Departments table that you’re already displaying and do various informative summaries and whatnot. On to the issues.

  1. “I’ll just hide that for you” – This is one of my two least favorite ‘features’ of SQL Command objects as they pertain to CRXI. You have created the command object described above, you hit print preview, or print… and now it no longer asks you for a value for Company. You know that @Company is still in your parameter list, you know that @Company is still in your report selection criteria… why is it gone? No one knows. Simply preview the report without putting in a company (since you cannot anyway) and it will run with whatever value you put in when you created the command object and saved it and it prompted for a company. Just run it to appease the SQL Command object Gods. Directly after this if you re-preview the report and tell it to prompt for new values, Oila! your parameter prompts are back.
  2. “I’ll just get rid of that for you” – This is one of my two least favorite ‘features’ of SQL Command objects as they pertain to CRXI. This one is perhaps more sinister than the first one on this list. You’ve created the above command object and later decide to hard-code the company because all your sales are reported in Company 1 (or whatever reason). You remove the parameter from your SQL query and either do or don’t manually delete it from the parameters list (more on this pet peeve next), save the command object and… Suddenly your @Company parameter is no longer in your Field Explorer -> Parameters list. It has been deleted; since you no longer need that parameter for your command object, you could not POSSIBLY need it for anything else in your report (like the 15 other places it may be in use). Just recreate the parameter with the same name and type, save the report and either close/re-open it, or re-preview it and everything is fine.
  3. “Once saved, not always saved” – I mentioned this earlier but if you create a parameter in a SQL Command and then save/close the command without actually inserting it into the command, it disappears from the parameter list and will not be there when you go back. This is not as huge a deal, but the ERP I work with alternately uses Date and DateTime types for its date fields. So I know I want to use the EndDate parameter but forget to check its type. I go into the command, add a parameter, name it EndDate, realize I don’t know which type but choose Date for now, save/close the command, go to check the type, find out it’s DateTime, go back to the command and now I have to recreate the parameter. As I said, not really a huge deal but annoying. I created the parameter, chances are I want it there… let me implement it later.

I’m willing to admit that I probably over-use this tool, probably use it in ways it really wasn’t meant to be used, and probably am missing some setting(s) within CRXI (please tell me how to change the behavior if you know how) that would make them work more reliably… but something that could be so powerful (and yes I realize I could achieve this by creating StoredProcedures, or Views if I don’t need parameters, but I do a lot of 1-off reports and don’t want to clutter the database AND StoredProcedures have their own fun issues with Parameters in use with CRXI) just seems to miss the mark on usability.

Also note that I’m mostly writing this because I forget the #1 above EVERY time I run into it and waste 30 minutes trying to figure out how to get CR to prompt me for those values again.

Advertisements
6 Comments leave one →
  1. lamont permalink
    February 21, 2010 1:35 pm

    Rick, I’m new to CR and I’ve only created one other report that used a main and one subreport. In this subreport there was only one parameter passed to it. Not knowing any better I gave it the parameter the same name in the subreport as it was in the main report. Now I’m working on the second report. It has 4 parameters being passed from main to sub. The main report values are chosen from a static list and the subreport should be getting the values passed from main to sub. Now here’s the issue. When I do a preview and pass the parameters it does not appear as if they are getting into the command. I can check the parameters in the parameters fields and the values are there. But they are not showing up in the command itself. If I hard code a value in the command it works fine. To check to see if the value getting into the command I have placed the field in a record row…. and the value is not there. If I place the parameter on the report I can see the correct value. I noticed you mentioned something about naming the parameters from the main report to the sub report the same thing. Could that be my issue?

    L.

    • dotnetrick permalink*
      February 22, 2010 10:44 pm

      Lamont,

      I’m hoping that through our conversation you will figure out what is going on. If and when we are able to address the issues, I look forward to posting it here. There just isn’t much out there for SQL Commands.

      • dotnetrick permalink*
        February 24, 2010 1:22 pm

        Lamont did end up resolving the issue, but I honestly cannot say what it was that was the problem or what fixed it. I never saw the full code of the SQL Command but I believe one of my trouble-shooting/diagnosis prompts led to a fix. I’d love to make a post detailing what the issue and resolution are but I’m not entirely sure.

  2. November 29, 2011 6:11 am

    I don’t know if you ever resolved #1

    But the report option ‘verify on first refresh’ does the trick for us here.

    It seems strange that useful information on the working of SQL commands seems to be really hard to come by!

    • November 29, 2011 6:25 am

      Update: and ‘verify stored procedures on first refresh’ also/

  3. John permalink
    December 15, 2011 8:56 am

    Hi to all…

    Do you have problem by adding a second or a third parameter into the SQL command??

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: