none
SSRS Visual Studio Matrix RRS feed

  • Question

  • Hi I am trying to make the following query as a matrix:

    SELECT distinct sending_organisation AS [Supplier], Sum(Case When Status = 'Defect' Then 1 Else 0 End) As Defect,Sum(Case When Status = 'Failed' Then 1 Else 0 End) As Failed,Sum(Case When Status = 'Success' Then 1 Else 0 End) As Success,SUM(Case When Status IN ('Success','Failed') THEN 1 ELSE 0 END) AS [Total Billable]FROM tx  CROSS APPLY ( VALUES (CONCAT(left(DATENAME(mm, Date_Reported),3),' ',                             DATENAME(yyyy, Date_Reported)),                      DATEPART(mm, Date_Reported)                     )            ) t1 (Date, Morder) where Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)GROUP BY sending_organisationORDER BY sending_organisation ASC;

    I want the output to look like this on visual studio:

    Supplier | Defect | Failed | Success | Total_Billable

    How do i do this as a matrix on visual studio? When I put supplier as row and rest in column it asks me to be something as details but what can i put?

    Thanks in advance

    Friday, June 22, 2018 8:03 AM

Answers

  • Actually if you're planning to use matrix then you dont need to do crosstabbing in your query

    You can simply bring the query like this

    SELECT sending_organisation AS [Supplier], Status,COUNT(*) AS StatusCount
    FROM tx  
    CROSS APPLY ( VALUES (CONCAT(left(DATENAME(mm, Date_Reported),3),' ',                             DATENAME(yyyy, Date_Reported)),                      DATEPART(mm, Date_Reported)                     )            ) t1 (Date, Morder) where Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
    GROUP BY sending_organisation,Status
    ORDER BY sending_organisation ASC;


    Then in your matrix

    use Row group as

    =Fields!Supplier.Value

    and column group as

    =Fields!Status.Value

    and for the data use expression as

    =Sum(Fields!StatusCount.Value)
    Also right click on your column group and choose Add Total -> After option to add Total_Billable column



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by taz 91 Tuesday, June 26, 2018 11:26 AM
    Friday, June 22, 2018 3:40 PM

All replies

  • Hi,

    In order to answer your question, I'm going to use these data examples :

    SELECT 'Supp1' AS Supplier,11 AS Defect,12 AS Failed, 6  AS Success, 40 AS Total_billable
    UNION ALL
    SELECT 'Supp2' AS Supplier,2 AS Defect, 45 AS Failed, 7  AS Success, 17 AS Total_billable
    UNION ALL
    SELECT 'Supp3' AS Supplier,5 AS Defect, 13 AS Failed, 4  AS Success, 13 AS Total_billable
    UNION ALL
    SELECT 'Supp4' AS Supplier,7 AS Defect, 12 AS Failed, 2  AS Success, 40 AS Total_billable

    So, after I created my dataset with the above data.

    First, you have to add the supplier in the row group :

    Then delete the column group :

    Add the second field in the data columns :

    And for each field, add a new column :

    At the end, your matrix should look like this :

    I hope this can help.


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Friday, June 22, 2018 3:05 PM
  • Actually if you're planning to use matrix then you dont need to do crosstabbing in your query

    You can simply bring the query like this

    SELECT sending_organisation AS [Supplier], Status,COUNT(*) AS StatusCount
    FROM tx  
    CROSS APPLY ( VALUES (CONCAT(left(DATENAME(mm, Date_Reported),3),' ',                             DATENAME(yyyy, Date_Reported)),                      DATEPART(mm, Date_Reported)                     )            ) t1 (Date, Morder) where Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
    GROUP BY sending_organisation,Status
    ORDER BY sending_organisation ASC;


    Then in your matrix

    use Row group as

    =Fields!Supplier.Value

    and column group as

    =Fields!Status.Value

    and for the data use expression as

    =Sum(Fields!StatusCount.Value)
    Also right click on your column group and choose Add Total -> After option to add Total_Billable column



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by taz 91 Tuesday, June 26, 2018 11:26 AM
    Friday, June 22, 2018 3:40 PM
  • Hi taz 91,

    According to your description, I think your original  dataest  might be changed like below by query

    If so, the current dataset structure is more similar to Table. I think you could use Table to achieve this. You could add [success], [fail], [total] and [defect] in Table, then right click [success ] to add parent group(row). You will get the result like below

    If this is not similar to your dataset or this is not what you want, please correct me.

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 25, 2018 2:52 AM
    Moderator
  • Hi Visakh thank you, this works but for total billable it is not the sum of all thh status option but only the sum of failed and success (excluding defect). How do i do this as status is one field?
    Tuesday, June 26, 2018 11:37 AM
  • Hi Visakh thank you, this works but for total billable it is not the sum of all thh status option but only the sum of failed and success (excluding defect). How do i do this as status is one field?

    just tweak the expression for Total Billable like this

    =Sum(IIF(Fields!Status.Value = "Failed" Or Fields!Status.Value = "Success",Fields!StatusCount.Value,0))


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, June 26, 2018 12:18 PM
  • great that works thanks Visakh :)
    Tuesday, June 26, 2018 1:23 PM