Dynamic Columns based on the Data Set

Question

hi ALL ,

I have a data set Months which is based on a store proc . The Months keep on changing at times . The store procedure returns 5 columns next month it will return 10 columns . I have created a pivoted based tablix currently it runs fine with 5 columns as i have created it from wizard based on the columns retrieved from the store proc . My concern is next month the procedure fetches 10 columns . So how can i make sure that it will be displayed in the report .

Please let me know if some more details is required

Thanks

Priya

Answer 1

You should bring variable fields as rows. Unpivot the query and bring the month name as row information in the dataset. Tablix has the functionality to convert it into the desire format. We can add a month column group.

http://msdn.microsoft.com/en-us/library/dd255263.aspx

---

Regards, RSingh

Answer 2

hi ALL ,

I have a data set Months which is based on a store proc . The Months keep on changing at times . The store procedure returns 5 columns next month it will return 10 columns . I have created a pivoted based tablix currently it runs fine with 5 columns as i have created it from wizard based on the columns retrieved from the store proc . My concern is next month the procedure fetches 10 columns . So how can i make sure that it will be displayed in the report .

Please let me know if some more details is required

Thanks

Priya

Sure thing. You can hide/visibility columns based on sproc result.  based on sproc result it will unhide or hide the columns and thus report will be dynamic.

Note:But for this you have to know  the max column you are going to return and hide the column based on condition.

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

Basically we use the query without pivot in or by doing unpivot in sproc and can have column grouping in ssrs as mentioned by Rsingh which is a good solution.

---

- please mark correct answers