Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Column Grouping on Matrix


Column Grouping on Matrix

Author
Message
tsmith-960032
tsmith-960032
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 217
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
Doug Lane
Doug Lane
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 14608
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
getoffmyfoot
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 412
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
rosie.stringfellow
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
tsmith-960032
tsmith-960032
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 217
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
Doug Lane
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 14608
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search