Dynamic table layout (numbers of columns depends on the query result)
Hi, I need your help. I need to create a report with a table. The number of columns is variable, meaning that the number of coulumns depends on the query. SSRS 2005 is able to provide dynamic table? If yes could you give me the right suggestions to build a report with dynamic (variable) number of columns? Thanks
May 30th, 2011 9:48am

Yes, you should be able to accomplish this using the matrix template. Take a look at this to get started - http://msdn.microsoft.com/en-us/library/ms157334(v=sql.100).aspx
Free Windows Admin Tool Kit Click here and download it now
May 30th, 2011 10:03am

Thanks, but this is not me issue. My problem is very complex: I went surfing the internet to find something useful. My problem is that I do not in advance the name of the columns, therefore I am asking if SSRS 2005, through code in the report properties, can enumerate the coulmns name (they are values in a table). Any help will be very appreciated. Thanks
May 30th, 2011 1:40pm

If you carefully read the article I posted, you'll know that the column names are the values in table. What you are looking for is exactly what you get by using a matrix template.
Free Windows Admin Tool Kit Click here and download it now
May 30th, 2011 1:57pm

Thank for replying. The problem is that I have never used the matrix. Any help will be very appreciated. Many thank
May 30th, 2011 2:10pm

Her is an article that will help you get up to speed - http://msdn.microsoft.com/en-us/library/ms157334(SQL.90).aspx If you have any specific questions, please let us know. Hope this helps.
Free Windows Admin Tool Kit Click here and download it now
May 30th, 2011 2:15pm

Hi and thanks, I have tried to use matrix tool but I still get problem: The problem is that the columns in the matrix are created dinamically, but since the name of the column that is in the table as data column is not single but listed several time. Therefore, in the matrix I get as much columns (same columns) as in the data column. For example. If in the table we have (this is only an example) : the table (dataset) is: (keeping in mind that Shops and fruit is variable: the values for the columns Shops and Fruits change everytime when running the query based on input parameters) Shops Fruits Quantity Shop1 banana 1 Shop2 banana 2 Shop1 pear 1 Shop2 pear 3 Using the matrix I will get as columns (BUT It is not I need): SHOPS BANANA BANANA PEAR PEAR Shop1 1 1 Shop2 2 2 The report that I need is: SHOPS BANANA PEAR Shop1 1 1 Shop2 2 3 I can do it in TSQL with Pivot function, the problem is on SSRS because I do not the columns names in advance. Matrix is useful, but I have a problem to use it as I need. How can I reach this goal? Thanks
May 31st, 2011 5:19am

Hi and thanks, I have tried to use matrix tool but I still get problem: The problem is that the columns in the matrix are created dinamically, but since the name of the column that is in the table as data column is not single but listed several time. Therefore, in the matrix I get as much columns (same columns) as in the data column. For example. If in the table we have (this is only an example) : the table (dataset) is: (keeping in mind that Shops and fruit is variable: the values for the columns Shops and Fruits change everytime when running the query based on input parameters) Shops Fruits Quantity Location Shop1 banana 1 A Shop2 banana 2 B Shop1 pear 1 A Shop2 pear 3 B The report that I need is: SHOPS BANANA PEAR LOCATION Shop1 1 1 A Shop2 2 3 B I can do it in TSQL with Pivot function, the problem is on SSRS because I do not the columns names in advance. Matrix is useful, but I have a problem to use it as I need. How can I reach this goal? Thanks
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2011 5:24am

Did you try what I suggested? If there are multiple values for a column, it will show up only once as SSRS will group by that column. Can you post the query you are using and of possible a screen shot of the report?
May 31st, 2011 8:30am

Hello, Now it looks OK. I will check further. Thank
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2011 9:44am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics