Column Grouping on Matrix

Question

Column Grouping on Matrix

tsmith-960032

SSCrazy

Points: 2518
More actions
October 9, 2010 at 1:03 pm

#230219

I have my ship from locations as row groups.
Now I want to set column groups, grouping by week.
Fields :
ShipDate
ShipFrom
ShipNumber
1/1 - 1/7 1/8 - 1/15
ShipFrom =Count(ShipNumber) =Count(ShipNumber)
ShipFrom =Count(ShipNumber) =Count(ShipNumber)
My question is how to define my column group (Group by)
The row grouping works fine. However, I get multiple columns that refer to the same grouping.
How would I define the the group in this situation, where I want to group by a date range?
I guess the same would hold true if it were a row grouping as well.
Thank you.
No matter what I do, I get multiple

Viewing 6 posts - 1 through 5 (of 5 total)

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

Doug Lane SSCrazy Points: 2118 More actions · Answer 1

Does the query return the date range as a column, for example "1/7 to 1/13" or does it simply pass the date and have RS do the grouping? If RS is grouping by the dates at runtime, you might want to add a field to the query that groups them by week. (A date lookup table with a WeekNumberOfYear column is handy for things like this.) This way, you can have RS group by week#, which should be easier to code and less work for RS to process.

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 2

Doug Lane (10/11/2010)
Does the query return the date range as a column, for example "1/7 to 1/13" or does it simply pass the date and have RS do the grouping? If RS is grouping by the dates at runtime, you might want to add a field to the query that groups them by week. (A date lookup table with a WeekNumberOfYear column is handy for things like this.) This way, you can have RS group by week#, which should be easier to code and less work for RS to process.

Doug is absolutely right. SSRS would do the calculation much less efficiently and much more slowly than the database could do it.

rosie.stringfellow Grasshopper Points: 11 More actions · Answer 3

I agree with the two previous replies. It's usually much more efficient to have SQL do any calculations than it is to have Reporting Services do them.

tsmith-960032 SSCrazy Points: 2518 More actions · Answer 4

Thank you everyone for your responses.

I actually worked it out as follows.

In the report grouping I put in

=Switch(DatePart("ww",LoadDate) = 1,1)

and so forth, up to 52.

I know you're probably laughing, but, it worked, and fast too.

Thanks again.

Doug Lane SSCrazy Points: 2118 More actions · Answer 5

tsmith-960032 (10/12/2010)
I know you're probably laughing, but, it worked, and fast too.

That's what's really important though, right? I'll have to try that sometime.