Hi Guys, Quick Question. I am pulling data from a CRM database and bumping it onto a report. The purpose of the report is to show the customer's name - the amount of cases they have had logged then (as a collapsable feature) details of the cases. The code i have for the "Amount of cases logged" is a simple count feature which counts the amount of rows present in the collapsable feature. I would like to know if i can Order my results so I can show the customers with the most calls logged at the top. Here is what i have: DECLARE @Today DATETIME; SET @Today =GETDATE() SELECT Incident.IncidentId, Incident.TicketNumber, Incident.equipment_type, Incident.CustomerIdName, Incident.OwnerName, Incident.CreatedOn, Incident.StatusCode, Incident.CaseTypeCode FROM MSCRM.dbo.Incident Incident WHERE Incident.CreatedOn BETWEEN Convert (DateTime, @StartDate, 103) AND @Today AND (Incident.CaseTypeCode=9) AND (Incident.equipment_type=10) AND (Incident.StatusCode=0) OR (Incident.StateCode=1) ORDER BY ........... Now, as i said above, the "Amount of cases logged" is just a simple row count : =Count(Fields!OwnerIdName.Value) This will produce: Company Name Cases Logged ----------------------------------------- Microsoft Corporation 5 Dell Corporation 7 HP 1 If possible i would like it to be: Company Name Cases Logged ----------------------------------------- Dell Corporation 7 Microsoft Corporation 5 HP 1 Is there any way i can order my output by the quantity contained in this field? Thanks, M

Hi Duffy202, You may consider adding interactive sorting to your report. http://msdn.microsoft.com/en-us/library/dd207011.aspx Remember to mark as an answer if this post has helped you.

Hi Igor, Thanks for the reply. From what I can see, the Interactive Sort will only allow you to sort values that are part of the query. The value i want to sort by isn't in the database it is just a Expression that counts the number of rows per customer eg: Company Name Cases Logged ---------------------------------------------- Microsoft Corporation 2 CASEREF-12345 Monitor Problem CASEREF-12222 PC Problem Dell Corporation 4 CASEREF-12223 PC Problem CASEREF-12228 PC Problem CASEREF-12229 PC Problem CASEREF-12224 PC Problem Thanks again for your help. M

There are many ways you can do this in SQL. A simple idea is as below. You can then use dataset order or order by the TotalIncidents column in the report. WITH IncidentCount(CustomerIdName, TotalIncidents) AS ( SELECT CustomerIdName, count(*) FROM MSCRM.dbo.Incident Incident WHERE Incident.CreatedOn BETWEEN Convert (DateTime, @StartDate, 103) AND @Today AND (Incident.CaseTypeCode=9) AND (Incident.equipment_type=10) AND (Incident.StatusCode=0) OR (Incident.StateCode=1) ) SELECT Incident.IncidentId, Incident.TicketNumber, Incident.equipment_type, Incident.CustomerIdName, IncidentCount.TotalIncidents, Incident.OwnerName, Incident.CreatedOn, Incident.StatusCode, Incident.CaseTypeCode FROM MSCRM.dbo.Incident Incident INNER JOIN IncidentCount ON IncidentCount.CustomerIdName = Incident.CustomerIdName WHERE Incident.CreatedOn BETWEEN Convert (DateTime, @StartDate, 103) AND @Today AND (Incident.CaseTypeCode=9) AND (Incident.equipment_type=10) AND (Incident.StatusCode=0) OR (Incident.StateCode=1) ORDER BY IncidentCount.TotalIncidents Desc

Hello duffy202, You can go to Group Properties, and you will find a sorting tab. There you can write your condition for count. And you can specify Ascending or Descending Order there. Hope it helps. Thank you, SammieRS.Sammie

Select "Tablix Properties" and then sorting option, click add button. For more clarity i have attached image for reference.. Pls mark as answer, if this helps- Kerobin