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

The importance of a good query

It is not always easy to use more then one query in Crystal Reports.

Not always you have a common ground which will make the report creation easy.

I can give you one good advise on this - always do your best to get all the data you need in one SQL query.

Use UNION if you need to bring different data.

I`v spent total of 5 hours trying to understand why the grouping is not being done and I only see the last result.

I`v search for formulas, suppress, anything I could imagine.

Finally I asked myself "Maybe the SQL query needs to be in one query instead of two?".

So I combined them using UNION as you can read in my previous post and it now works well?

So if the report does`n work, maybe the problem is not in the report.

Shahar

crooked is the new straight

One thing you always study in almost every school is hot to work the right way.

They`ll tell you how to do things and how not to do things because and etc.

I think the most important thing they don`t teach you is how to work the wrong way, crooked.

When you work crooked you somethimes get to higher places then you would have reached

if you worked the "correct" way.

If the database where I`m working now would have been built the way it should be,

I would`n have learen how to build a function to parse text in SQL query.

I the Crystal Report application would have enable me to do certain things,

I would`n have learned how to by pass it and create Multiple groups which are not nested.

Who ever workd with Crystal Report in the past, knows that it is not possible, and also

other work arounds like several subreport inside a subreport is also not possible.

I needed to create a main report with details for each outcome in the report.

In the details I needed to create several zones and each one of them needed to show different data.

One way is to create multi grouping which is not possible.

Another way is to create several subreports, but subreport inside a subreport is also not possible.

The solution I`v come up with a friend here is simply UNION.

In the SQL query we`re retrieving the data with, we did a very complex UNION in order to get all the data we needed in one SQL table, and the cherry on the cake is that we added and extra column which is the grouping identifyer which splits the different data for us.

A simple example of what we did:

-------------------------------------------------------------------------------------------------------

-- This is the first qury for general event details
select    distinct
        1 as groupNumber,
        Lupc_Description 'Parameter name',
        Lupv_Description 'Parameter value',
        Evtt_Event_Id 'Event ID',
        null as 'Event Type',
        null as 'Event date',
        User_Name as 'Caller Name',
        Evtt_UpdateTime as 'Update Time'
from    ...
where    ...

union

-- This is the second qury for event parameters
select    distinct
        2 as groupNumber,
        null as 'Parameter name',
        null as 'Parameter value',
        Evnt_Id 'Event ID',
        Evty_Name 'Event Type',
        Evnt_OpenDate 'Event date',
        User_Name 'Caller Name',
        null as 'Update Time'
from    ...
where    ...

order by groupNumber

-------------------------------------------------------------------------------------------------------

Now we have group identifyer  'groupNumber', and all we need is to create a group inside the Crsytal Reports application according to our new group.

Hope this helps all who ever encountered this problem, and I also hope the the Crystal Report team will find a way to allow us Multi Grouping and Subreport inside a subreport.

Shahar