Matrix Last Column Sort Descending -- sql server reporting services

sql server reporting services : Matrix Last Column Sort Descending

sezbee NO[at]SPAM gmail.com
9/19/2007 4:26:13 PM

I would like to sort the following matrix data:

July-07 Aug-07 Sep-07
Type A 5 1 4
Type B 6 3 3
Type C 4 2 6
Other 5 5 5

by the maximum date column (i.e. Sep), please note this month will
change each month

July-07 Aug-07 Sep-07
Type C 4 2 6
Type A 5 1 4
Type B 6 3 3
Other 5 5 5

I have tried the following in: Properties / Groups / Rows / Sorting

EXPRESSION
=IIF(Fields!Date.Value = Max(Fields!Date.Value), IIF(Fields!Type.Value
= "Other", -100, Fields!Amount.Value), -1000)

DIRECTION
Descending

With no success.

Any help would be greatly appreciated

Thanks
Sarah

sezbee NO[at]SPAM gmail.com
9/19/2007 9:45:26 PM

Hi Enrique,

Thanks for you suggestions. I have now added a column into the SP
called Rank, this ranks the last months data by amount, I then try to
sort the ROW by Rank and still no success.

Thanks
Sarah

EMartinez
9/20/2007 2:28:58 AM

[quoted text, click to view]

If I understand you correctly, you might be able to get away with:
=iif(Fields!Date.Value = Max(Fields!Date.Value), Fields!
ValueField.Value, 0): Direction: Desc
If this does not work, you may need to sort based on an additional
field added to the returned dataset that has its value determined in
the stored procedure/query that is sourcing the report; possibly, via
cursor or while loop.
Hope this helps.

Regards,

Enrique Martinez
Sr. Software Consultant

sezbee NO[at]SPAM gmail.com
9/20/2007 9:00:48 PM

Hi,

The Rank column is calucated in T-SQL, it is based on the amount
value, which is the coulmn of data in the matrix table. To add to my
worries, my chart is no longer working (which I think is related to
Rank), at least it's Friday :-)

Sarah

EMartinez
9/21/2007 1:21:19 AM

[quoted text, click to view]

You're welcome. Are you sorting the main group in the matrix control
by the Rank column? If not, this might help.

Regards,

Enrique Martinez
Sr. Software Consultant

sezbee NO[at]SPAM gmail.com
9/23/2007 2:36:40 PM

All working this morning!

ORDER BY
Date DESC
Rank

in SP worked perfectly :-)

thanks for your help

EMartinez
9/25/2007 12:49:03 AM

[quoted text, click to view]

You're welcome. Let me know if I can be of further assistance.

Regards,

Enrique Martinez
Sr. Software Consultant

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn