Dynamic Grouping

[PavanKumarU]

Hi,
May be i am missing something here. Hope one of you can help me.
I am trying to dynamic grouping on one of my reports and want the user to pass a parameter on which i would do the grouping. To explian better, lemme take an example.
My report would take City, Country and zipcode as parameters for grouping (there are other parameters but these are for grouping). I created three formulas and created three groups on them. If the user passes city as the first param then the first grouping would be done on city,second on zip if user passes zip as my second param and so on.
What bothers me is, if the user doesnt pass anything for the third group, i want that group to be removed (dynamically). Can this be done?
Hope i am clear abt my qiuestion.
any help is much appreciated.
Thanks,
Pavan

 

[synapsevampire]

I think that the concern is creating a default for the grouping when nothing is passed.

Add to your group formula something like:

if {?grp} = "City" then
{av_AtsAddress.chvCity}
else
if {?grp} = "Zip" then
{av_AtsAddress.chrZip}
else
"No Group"

Now suppress the group if the value is "No Group", since all will be "No Group" as a default, it doesn't really group anything.

You can elaborate on the formula to allow for other choices.

-k

 

[Ngolem]

That won't work SV...He wants 3 grouping formulas

Pavan

my approach to doing this is to give the client limited options as to how something is grouped.

I would have a parameter called Report Grouping

?ReportGrouping (numeric)

Choose the number of one of the groupings below

1 Country, City and Zipcode (default)
2 City and Zipcode
3 Country and Zipcode

Now you create 3 Grouping formulas

@Group1

If {?ReportingGrouping} = 2 then
{Table.city}
else If {?ReportingGrouping} = 3 then
{Table.ZipCode}
else
{Table.Country}; // The default if 1 or any other number
// entered

****************************************
@Group2

If {?ReportingGrouping} = 2 or {?ReportingGrouping} = 3 then
{Table.ZipCode}
else
{Table.City}; // The default if 1 or any other number
// entered

****************************************
@Group3

If {?ReportingGrouping} = 2 or {?ReportingGrouping} = 3 then
"Name of the next group" (*)
else
{Table.ZipCode}; // The default if 1 or any other number
// entered

(*) Put the name of the next group here...then in the Heirarchytree it will look like you are labelling the next group.

*************************************************

By limiting you user's choices and composing aformulas that give default values for improper input...you reduce error problems....The only thing esle that I do is also show the Option that they chose for the parameter on the report somewhere....that way if they did not get the report they were looking for they can see that it was because they entered the wrong parameter by mistake

Jim Broadbent

 

[PavanKumarU]

hi,
Thanks for the messages.
But Jim, what if user downst enter any params? I dont want any groouping there, just a straight order by in my stored proc would do in that case. doesnt it?
In the path you suggested, there will definitely be three groups and we are choosing them, in case user doesnt provide anything.But, yes i agree with you to provide the user with minimum options and having defaults. But with some clients you dont have the comfort of suggesting something. do u?
anywayz i really appreciate your help and will look forward to any more suggestions.
Thanks to you too synapsevampire
Best Regards,
Pavan

 

[Ngolem]

That is no problem....just set the default values for each formula to a string constant...this will prevent any grouping at all....for example

@Group1
If {?ReportingGrouping} = 1 then
{Table.Country}
else If {?ReportingGrouping} = 2 then
{Table.city}
else If {?ReportingGrouping} = 3 then
{Table.ZipCode}
else
"No Group"; // The default if other number entered
Jim Broadbent

 

[PaulDeshayes]

Hello Pavan,

I simplify your example, tell me if this is what you look for, more or less :

- user selects a criteria for grouping (like "group by city", "group by name", etc.&quot
- this field can be null, and in that case the data won't be grouped.

An easy way would be to conditionally:
- suppress the group header-footer when ?GroupBy is null
- group by the field you want data to be "sorted by" when ?GroupBy is null

So your @GroupBy formula would be something like
-------- @GroupBy --------
select {?GroupBy}
case "Product" : {myTable1.PRODUCT} // GROUP BY "Product"
case "Customer" : {myTable1.CUSTOMER}// GROUP BY "Customer"
case "Product Family" : {myTable2.ProductFamily}// GROUP BY "Product Family"
default: {myTable.Name} // FIELD TO SORT DATA BY, WHEN NO ?GROUP-BY SPECIFIED; GH AND GF HAVE TO BE REMOVED.
-------- / @GroupBy --------

I hope it answers...

/Paul

 

[PavanKumarU]

Paul,
Thanks for the message. Thats exactly what i am doing. And Jim, to be frank, i havent understood your example. Moreover, your code works only with one parameter and then 'we' deciding what to group on. Isnt it?
I need three params, all could be nulls, but when passed with valid info, want to group on them. If param for group 1 is passed there wont be group 2 and 3. only group 1.
Paul, I am doing exactly the same as you suggested. I am suppressing the sections whenever i have null in the group params, and using code provided by synapsevampire except for his "no group" string.
Thanks you very much all you guys. I really appreciate it and it has been a pleasure working with you.
Thank You,
Regards,
Pavan

 

[lyanch]

The easiest way to handle... if they only send param1

when setting the field for group2, if it is zero length (params aren't ever null) then set the grouping in 2 = to one and suppress the group headers and footers..

in three, if it is zero length.. use group two.. if group two is zero length .. use one..

For the first group.. I always set a "default" for sorting purposes and suppress the headers/footers so it doesn't "look" like there is any grouping..

Lisa

 

[homio1]

I have the same problem. I able to suppress the group..however, it still shows up on the group tree as blank (like a ----). How can I not show it on the group tree?

My problem is much simpler...I have a sort option that the user pass in:
Last Name
Grade Level
Teacher Name

My first group is by the sort option they selected. However, when user select either Grade Level or Teacher Name, they want to group by the Last Name (be able to use the group tree to navigate)...For these two sort, I'm fine. BUT when they select the LAST NAME sort, I don't want to display the second group (same grouping)...I can suppress not to show it in the report, but the group tree has it but blank.

thanks!