none
SSRS 2008 R2 matrix report issue

    Question

  • Hi, I am using SQL Server 2008 R2 Reporting Services. I am creating a matrix report and have no groupings. I do not see Store A & Store B sales Transactions on the same line. See below image.

    I would like to report something like this(Please see below image).

    Thanks in advance..................


    Ione

    Thursday, July 18, 2013 11:05 PM

Answers

  • Hi,

    You need to create row group. Please change the query to,

    ;with storedata as 
    (
    	SELECT 1 StoreId, 'Store A' Store, '7/1/2013' [SaleDate], 'Electronics' [Type], 'Camera' [Product], 'ABC123' [Code]
    	UNION ALL
    	SELECT 1, 'Store A', '7/1/2013', 'Electronics', 'Notebook', 'XYZ459'
    	UNION ALL
    	SELECT 1, 'Store A', '7/1/2013', 'Home Appliance', 'Washing Machine', 'XSFD 31221' 
    	UNION ALL
    	SELECT 1, 'Store A', '7/2/2013', 'Home Furnishings', 'Love Seat', '8398'
    	UNION ALL 
    	SELECT 1, 'Store A', '7/3/2013', 'Groceries', 'Wheat Bread', '753295'
    	UNION ALL
    	SELECT 1, 'Store A', '7/4/2013', 'Electronics', 'Camcorder', 'WZS347'
    	UNION ALL
    	SELECT 1, 'Store A', '7/4/2013', 'Electronics', 'Recorder', 'XZD42895'
    	UNION ALL
    	SELECT 1, 'Store A', '7/4/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    	UNION ALL
    	SELECT 2, 'Store B', '7/1/2013', 'Electronics', 'Camera', 'ABC123'
    	UNION ALL
    	SELECT 2, 'Store B', '7/1/2013', 'Electronics', 'Notebook', 'XYZ459'
    	UNION ALL
    	SELECT 2, 'Store B', '7/2/2013', 'Home Appliance', 'Washing Machine', 'XSFD 31221' 
    	UNION ALL
    	SELECT 2, 'Store B', '7/3/2013', 'Home Furnishings', 'Love Seat', '8398'
    	UNION ALL 
    	SELECT 2, 'Store B', '7/4/2013', 'Groceries', 'Wheat Bread', '753295'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Camcorder', 'WZS347'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Recorder', 'XZD42895'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Recorder', 'XZD42895'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    )
    select * 
    	,ROW_NUMBER() Over(Partition by [StoreId] order by [SaleDate],[Type],[Product],[Code])  DateRank
    from storedata

    Use daterank for rowgroup and storeid for column group.

    Regards

    Srini

    • Marked as answer by ione721 Monday, July 29, 2013 3:41 PM
    Friday, July 26, 2013 2:44 PM

All replies

  • The best way to do this (according to me) is to create a field called RankDate in your dataset query which will return the same number for the same dates. Now you can place this field in the row grouping and then then the dates will appear in the same line (as they are belonging to the same RankDate value). You can then hide the RankDate row so it is not visible in the report.

    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Friday, July 19, 2013 3:39 AM
  • Thank you Jason for your response. Your trick worked upto an extent (Well if I have multiple sale transactions on 07/01 I get just 1 random transaction). But I want my matrix report to show all records with no blanks in between is there a work around.

    Regards...........


    Ione



    • Edited by ione721 Monday, July 22, 2013 3:42 PM typo
    Friday, July 19, 2013 5:33 AM
  • Hi ione721,

    You can refer to the IsNothing() function, for example:
    =IIF(IsNothing(Fields!Date.Value),"Blank",Fields!Date.Value)

    For more information about expression example, please see:
    http://technet.microsoft.com/en-us/library/ms157328.aspx

    Best Regards,


    Elvis Long
    TechNet Community Support

    Thursday, July 25, 2013 10:54 AM
  • Hi,

    Modify RankDate (suggested by Jason), so that it generates dense_rank partition by date and store. Use that for row grouping

    This ensures that, if there are multiple sales on 7/1 under any store it will generate unique row grouping.

    Thanks and regards.

    Srini  

    Thursday, July 25, 2013 11:46 AM
  • Thanks all for your response. Nothing worked out so far. Problem still persists.

    Regards...............


    Ione

    Thursday, July 25, 2013 3:02 PM
  • Hi, Query dataset.

    DECLARE @Sale TABLE (StoreId INT, Store VARCHAR(50), SaleDate DATE, [Type] VARCHAR(50), Product VARCHAR(50), Code VARCHAR(50)) 
    INSERT INTO @Sale
    SELECT 1, 'Store A', '7/1/2013', 'Electronics', 'Camera', 'ABC123'
    UNION ALL
    SELECT 1, 'Store A', '7/1/2013', 'Electronics', 'Notebook', 'XYZ459'
    UNION ALL
    SELECT 1, 'Store A', '7/1/2013', 'Home Appliance', 'Washing Machine', 'XSFD 31221' 
    UNION ALL
    SELECT 1, 'Store A', '7/2/2013', 'Home Furnishings', 'Love Seat', '8398'
    UNION ALL 
    SELECT 1, 'Store A', '7/3/2013', 'Groceries', 'Wheat Bread', '753295'
    UNION ALL
    SELECT 1, 'Store A', '7/4/2013', 'Electronics', 'Camcorder', 'WZS347'
    UNION ALL
    SELECT 1, 'Store A', '7/4/2013', 'Electronics', 'Recorder', 'XZD42895'
    UNION ALL
    SELECT 1, 'Store A', '7/4/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    UNION ALL
    SELECT 2, 'Store B', '7/1/2013', 'Electronics', 'Camera', 'ABC123'
    UNION ALL
    SELECT 2, 'Store B', '7/1/2013', 'Electronics', 'Notebook', 'XYZ459'
    UNION ALL
    SELECT 2, 'Store B', '7/2/2013', 'Home Appliance', 'Washing Machine', 'XSFD 31221' 
    UNION ALL
    SELECT 2, 'Store B', '7/3/2013', 'Home Furnishings', 'Love Seat', '8398'
    UNION ALL 
    SELECT 2, 'Store B', '7/4/2013', 'Groceries', 'Wheat Bread', '753295'
    UNION ALL
    SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Camcorder', 'WZS347'
    UNION ALL
    SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Recorder', 'XZD42895'
    UNION ALL
    SELECT 2, 'Store B', '7/5/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    UNION ALL
    SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Recorder', 'XZD42895'
    UNION ALL
    SELECT 2, 'Store B', '7/5/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    GO

    Thanks in advance...........

    Ione

    Thursday, July 25, 2013 3:50 PM
  • Hi,

    ;with storedata as 
    (
    	SELECT 1 StoreId, 'Store A' Store, '7/1/2013' [SaleDate], 'Electronics' [Type], 'Camera' [Product], 'ABC123' [Code]
    	UNION ALL
    	SELECT 1, 'Store A', '7/1/2013', 'Electronics', 'Notebook', 'XYZ459'
    	UNION ALL
    	SELECT 1, 'Store A', '7/1/2013', 'Home Appliance', 'Washing Machine', 'XSFD 31221' 
    	UNION ALL
    	SELECT 1, 'Store A', '7/2/2013', 'Home Furnishings', 'Love Seat', '8398'
    	UNION ALL 
    	SELECT 1, 'Store A', '7/3/2013', 'Groceries', 'Wheat Bread', '753295'
    	UNION ALL
    	SELECT 1, 'Store A', '7/4/2013', 'Electronics', 'Camcorder', 'WZS347'
    	UNION ALL
    	SELECT 1, 'Store A', '7/4/2013', 'Electronics', 'Recorder', 'XZD42895'
    	UNION ALL
    	SELECT 1, 'Store A', '7/4/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    	UNION ALL
    	SELECT 2, 'Store B', '7/1/2013', 'Electronics', 'Camera', 'ABC123'
    	UNION ALL
    	SELECT 2, 'Store B', '7/1/2013', 'Electronics', 'Notebook', 'XYZ459'
    	UNION ALL
    	SELECT 2, 'Store B', '7/2/2013', 'Home Appliance', 'Washing Machine', 'XSFD 31221' 
    	UNION ALL
    	SELECT 2, 'Store B', '7/3/2013', 'Home Furnishings', 'Love Seat', '8398'
    	UNION ALL 
    	SELECT 2, 'Store B', '7/4/2013', 'Groceries', 'Wheat Bread', '753295'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Camcorder', 'WZS347'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Recorder', 'XZD42895'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Recorder', 'XZD42895'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    )
    select * 
    	,ROW_NUMBER() Over(Partition by [SaleDate],[StoreId] order by [Type],[Product],[Code])  DateRank
    from storedata

    row Group - SaleDate + DateRank

    Col group - storeid

    Regards

    Srini

    Thursday, July 25, 2013 5:52 PM
  • Thanks Srini for your response. I you look at Store B I do not want blank records in between this is what I am struggling with. Is there a work around for this issue?

    Regards.........


    Ione


    • Edited by ione721 Thursday, July 25, 2013 8:29 PM incomplete
    Thursday, July 25, 2013 8:22 PM
  • Hi,

    If you don't want the balnk lines, storeA third record (which is 7/1 sales) aligns with storeB first record of 7/2. Is it this ok?

    Then try removing the row group completely, only keep column group.

    Regards

    Srini 

    Thursday, July 25, 2013 9:22 PM
  • Thanks Srini, Yes, I am ok with that and had my matrix with no groupings. If you read my OP "I am creating a matrix report and have no groupings." 

    Tried all different things no luck yet.


    Ione

    Friday, July 26, 2013 4:38 AM
  • Hi,

    You need to create row group. Please change the query to,

    ;with storedata as 
    (
    	SELECT 1 StoreId, 'Store A' Store, '7/1/2013' [SaleDate], 'Electronics' [Type], 'Camera' [Product], 'ABC123' [Code]
    	UNION ALL
    	SELECT 1, 'Store A', '7/1/2013', 'Electronics', 'Notebook', 'XYZ459'
    	UNION ALL
    	SELECT 1, 'Store A', '7/1/2013', 'Home Appliance', 'Washing Machine', 'XSFD 31221' 
    	UNION ALL
    	SELECT 1, 'Store A', '7/2/2013', 'Home Furnishings', 'Love Seat', '8398'
    	UNION ALL 
    	SELECT 1, 'Store A', '7/3/2013', 'Groceries', 'Wheat Bread', '753295'
    	UNION ALL
    	SELECT 1, 'Store A', '7/4/2013', 'Electronics', 'Camcorder', 'WZS347'
    	UNION ALL
    	SELECT 1, 'Store A', '7/4/2013', 'Electronics', 'Recorder', 'XZD42895'
    	UNION ALL
    	SELECT 1, 'Store A', '7/4/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    	UNION ALL
    	SELECT 2, 'Store B', '7/1/2013', 'Electronics', 'Camera', 'ABC123'
    	UNION ALL
    	SELECT 2, 'Store B', '7/1/2013', 'Electronics', 'Notebook', 'XYZ459'
    	UNION ALL
    	SELECT 2, 'Store B', '7/2/2013', 'Home Appliance', 'Washing Machine', 'XSFD 31221' 
    	UNION ALL
    	SELECT 2, 'Store B', '7/3/2013', 'Home Furnishings', 'Love Seat', '8398'
    	UNION ALL 
    	SELECT 2, 'Store B', '7/4/2013', 'Groceries', 'Wheat Bread', '753295'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Camcorder', 'WZS347'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Recorder', 'XZD42895'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Electronics', 'Recorder', 'XZD42895'
    	UNION ALL
    	SELECT 2, 'Store B', '7/5/2013', 'Home Appliance', 'Microwave Oven', 'DAQ38948' 
    )
    select * 
    	,ROW_NUMBER() Over(Partition by [StoreId] order by [SaleDate],[Type],[Product],[Code])  DateRank
    from storedata

    Use daterank for rowgroup and storeid for column group.

    Regards

    Srini

    • Marked as answer by ione721 Monday, July 29, 2013 3:41 PM
    Friday, July 26, 2013 2:44 PM
  • Thanks very much Srini, I will work on it and let you know.

    Regards.................


    Ione

    Sunday, July 28, 2013 7:53 AM