Crystal Reports 9.0 how to skip parameter fields when printing a report with multiple parameters?

Upon printing my report, users add details in the parameters and the report runs calculations based on this info. However, not all fields are mandatory, some are optional. Right now I cannot bypass any parameter fields. How can I allow users to skip those parameters they don't need?

As far as I know a parameter cannot exactly be optional.

One of my tricks is to include the value "ALL" in the parameter's list of values. You can make the value "ALL' the default value.

The way that I implement it, the value "ALL" is a way for the user to specify that he/she does not want to filter any data with this parameter thereby including ALL possible values.

In your record selection criteria (where you program your Crystal report to restrict which records are returned from the database) you would need to include something like this:

and
// Filter on MYPARAMETER unless value = 'ALL'
(
if {?MYPARAMETER}[1] <> 'ALL'
then
({TABLENAME.FIELDNAME} in {?MYPARAMETER})
else
if {?MYPARAMETER}[1] = 'ALL'
then
true
)

If your parameter is numeric, then modify the parameter prompt text to tell the user to input a specific value (e.g. 0 or 9 or 999999999 or -1) whenever they don't want to the parameter to be used as a filter. Modify the default value of the parameter to that special value (0, 9, 999999999, -1). Include a clause in your record selection criteria similar to this:

and
// Filter on MYPARAMETER unless value = 0
(
if {?MYPARAMETER}[1] <> 0
then
({TABLENAME.FIELDNAME} in {?MYPARAMETER})
else
if {?MYPARAMETER}[1] = 0
then
true
)

I thought of another option. You could create another parameter named "Filter on MYPARAMETER" which has the values Yes or No (or True or False). If the user chooses Yes then you would filter using the numeric value in MYPARAMETER. If the user chooses No then you would not filter using MYPARAMETER. The record selection criteria would look something similar to this:

and
// Optional filter on MYPARAMETER
(
if {?Filter on MYPARAMETER} = 'Yes'
then
({TABLENAME.FIELDNAME} in {?MYPARAMETER})
else
true
)

Anyone know the best way to convert crystal reports the ms reporting services.? Anyone know the best way to convert crystal reports the ms reporting services.?

If you don't have that many, do it manually. If you've got a lot and you have the money to spend on migration software, I recommend Radius by 90degrees software (http://www.90degreesoftware.com/). They have a demo version on their website. Also, check out the link of Microsoft partners that do Crystal to MSRS migration I listed below.

Can any one tell me the good website to start learning Crystal Reports 9?

I need to design some chart objects using crystal reports.

You can try the Complete Refrence Book for Crystal Reports 9:

http://books.google.com/books?hl=en&id= ... #PPA400,M1

Animate Logo Team
http://www.animatelogo.com

The software is way too expensive and i can't afford it, but how am i suppose to learn how to use crystal reports. I heard it is a useful skill to know.

Why don't you download a free trial copy of Crystal Reports 2008? You'll have 30 days to learn without spending a dime.

Click the Try Now button at http://www.businessobjects.com/product/ ... alreports/

Another option is to find someone who will give you their keycode and CD of an old version of the software, such as Crystal Reports 10.

When learning how to use the software be sure to read the User Guide (it's included in the software).

I also recommend download some sample reports from https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/bobj_download/main.htm

How we can calculate the sum of two different columns in Crystal reports?

How we can calculate the sum of two different columns in Crystal reports . For exapme in First column (Filed qty etc) values are 5 & 2 and in second column (Field price etc) values are 6 & 3. Then answer show in third column (sum etc) 11 and 5 but how we can in crystal.

Create the following formula in your report:

// Cost
{YOURTABLENAME.QTY} * {YOURTABLENAME.PRICE}

Insert the Cost formula in the details section of your report. If you have group footers or report footer in which you want to display the Cost subtotal, then insert a summary of the Cost formula into each footer section that you wish to have a subtotal or grand total.

I have an existing Crystal Report that is working. It calls a stored procedure in a SQL Server database. I am trying to add a new input parameter to the stored procedure, but the Crystal Report will not "fix up" the database. Usually if I add a field to a stored procedure and call Verify Database it will tell me the database has changed and ask me if I want to "fix up" the database. It is not doing that now. Is it not possible to change the parameters in a stored procedure being used by a Crystal Report? Do I have to create an entirely new Crystal Report or is there some shortcut to revising my existing report?
To DT: It looks like that will work. I will have to add the fields back to the report but at least the headers stay in place.

Thanks for your quick reply.

Database->Remove and remove the current DB

Then Database->Add database

Have you checked the ODBC connections?

I'm fairly new to Crystal Reports 2008. I was given the task of compiling data saved in 100+ Excel spreadsheet. All Excel spreadsheet have same exact field/column types. Is there any way to use the UNION ALL command in Crystal 2008?

Refer to http://en.wikipedia.org/wiki/Union_(SQL) for some basic info on UNION ALL.

A UNION ALL can be used in a SQL statements to combine the results of multiple select statements. When you create your Crystal Report, you can create a custom SQL statement that uses UNION ALL.

But is this really what you want to do? Were you planning on writing a single SQL statement that performs 100+ UNION ALLs?

Why don't you take all of the Excel docs and do a "Save As" to CSV format? Then take the 100+ CSV files and combine them into a single file.

Now you can create a Crystal Report that queries one data source -- a single .CSV file -- and save yourself the headache of maintaining a monster-size SQL statement.


Here's info on how to create a custom SQL query in a Crystal Report:

Defining an SQL Command
==================

If the database you are using supports a query language such as SQL, you can write your own command which will be represented in Crystal Reports as a Table object. This allows database users complete control of the data processing that gets pushed down to the database server. A user who has experience with databases and the SQL language can write a highly optimized command that can considerably reduce the size of the set of data returned from the server.

You can write your own command by using the Add Command node in the Database Expert to create a virtual table which will represent the results of processing the command. Once the command has been created, you can store it in the BusinessObjects Enterprise Repository so it can be shared between many users.

Note: Some of the Crystal Reports native database drivers do not support the Add Command feature:

* DB2 Server
* Sybase Server
* Informix Online Server

When you use a virtual table that was created as a Command, or when you use a Command from the BusinessObjects Enterprise Repository, Crystal Reports does not alter the syntax of the SQL submitted to the server (that is, it does not automatically add quoting or escape characters). This behavior also applies to parameters used in Commands. Therefore, you must add the quoting and escape characters that are necessary for your database driver.

To create a command object
--------------------
1. In the Database Expert, expand the Create New Connection folder and browse the subfolders to locate your data source.
2. Log on to your data source if necessary.
3. Under your data source, double-click the Add Command node.
4. In the Add Command to Report dialog box, enter an appropriate query/command for the data source you have opened.
For example:

SELECT
Customer.`Customer ID`,
Customer.`Customer Name`,
Customer.`Last Year's Sales`,
Customer.`Region`,
Customer.`Country`,
Orders.`Order Amount`,
Orders.`Customer ID`,
Orders.`Order Date`
FROM
Customer Customer INNER JOIN Orders Orders ON
Customer.`Customer ID` = Orders.`Customer ID`
WHERE
(Customer.`Country` = 'USA' OR
Customer.`Country` = 'Canada') AND
Customer.`Last Year's Sales` < 10000.
ORDER BY
Customer.`Country` ASC,
Customer.`Region` ASC

Note: The use of double or single quotes (and other SQL syntax) is determined by the database driver used by your report. You must, however, manually add the quotes and other elements of the syntax as you create the command.

5. Optionally, you can create a parameter for your command by clicking Create and entering information in the Command Parameter dialog box.
For more information about creating parameters, see To create a parameter for a command object.

6. Click OK.
You are returned to the Report Designer. In the Field Explorer, under Database Fields, a Command table appears listing the database fields you specified.

Note:
* To construct the virtual table from your Command, the command must be executed once. If the command has parameters, you will be prompted to enter values for each one.
* By default, your command is called Command. You can change its alias by selecting it and pressing F2.

To edit a command object
---------------------------
1. In the Selected Tables area of the Database Expert, select the command you want to edit.
2. Right-click the command and select Edit Command from the shortcut menu.
Note: If the command is stored in the BusinessObjects Enterprise Repository, you must disconnect it before editing. For more information, see Modifying objects in the repository.
3. Make the changes you want in the Modify Command dialog box and click OK when you're done.

To create a parameter for a command object
-----------------------------------------
You can create a parameter field while working in the Add Command to Report dialog box or the Modify Command dialog box.

1. In the Parameter List area of the appropriate command dialog box, click Create.
2. In the Command Parameter dialog box, enter the following information in the fields provided:
* Parameter Name
Enter the name you want to identify your parameter by.
* Prompting Text
Enter the text you want to appear when the program prompts you.
* Value Type
Select the data type of the parameter field.
* Default Value
Enter the value you want the program to use if you do not supply a new value. This is an optional step.
3. Click OK.
Your parameter is added to the Parameter List. You can modify or delete it by returning to the Modify Command dialog box.

To add a parameter to a command object
-----------------------------
1. Place the cursor in the query text in the spot where you want to insert the parameter.
2. Double-click the parameter's name in the Parameter List.
The parameter is added where your cursor is sitting. It should look like this:

WHERE
Customer.`Country` = '{?CountryParam}'

Note: You must manually add quotes and other elements of syntax as you create your command.

If you want to change the parameter field you've created, go to the Field Explorer dialog box and select it from the Parameter Fields node. See Parameter Fields and Prompts for more information.

I'm using a third-party software program that stores our inventory in a .dat file but the software has terrible reporting. Can I use crystal reports to connect directly to the dat file? Thanks.

Is the .dat file a text or binary file? If it's binary then then answer is probably no. If it is a text file and if each record in the .dat file has the same formatting, then you can define an ODBC data source that points to the .dat file and run reports against it.