none
RDL - Dynamically create column from values

    Question

  • I am creating an RDL and I am querying from a database.

    I am selecting this columns from my database:

    ID, Car Brand, Car Model, Year

    Now what I want to show in my RDL is the following columns

    ID, Car Brand, Car Model, 2009, 2010, 2013 - (NOTE: the year are from the Year column in my database and I want it to make it as a column in my RDL, and also dynamically, whenever a new year (2014) is added in the Year column in my database, then this will create a new column in my RDL so it will become ID, Car Brand, Car Model, 2009, 2010, 2013, 2014


    ----------------------- Sharepoint Newbie

    Wednesday, July 10, 2013 7:56 AM

Answers

  • Hi Fix,

    Please refer to the steps below to do it.

    1. Drag a a table control to your design surface.
    2. Delete the Header row and drag [Count] field to the first column.
    3. Right-click [Count] cell>Add Group>Parent Group (Column Group)
    4. Select [Year] field on the Group by drop-down list on Tablix group window.
    5. Right-click [Count] cell>Add Group>Parent Group (Row Group)
    6. Select [CarModel] field on the Group by drop-down list on Tablix group window.
    7. Insert a column to the left of [CarModel] and drag [CarBrand] to this cloumn.
    8. Right-click Details on the Row Groups pane>Delete Group>Delete Group only

    The report looks like below.

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Thursday, July 11, 2013 1:52 AM
    Moderator

All replies

  • We can achieve this using Dynamic PIVOT Command.

    Can you display a format of the output along with data ?


    Regards, RSingh



    • Edited by RSingh() Wednesday, July 10, 2013 8:14 AM add txt
    Wednesday, July 10, 2013 7:58 AM
  • Hi FixMe,

    As such there is not way to create dynamic columns in the SSRS report. 

    In case you want to add dynamic column, you need to design the columns prehand and make them visible true and false based on the condition.

    Here are some link that can help you : 

    http://sql-developers.blogspot.in/2010/10/displaying-dynamic-columns-in-ssrs.html

    http://www.codeproject.com/Articles/11254/SQL-Reporting-Services-with-Dynamic-Column-Reports

    Other options if you want to displaying some measurable value under Year, then you can go for matrix report(with column group) where value of column becomes a column.

    Here some link for matrix report.

    http://bhushan.extreme-advice.com/create-matrix-report-in-ssrs/

     


    Regards Harsh

    Wednesday, July 10, 2013 8:34 AM
  • Here's an example. here's the content of my query:

    Here's my expected output in my RDL/SSRS


    ----------------------- Sharepoint Newbie

    Wednesday, July 10, 2013 8:36 AM
  • Its a perfect example of Matrix report.

    You need a RowGroup on Column = Car Brand and Column Group on = Year, and the intersection value of matrix report = count(Count.Value).

    To lean Matrix report use this link => 

    http://msdn.microsoft.com/en-us/library/ms157334(v=sql.100).aspx

    http://www.youtube.com/watch?v=zERexbgCG5A


    Regards Harsh

    Wednesday, July 10, 2013 8:52 AM
  • How can this be done using table, and not by using Matrix?

    Thanks


    ----------------------- Sharepoint Newbie

    Wednesday, July 10, 2013 9:46 AM
  • Hi,

    I am not sure, why can't you can use the matrix control, It is matching with your requirement perfectly.

    Both Table and Matrix control have there own significance, Table can be converted into matrix but required complex row and column group design and  believe me you do not want go that step.

    Here is msdn article explaining the difference between Table and Matrix control in SSRS:

    http://technet.microsoft.com/en-us/library/dd220592.aspx.


    Regards Harsh

    Wednesday, July 10, 2013 11:45 AM
  • Sorry, I missed something in my expected output.

    Output should be like this:

    I saw in the report builder that matrix has like row headers and tables do not have. I dont want to have row headers so I want to build it using tables


    ----------------------- Sharepoint Newbie

    Thursday, July 11, 2013 12:28 AM
  • Hi Fix,

    Please refer to the steps below to do it.

    1. Drag a a table control to your design surface.
    2. Delete the Header row and drag [Count] field to the first column.
    3. Right-click [Count] cell>Add Group>Parent Group (Column Group)
    4. Select [Year] field on the Group by drop-down list on Tablix group window.
    5. Right-click [Count] cell>Add Group>Parent Group (Row Group)
    6. Select [CarModel] field on the Group by drop-down list on Tablix group window.
    7. Insert a column to the left of [CarModel] and drag [CarBrand] to this cloumn.
    8. Right-click Details on the Row Groups pane>Delete Group>Delete Group only

    The report looks like below.

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Thursday, July 11, 2013 1:52 AM
    Moderator
  • Thanks for the steps.

    However, my output is something like below. I would like to merge the Hyundai Tucson as one row.


    ----------------------- Sharepoint Newbie

    • Proposed as answer by Harsh Kumar Friday, July 12, 2013 5:15 AM
    Friday, July 12, 2013 3:01 AM
  • Hi Fix,

    I have recreated the report base on the steps above, everyting works fine. In your scenario, please ensure that you have done the step:Right-click Details on the Row Groups pane>Delete Group>Delete Group only

    If the issue persists, please refer to my steps to recrete the report step by step.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, July 12, 2013 3:18 AM
    Moderator
  • I have some issue.

    when there are no cars with model 2010, the 2010 column doesnt show up. I want to still show the 2010 column even if there are no data in it


    ----------------------- Sharepoint Newbie

    Friday, July 12, 2013 7:50 AM
  • That cannot be achieved. The cloumn depend on the data on the database, if there is no records on 2010, then this column will no show on the report.
    Friday, July 12, 2013 8:07 AM
  • I think it can be achieved but can be handled in my dataset query, but how?

    ----------------------- Sharepoint Newbie

    Friday, July 12, 2013 9:10 AM
  • Can you make sure that your Matrix looks like the one below ? Refer Aggregrate SUM function.


    Regards, RSingh

    Friday, July 12, 2013 5:42 PM
  • Thanks for the answers, since my original inquiry have been answered, I just created another topic for the issues i have encountered.

    Any help is appreciated.

    Heres the new topic http://social.msdn.microsoft.com/Forums/en-US/63d9fc9c-d60f-4931-a42b-b6fdb3ad5099/display-column-based-on-another-table-in-rdl


    ----------------------- Sharepoint Newbie

    Sunday, July 14, 2013 9:13 AM