Dynamic and multiple Grouping in BIRT

arjunmanju

Hi all,<br />
I am new to BIRT and got some requirement where we need to provide some options for users to select on which criteria (based on which column) the report must be generated .. i.e grouping Dynamically.... <br />
Hope this example may help in understanding what exactly i want to say<br />
<br />
<br />
<strong class='bbc'>Step 1.</strong> User selects report name and enters search criteria<br />
a. Search criteria<br />
i. All Inquiry search fields including:<br />
1. Company unit<br />
2. Date range<br />
3. Status<br />
4. Priority<br />
<br />
<strong class='bbc'>Step 2.</strong> User specifies required grouping options<br />
<br />
Grouping options include:<br />
i. Group by category<br />
ii. Group by company unit<br />
iii. Group by product <br />
<br />
<br />
<strong class='bbc'>Step 3.</strong> User generates report<br />
<br />
Default fields displayed<br />
<br />
i. Inquiry ID<br />
ii. Version<br />
iii. Priority<br />
iv. Category<br />
v. Received date<br />
<br />
example is when a user selects Group BY Therapeautic area and also group by product .,..the report should look like <br />
<br />
If the user groups by TA and Product, the report would look like<br />
<br />
TA: Cardivasular<br />
<br />
Product: Amoxycillin 100 mg<br />
<br />
ID Version Priority Received Status Category Contact Response<br />
001 1 High 01/01/10 Open Stability Harshada 02/01/10<br />
002 <br />
003 <br />
004 <br />
005 <br />
<br />
Product: Lasix 25 mg<br />
<br />
<br />
006 <br />
007 <br />
008 <br />
009 <br />
010 <br />
<br />
TA: General disorders<br />
<br />
Product: Ibupfrofen 100 mg<br />
<br />
ID Version Priority Received Status Category Contact Response<br />
011 1 High 02/01/10 Open General Harshada 02/01/10<br />
012 <br />
013 <br />
014 <br />
015 <br />
<br />
<br />
Please Help me out!!! how can i achieve dynamic grouping for different criteria or column

Rin

Hi,

I was also dealing with this issue just several days before, there are 2 ways for doing this:
One is to use a dynamic dataset, which is dynamically generate SQL statements in BeforeOpen Script of the dataset, based on the parameter which selected by the user, but this method will lead to a result that SQL need to be executed for each cross tab, which means a lot hits on the Database.

Another is use a fixed dataset with a data cube. This method just need to hit the Database once.
Actually there are also 2 ways to do this, plz look here:
http://www.birt-exchange.org/org/forum/index.php/topic/22690-let-user-choice-the-dimension-in-datacube/page__p__79131&#entry79131

If you don't have a lot columns, I will recommend the method suggested by mwilliams, it's quite simple and works well.