none
SSRS and SQL Pivot RRS feed

  • Question

  • i need help on the following.

    I have the basic query below:

    select count(transactions)
    from tx
    where customer = 'AA'

    This gives me a count of all transactions for the relevant client.

    What I want is a query that gives me the same output but broken down into the LATEST last 12 weeks (Monday-Sunday is one full week). The date_reported column is where the date is from the tx table. These values should be presented as 12 columns with the header of each column presented as the last date of the week (ie Sunday's date).

    Furthermore the total transactions are split into status- failed and success. I would like the rows of the transactions to be failed and success so the final table would look like this:

                   25/03/2018 (week 1)| 01/04/2018| ........ |17/06/2018 << (week 12)
    Success            100            |     200   | ........ |    150   
    Failed              3             |     4     | ........ |    6 

    Any ideas how this can be done? I am using sql server but the table will be created on SSRS visual studio, so maybe it is easier to be done there?

    Thanks you in advance

    Thursday, June 28, 2018 10:53 AM

Answers

  • Since you didnt post the table structure details, Im going to assume column names. So make sure you replace columns with actual names

    So as per your explanation what you need is this as a query

    SELECT TransactionStatus,DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0) AS Weekdate,COUNT(transactions) AS TranCount
    FROM tx
    WHERE customer = 'AA'
    AND DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0) >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7 * 7,0)
    AND DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
    GROUP BY TransactionStatus,DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0)

    Then in your report use a matrix container

    Include TransactionStatus column in row group

    WeekDate column in column group

    and for the data portion add expression like

    =Sum(Fields!TransactionCount.Value)


    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 Thursday, June 28, 2018 3:12 PM
    Thursday, June 28, 2018 11:24 AM

All replies

  • Since you didnt post the table structure details, Im going to assume column names. So make sure you replace columns with actual names

    So as per your explanation what you need is this as a query

    SELECT TransactionStatus,DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0) AS Weekdate,COUNT(transactions) AS TranCount
    FROM tx
    WHERE customer = 'AA'
    AND DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0) >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7 * 7,0)
    AND DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
    GROUP BY TransactionStatus,DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0)

    Then in your report use a matrix container

    Include TransactionStatus column in row group

    WeekDate column in column group

    and for the data portion add expression like

    =Sum(Fields!TransactionCount.Value)


    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 Thursday, June 28, 2018 3:12 PM
    Thursday, June 28, 2018 11:24 AM
  • Hi visakh, thank you for your response.

    The above query works but it only restricts it to the current week. Is there a way of me getting values for the LAST 12 weeks? So my matrix on Visual Studio has 12 columns?

    Thursday, June 28, 2018 12:26 PM
  • Hi visakh, thank you for your response.

    The above query works but it only restricts it to the current week. Is there a way of me getting values for the LAST 12 weeks? So my matrix on Visual Studio has 12 columns?

    Can you tweak it like this and see

    SELECT TransactionStatus,DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0) AS Weekdate,COUNT(transactions) AS TranCount
    FROM tx
    WHERE customer = 'AA'
    AND DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0) >= DATEADD(wk,-12,DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7 * 7,0))
    AND DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,0) < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) 
    GROUP BY TransactionStatus,DATEADD(dd,DATEDIFF(dd,0,txdate)/7 * 7,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

    Thursday, June 28, 2018 7:57 PM
  • This is perfect, thanks Visakh you truly are an SSRS/SQL guru
    Friday, June 29, 2018 8:35 AM
  • This is perfect, thanks Visakh you truly are an SSRS/SQL guru

    You're welcome :)

    Thanks for the kind words too


    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

    Friday, June 29, 2018 8:54 AM