Crystal reports command parameter

Crystal reports command parameter DEFAULT

Crystal Reports SQL Commands

Using a SQL Command in a Report

Once you ve created a SQL Command, it will appear in the Selected Tables list of the Database Expert just like a native table from a database. Once you close the Database Expert, any fields that the SQL Command returns will appear in the Field Explorer ”just drag and drop them onto the report as you would any other database field.

You can even link a SQL Command to another database table in the Database Expert before you proceed to report design. And, you can add an additional table to the report later, linking it to the existing SQL Command on one or more common fields. If you link additional tables to the SQL Command, be aware that you ll be limited in your join and link type choices, as described earlier in the chapter under Using Multiple Database Types in the Same Report.

Using a Parameter-Based SQL Command

If you added one or more parameters to your SQL Command, you ll find that the command will prompt you for parameter values as you create the command, and later when you refresh a report based on the command. Notice, also, that the parameter will appear in the Field Explorer as though you added it to the report (Chapter 14 discusses report-based parameter fields).

When you refresh the report, you ll be prompted to choose whether to rerun the report with existing parameter values or to specify new parameter values. If you choose to specify new, the familiar parameter prompt will appear asking for values for the SQL Command parameters. You can even place the parameters you added to the command right on the report, or use the command parameters in report formulas.

Caution  

When you create a SQL Command “based parameter, you don t have the choice of advanced parameter types, such as multi-value or range. Even if you edit the resulting parameter from within the Field Explorer, you probably won t be able to make such choices. You can, however, click the Set Default Values button to supply a pick list of default values, set length limits or an edit mask for string parameters, or set range limits for number or date parameters. See Chapter 14 for more information on these options. You can even rename a command-based parameter in the Field Explorer the same way you would rename a regular parameter field (although this won t change the name of the parameter in the SQL Command).

Using a SQL Command from the Repository

If you ve added a SQL Command to the repository using steps described previously in the chapter, the command will be available to other reports you design, as well as to others who are connected to the same repository. When you initially display the Database Expert, simply click the plus sign next to the Repository category of the Available Data Sources list. If prompted, log on to Crystal Enterprise. Then, navigate to the desired folder in the Crystal Enterprise Explorer. Only SQL Commands will appear within the folder ”other repository objects such as text objects and bitmap images won t appear. Either double-click on the SQL Command you wish to add to the report or select it and click OK. The command will then appear in the Available Data Source list with a lower-level category object labeled Query. Click the Query entry for the desired SQL command and use the right-arrow button to add it to the Selected Tables list. You may now add and link other tables, or click OK to close the Database Expert and add the repository-based command to the report.

click to expand
Sours: https://flylib.com/books/en/4.229.1.99/1/

Contact US

INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Us!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tek-Tips Posting Policies

Contact US

Using Case in a crystal reports command parameter.

Using Case in a crystal reports command parameter.

mohebkasem(IS/IT--Management)

(OP)

Hi All,
I am building a report with a sql server command script. I am trying to set up a report the user has the flexibility to run it with two options. The report keeps giving me a syntax error.

Ultimately what I'd like to write is something in this effect.

SELECT
Field_1
Field_2
FROM Table_A
WHERE
CASE WHEN {?Parmater1 = 'All' THEN
Field_4 in (Select id form Table_B)
ELSE Field Field_5 = {?Parmater2}
END

Your help is much appreciated.
Thank you

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Sours: https://www.tek-tips.com/viewthread.cfm?qid=1806214
  1. American broadband service outage
  2. Garmin 51 lm gps
  3. Escape machine city walkthrough

 locked
How to pass parameter to crystal report command where the parameters(Date Values) are passed on runtime

Hi,

I have a table 'Supply' which contains various supply entries on various dates.

And I can query my database like this to get supplies made between DATE1 and DATE2

select * from Supply where SupDate between '10/01/12' and '14/01/12'

Now, i want to query my database as above and have to generate Crystal Reports of it, but i want to pass this FROM and TO date values in runtime.

In order to do this i have created a form like this with date pickers with 'Run report' button and OnClick: to generate report based on the From and To date selected.

Also in the Crystal report file i have created two Parameter fields FromDate & ToDate and created a Command like this

select * from Supply where SupDate between {?FromDate} and {?ToDate}

Now, I want to pass the chosen date values to these Crystal report parameters and on clicking Run Report button the results should be printed in Crystal reports.

Can anyone help me to make the functionality like specified above which will be very much helpful for everyone who are New to Crystal Reports. Many Thanks in Advance.

- Jai


Many Thanks, Jai :)

Sours: https://social.microsoft.com/Forums/en-US/04d8d4d5-d918-40a2-9c5b-9f73ce0e3df1/how-to-pass-parameter-to-crystal-report-command-where-the-parametersdate-values-are-passed-on?forum=Offtopic

Contact US

INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Us!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tek-Tips Posting Policies

Contact US

thread767-1784919

Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

Passing Multi-Value Parameter result to Crystal Command Sql - Can it be done??

BeeBear2(TechnicalUser)

(OP)

Hi, I know that you can't create a multi value paramter in the Crystal Command, but is there a way where you can pass the result of a parameter selection
EG create those parameters in Crystal not in Command and create a result formula similar to Join{?param}',') to create a string to use in an IN clause in the SQL.

I had to replace my standard table join way, with a select statement in SQL due to poor report performance, and now I have parameter issues.
I had a look at this FAQ (http://www.tek-tips.com/faqs.cfm?fid=6779) but It requires the Main report to house the parameters and pass to a Subreport where all the report action happened. I cant do that as I need a drill-down level below this.

Also, I need the option of Dynamic Cascading parameters aswell, with the 'All' option included.

I am using Crystal 2016 on a SQL Database.

Any help would be greatly appreciated.

Thanks

BeeBear

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Sours: https://www.tek-tips.com/viewthread.cfm?qid=1784919

Command crystal parameter reports

I'm going to assume that the command for your report is actually more complex than what you've posted.  I would try this:

1.  In the command, create the command parameter as a number and turn on the Multi-select or Multiple Values option for it. (We'll edit this in the main report to configure it correctly....)

2.  Change the last line of your command to:

 

where ({?CoNbr} = 0 or cpy.Company in {?coNbr})

 

3.  Create a second command that will provide the values for your paramter.  It will look something like this:

 

Select 0 as Company, '*All' as Name

UNION

Select cpy.Company, cpy.Name

from LSDEV.dbo.APCOMPANY cpy

order by 2

 

4.  Edit the {?CoNbr} parameter in the MAIN REPORT.  Set it to dynamic and point it to the command that you just created - value field is Company and display field is Name.  When you run the report, the list in the parameter should appear in alphabetical order by company name with "*All" at the top.

 

-Dell

Sours: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17571
Crystal Reports Tutorial Creating Parameter Fields-11 Business Objects Training Lesson 13.3

Crystal Reports - Adding a parameter to a 'Command' query

When you are in the Command, click Create to create a new parameter; call it . Once you've created it, double click its name to add it to the command's text. You query should resemble:

If desired, link the main report to the subreport on this () field. If you don't establish a link between the main and subreport, CR will prompt you for the subreport's parameter.

In versions prior to 2008, a command's parameter was only allowed to be a scalar value.


The solution I came up with was as follows:

  1. Create the SQL query in your favorite query dev tool
  2. In Crystal Reports, within the main report, create parameter to pass to the subreport
  3. Create sub report, using the 'Add Command' option in the 'Data' portion of the 'Report Creation Wizard' and the SQL query from #1.
  4. Once the subreport is added to the main report, right click on the subreport, choose 'Change Subreport Links...', select the link field, and uncheck 'Select data in subreport based on field:'

    NOTE: You may have to initially add the parameter with the 'Select data in subreport based on field:' checked, then go back to 'Change Subreport Links ' and uncheck it after the subreport has been created.

  5. In the subreport, click the 'Report' menu, 'Select Expert', use the 'Formula Editor', set the SQL column from #1 either equal to or like the parameter(s) selected in #4.


Note the single-quotes and wildcard characters. I just spent 30 minutes figuring out something similar.

Sours: https://newbedev.com/crystal-reports-adding-a-parameter-to-a-command-query

Now discussing:

Giving Parameter in SQL Command of Crystal Report. and Passing to Database LockedRSS

Thanks for you reply it helps me to solve my problem.

Yes my precedure is working fine in query analyzer. ok Now i have tried with normal sql query instead of procedure Please see the following steps which i have followed.

 Step1: I have created a parameter @catid as bellow showed in picture.

 

 Step2: I have written a sql query select *from categories where categoryid={[email protected]} and while pressing OK in Modify Command dialog it is giving the error

Details: ADO Error Code: 0x80040e10
Source: Microsoft OLE DB Provider for SQL Server
Description: No value given for one or more required parameters.

 

 

Error Message

 

Please let me know the solution. 

Sours: https://forums.asp.net/t/1073788.aspx?Giving+Parameter+in+SQL+Command+of+Crystal+Report+and+Passing+to+Database


9502 9503 9504 9505 9506