locked
SSRS Dynamic Date columns using Matrix RRS feed

  • Question

  • Hi,

    I am having trouble with one of my reports i.e.

    I need to populate monthly data on my report so I wanted the vertical columns to be populated dynamically.

    If user selects Jan month it should show 31 columns and if he selects Feb it should only populate till current date i.e. 4th Feb that means only 4 nos of columns shall be populated dynamically.

    Currently, I have created 31 static tablixes but that does not make sense when are providing start date/end date to the user.

    So,Could you please suggest how can i achieve the same?

    Thanks for your support and understanding!

    With Regards,

    Mohammad Afshaan Shaikh

    Monday, February 4, 2019 4:44 AM

Answers


  • Hi Mohammad Afshaan Shaikh,

    Per my understanding, you want to make the columns dynamically response to the month parameter chosen by the client, right?
    As I know, Matrix could expand column automatically based on your data.  You dataset need be similar to below(For January(which like my “a” in dataset), you need to make sure it have 31 rows in dataset, if you only have 20 rows in dataset, you might will have 20 columns for January)

    For month selection, you could follow below steps:

    1.       Create a MonthParameter with 12 available value from 1 to 12 labeled with “Jan”, “Feb”…”Dec”
    2.       Create the dataset using query like : SELECT * FROM MyDatabase WHERE MONTH([DateInDatasource])=@MonthParameter.
    3.       Populate the column with the field from the dataset of 2.step.

    Then, the Tablix should have correspond date period data that user choose.

    If this is not what you want, please inform me more details (such as dataset, your expecting output)

    Hope this would help you.

    Best regards,
    Lukas

    Monday, February 4, 2019 8:06 AM
  • I think what you're looking at is dynamic pivot

    In SSRS you can get this using Matrix container

    Just give date column as column group and it will create one column for each date value available in your dataset

    Based on other columns and your requirement you can give one or more columns in row group and add data columns to data part (using an aggregate function like Sum,Count etc as pivotting is an aggregate operation)

    an example is shown in below link

    https://www.sqlservergeeks.com/sql-server-how-to-handle-dynamic-column-report-in-ssrs/


    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

    Monday, February 4, 2019 8:39 AM

All replies


  • Hi Mohammad Afshaan Shaikh,

    Per my understanding, you want to make the columns dynamically response to the month parameter chosen by the client, right?
    As I know, Matrix could expand column automatically based on your data.  You dataset need be similar to below(For January(which like my “a” in dataset), you need to make sure it have 31 rows in dataset, if you only have 20 rows in dataset, you might will have 20 columns for January)

    For month selection, you could follow below steps:

    1.       Create a MonthParameter with 12 available value from 1 to 12 labeled with “Jan”, “Feb”…”Dec”
    2.       Create the dataset using query like : SELECT * FROM MyDatabase WHERE MONTH([DateInDatasource])=@MonthParameter.
    3.       Populate the column with the field from the dataset of 2.step.

    Then, the Tablix should have correspond date period data that user choose.

    If this is not what you want, please inform me more details (such as dataset, your expecting output)

    Hope this would help you.

    Best regards,
    Lukas

    Monday, February 4, 2019 8:06 AM
  • I think what you're looking at is dynamic pivot

    In SSRS you can get this using Matrix container

    Just give date column as column group and it will create one column for each date value available in your dataset

    Based on other columns and your requirement you can give one or more columns in row group and add data columns to data part (using an aggregate function like Sum,Count etc as pivotting is an aggregate operation)

    an example is shown in below link

    https://www.sqlservergeeks.com/sql-server-how-to-handle-dynamic-column-report-in-ssrs/


    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

    Monday, February 4, 2019 8:39 AM