Please can anyone tell me how to use Bucket Concept in SSRS

Question

Please can anyone tell me how to use Bucket Concept in SSRS

ritesh_shri9

SSC Veteran

Points: 272
More actions
June 3, 2008 at 11:13 pm

#188506

I am generating one report in which i have to combine ages into three levels like 18-40,41-60 and 60 above........i am doing this witout using cube so please could anyone solve this query for me .i m not able to combine the ages into above mentioned threee groups.........actually its nt accepting the syntax

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

makarand.ankalkote Valued Member Points: 59 More actions · Answer 1

Actually,ritesh i m also facing the same problem.So if u get the answer for this query then tell me also .my cell no.-( as u know).

Peter Rijs Hall of Fame Points: 3400 More actions · Answer 2

If good old T-SQL is what you're looking for:

[font="System"]SELECT (CASE WHEN Age > 60

THEN 'Old'

ELSECASE WHEN Age > 40

THEN 'MiddleAge'

ELSECASE WHEN Age > 18

THEN'Young'

ELSE 'Baby'

END

END) AS AgeGroup

FROM ViewOfAges[/font]

Peter Rijs
BI Consultant, The Netherlands

Mike Hayes-368824 Old Hand Points: 353 More actions · Answer 3

Try something like:

SELECT

SUM(CASE WHEN age>=60

THEN measure ELSE 0 END) AS over_60,

SUM(CASE WHEN age>40 AND age <60

THEN measure ELSE 0 END) AS over_40,

SUM(CASE WHEN age>18 AND age <=41

THEN measure ELSE 0 END) AS over_18

FROM

measure_source_data

matzer SSCrazy Eights Points: 9727 More actions · Answer 4

As an alternative to using T-SQL, you can add a calculated field to your report dataset and use an expression like:

=Switch(

Fields!Age.Value >= 18 And Fields!Age.Value <= 40, "18-40",

Fields!Age.Value >= 41 And Fields!Age.Value <= 60, "41-60",

Fields!Age.Value > 60, "Above 60"

)

Peter

ritesh_shri9 SSC Veteran Points: 272 More actions · Answer 5

Thnks peter fr tht code bt thts not exactly wht i was looking fr.see i have a age column in a table called Party_dim .now i created one parameter and wht i want to show in prompt is 18-40,41-60 nd 60 above options when drill down.i did tht but when the report is generated its not combining the values in tht selected range.i.e if i select 18-40 than it should combine the values frm age 18 to age 40and display the result in single report......please help me i m not able to proceed further..

matzer SSCrazy Eights Points: 9727 More actions · Answer 6

I hope I do unstand your question this time.

Set the following label/value pairs as the available (non-queried) values to your parameter (let's name it AgeRange):

18-40, 18040

41-60, 41060

61 and above, 61999

Now you can add a filter to your dataset and test for

Fields!Age.Value >= Parameters!AgeRange.Value Mod 1000 and Fields!Age.Value <= CInt(Parameter!AgeRange.Value / 1000).

As an alternative, if you are using T-SQL you can add a simular restriction in your where clause, i.e. ... WHERE Party_dim.Age BETWEEN @AGERANGE % 1000 and @AGERANGE / 1000 and bind query parameter @AGERANGE to you report parameter Parameters!AgeRange.Value

Peter

Peter Rijs Hall of Fame Points: 3400 More actions · Answer 7

Though I deem Peter Brinkhaus' solution as not very maintainable (If I would stumble across this code :pinch: it would take me some time to figure out what's going on), I have to applaud him for a rather elegant and probably performant solution!

Well thought, man :Wow:

Peter Rijs
BI Consultant, The Netherlands

ritesh_shri9 SSC Veteran Points: 272 More actions · Answer 8

thnks peter for tht idea................i applied it but in little differnt way and it worked for me.Thnks for tht switch expression too becoz tht expression helped me too in sorting the values.

WayneS SSC Guru Points: 95405 More actions · Answer 9

I like this. But if I'm reading this right, the logic is reversed.

Shouldn't it be:

Fields!Age.Value >= CInt(Parameter!AgeRange.Value / 1000) and Fields!Age.Value <= Parameters!AgeRange.Value Mod 1000?

the CInt(AgeRange/1000) returns 18; the AgeRange mod 1000 returns 40

so Age >=18 and Age <=40

Peter Brinkhaus (6/12/2008)
I hope I do unstand your question this time.
Set the following label/value pairs as the available (non-queried) values to your parameter (let's name it AgeRange):
18-40, 18040
41-60, 41060
61 and above, 61999
Now you can add a filter to your dataset and test for
Fields!Age.Value >= Parameters!AgeRange.Value Mod 1000 and Fields!Age.Value <= CInt(Parameter!AgeRange.Value / 1000).
As an alternative, if you are using T-SQL you can add a simular restriction in your where clause, i.e. ... WHERE Party_dim.Age BETWEEN @AGERANGE % 1000 and @AGERANGE / 1000 and bind query parameter @AGERANGE to you report parameter Parameters!AgeRange.Value
Peter

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

matzer SSCrazy Eights Points: 9727 More actions · Answer 10

Oops, you're absolutely right! I agree with Peter Rijs, it's easy to make a mistake without proper testing. Anyway, the idea was clear, I suppose.

Peter