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