Cross Tabbed report. Help

Posted by: pete-newman on 4 August 2017, 2:34 pm EST

  • Posted 4 August 2017, 2:34 pm EST

    Im still struggling with getting to grips with producing a cross tabbed report. I have looked at the productsweeklysales example and am still drawing a blank.

    My ado SQL is as follows

    Select Year(RecievedDate) AS 'Year',
           DateName(Month, RecievedDate) as 'Month',
           Count(Licence) as 'FileCount',
           Month(RecievedDate) As 'MonthNo'
    From  BacsHdrYearly
    Where SubmissionType = 'Live'
    GROUP BY Year(RecievedDate) ,DateName(Month, RecievedDate),Month(RecievedDate)
    ORDER BY Year(RecievedDate),Month(RecievedDate)

    which in T-SQL  gives this result

    Year        Month                          FileCount   MonthNo    
    ----------- ------------------------------ ----------- -----------
    2004        January                        1890        1
    2004        February                       1856        2
    2004        March                          2149        3
    2004        April                          1927        4
    2004        May                            1928        5
    2004        June                           2167        6
    2004        July                           2058        7
    2004        August                         2015        8
    2004        September                      2062        9
    2004        October                        2022        10
    2004        November                       2224        11
    2004        December                       2153        12
    2005        January                        2043        1
    2005        February                       2028        2
    2005        March                          1654        3



    Im trying to get the following layout as

    Year      Jan      feb     March    ect

    2004      1890   1856    2149 
    2005      2043   2028    1654

    can anybody explain inlaymans terms how to do this, or provide a simple example with explaintion

  • Replied 4 August 2017, 2:34 pm EST

    I dunno if this is what you call "layman's terms", but these are the simplist approaches with the most consistent return that come to my mind.

    1)
    If you do not mind a return that looks like this...
    Year      Jan      feb     March    ect
    2004      
                1890   1856    2149 
    2005      
                2043   2028    1654

    You can put in a group header for the Year and then use 12 columns in your detail section with AcrossDown as your setting.

    2)
    To match your exact desired results you can break the report into Parent and Sub reports. Parent report would be similar to the group header on the previous suggestion and query on the year. The Sub would for the months with a WHERE based on the year fed it by the Parent. Again the Sub would be using columns with AcrossDown.

    I see a potential problem if your query does not return a full year for the first and every year following save for the last with both suggestions.
  • Replied 4 August 2017, 2:34 pm EST

    Try this sql:

    SELECT
    YEAR(RecievedDate) AS 'Year',
    MONTH(RecievedDate),     
    SUM(CASE WHEN MONTH(RecievedDate) = 1
             THEN 1
             ELSE 0
        END) jan_count, 
    SUM(CASE WHEN MONTH(RecievedDate) = 2
             THEN 1
             ELSE 0
        END) feb_count,
    SUM(CASE WHEN MONTH(RecievedDate) = 3
             THEN 1
             ELSE 0
        END) mar_count,

    ...

    SUM(CASE WHEN MONTH(RecievedDate) = 12
             THEN 1
             ELSE 0
        END) dec_count   

    FROM  BacsHdrYearly
    WHERE SubmissionType = 'Live'
    GROUP BY YEAR(RecievedDate),MONTH(RecievedDate)
    ORDER BY YEAR(RecievedDate),MONTH(RecievedDate)
     
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels