February 4, 2009 at 5:00 am
Hi,
I am relatively new to reporting services and to this forum, so i will try to give as many details as possible and would appreciate your help.
I am trying to create a Matrix report using a data set with the following query:
select company.Employees.Name, data.Operations.Time, sum(data.Operations.Revenue)
from data.Operations inner join company.Employees on data.Operations.SalesRepId = company.Employees.EmployeeId
where data.Operations.Time >= dateadd(day, 0-@last, (getdate()-1))
group by company.Employees.Name, company.Employees.EmployeeId, data.Operations.SalesRepId, data.Operations.Time
having (company.Employees.EmployeeId=data.Operations.SalesRepId)
order by data.Operations.Time, sum(data.Operations.Revenue)
i set the company.Employees.Name on columns, data.Operations.Time on rows
and what i got in the preview is that the columns are sorted according to the revenue of the first day of the report (which is on the right hand side), and i want to change it so the columns would be sorted according to the revenue of the last day of the report (which is on the left hand side)
i tried to go over the report properties but found nothing, i aslo tried to play with the order of the query but no luck.
any idea?
much appreciated
PH.
February 4, 2009 at 5:29 am
Looking back, I think that the columns are displayed in the order they are returned. Please note that the column order behaviour may reflect that of a character data type.
Max
February 4, 2009 at 6:28 am
sorry i didn't understand what you mean; but let me explain what is happening by visualizing it here
Jan 01 | Jan 02 | Jan 03
Jack $700 | $ 90 | $ 25
Tom $650 | $1080 | $23
Nick $25 | $700 |$ 1000
meaning the Names are sorted according to the value of the left columns of the report.
but i want it to be
Jan 01 | Jan 02 | Jan 03
Nick $25 | $700 |$ 1000
Jack $700 | $ 90 | $ 25
Tom $650 | $1080 | $23
meaning according to the right column of the report.
now how can i change that?
February 4, 2009 at 7:06 am
Hi PH
I think this should do what you need:
SELECT Name, Time,TotalRevenue, CASE WHEN Time=LatestDate THEN TotalRevenue ELSE 0 END as SortOrder
FROM
(SELECTcompany.Employees.Name, data.Operations.Time, SUM(data.Operations.Revenue) as TotalRevenue,
(SELECT MAX(data.Operations.Time)
FROM data.Operations
WHERE data.Operations.Time >= dateadd(day, 0-@last, (getdate()-1))) as LatestDate
FROMdata.Operations INNER JOIN company.Employees ON data.Operations.SalesRepId = company.Employees.EmployeeId
WHEREdata.Operations.Time >= dateadd(day, 0-@last, (getdate()-1))
GROUP BY company.Employees.Name, data.Operations.Time --company.Employees.EmployeeId, data.Operations.SalesRepId,
--having (company.Employees.EmployeeId=data.Operations.SalesRepId)
--ORDER BY data.Operations.Time, sum(data.Operations.Revenue)
) x
ORDER BY (CASE WHEN Time=LatestDate THEN TotalRevenue ELSE 0 END) DESC
This should return the data in the order that you want, although I always tend to use SSRS to sort my data. To do so, edit the relevant matrix group to sort by the SortOrder field.
Regards
Alun
February 4, 2009 at 7:54 am
Hi Alun,
can you elaborate on "edit the relevant matrix group to sort by the SortOrder field"
how do i do that?
thanks
PH
February 4, 2009 at 7:58 am
I agree that it's easier to do the sorting in the SSRS report.
click on the group that you want to change the order of.
right click and choose edit group.
Click on the sorting tab.
enter the appropriate column(s) and direction and you'll be all set.
-Luke.
February 4, 2009 at 11:03 am
Generally I'd agree and I even remember agreeing with my lecturer and colleagues, however, I just checked the matrix report I was referring to and I sucessfully display the data in a sorted format from the dataset (Command Type: proc) in the report without using Group Sorting. I think I took this path due to having worked with "client-side recordsets" in the past where client-side ordering was greatly discouraged (although your reporting services server should be able to handle it 😉 ). Strangely, my report even reflects data similar to Levin's where the primary column group is also displayed as Mmm YY.
It sorts from the proc by use of a derived column called MonthKey int, with the value(s) returned looking like: 200901 (2009 is the year and 01 the month). MonthKey is populated by using:
SELECT MonthKey = 100 * DATEPART( yy , GETDATE() ) + DATEPART( mm , GETDATE() ) -- WHERE GETDATE() is your datetime value
In my final output, MonthKey is the first ORDER BY expression, followed by (as in Levin's example code) : company.Employees.Name, with any further ordering dictated by your secondary Column and Row groups.
Please give it a go and let me know if it works for you.
Max
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply