Column Order in Matrix

Using SRS 2000.
When creating a matrix report, can you order the columns? By default, they
are in alpha order. My columns are the months of the year. Using Order By
in the query doesn't work.
I want January, February..., not April, August...If you have an actual DateTime field in the dataset, you could just add a
sort expression on the matrix column grouping like
=Month(Fields!SalesDate.Value)
If the column groupings are just based on a string field that actually
contains the month names and there are no other fields that would provide
full datetime information, you could create some "faked" datetime object
based on your month string and then use a sorting expression like
=Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"del" <del@.discussions.microsoft.com> wrote in message
news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> Using SRS 2000.
> When creating a matrix report, can you order the columns? By default,
> they
> are in alpha order. My columns are the months of the year. Using Order
> By
> in the query doesn't work.
> I want January, February..., not April, August...|||Thanks for that. That converts the string to the month number, but it
doesn't sort them 1,2,3... It's still in the same order, but with numbers.
I thought I was being smart by converting in the query using datepart and
datename.
I will look through the info you gave.
"Robert Bruckner [MSFT]" wrote:
> If you have an actual DateTime field in the dataset, you could just add a
> sort expression on the matrix column grouping like
> =Month(Fields!SalesDate.Value)
> If the column groupings are just based on a string field that actually
> contains the month names and there are no other fields that would provide
> full datetime information, you could create some "faked" datetime object
> based on your month string and then use a sorting expression like
> =Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "del" <del@.discussions.microsoft.com> wrote in message
> news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> > Using SRS 2000.
> >
> > When creating a matrix report, can you order the columns? By default,
> > they
> > are in alpha order. My columns are the months of the year. Using Order
> > By
> > in the query doesn't work.
> >
> > I want January, February..., not April, August...
>
>|||Here's what I did:
In the query, I used date(mm,field) to convert the date in to a month. Then
In the matrix report, I sorted by order and used the monthname function. So
after it was sorted numerically, I used the expression,
=monthname(Field!...Value) and it returns the name of the month.
I would have never looked if you hadn't pointed me in the right direction.
Thanks!
"del" wrote:
> Thanks for that. That converts the string to the month number, but it
> doesn't sort them 1,2,3... It's still in the same order, but with numbers.
> I thought I was being smart by converting in the query using datepart and
> datename.
> I will look through the info you gave.
> "Robert Bruckner [MSFT]" wrote:
> > If you have an actual DateTime field in the dataset, you could just add a
> > sort expression on the matrix column grouping like
> > =Month(Fields!SalesDate.Value)
> >
> > If the column groupings are just based on a string field that actually
> > contains the month names and there are no other fields that would provide
> > full datetime information, you could create some "faked" datetime object
> > based on your month string and then use a sorting expression like
> > =Month(DateValue(Fields!MonthNameString.Value & " 1, 2005"))
> > See also:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctmonth.asp
> > http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateValue.asp
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "del" <del@.discussions.microsoft.com> wrote in message
> > news:0CFB34D1-4ACA-4418-A0F6-F5278AFE48AF@.microsoft.com...
> > > Using SRS 2000.
> > >
> > > When creating a matrix report, can you order the columns? By default,
> > > they
> > > are in alpha order. My columns are the months of the year. Using Order
> > > By
> > > in the query doesn't work.
> > >
> > > I want January, February..., not April, August...
> >
> >
> >

Column headers for Matrix

I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.

Can someone help?

You'll need to add another column like Sort or Rank and populate that accordingly. Then sort the output based on that column. If you have several columns that are dynamic that would be best.

pseudo-code for the sort column in your SQL would be:
Case
When "Followup 1" Then 1
When "Initial" Then 2
When "Followup 2" Then 3
End as MyCustomRank

You can also use an expression in the matrix sort...same concept. The pseduo-code for that would be

=IIF(Fields!YOURFIELD.Value = "Followup 1", 1, IIF(Fields!YOURField.Value = "Initial", 2..... and so on

|||

Thank You very much

It worked!

Column headers for Matrix

I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.

Can someone help?

You'll need to add another column like Sort or Rank and populate that accordingly. Then sort the output based on that column. If you have several columns that are dynamic that would be best.

pseudo-code for the sort column in your SQL would be:
Case
When "Followup 1" Then 1
When "Initial" Then 2
When "Followup 2" Then 3
End as MyCustomRank

You can also use an expression in the matrix sort...same concept. The pseduo-code for that would be

=IIF(Fields!YOURFIELD.Value = "Followup 1", 1, IIF(Fields!YOURField.Value = "Initial", 2..... and so on

|||

Thank You very much

It worked!