Monday, March 19, 2012

Matrix Last Column Sort Descending

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
SarahOn Sep 19, 6:26 pm, sez...@.gmail.com wrote:
> 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
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|||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|||On Sep 19, 11:45 pm, sez...@.gmail.com wrote:
> 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
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|||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|||All working this morning!
ORDER BY
Date DESC
Rank
in SP worked perfectly :-)
thanks for your help|||On Sep 23, 4:36 pm, sez...@.gmail.com wrote:
> All working this morning!
> ORDER BY
> Date DESC
> Rank
> in SP worked perfectly :-)
> thanks for your help
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

No comments:

Post a Comment