SQLTeam.com | Weblogs | Forums

SSRS - Sorting a Matrix

Hey,

I have a matrix where I'm trying to sort results by the total 'Difference' but I'm running into issues.
The Matrix lists companies, how many tests we did last year/ this year and the difference (trying to identify people we have done more/less tests for).

>     SELECT
>   CAST(Jobs.ID AS INT) AS [Jobs ID]
>   ,Tests.ID AS [Tests ID]
> ,Clients.ID
>   ,Clients.CompanyName
>   ,Clients.Active
> ,CASE 
>   WHEN Clients.CompanyName LIKE 'Adecco%' THEN 'Adecco'
>   WHEN Clients.CompanyName LIKE 'Amazon%' THEN 'Amazon'
>   WHEN Clients.CompanyName LIKE 'Mott MacD%' THEN 'Mott MacDonald Group'
>   WHEN Clients.CompanyName LIKE 'Ringway Jacobs%' THEN 'Ringway Jacobs'
>   WHEN Clients.CompanyName LIKE 'SGS%' THEN 'SGS'
>   WHEN Clients.CompanyName LIKE 'Freightliner%' THEN 'Freightliner'
>   WHEN Clients.CompanyName LIKE 'Amec Foster Whee%' THEN 'Amec Foster Wheeler'
>   WHEN Clients.CompanyName LIKE 'Alstom Transport%' THEN 'Alstom Transport' 
>   WHEN Clients.CompanyName LIKE 'Gist%' THEN 'Gist'
>   WHEN Clients.CompanyName LIKE 'Ainscough%' THEN 'Ainscough'
>   WHEN Clients.CompanyName LIKE 'Eurovia%' THEN 'Eurovia'
>   WHEN Clients.CompanyName LIKE 'Hitachi%' THEN 'Hitachi'
>   WHEN Clients.CompanyName LIKE 'Eaton%' THEN 'Eaton'
>   WHEN Clients.CompanyName LIKE 'Murphy%' THEN 'Murphy'
>     ELSE Clients.CompanyName END AS [CompanyNameCombined]
>   ,Clients.PurchaseOrderNumberIsMandatory
>   ,JobStatuses.Description AS [JobStatuses Description]
>   ,Appointments.StartDateTime
>   ,YEAR(Appointments.StartDateTime) AS [Year]
>   ,MONTH(Appointments.StartDateTime) AS [Month]
> ,CASE WHEN  Clients.CompanyName IS NOT NULL THEN 1 ELSE 0 END AS [NumberofTests]
> ,SUM(CASE WHEN YEAR(Appointments.StartDateTime) = YEAR(getdate()) THEN 1 ELSE -1 END) AS [2018v2017]
>   ,Appointments.EndDateTime
>   ,TestStatuses.Description AS [TestStatuses Description]
>   ,ServiceTypes.Description AS [ServiceTypes Description]
>   ,JobCandidates.CandidateName
>   ,TestIdentifiers.Description AS [TestIdentifiers Description]
> ,ClientContacts.Fullname
> ,MedicalCentres.Name
>   ,Jobs.OnSiteAddress1
>   ,Jobs.OnSiteAddress2
>   ,Jobs.OnSiteAddress3
>   ,Jobs.OnSiteAddress4
>   ,Jobs.OnSiteTown
>   ,Jobs.OnSiteCounty
>   ,Jobs.OnSitePostcode
>   ,Jobs.CancellationReason
> ,Industries.Description AS [Industry]
> ,Users.FirstnameAndSurname
> ,ClientContacts.Email
> ,MedicalProfessionals.Fullname AS [Medical Professional]
> ,Tests.PriceToClient
> ,MedicalProfessionalQualifications.Description AS [Qualification]
> ,TestClinicalReports.IsCaseClosed
> ,Clients.AccountNumber
> ,Certificates.DatePostRoomPrinted
> ,MedicalCentreTypes.Description
> ,Tests.DateReferred
> ,FinalResults.Description AS [Final Result]
> ,Candidates.DateOfBirth
> ,Certificates.ExpiryDate
> ,Certificates.DateClientPrinted
> ,Jobs.PurchaseOrderNumber
> ,ServiceTypes.AppointmentMinutes
> ,MIN(JobInvoices.InvoiceNumber) AS [Invoice Number]
> ,MedicalCentres.Postcode
> ,ReferralStatuses.Description AS [ReferralStatus]
> ,MIN(Adjustments.ID) AS [Adjustment]
> ,Jobs.PreviousJobNotes
> ,JobCandidates.IndustryReference
> ,CONVERT(varchar(12),Appointments.StartDateTime,103) + MedicalCentres.Name + MedicalCentreRooms.Description AS Concat
> FROM
>   Jobs
>   FULL OUTER JOIN Clients
>     ON Jobs.ClientID = Clients.ID
>   FULL OUTER JOIN JobStatuses
>     ON Jobs.JobStatusID = JobStatuses.ID
>   FULL OUTER JOIN JobCandidates
>     ON Jobs.ID = JobCandidates.JobID
>   FULL OUTER JOIN Tests
>     ON JobCandidates.ID = Tests.JobCandidateID
>   FULL OUTER JOIN TestStatuses
>     ON Tests.TestStatusID = TestStatuses.ID
>   FULL OUTER JOIN Appointments
>     ON Tests.AppointmentID = Appointments.ID
>   FULL OUTER JOIN ServiceTypes
>     ON Tests.ServiceTypeID = ServiceTypes.ID
>   FULL OUTER JOIN TestIdentifiers
>     ON Tests.TestIdentifierID = TestIdentifiers.ID
> FULL OUTER JOIN ClientContacts
> ON Jobs.ClientContactID = ClientContacts.ID
> FULL OUTER JOIN MedicalCentreRooms
>     ON MedicalCentreRooms.ID = Appointments.OffSite_MedicalCentreRoomID
> FULL OUTER JOIN MedicalCentres
>     ON MedicalCentreRooms.MedicalCentreID = MedicalCentres.ID 
> FULL OUTER JOIN Industries
> ON Clients.IndustryID = Industries.ID
> FULL OUTER JOIN Users
> ON Jobs.CreatedByUserID = Users.ID
> FULL OUTER JOIN MedicalProfessionals
> ON Tests.TestPerformedByMedicalProfessionalID = MedicalProfessionals.ID OR Appointments.OnSite_MedicalProfessionalID = MedicalProfessionals.ID
> FULL OUTER JOIN MedicalProfessionalQualifications
> ON MedicalProfessionals.MedicalProfessionalQualificationID = MedicalProfessionalQualifications.ID
> FULL OUTER JOIN TestClinicalReports
>     ON Tests.ID = TestClinicalReports.TestID
> FULL OUTER JOIN Certificates
> ON Tests.CurrentCertificateID = Certificates.ID
> FULL OUTER JOIN MedicalCentreTypes
>     ON MedicalCentres.MedicalCentreTypeID = MedicalCentreTypes.ID
> FULL OUTER JOIN FinalResults
> ON Certificates.FinalResultID = FinalResults.ID
> FULL OUTER JOIN Candidates
>     ON JobCandidates.CandidateID = Candidates.ID
> FULL OUTER JOIN JobInvoices
>     ON Jobs.ID = JobInvoices.JobID
> FULL OUTER JOIN ReferralStatuses
> ON Tests.ReferralStatusID = ReferralStatuses.ID
>   FULL OUTER JOIN Adjustments
>     ON Jobs.ID = Adjustments.JobID
> WHERE
>   JobStatuses.Description NOT LIKE N'Cancelled'
> AND (YEAR(Appointments.StartDateTime) = YEAR(getdate()) OR YEAR(Appointments.StartDateTime) = YEAR(getdate())-1)
> AND (MONTH(Appointments.StartDateTime) = MONTH(getdate()) OR MONTH(Appointments.StartDateTime) = MONTH(getdate())-1 OR MONTH(Appointments.StartDateTime) = MONTH(getdate())-2 or MONTH(Appointments.StartDateTime) = MONTH(getdate())+1  )
> AND Clients.CompanyName LIKE @Company
> AND TestStatuses.Description NOT LIKE 'Cancelled'
> AND Clients.CompanyName NOT LIKE '%test%'
> AND Clients.CompanyName NOT LIKE 'DCSL'
> AND Clients.Active = 'True'
> GROUP BY 
>   Jobs.ID
>   ,Tests.ID
> ,Clients.ID
>   ,Clients.CompanyName
>   ,Clients.Active
>   ,Clients.PurchaseOrderNumberIsMandatory
>   ,JobStatuses.Description 
>   ,Appointments.StartDateTime
>   ,Appointments.EndDateTime
>   ,TestStatuses.Description 
>   ,ServiceTypes.Description 
>   ,JobCandidates.CandidateName
>   ,TestIdentifiers.Description
> ,ClientContacts.Fullname
> ,MedicalCentres.Name
>   ,Jobs.OnSiteAddress1
>   ,Jobs.OnSiteAddress2
>   ,Jobs.OnSiteAddress3
>   ,Jobs.OnSiteAddress4
>   ,Jobs.OnSiteTown
>   ,Jobs.OnSiteCounty
>   ,Jobs.OnSitePostcode
> ,Industries.Description
> ,Users.FirstnameAndSurname
> ,ClientContacts.Email
> ,MedicalProfessionals.Fullname 
> ,Tests.PriceToClient
> ,MedicalProfessionalQualifications.Description
> ,TestClinicalReports.IsCaseClosed
> ,Clients.AccountNumber
> ,Certificates.DatePostRoomPrinted
> ,MedicalCentreTypes.Description
> ,Tests.DateReferred
> ,FinalResults.Description
> ,Candidates.DateOfBirth
> ,Certificates.ExpiryDate
> ,Certificates.DateClientPrinted
> ,Jobs.PurchaseOrderNumber
> ,Jobs.CancellationReason
> ,ServiceTypes.AppointmentMinutes
> ,MedicalCentres.Postcode
> ,ReferralStatuses.Description
> ,Jobs.PreviousJobNotes
> ,JobCandidates.IndustryReference
> ,MedicalCentreRooms.Description
> 
> ORDER BY
> 
> SUM(CASE WHEN YEAR(Appointments.StartDateTime) = YEAR(getdate()) THEN 1 ELSE -1 END)

I've tried ORDER BY & Interactive sorting. Order By does nothing, interactive sorting looks to do something, but they're not really in order and it then just throws the figures out a little.

Any help welcome, thanks

Click the drop-down arrow next to the Company Name in the Row Groups (at the bottom left), select properties, and in the sorting tab, set your sort order.

Thanks for your reply, it looks like this does something - but again it just makes the figures incorrect when they were fine before.

it seems like there is more to this than meets the eye!

wrong|690x249

Tablix allows you to sort columns and/or rows, and allows sorting on groups. Based on the information you provided, I am not able to discern exactly what needs to be done. Experiment with the drop-downs in the Row Groups and the Column Groups, and in each level of grouping if you have multiple levels by changing the sorting and observing the behavior when you change each. Remove any sorting you have on the Tablix itself.

Also get rid of the Order By clause in your query as that will conflict with the RDL sorting.