Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column Grouping on Matrix Expand / Collapse
Author
Message
Posted Saturday, October 09, 2010 1:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 07, 2013 3:33 PM
Points: 74, Visits: 196
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
Post #1001744
Posted Monday, October 11, 2010 8:31 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, January 13, 2014 10:46 AM
Points: 509, Visits: 14,593
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.
Post #1002215
Posted Monday, October 11, 2010 9:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 1:32 PM
Points: 335, Visits: 392
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.
Post #1002275
Posted Tuesday, October 12, 2010 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 15, 2012 8:27 AM
Points: 1, Visits: 2
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.
Post #1002873
Posted Tuesday, October 12, 2010 8:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 07, 2013 3:33 PM
Points: 74, Visits: 196
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.
Post #1002882
Posted Tuesday, October 12, 2010 8:57 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, January 13, 2014 10:46 AM
Points: 509, Visits: 14,593
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.
Post #1002904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse