Missing Parameters Value

I`m working with crystal report in asp.net and sometimes I get the error:

"Missing Parameters Value"

An annoying error I receive every now and then and it is never clear were the error is.

Mostly when you run into this error the problem is simple - you did not enter all the parameters required in

order to create the report.

Sometimes you`re absolutely sure you entered all parameters and from some unknown reason that still doesn`t work.

A little hint: look at an old post of mine talking about connecting the subreport to the main report.

If the main report is not connected o.k. to the subreport, the above error could result a few hours of searching in the wrong place.

The importance of order

Today I`ve found out how important is the order in order (by).

In Crystal reports you can create groups and give them order of display.

The order can be Asc, Desc, Specified order (pre determined) or in original order.

From some reason the original order didn`t work as I wanted, it kept giving me mixed up results.

After a lot of testings I`ve come to realize that in order to keep the group together, the order of the order by in SQL query is important.

The first column in the order by must be the one you`re grouping by.

And if you`re making group inside a group, the second column and so on in the order by query must match your groupings.

Shahar

Previous and PreviousIsNull

Today I`v finished a long running development project and started testing the hidden corners, those little request of info that will bring the user bad info if any.

I`v entered an SQL where clause that I know will bring me two rows back with one ID only (same ID for both rows). From some reason I really don`t know why, the previous command suppressed both row and not only one of them. My basic logic is that only one will appear.

I guess I`m wrong :(

I`ve digged in the formulas and found the command PreviousIsNull and after several tests I got the the correct requested formula that will bring me the rows.

Here is the formula for your ref:

(NOT PreviousIsNull({TableFieldName}) AND {TableFieldName} = Previous({TableFieldName}))

With this formula I tell crystal report engine to suppress if both the previous record exists and is the same as the current one.

Hope this helped someone with this weird issue.

Shahar

The weekly tip

Sometimes you need to bring duplicated data from your Data Source using union in order to bring all requested information.

Last post I`ve showed you how to remove duplicated detail line from the report.

Today I`ll show you what to do when you need to sum the actual lines.

If you`ll create a new running total fields formula you`ll get all the lines including null for the duplicated lines.

If you want only to count the not null rows select "Use a formula" and click the formula button.

And then enter the formula as below:

not isnull({TableFieldName})

Now the total will bring you only the not null row.

Good luck

Shahar

Suppress if duplicated

Hi

Usually, when you make SQL queries, you don`t want to see "duplicated" rows.

You work very hard from bottom all the way to top to make sure that every row in your query has only unique values.

Sometimes you know you there is not option other then getting a duplicated value.

Crystal Reports comes to the rescue with the "Suppress if duplicated" option.

You can do a simple click on the option to select it on a field and by that only the field will be suppressed if duplicated, but usually you want to suppress an entire details row (somewhat like group by in an SQL query).

For this we have functions.

Right click on the sections and select "Section Expert . . . ".

Choose the section you wish to suppress (usually the details section) and select the option "Suppress if Duplicated".

Click on the "X-2" button (Formula workshop) and enter the formula as follow:

{table.field} = Previous({table.field}).

This will suppress the details row every time the previous row has the same table field value.

Good luck

Shahar

Time Conversion in Crystal Reports

Today I noticed a strange action by Crystal Report.

In the software I`ve done preview on a report and the date appeared as "hh:mm:ss dd/mm/yyy"

In the web site using it the date appeared as ".hh:mm:ssAM/PM dd/mm/yyy".

I don`t really know what`s the difference between the two and it change when switching between one another but this can be easily solved by stating the Date type as Custom while making the report:

1. Right click on the DateTime field you wish to present and select "Format Field"

2. In "Date and Time" Tab click "Customize....".

3. On "Date" Tab under DateType parameter select Custom.

This will make sure the date is set the same in both places.

Shahar

Linking the subreport to the main report

This morning I`v struggled a bit to link the subreport to my main report and I finally managed to do this.

This is how I did it:

1. Place the field you which to pass it`s data to the subreport on the details section and mark it as suppress.

2. On the subreport create a parameter and give it a desired name.

3. back on the main report, right click with the mouse on the "link" to the subreport and select "Change Subreport Links".

4. Select the field you wish to pass it`s data and click ">" button. Below you`ll get options.

5. On the left side you should see the parameter you created. Select it and the right side should become unavailable.

This is it.

Hope this little guide helps.

Shahar