Selecting order of parameters to determine order of groups 3

[zyane]

Hi All
I am having difficulties solving this problem and am not even sure this is possible but here it goes.

I have three parameters: region, month, city. The users of this report have requested to select the parameters in different orders (ie. city region month or maybe month, region, city) and the report will group the data in this same order.

group 1 city
group 2 region
group 3 month

or

group 1 month
group 2 region
group 3 city

does anyone have any ideas on how to accomplish this?

thanks in advance

 

[synapsevampire]

Build 3 formulas to be used for grouping, and have each formula change based on the parameter choice, as in:

@group1 formula
If {?GroupChoice} = "month, region, city"
then
{table.month}
else
If {?GroupChoice} = "region, city, month"
then
{table.region}
else If {?GroupChoice} = "month, city, region"
then
{table.month}

...and build 2 more based on the parm choice

Group by these formulas

There may be a slight trick with doing the month as it would naturally be a number, not a string, so convert it to a string and pad it with a 0 and everthing should be just fine...

-k

 

[Ngolem]

there is no problem doing this with a little thought

AS SV has pointed out you use 3 grouping formulas - one for each group.

The results of all of the formula if-then formulas MUST have the same datatype for this to work....generally speaking that is a String type

As far as the parameters involved you can adopt 1 of 2 approaches

1. Give the user combinations of groupings to choose from with no option to choose their own

eg.

?Report Grouping
Parameter type : String
Description : Select one of the following report groupings
1 - city region month
2 - city month region
3 - month city region
4 - region city month

Default setting: 1

The advantage to this method is the ease of programming since the parameter is predictable.
The disavantage is that the user will want another combination and even with 3 values there are 6 combinations and there is limited space in the description to give all possibilities

2. Give the user 3 parameters to enter...?ReportGroup1, ?ReportGroup2, ?ReportGroup3,

Set as the default the most common grouping...ie:

?ReportGroup1 - Region
?ReportGroup1 - City
?ReportGroup1 - Month

But let them choose the values themselves

Advantage : No complaints about not being to get their favourite view of the data.
Disadvantage: must take care to deal with bad user input

I prefer method 2 myself since I hate revisiting a report to create another case

Method 2 formulas

the trick here is to a) have the grouping values as strings
b) assign a default and handle bad input

the first group is straight forward

@Group1

if {?ReportGroup1} = "City" then
{Table.City} //assuming it is a string
else if {?ReportGroup1} = "Month" then
totext({Table.Month},"MM&quot
else
{Table.Region}; //this is the default value and catch-all for bad input

@Group2

if {?ReportGroup2} = "Region" and
{?ReportGroup1} <> &quot;Region&quot; then
{Table.Region}
else if {?ReportGroup2} = &quot;Month&quot; and
{?ReportGroup2}<> &quot;Month&quot; then
totext({Table.Month},&quot;MM&quot
else
{Table.City}; //this is the default value and catch-all for bad input


@Group3

if {?ReportGroup3} = &quot;Region&quot; and
({?ReportGroup1} <> &quot;Region&quot; or
{?ReportGroup2} <> &quot;Region&quot; ) then
{Table.Region}
else if {?ReportGroup3} = &quot;City&quot; and
({?ReportGroup1} <> &quot;City&quot; or
{?ReportGroup2} <> &quot;City&quot; ) then
{Table.City}
else
totext({Table.Month},&quot;MM&quot; //this is the default value and catch-all for bad input


NOW THE FINAL THING I DO

Is create a formula called

@ReportSorting

WhilePrintingRecords;

&quot;Report sorted by : &quot; + {?ReportGroup1} + &quot;/&quot; + {?ReportGroup2} + &quot;/&quot; + {?ReportGroup3);

I put this formula in the report header somewhere so that the user can see why his report did not function the way he thought it would (ie...he typed improper input)....trust me ...it saves a lot of headache later on if you do this.

Hope this helps you








Jim Broadbent

 

[Ngolem]

Sorry...a typo in this formula

@Group2

if {?ReportGroup2} = &quot;Region&quot; and
{?ReportGroup1} <> &quot;Region&quot; then
{Table.Region}
else if {?ReportGroup2} = &quot;Month&quot; and
{?ReportGroup1}<> &quot;Month&quot; then
totext({Table.Month},&quot;MM&quot
else
{Table.City}; //this is the default value and catch-all for bad input




Jim Broadbent

 

[doyledirk]

Hi
I found the best way is to use a flag within a Stored Procedure to accomplish this.

create a drop down box that will allow the user to select the relavent combination thay would like displayed. once they select a grouping the relavent section of the SP will be selected.
e.g
CREATE PROCEDURE [dbo].[IMS_SP_REP_By_Year_Ledger_FundType]


@lang char(6),
@GroupBy Char(1),
' Suructure the SQL so that the grouping will be displayed correctly

AS
begin
if @lang = 'Lang1'
begin
if @GroupBy = 'V'
begin
select * from ....
end

if @GroupBy = 'U'
begin
select * from ....
end
end
if @lang = 'Lang2'
begin
if @GroupBy = 'V'
begin
select * from ....
end

if @GroupBy = 'U'
begin
select * from ....
end
end

end
GO