locked
DYNAMIC TABLES AND COLUMNS WITH SSRS RRS feed

  • Question

  • Hi All,

    I have result from Query as below and I want to show them as mentioned below.I want to implement breakdown by Department first and then by name and then show the dynamic userItems and I want to restrict the columns as per max 9 or so.I was able to achieve by showing by showing userItems as rows but I want to show them as columns and make a restriction on number of columns.

    OUTPUT from Query:---->

    Department Name userItems(dynamic)
    Sales Ram Phone
    Sales Ram Tablet
    Sales Ram Bluetooth
    Sales Ram Speaker
    Sales suraj DVD
    Sales Virat Radio
    marketing Vignesh TV
    marketing Vignesh Computer
    marketing Naresh Headset

    What I want to show in SSRS--->

    DEP---Sales
                           userItem1         item2     item3                  item4
      Ram              phone               Tablet     Bluetooth         Speaker
       Suraj             DVD
    Virat               Radio
    DEP---marketing
    Vignesh            TV                Computer
    Naresh              Headset

    Thank You in Advance and if needed i can share more Info regarding this

    Wednesday, August 12, 2020 5:47 PM

Answers

  • Hi,

    Hi JaswanthKumar,

    If you want above outcome,  you need to add an additional column in Your T-SQL query. Add a ItemNumber column, use this to arrange the items.

    SELECT *, ROWNUMBER() OVER (PARTITION BY Name) as ItemNumber
    FROM yourTable

    Then use matrix to design the report.

    row would be Name, column would be ItemNumber.

     

    You can use Matrix (instead of table) to generate dynamic columns. There is no changes required in your sql query for matrix report (its just a list of Department,Name, UserItems) , but the Matrix control in the SSRS will handle all this. You can refer the below links https://msdn.microsoft.com/en-us/library/dd207149.aspx

     

    As your description,you want to limit no. of columns in SSRS matrix report, please refer to http://masud-ahmed-ssrs.blogspot.com/2012/06/limit-no-of-columns-in-ssrs-matrix.html.

    Hope this helps,

    Regards,

    Joy


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.




    • Edited by Joy_Zhao Thursday, August 13, 2020 3:17 AM
    • Proposed as answer by Joy_Zhao Friday, August 14, 2020 3:54 AM
    • Marked as answer by JaswanthKumar Friday, August 14, 2020 4:46 PM
    Thursday, August 13, 2020 2:14 AM

All replies

  • Hi,

    Hi JaswanthKumar,

    If you want above outcome,  you need to add an additional column in Your T-SQL query. Add a ItemNumber column, use this to arrange the items.

    SELECT *, ROWNUMBER() OVER (PARTITION BY Name) as ItemNumber
    FROM yourTable

    Then use matrix to design the report.

    row would be Name, column would be ItemNumber.

     

    You can use Matrix (instead of table) to generate dynamic columns. There is no changes required in your sql query for matrix report (its just a list of Department,Name, UserItems) , but the Matrix control in the SSRS will handle all this. You can refer the below links https://msdn.microsoft.com/en-us/library/dd207149.aspx

     

    As your description,you want to limit no. of columns in SSRS matrix report, please refer to http://masud-ahmed-ssrs.blogspot.com/2012/06/limit-no-of-columns-in-ssrs-matrix.html.

    Hope this helps,

    Regards,

    Joy


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.




    • Edited by Joy_Zhao Thursday, August 13, 2020 3:17 AM
    • Proposed as answer by Joy_Zhao Friday, August 14, 2020 3:54 AM
    • Marked as answer by JaswanthKumar Friday, August 14, 2020 4:46 PM
    Thursday, August 13, 2020 2:14 AM
  • Hi,

    Has your problem been solved?

    If you have any question, please feel free to let me know.

    If your problem has been solved, please click "Mark as Answer" on my reply, thank you for your understanding.

    Best Regards,

    Joy



    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 14, 2020 3:55 AM
  • Thanks Zoy for your quick response I was not able to do this actually but indeed i have taken a subreport and made grouping in the subreport query but I am marking this as accepted so someone can try it out.

    \


    jaswanth kumar

    Friday, August 14, 2020 4:48 PM