How To do Random Aggregation in Report Studio

[karan27]

Scenario is i have 19 Countries and need first 5 rank to appear first with respect to its amount_sold and 6th tank should be total of rest countries.

Query
Rank  Country Amount_sold
1            A            111
2            B            222
3            C            333
4            D            444
5            E            555
.             .              .           
.             .              .
19          Z             .
                Total    xxx

Now i need following output in List report


Rank Country  Amount_sold
1            A            111
2            B            222
3            C            333
4            D            444
5            E            555
6            REST      Total of (Rank_6 to Rank_19)


Thanks in Advance.....

[schrotty]

Hi,

do you have tried to solve this with report variables?

1. create a Data Item "rank_detail" function: rank([Amount_sold)
2. create a next Data Item "rank_group" function: IF ([rank_detail <=5) THEN ([rank_detail]) ELSE (6)
3. create a Variable "single_or_group_country" function IF ([Query1].[rank_group] <=3) THEN ('single_country') ELSE ('other_country')
(Add 2. Values single_country and other_country)
4. in the Report page create a List and drag the Items [rank_group],[County],[Amount_sold] from your Query.
5. create list groups  by the [rank_group] Item and add the total for [Amount_sold]
6. For each Column/Total-Line set in the properties the Style Variable to your Variable "single_or_group_country"
7. Set the variable to "single_country" and hide the Total-Line. (Properties -> Box Type: None.) After this set the variable to "other_country" and hide the Columns
8. Run your report and enjoy.  8)

In the attachements is an example Report. (10.2.2, Package: Go Data Warehouse (query))

[karan27]

Thanks Schrotty for ur reply.

I want exact output as u sent me in attachment.

But I have tried as per ur steps but i got different output (plz see attachment)

Can u plz tell me how to hide countries appear as rank_6.
and rename Rank6 as rest
ex..
Rank   Countries    Amount_sold
6             rest      123


Thanks in advance....

[navissar]

Try the following setup: Create one query for 1-5, filter to the first 5 products.
Create a second query for rest, filter to exclude 1-5 and aggregate.
Then union the queries, and use the outcome of the union for your list.

[karan27]

Thanks Nimrod Avissar for reply.

I have tried this but didn't get total for rest countries.

[schrotty]

Hi karan,

if you follow Nimrod's way you have to consider to set in the filter properties "Application: After Auto Aggregation".

When the other countries still apears in the Union then add in the previous queries two Dataitems:
1. for rank 1-5 set the first item to the rank-number. In the other countries set the value to 6
2. for rank 1-5 set the second Item to the country-name. for the other countries set the value to 'other'

In the Union use the new created Items instead for the original-Items and set in the properties-pane the Aggregation function to 'None' (Total for Amount).

Thank you Nimrod for your reply.  :)


If you follow the way with the Report variables your screenshoot looks pretty good.
Please check for each list-column-Body (not list column Title!) to set the Style variable. (see Attachement)


Schrotty.

[karan27]

Thanks Schrotty for help.

I got the output.  8)

Once again thanks a lot.  :)