ssrs matrix report 3

[smitty691]

How do I group the column headings on a matrix sales report to use quarters (like Jan thru March) instead of month name or month number?

 

[xlbo]

Easiest way is to create your quarters in your query rather than in the report.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AlexCuse]

I'm not sure how to do the grouping on columns (in SSRS) but would be interested to know. When I try making the columns show only the quarter, it does so, but still has a separate column for each date.

Perhaps a better option would be select (and group by)

datepart(qq, myDate) 

--and 

year(myDate)

in your SQL itself?

This would also lead to a smaller result set being sent across the network in some cases.

Hope this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[AlexCuse]

I've gotta stop getting sidetracked while posting!

However, the fact that you reached the same conclusion is somewhat reassuring Geoff

Can you even do grouping like this in the matrix report?

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[xlbo]

I think you can Alex but it would be a case of using a variable in the report to create the quarters and then grouping on that - could either be done using an IIF statement or a select case in a function within the code window

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[smitty691]

xlbo:

how do i do that in a sql select statement?

 

[AlexCuse]

Hehe, that was rather easy xlbo. I must have messed up my groupings the first time I tried it somehow. I really didn't need to use the code window at all, just set up a column group on

=DatePart(DateInterval.Quarter, Fields!call_date.Value) & "Q " & DatePart(DateInterval.Year, Fields!call_date.Value)

Smitty - All that aside, I think you're best off to do this in your SQL because it will shrink your result set being passed around. See my post above, it shows how to get the quarter (and presumably you'll need year as well) from a date column.

Hope this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[smitty691]

adding this to the query did it:

TO_CHAR(database.sales_date, 'Q') AS QDATE

and then grouped columns on QDATE

Thanks so much for all your help!

 

[xlbo]

Nice one Alex - didn't know about the DateInterval.Querter, DateInterval.Year functions

noted for future ref!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question