Wednesday, July 13, 2011

Dynamic Column RDLs

When creating reports in RDL there may be cases where you want to represent data in a table, but table column are chosen by the user at the runtime. Assume you have no clue about how many columns will the user choose for the table and what kind of data will it represent.

Normal tables in reports require statically laid columns with statically defined expressions to populate data in them. Matrix comes handy in situations like these. I have explained how to use a matrix to create a table with dynamically populated columns using Business Intelligent Studio that comes with SQL Server Reporting Service (SSRS).

To use this feature database query should result in the following format.

Employee ID (emp_id)

Column Name (col_name)

Column Value (col_value)

1

Name

John

1

Age

25

1

Designation

Software Engineer

2

Name

Smith

2

Age

31

2

Designation

UI Designer

  1. Add a ‘Matrix’ from the toolbox to the canvas
  2. Set the necessary basic elements in the report. Such as Data Source, Data Set, Parameters, etc.
  3. In the newly added matrix insert a ‘Row Group’, and a ‘Column Group’ as follows,
  • Row Group: rows should be group by employee id. In this case ‘col_name’. Hence no 2 employees can have same employee id. If it happens to be, then the initial employee records get overwrite by the second employee who has the same id value.
  • Column Group: columns should be group by column name. In this case ‘col_name’. Hence no 2 columns can have the same name.
  • Cell value should be the data value that satisfies both Column and Row Group conditions. In this case ‘col_value’.

Employee ID <- This is a text box

col_name <- Column Name field

emp_id<- Employee ID field

col_value <- Column Value field

Now when this RDL is deployed and viewed, it will output a table as follows,

Employee ID

Name

Age

Designation

1

John

25

Software Engineer

2

Smith

31

UI Designer

Hence no knowledge is required on number of columns or their content when creating this kind of tables.

24 comments:

  1. Very good doc. may i know how to define Cell value.. Im new to SSRS.

    ReplyDelete
    Replies
    1. Define the Data cell to col_value.

      Delete
  2. Hmm Great article...But,how to manage filters? If we apply filters only the matching columns would be displayed but the rest of them would be displayed...any ideas?

    ReplyDelete
  3. Thanks for this article!!! You saved my life :)

    ReplyDelete
  4. Replies
    1. Nothing is useless dude...

      Delete
    2. This comment has been removed by the author.

      Delete
  5. Exactly what I was looking for! Thank you!!!

    ReplyDelete
  6. Great.....but there is a small problem... can we do grouping in here....

    ReplyDelete
  7. You saved my day!!!!!!

    ReplyDelete
  8. Thank you very much! This is exactly what I needed!

    ReplyDelete
  9. you saved my life ;-) thx

    ReplyDelete
  10. hi,
    could you share the DB sql query that resulted the following query results in the format you mentioned above ?

    Employee ID (emp_id) ,Column Name (col_name) , Column Value (col_value)

    ReplyDelete
    Replies
    1. My guess will be:

      SELECT emp_id, 'Name' AS [col_name], Name AS [col_value]
      FROM empTable
      UNION
      SELECT emp_id, 'Age', Age
      FROM empTable
      UNION
      SELECT t1.emp_id, 'Designation', ed.designation
      FROM (
      SELECT e.emp_id, des.id, des.title
      FROM empTable e FULL OUTER JOIN designation des ON des.Title IS NOT NULL
      ) AS [t1]
      LEFT JOIN empDesignationTable ed ON t1.emp_id = ed.emp_id AND t1.id = ed.des_id
      GROUP BY t1.emp_id, t1.id, t1.title

      Delete
    2. That's of course if the Designation is a many-to-many relation

      Delete
  11. Thanks! helped tremendously!!

    ReplyDelete
  12. thanks alot for your post,i have a question how i can color the record that contains an employes whose age is greater than 25 for example with Red Color ?
    as i am new to SSRS ,i know that i can do that by the background color expression
    =IIF(Fields!Age.Value > 25, "Red", "White")
    but this is with static columns ,how can i do that with the dynamic ones?

    ReplyDelete
  13. Thanks a lot for the article.

    ReplyDelete
  14. Hi, Thanks for vaulable post. Wondering if we don't know the column names and number of columns at design time, I mean, the number of columns and table differs at runtime then how would we arrive with a dataset with following structure?
    Column Name (col_name) , Column Value (col_value)

    If someone can post the t-sql query for above format, it would be great help.

    ReplyDelete
  15. Hi, How to dynamically add new columns to report created with Reporting Services? How to dynamically add new columns to report rdlc? I want to use the rdlc very times with different information.
    Thank you!!!

    ReplyDelete
  16. Thank you - I think you just saved me - Hopefully -

    ReplyDelete
  17. I don't entirely understand how this works? If I simply have a "SELECT * FROM Table1" for my dataset and my report displays all columns, how would I do this? Would I have to modify my dataset output to match above? It doesn't make sense to me.

    ReplyDelete
  18. Dear Sonali,
    If you can not compromise on the structure of the table, then how is it possible to create columns dynamically / on run time. I mean if you have to show the data in table's columns rather than matrix's rows. Hope you got my question!

    ReplyDelete
    Replies
    1. The matrix component can be used to pivot a table. Lets say as an example we have a dataset where it contains employee attributes such as name, age, address, designation, dob etc as name value pairs. I.e. a particular employees particular attribute is a row (EmployeeID | Attribute Name | Attribute Value). So, by using matrix component you can convert (pivot) those attributes to columns and show.
      (EmployeeID | Name | Age | Address | Designation | DOB)

      Delete