Skip to Content
Technical Articles
Author's profile photo Dell Stinnett-Christy

Best Practices When Using Commands with Crystal Reports

In version 9 and newer Crystal has a feature called a “Command”.  A Command is a SQL Select statement that the user can write to extract data for a report.  Commands are available for most, but not all, databases.  To determine whether a Command can be created for a specific type of connection, open the connection in Crystal; the first line under the name of the open connection will be “Add Command”.

Commands use the SQL syntax of the database that is being connected to. So, for example, when connecting to MS SQL Server, the “getDate()” function is used to get the current date but in Oracle it would be “SysDate”.  Crystal formulas cannot be used in a command, so any calculations or filters must use SQL syntax.

What follows are some best practices for working with Commands in Crystal.

A Command should include ALL of the data required in a report.

When a Command is linked in the Database Expert with other tables or Commands, Crystal is unable to push the joins to the database for processing. Instead, all of the data from the Command along with all of the data from the tables is pulled into memory where Crystal will process the join in memory.  With inner joins, this frequently means that much more data is brought into memory than will actually be used on the report.  As a consequence, joining Commands to tables or other Commands can significantly slow down report processing

Any data filtering MUST be done in the Where clause of the Command.

When the Select Expert is used against a command, Crystal is once again unable to push the filter down to the database and instead pulls all of the data into memory and processes the filter there.  Just like when a Command is joined to tables or other Commands, this can significantly slow down report processing.

SQL Expressions.
It is not possible to create or use SQL Expressions in the Field Explorer for the report when using a Command. Instead, the syntax of the SQL Expression  should be used in the Command itself as either an additional “field” in the Select clause or as part of the Where clause.

Parameters.

1.  Parameters that are used in a Command MUST be created in the Command Editor.  The Command Editor cannot “see” parameters that are created in the Field Explorer for the report.  However, in the Command Editor only the Parameter Name, Prompting Text, Value Type, Default Value, and whether the prompt allows multiple values can be set.  After the Command is saved, the parameters can then be modified in the Parameters section of the Field Explorer to set additional properties.

Also, if a parameter is created but not used in the Command Editor, the parameter will not actually be created for the report.  However, once a parameter is created in the Command Editor and used in a Command, it can be modified in the Parameters section of the Field Explorer for the report. In this way the parameter can be made dynamic and almost any other properties can be set.

2.  When a parameter is a string value, it must be surrounded by single quotes in the Command in order for it to work.  When a parameter is multi-select, use “in” instead of “=”.  So, a where clause might look like this:

Where MyTable.StringField = {?String Param}

and MyTable.NumberField in {?Multi-select Number Param}

and MyTable.DateField = {?Date Param}

3. Parameters that are used in Commands CANNOT be optional.   Instead, I generally use a default of “*All” (the asterisk causes this to sort to the top in dynamic parameters) to indicate that all values should be selected.  In the Where clause, it would be used something like this:

Where (‘{?String Param}’ = ‘*All’ or MyTable.StringField = ‘{?String Param}’)

4. DO NOT use the Command that provides data for the report to provide data for dynamic parameters.  The parameters will not show any field values until after the Command has been run. Instead, do one of the following:

a.  If the report is being run through BusinessObjects (BOBJ) or Crystal Reports Server (CRS), use the Business View Manager that is installed with the Client Tools to create the List of Values that will provide data to a dynamic parameter.  In general, ALL dynamic parameters for reports that are running in BOBJ or CRS should be created and managed in the Business View Manager – it’s more efficient and allows for parameters to be reused instead of recreating them for every report.  At some point in the future I plan to post a blog that walks through how to do this.

b.  If the report is not being run through BOBJ or CRS, then do the following:

      • In the Database Expert, add a Table or Command
        that will provide the data for the parameter.
      • DO NOT link this Table or Command to anything.  Crystal will throw a warning that this is generally not supported.  This warning can be ignored because this type of configuration is supported for this purpose.
      • Use fields from the new Table or Command in the
        parameter configuration to provide data for the parameter.
      • DO NOT use fields from the new Table or Command
        anywhere else in the report.

When these best practices are followed, Commands can become a very powerful tool to provide data in Crystal Reports.  Especially since there are things that can be done in a Command that cannot be done when linking tables in Crystal.  For example, it is possible to filter dated data to show only the most recent date, use a “Having” clause to show only data that meets a certain summary threshold, work with “Union” queries, or even pivot data so that it can be shown as if it were a cross-tab without actually using a cross-tab.  Just about anything that can be done in a SQL query in a database can be done in a Command, which makes it a powerful tool for reporting.

(Edited 2/8/2022 – added info about setting parameter properties.)

Assigned Tags

      72 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      wondeful

      Author's profile photo Former Member
      Former Member

      Thank you for this post. It was helpful to know these limitations and "gotchas". 

      Author's profile photo Former Member
      Former Member

      Dell,

      your information in the section above is very informative. I really appreciate the explanation especially to some who are new to Crstal reports and how the add command works effortlessly.

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      I'm glad it was helpful!

      -Dell

      Author's profile photo Mark Richardson
      Mark Richardson

      As with most things this COMMAND Feature works better / easier in CR-2013 than in CRYSTAL REPORTS FOR ENTERPRISE.

      Hope that CR4E gets some "love and attention" in 2016 from a User Experience and Customer Feedback P.O.V.

      Lot's of little things are either missing or too-cumbersome in the current release.

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      I absolutely agree with you Mark.  I've been using Crystal for over 19 years and I avoid CR4E when I can - I just can't make it "sing" like I can "classic" Crystal because the features just aren't there.

      -Dell

      Author's profile photo Former Member
      Former Member

      Great post! I wish I could read it last week. I wasted several days struggling on providing data for dynamic parameter until I read it today. Thank you, Dell.

      Author's profile photo mark Johnson
      mark Johnson

      Dell, I just want to confirm...the Select Expert information....

      Any data filtering MUST be done in the Where clause of the Command.

      When the Select Expert is used against a command, Crystal is once again unable to push the filter down to the database and instead pulls all of the data into memory and processes the filter there.  Just like when a Command is joined to tables or other Commands, this can significantly slow down report processing.



      If I want to select invoices by date (considering we have a lot of invoices).... the SQL Command would bring back all of the invoices and if I add a Select Expert filter...


      example...


      {Command.InvDate} IN (

      IF {?DateRange}= "YearToDate" THEN YearToDate

      ELSE IF {?DateRange}= "LastFullMonth" THEN LastFullMonth

      ELSE IF {?DateRange}= "MonthToDate" THEN MonthToDate

      ELSE IF {?DateRange}="LastFullWeek" THEN LastFullWeek

      ELSE IF {?DateRange}= "Yesterday" THEN CurrentDate-1

      ELSE CurrentDate

      )

      The above Select Expert filter/formula would be run AFTER the SQL command is run ?

      after it pulls back all of the invoices...

      so, the only way to have an effect on the sql command before it is executed, is to have sql command parameters...

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      That is correct.  In the command, I might rewrite it like this for the Where clause (assuming you're on SQL Server - syntax may be different for other databases):

      table.InvDate >=  --Get the start date of the time period

        (Case

           when '{?DateRange}' = 'YearToDate' then (GetDate() - DatePart(dy, GetDate()) + 1)

           when '{?DateRange}' = 'LastFullMonth' then (DateAdd(month, -1, GetDate()) - DatePart(d, GetDate()) + 1)

           when '{?DateRange}' = "MonthToDate" then DateAdd(d, (-1 * DatePart(d, GetDate())), GetDate()) + 1

           when '{?DateRange}' = 'LastFullWeek' then DateAdd(ww, (-1 * DatePart(w, GetDate())), GetDate())

           when 'Yesterday' then GetDate() -1

           else GetDate()

         end)

      table.InvDate <= --Get the end date of the time period

      (Case

           when '{?DateRange} = 'LastFullMonth' then DateAdd(d, (-1 * DatePart(d, GetDate())), GetDate())

           when '{?DateRange} = 'LastFullWeek' then DateAdd(d, (-1 * DatePart(dw, GetDate())), GetDate())

          else GetDate()

        end)

      I can translate this to Oracle if that's what you need.

      -Dell

      Author's profile photo mark Johnson
      mark Johnson

      This is great, thanks for the above input

      Author's profile photo Former Member
      Former Member

      I have to add a comment in the sql command, but it taking forever to get back to save, is there a way that you can save it without having crystal go back to check the original code ?

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      No.  Crystal has no way of knowing what type of changes were made so it will verify the query for ALL changes made in the Command Editor, even if it's just an added comment.

      -Dell

      Author's profile photo Former Member
      Former Member

      I'm using Crystal Reports 2013.
      I have searched for a solution to my question but have not found one.
      I have 2 static parameters (P1 & P2).
      I want to create a dynamic parameter (P3) that uses the 2 static parameters to limit records listed in the dynamic parameter.

      I initially thought a Dynamic Cascading Parameter would work, but it forces the user to re-enter the first two parameters (P1 & P2).

      Then I looked at a Command, but a Command forces the parameter definition to be within the Command Editor, and the user would be forced to re-enter the first two parameters, just as with a Dynamic Cascading Parameter.

      I have used Crystal Reports before, but I'm definitely not an expert.

      Any suggestions?

      Scott

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Try this:

      Use the Command for the fields for the Dynamic Cascading Parameter ONLY - do NOT use its fields anywhere else!  Create the two static parameters for the command only - do not create them in the Field Explorer. If you need them elsewhere in the report, you can use them after they've been defined for the parameter.

      After you've created the first two parameters in the Command and saved it, edit them in the Field Explorer to add any static values that you might need.  Then create the dynamic parameter using values from the command.

      Your report should now prompt for the two static params, then for the dynamic one.

      -Dell

      Author's profile photo Ross Coker
      Ross Coker

      Dell,

      I've done a lot of research over the past 2 days regarding "Parameters that are used in a Command allowing multiple values to be set".  I've got a Crystal Report which uses a Command for the entirety of the report and has 6 parameters provided via the "Modify Command" window.   2 of those 6 parameters are multiple value parameters

      Whenever we run the report and pick 0-All or any single value in either of those two parameter fields it works fine, but as soon as we enter more than one parameter in either of those two, it generates an error.  The Command WHERE clause seems to be correct as it relates to those two parameters:

      and (0 IN ({?Parameter1}) OR tablealias.field1 IN ({?Parameter1}))
      and (0 IN ({?Parameter2}) OR tablealias.field2 IN ({?Parameter2}))

      but whenever more than one value is used in either parameter within the "Enter Values" parameter screen and the report is run, it spits out a Failed to retrieve data from the database message box, with another notation of Incorrect syntax near ','

      I've done a lot of research recently that implies older versions of Crystal Reports couldn't handle the this sort of multiple value parameter situation, but your post didn't seem to have any caveats to this situation, so I'm wondering if this is truly still an issue.  I'm using Crystal Reports 2013 SP 5 and am having this issue.   Many tech/help websites claim this is an issue but all of the posts similar to below are fairly dated:

      "The limitation is that CR Commands do not have a mechanism to allow for the use of multivalue parameters.  You cannot simply put in the command sql "table.fieldname IN ({?Parameter})" because the parameter is technically an array in crystal that is not comma delimited in a parsable string by a database server".

      I'm hoping you can help me understand if this is still an issue with my version of CR or if there is something else going on that is causing my report to not be able to interpret multiple values even though they are set up as such in both the SQL and in the Command Parameter setup (via the "Allow multiple values" checkbox.

      Thanks,

      Ross

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      You need to remove the parentheses around the parameters so that it looks like this:

      and (0 IN {?Parameter1} OR tablealias.field1 IN {?Parameter1})
      and (0 IN {?Parameter2} OR tablealias.field2 IN {?Parameter2})

      Crystal will automatically add the parentheses with multi-value parameters.

      -Dell

       

      Author's profile photo Ross Coker
      Ross Coker

      Thanks!  I had since found out I was using an outdated copy of the report.  The newer version of the report had made the exact corrections to that portion of the code, that you suggested.

      Author's profile photo Former Member
      Former Member

      Hi -- I am passing the multi values parameter form parent report as sql recognizable format.  It will be like 'ABC','CDE'

      I am using this parameter in the child report command editor. Should I use it as network in [{?NETDS}]. Or without any braces?

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      So you're sending this list of values as a single string?  I haven't ever done this, but I would try it with parentheses.  Remember that in the command you're using the syntax of the database not Crystal's syntax.  If your database requires square brackets for "in" clauses, then use the square brackets. But most databases require that the list be between parentheses like ({?NETDS}).

      -Dell

      Author's profile photo Rayna Curtis
      Rayna Curtis

      Hi Dell,

      I am having a struggle with a Crystal where the command is in a subreport.  At the end of a report I have built showing products the client bought, I am then trying to list all items that did not appear on the top part of the report...ie all items that the client could have purchased but did not.

      So...I am handing in a parameter for the Purchase ID from the main report, and selecting all items in the "purchased" table in the subreport.  Then I am saying (in a Command) give me all the items in the Product table that aren't in the Purchased table.  I created the parameter for the Purchase ID, and put it in the Command.  I then hand in the Purchase ID from the top level...and I match them up in the SQL Select.  But the Crystal still insists on prompting for both the original Purchase ID as well as the second one I built for the command.  Do you have any ideas what I might be doing incorrectly?  I would appreciate any guidance!

      -Rayna

       

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Have you linked the two prompts?  To do this, right-click on the subreport in the main report and choose  "Change Subreport Links...".  Under "Available Fields" on the top left, select the Purchase ID from the main report and move it to "Field(s) to link to" at the top right.  Uncheck "Select data in subreport based on field" on the bottom right.  On the bottom left, select the subreport parameter under "Subreport parameter field to use:"

      This will link things together so that the parameter in the subreport will get its value from the main report.

      -Dell

      Author's profile photo Rayna Curtis
      Rayna Curtis

      You're a genius!!  I have been working with Crystal Reports for more than a decade, and I don't believe I have ever touched that lower left hand field...I only ever selected the lower right hand one, to join the left one and the right one!

      Thank you ever so much!

       

      Author's profile photo Former Member
      Former Member

      Hi Dell,

       

      I've been using Crystal since the mid '90s but have only started working seriously with the command in Crystal in the past few years.

      My issue is, I need to add two dynamic cascading parameters to my where clause in the command, based on fields in the database (Region - Postcode).  So depending on which Region(s) you pick, you get a subset of all the postcodes in that selection - plus the 'All' option.

      The report also has to be able to select 'All' Regions.

      My command is made up of 2 main Select statements - One creates a one-line summary per Region. and one is a Total level one line summary.

      The reason for this is that I need to do a distinct count of a 'PersonNumber' field that can be replicated across a few Regions.  I need it to count once per Region that its in, and once for the Total.

      I have already put my date range param into the Where clause, but I am stuck when it comes to the rest.

      I have a static table that holds the values for the Region/Postcode, and these need to be able to select 'All' as a value.

      I also need to add a couple of other list based parameters from the database code fields.

      Would love your assistance with this.

      Thanks 🙂

      Author's profile photo Former Member
      Former Member

      It wouldnt let me edit so....

      Just a thought - Can I create the parameter in Crystal and send the result back to the Command?  Along the lines of {?Command Param} = {?CrystalParam Multivalue Selection}

      Where the crystal Parameter is populated with an unlinked table (EG RegionMapping) and the result is sent back?

       

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      As I note above, you’ll have to create the parameters in the Command Editor in order to be able to use them in your command.  I would add something like the following command to your report to provided data to your dynamic prompt (assuming SQL Server back-end):

      Select ‘*All’ as RegionCode, ‘*All’ as PostalCode”

      UNION ALL

      Select RegionCode, PostalCode from Regions

      Order by RegionCode, PostalCode

      Do not link this to your other command, use it just for your dynamic prompt.  I use “*All” because it will always sort to the top.

      In your other command, create the prompt for Postal Code and add something like the following to your where clause:

      and ({?PostalCode} = ‘*All’ or MyTable.PostalCode in {?PostalCode})

      Save your command.  In the Field Editor, edit the {?PostalCode} parameter, make it dynamic, and use the new command to make it a cascading prompt.

      -Dell

      Author's profile photo Former Member
      Former Member

      Hi Dell,

      I have a report that I created in Crystal Reports 2016 that pulls data based on a begin and end date parameter. The report runs as expected when initialed from Crystal.  I get prompted for the dates.

      Here is the parameter section:

      AND m.OrdDat BETWEEN {?BeginDate} AND {?EndDate}

      However, when I import this report into our  McKesson HomeCare application, I get prompted for the parameters, but the report returns blank.  If I upload the same report without any parameters, the data displays correctly.   If I upload a report with the dates hardcoded

      and m.OrdDat BETWEEN '20180201' and '20180228'

      I get the expected results .from the application.  Could this be a compatibility issue with the application?  There are some custom reports that were generated in a previous version of CR that do work within the application fine.  I dug a little deeper and found that if I look at the SQL Query of the older report, the parameters portion of the code looks like this:

      WHERE ("DocVstCtn"."DatIn">={ts '2018-02-01 00:00:00'} AND "DocVstCtn"."DatIn"<{ts '2018-03-01 00:00:00'})

      And in CR 2016 Show SQL Query, I still get the parameter name, but not the parameter value.

       

      m.OrdDat BETWEEN {?BeginDate} AND {?EndDate}

      Any thoughts or insight would be immensely appreciated.

      -David

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Hi David,

      When you show the SQL Query in the new report, have you entered the date parameters yet?  If not, run the report so that you have to enter them and then see what the query looks like.  Those parameters won't have any values unless you enter them before looking at the query.

      I vaguely recall reading a question on the forums about dates being handled slightly differently, but I'm not sure where and which version that was talking about.  I suggest asking this question and setting the primary tag to "SAP Crystal Reports"  so we can see what Don Williams from SAP might have to say on the subject.

      -Dell

      Author's profile photo Former Member
      Former Member

      Hi Dell,

      I'm working on a Crystal report using SQL Server and I'm using a Command to query a view since the dataset is huge. I was able to add a parameter in the command for a date, but the data set was still over 12 million records. I need to filter by some of the other required parameters, which are currently field parameters, and they are all a list of static strings that allow for multiple selects. When I added these parameters to the SQL command it seems like they are only seeing the first value and not the other selected values for each parameter using the syntax :

      AND ([PlantWhse.Plant] in {?Plant}) AND ([PlantWhse.WarehouseCode] in {?Warehouse})

      If I wrap either one of these in () such as ({?Plant}) I get a SQL error about not being able to identify ',' and no records are returned. I need to get something like AND ([PlantWhse.Plant]  in ('Plant01', 'Plant02') etc.  I can get the SQL query to return records with this criteria, but I don't think the Command parameter is functioning that same way. Any advice?

       

      thanks,

      Kevin

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Hi Kevin,

      What you have looks like it should work.  Can you post a screen shot of how the parameters are set up - not the definition in the Command Editor, but the one in the Field Explorer.

      Thanks!

      -Dell

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Also, if you look at "Show SQL Query" on the database menu after you've set the parameters and run the report, it should show you how it is passing the parameters to the report.

       

      Author's profile photo Former Member
      Former Member

      I did the show SQL query and it just shows this

      Where ( [PartTran.TranDate] <= {?AsofDate}) AND ([PlantWhse.Plant] in {?Plant}) AND ([Part.ClassID] in {?Part Class})

      It turns out the issue I had was with the parameter itself. The warehouse codes were used in the report as a left(4) of the actual code, so the SQL query couldn't do that in a where clause for a list of strings.

      The {?Plant} and {?Part Class} are both multi-select strings that do work in the WHERE clause of the command. This blog was a huge help.

      Thank you

      Author's profile photo ZEEK ZEEK
      ZEEK ZEEK

      Hi Dell,

      Can't believe your post has made all the way from 2016 to 2018. I have a silly question/issue. I have a report that is built by importing tables from the connection and then uses select expert to filter. I need to bring in another table for a field to filter on so that i can restrict the data population coming in. The issue is when i bring that table in and join it and then filter on a column from this new table, it places that column in my select clause of the query, now you may ask "what is the harm in it?". The harm is that this filed can store multiple values per one unique record, which throws off my distinct counts, making my distinct records count multiple times because of this one field.

      How can i make sure this field does not come in my select clause so that my counts do not go crazy?

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

       

      You should really ask this as a question in the regular forums.  Having said that, what you’ll do in your command is take the field out of the Select fields and add the word “Distinct” after the “Select” at the top of the query.

      -Dell

      Author's profile photo ZEEK ZEEK
      ZEEK ZEEK

      I have the option of re-creating the report using command statement but i wanted to see if i was missing a small setting somewhere or something that can prevent bringing in the column in the select of the report (when the report is creating by importing tables).

      Sorry, yes you are right, i should have posted in the regular forums, thanks for your response though, i appreciate it.

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Unfortunately, there is no way to tell Crystal to not put that field into the select list if your linking tables together - it's just the way Crystal works.

      -Dell

      Author's profile photo Masood Ahmed
      Masood Ahmed

      Hello Dell,

      I reached this article while troubleshooting an issue with a crystal report using dynamic parameter. The article is very helpful. Thanks for sharing!

      I have a crystal report that has three input parameters. Two parameters are static "Date" type parameters. The third parameter is a "Dyanmic" parameter that is using an existing data source which is the result of a "Command" (i.e., list of user names that I want to show up in the prompt as a dropdown list). I uploaded this report on my C# based web application. When I run the report, the crystal report viewer gives prompts to enter the database logon credentials again before prompting to enter parameter values. Not sure why it's prompting even though the credentials are assigned at runtime using C#. I have other reports that do not use dynamic parameters and are not prompting to enter database logon credentials.

      Can you please help me troubleshoot this by sharing your knowledge? Attached is the screenshot of the prompt I am getting.

      I am using CR 2016 Support Pack 3 - Version 14.2.3.2132

      Thank you,

      -Masood

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Are the command for the dynamic params and the rest of your report using the exact same connection?  Having different connections is the only thing I can think of that might cause this unless you're using the Business View Manager to do the dynamic param.

      -Dell

      Author's profile photo Masood Ahmed
      Masood Ahmed

      Dell, thanks for the quick response. Really appreciate it! Yes, the command for the dynamic params and the rest of the report is using the same connection. Actually, the main report has ONLY one parameter which is dynamic and is using the command. This dynamic parameter value is being passed to the sub-report which has other static fields and a command that is connecting to the datasource using the same connection which is being used in Main report.

      Is it possible to create different connections to the same data source?

      I also tried creating two different connections. One for the Main report which is connecting to "Server 1" and the other connection in Sub report which is connecting to "Server 2". Both servers have the same database. But still I am getting the same database logon prompt. Any suggestions? Not sure where I am going wrong. Is it because I am passing the dynamic parameter value to the subreport? Please advise.

      Thanks,

      - Masood

       

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      How is the parameter defined in the subreport?  Is it defined to just take in the value from the link or is it defined as a dynamic param there in addition to where it's defined in the main report?

      Also, your command and the rest of the data in the report should be on the same data connection - not different ones.

      Are you running the report from Crystal itself or through another application?

      -Dell

      Author's profile photo Masood Ahmed
      Masood Ahmed

      Hello Dell, yes, the original report and the sub-report are using the same connection for the command and the rest of the data.

      The parameter defined in the subreport is a static "String" type parameter which takes its value from the main report from the link.

      I am running the report from a .NET based web application. The report works fine on crystal, but when it runs on crystal reports viewer via web application. It gives the additional database logon prompt. Also, the database credentials are assigned by C# code.

      I came across the following article discussing the same issue, but did not find an answer to the problem I am facing.

      https://archive.sap.com/discussions/thread/3645433

      Please advise.

      Thanks

      -Masood

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Hi Masood,

      Unfortunately, I don't have an answer for you. You should probably re-post your question to the "SAP Crystal Reports" where it will get a wider audience, including a couple of folks who work for SAP, who may have a better answer for you.

      -Dell

      Author's profile photo Masood Ahmed
      Masood Ahmed

      Dell, No problem. Thanks for your quick response! I will try re-posting the question.

      -Masood

      Author's profile photo Brian Hawk
      Brian Hawk

      Hi Dell,

      I'm new to Crystal report and want to use SQL command to retrieve the data from database. search and find this article and help me lots.  The problem I have is the parameters window does not prompt after I deployed it, but I do see parameters when I save the command.

      In the command window, I created two parameters, CompanyCode and PONum,  Here is my where clause.

      WHERE ( '{?CompanyCode}' = 'ALL' OR SPM.COMPANYCODE = '{?CompanyCode}')
      AND ( '{?PONum}' = 'ALL' OR SOM.PONUMBER = '{?PONum}' )

      When I save the command, it does show the parameter window to let me enter the values and it is saved successfully.

      Both parameters are string type and static, also i modified the parameters in Parameter fields to make them Editable in Show on (Viewer) Panel, it doesn't help either. The parameters window does not prompt. The report will retrieve all the data.

       

      If I remove the where clause and parameter list in the command window and re-create parameters in Parameter Fields in Field Explorer, the parameter window does prompt after I deploy it.

       

      SAP crystal Report, SAP BO, CR Developer,Version: 14.01.4.738, Oracle Database.

       

      What do I missed?  Your help is much appreciated!

       

      Brian

       

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Hi Brian,

      Are you saving data with your report?  If so, I suggest turning that off in the File menu and then closing Crystal.  Next, open the report again in Crystal and try running it again.  Do the prompts appear?

      Also, what are the exact properties that you've set on the parameters in the Parameters section of the report?

      Finally, questions like this should really be asked in the forums under the SAP Crystal Reports tag - that way you have several people who will see it in addition to myself.

      -Dell

      Author's profile photo Brian Hawk
      Brian Hawk

      Hello Dell,

      Wow, you are absolutely right.  "Save Data with report" was checked accidentally.

      Thank you very much and your help is much appreciated!

      Have a great day!

       

      -Brian

      Author's profile photo Richard Benitez
      Richard Benitez

      Hello Dell, it's a pleasure to say hello from Paraguay, I have a problem and I would like you to help me. I am working with VB.net 2017 and cystal, the database is Oracle 11g. the problem is that I have a report with a sql inside. I have problems when I want to pass the value of the parameter, however it happens I always give ellor. What is the correct way to pass parameters to a sql if Oracle is used?

       

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      If the field is a string field, you need to put the parameter inside single quotes like this:

      and u.cod_usuario = '{?p_where}'

      Author's profile photo Lusia Li
      Lusia Li

       

      Hi Dell,

      Got a question regarding charts. I've created a command object to retrieve data for a chart on the report, and would like to create another chart on the same report using a sub set of data from the command object. It's like a zooming chart to the original chart. Is it do-able and how? I've been looking at the graphic charts and there seem to be not many choices for selecting data into a chart.

       

      Thanks,

      Lusia

       

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Hi Lusia,

      In the future, you should really ask questions like this under the Crystal Reports tag in the community so that they're easier for others to search for.

      Having said that, the only way I can think of to do this would be to use a subreport with a similar command that is filtered down the way you want to see it.

      -Dell

      Author's profile photo athi a
      athi a

      Hi Dell,

      I’m new to Crystal Report. I’ve requirements that need me to create a report to count the document status for a certain department in cross-tab and have to list all the status either it null. I’ve made some research and found out that the best solution to solve this problem is only by using the SQL command.

      Unfortunately, I’m not familiar with SQL. How can I write a command to create a temporary table to show all distinct list of status and department. Then, cross join these two temp table and left join the main table that contains status and dept fields to get the count of status by department?

      Below is command that I’ve been tried before:

      SELECT 
      DISTINCT PS.STATUS, 
      PS.IDNO,
      PS.DOC_DATE,
      PSCR.ASSIGNEDID,
      USER.DEPT_CD,
      USER.DEPT_NM
      FROM PS
      INNER JOIN PSCR ON PSCR.IDNO = PS.IDNO
      LEFT JOIN USER ON USER.USER_ID = PSCR.REQID
      ORDER BY  PS.DOC_DATE

      The result displayed:

               Dept 1 Dept 2 Dept 3
      Status 1   2       1      0
      Status 2   0       2      1
      Status 3   1       0      3
      

      But the expected result should be like this:

      • The status should be displayed although it’s contain null value
               Dept 1 Dept 2 Dept 3
      Status 1   2       1      0
      Status 2   0       2      1
      Status 3   1       0      3
      Status 4   0       0      0

      Can you please help me?

      Thanks

      -Athi-

       

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Hi Athi,

      Please post this as a question in the SAP Crystal Reports tag.  There are definitely ways to do this without using a command.

      -Dell

      Author's profile photo Mohamed Haneeq
      Mohamed Haneeq

      Hi Dell,

      I am new to crystal and was able to create dynamic parameters with the help of this tutorial Thank you very for this blog post..

      I have to use same parameter(paramCompanyId) in both the commands.

      I will be setting the parameter value on code.

      crystalReportDocument.SetParameterValue("paramCompanyId", LoggedInMembersCompanyID);

      Note: command: crmleadsbycustomer  =>  retrieves report data
      command: crmopportunity => dynamic filter dropdown data

      Issues:

      1. If I use the same parameter it keeps on prompting without executing the report.
      2. I don't want user to enter parameter on prompt so I am setting up in code.
      3. I don't want to use separate parameters for each command (this way works fine)

      can u pls help me in this regard.

      Thanks
      -Haneeq-

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Please ask this question under the SAP Crystal Reports tag in the Community instead of as a comment on this blog.  That way others will be able to find the answer.  Thanks!

      Author's profile photo Serena Gunter
      Serena Gunter

      An interesting thing is happening... I have a simple command that I have not created any parameters in.  I tried creating a parameter, and the report started coming back blank.  I have made command reports with parameters frequently, so I know my syntax is correct.

       

      BUT, on a whim I created a parameter via the Crystal reports Parameter Fields (not within the command tool) and it worked.  It filtered the records without the parameter having to be set in the command.

       

      I am using Crystal Reports 2016 SP4.

       

      Is this a new behavior?

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      It shouldn't be.  Did you use the parameter in the command?  Or did you use it in the Select Expert.  If you used it in the Select Expert, it will work, but it would cause significant slowness in the report if your query returns lots of record because, as I say above, Crystal will pull all of the data from the query into memory and filter it there.  If the query doesn't return many records, the effect of doing this will be negligible.

      -Dell

      Author's profile photo José Niño
      José Niño

      Hi Dell!

      I have a need to create a new report with multiple value parameters and this post has been very helpful.

      Thank you very much!

      Author's profile photo dod zad
      dod zad

      Hi Dell,

      I have concerns related the Command

      Can I create a report out of commands instead of mapping direct DB tables in the filed explorer to reduce the processing time?

       

      I understood form the post above that when connecting a command with DB the data will be pulled in crystal RAM. What if I have a report created out of command? where the data will be processed?

      I appreciate your feedback

       

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Processing in Crystal RAM only occurs if you link multiple commands or use the Select Expert for filtering the data instead of the Where clause of the command.

      -Dell

      Author's profile photo dod zad
      dod zad

      Thank you for your response.

       

      Can I make the tables join and where clause in a single command in order to make the processing on DB level instead crystal RAM.

      I have a huge report above 30 000 record. The report is slow due to the group summation. Thus I am thinking of different ways to retrieve the totals instead of having crystal calculating them.

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Absolutely.  A Command is nothing more than a SQL Select statement or a call to a stored procedure.  Just about anything you can do in SQL in your database you can do in a Command.

      I highly recommend using a command for processing summaries on large numbers of records.

      -Dell

      Author's profile photo dod zad
      dod zad

      Hi,

       

      What is the maximum No of record a fitter can retrieves from the DB. I have a filter which retrieves 5 pages (1206 out of 2100) of record and ignored the rest.

       

      Anyway to solve this?

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Please ask this as a Question in the SAP Crystal Reports tag.

      Author's profile photo Terry Seale
      Terry Seale

      I get errors when trying to use case or IIF in a command object.  The following generates an error on Like

       

      WHERE (Case When '{?IssueStock}' = 'Stock' Then Material_Trans.Tran_Type Like 'Transfer' And Material_Trans.Material_Trans_Date Between {?SDate} And {?EDate} Else Material_Trans.Tran_Type Like 'Transfer' End)
      ORDER BY Material_Trans.Material_Trans_Date DESC

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Try something like this instead:

      WHERE 
      ('{?IssueStock}' = 'Stock' and 
        Material_Trans.Tran_Type Like 'Transfer' 
        And Material_Trans.Material_Trans_Date Between {?SDate} And {?EDate}) 
      OR (Material_Trans.Tran_Type Like 'Transfer') 

      -Dell

       

      Author's profile photo Mark Pulles
      Mark Pulles

      Hi Dell,

      Thank you very much for your quick response and helping me out on this!

      Your article was clear and already helpful within 3 sentences.

      It's my first time with CR and it immediate annoys me with missing this kind of basic information/steps which should be very intuitive imo.

      From now I'll never forget that "Command" actually means: "enter your SQL statement here" and "Create new report from a query" means something else...

      A BIG thank you again!

      Mark

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Thanks Mark!  I wrote this because I was getting tired of typing the same information over and over as I answered questions.  Yes, the blog is about 7 years old, but none of the information has required a change as the functionality remains the same.

      Good luck with your reporting!

      -Dell

      Author's profile photo sm rabby
      sm rabby

      Hi,

       

      I have created a command [ select id, name, address, mobile, from employee]. and design crystal report name emyinformation.rpt with command field. My question is now how i use this command in my c# code. How to call command in my c# code. We can call store procedure in c# code easily.But how to use this command in c# code have no idea. Please help.

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      Why do you need to run the command in C#?

      -Dell

      Author's profile photo Carla Rogers
      Carla Rogers

      Given my limited experience with information technology, even in the area of crystal reports, I was not able to grasp the meaning of some portions of this advanced, to me, article.  That said, I think I might have been able to see how a report I am working on is slowed down by its failure to adhere to best practices when using Command for SQL.  My hope is this picture shows  a place where we potentially could be introducing a problem in to the time needed for the report to finish.

      This%20image%20is%20an%20attempt%20to%20expose%20a%20potential%20problem%20in%20how%20a%20report%20is%20setup%2C%20giving%20an%20idea%20of%20it%20could%20be%20made%20to%20run%20faster.

      This image is an attempt to expose a potential problem in how a report is setup, giving an idea of it could be made to run faster.

       

      Author's profile photo Tichaona Gaza
      Tichaona Gaza

      Hi Dell,

      So I have a simple layout using command as a datasource, performance is fast when running the layout on its own. When I add it as a subreport, I have 3 layouts that I want printed at once (avoiding the print sequence) it takes forever to load unlike the other layouts that use tables .

      Have you ever experienced this before and is there a reason why it is slow when it is a subreport? Any way to improve the performance?

      Kind Regards

      Tich

      Author's profile photo Dell Stinnett-Christy
      Dell Stinnett-Christy
      Blog Post Author

      In the future, please ask questions like this as a question, not as a comment to a blog.

      Are you linking to the command as a subreport?  If so, did you create the parameter that you're linking to in the Command Editor or did you have the subreport link created it as a "p-" parameter where it gets used in the Select Expert?  I suspect that the latter is the case, in which case it would cause the slowness you're experiencing.

      If this is the case, to resolve the issue do the following in the subreport:

      1.  In the Command Editor, create a parameter for the field you're linking on.
      2.  Remove the formula from the Select Expert.
      3.  Remove the "p-" parameter from the parameter list.
      4.  In the main report, edit the subreport links.
      5.  In the bottom right of the link editor, uncheck "Select data in subreport based on field:"
      6.  In the bottom left of the link editor, use the drop-down to select the parameter from the   command.
      7.  Click on OK to save.

      -Dell

      Author's profile photo Tichaona Gaza
      Tichaona Gaza

      Hi,

      Noted, will do that next time.

      Thank you, your instructions worked.

      Kind Regards,

      Tich