Dynamic Grouping and Sorting in a report

[Desperado]

Hi All,

I have to create a report using dynamic Group By and Sort By. The report gets the inputs from a jsp.
In th java page there is a drop down for Group By which shows "Department, Plant, Unit...". These are different columns of a table in the database. So, if a user selects department in the UI from the drop down, I have to display values for all the departments based on the selection.
ie,

The output will be like this:

Dept:ap
nameÃ,  Ã,  Ã,  Ã,  idÃ,  Ã,  Ã,  Ã,  deptÃ,  Ã,  Ã,  Ã,  groupÃ,  Ã,  Area
abcÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  1Ã,  Ã,  Ã,  Ã,  Ã,  Ã, apÃ,  Ã,  Ã,  Ã,  Ã,  Ã, buÃ,  Ã,  Ã,  Ã, NY

dept:pu
nameÃ,  Ã,  Ã,  Ã,  idÃ,  Ã,  Ã,  Ã,  deptÃ,  Ã,  Ã,  Ã,  groupÃ,  Ã, Area
acdÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  2Ã,  Ã,  Ã,  Ã,  Ã,  puÃ,  Ã,  Ã,  Ã,  Ã,  Ã, auÃ,  Ã,  Ã,  Ã,  Ã, CA


if the user selects a plant from the drop down then the o/p shd be based on the plants for all the plants.

i think i'm clear upto this point....

The next thing is after selecting group by , there is one more column called sort by in the jsp page... So, the user can select the sort by(order By) option too..like "Area, Staff, Employee..."

if the user selects group by as Department and sort by as Area the output shd be for the department in ascending order of area(location)


The o/p will be as follows:

dept:pu
nameÃ,  Ã,  Ã,  Ã,  idÃ,  Ã,  Ã,  Ã,  deptÃ,  Ã,  Ã,  Ã,  groupÃ,  Ã, Area
acdÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  2Ã,  Ã,  Ã,  Ã,  Ã,  puÃ,  Ã,  Ã,  Ã,  Ã,  Ã, auÃ,  Ã,  Ã,  Ã,  Ã, CA

Dept:ap
nameÃ,  Ã,  Ã,  Ã,  idÃ,  Ã,  Ã,  Ã,  deptÃ,  Ã,  Ã,  Ã,  groupÃ,  Ã,  Area
abcÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  1Ã,  Ã,  Ã,  Ã,  Ã,  Ã, apÃ,  Ã,  Ã,  Ã,  Ã,  Ã, buÃ,  Ã,  Ã,  Ã, NY


How to start with this .... is there anything like dynamic groupingÃ,  and sorting as all these Department, plant, unit .. are different columns from one or different tables....

Is there any approach to start with..?

Thanks in advance.

[BIsrik]

check the tutorial from Reportnet_Addict...

Srik

[Desperado]

can u plz tell me which exact report to look into

[BIsrik]

look for the custom sorting one...

Srik

[BIsrik]

try even using conditional blocks..

Srik

[Near]

Hey,

You should avoid using conditional blocks if they contain specific sql queries.Ã,  It should be noted that even when a condition is not met, the SQL query within the block will run regardless! This can greatly effect performance.Ã, 

[BIsrik]

r u sure on the conditional block part..

Srik

[Near]

It's easily tested.Ã,  However Cognos as well as some reputed experts will tell you that it does not execute the query. This simply is not true!Ã,  But test it yourself if you are in doubt.Ã, 

I was working with a somewhat lengthy complex report using several conditional blocks and execution was very slow.Ã,  We traced the results and found that in fact the query was being run even though the condition on the block was met in order to hide results. Caution should be used in cases of this kind.Ã,  I have been told that this has been fixed in 8 but I've not been able to confirm it.Ã, 

Happy testing!
::)

Ã,  Ã, 

[BIsrik]

ur r8. i checked that with cognos and they say it for an enhancement request..

Srik

[Near]

Yeah right...enhancement.Ã, 

It was either an oversight or bug. I'm guessing it was a bug because most oversights are in reality bugs.Ã,  Anyway... I also was in contact with Cognos and learned that it was an enhancement for the next release and although I've not been able to confirm it, I've been told it has already been updated in version 8.

LaterÃ,  :o

[JoeBass]

Does anyone have ideas of how to work around Near's observation that hidden Conditional blocks don't keep their queries from firing?  I have three lists - only one of which is shown during any given report run.  The running of the other two makes the report unusable.

A drill through type approach may work, but it wouldn't be very pretty. 

[BIsrik]

the other way would be like have an IF else clause in a data itme...eg. if( ?p1? = 'A') then (Col1) else (Col2)..

Srik

[JoeBass]

Thanks BIsrik.  If I'm understanding correctly, you're saying that I'll see a performance increase by having all fields in one big query instead of three smaller ones?