Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 Reporting Services Development
 Dynamic Columns

Author  Topic 

Sachin.Nand

2937 Posts
Posted - 2010-02-17 : 21:52:56
Hi,
We have a set of reports that are displayed on a web pages & on pdf files which are done by some third party pdf writers.All of them work fine.
We are planning to move the reports displayed on the web pages to SSRS.
The problem is the columns of reports are not static.They are dynamic & can change according to different parameter values passed.Its kind of a cross tab report.We have about 50 sp's that way.Is there any way I can do it SSRS?I mean I just cannot drag & drop the column names from the data set on to the report.



PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-18 : 01:26:24
yup. you can do that. As you're saying that its cross tab report I assume your column names in report are by itself contained as value of a field in your query behind. Then its simply a matter of using a matrix container and adding the field whose value is to be cross tabbed in column group and giving any other row groupings. Add the relevant aggregate expressions in your data part and you should get dynamic cross tabbed results based on data available in the source field.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-02-18 : 05:53:47
Thx for your reply.I am kind of new to SSRS so please bear if I ask something stupid.
The thing is I cannot use the SP statements in the report the SP itself will pass the resultset.It can have any n number of columns with differing column names.Say for example resultset can have Col1,Col2 or Col1000,Col2000,Col2555 or Col10,Col30 as columns not as field.The column names are also not static.So its not possible to know the columns first hand.
Example

Col1    Col2
value1  value 2

OR

Col1000    Col3000    Col4000
value1000  value3000  value4000





PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-18 : 05:57:57
quote:
Originally posted by Idera

Thx for your reply.I am kind of new to SSRS so please bear if I ask something stupid.
The thing is I cannot use the SP statements in the report the SP itself will pass the resultset.It can have any n number of columns with differing column names.Say for example resultset can have Col1,Col2 or Col1000,Col2000,Col2555 or Col10,Col30 as columns not as field.The column names are also not static.So its not possible to know the columns first hand.
Example

Col1    Col2
value1  value 2

OR

Col1000    Col3000    Col4000
value1000  value3000  value4000



PBUH



Oh That is a problem
SSRS by itself cannot change the dataset information at runtime like this. So once your execute procedure once in data tab, it caches the resultset metadata info (column info) in it. Any subsequent changes in sp resultset requires you to come back and refresh dataset for its information to get updated
The easiest solution will be to make SP return it in row format and make report do cross tab (if at all possible)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-02-18 : 06:21:05
I also thought about that.
But I just thought if there is a way around to do it in SSRS cause I was kind of lazy to change 50 SPs so that it can fit in SSRS.
Thx for the feedback.

PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-18 : 06:48:52
quote:
Originally posted by Idera

I also thought about that.
But I just thought if there is a way around to do it in SSRS cause I was kind of lazy to change 50 SPs so that it can fit in SSRS.
Thx for the feedback.

PBUH

50 Sp's are designed like this? can I ask reason for taking this approach?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-02-18 : 07:00:49
quote:
Originally posted by visakh16

quote:
Originally posted by Idera

I also thought about that.
But I just thought if there is a way around to do it in SSRS cause I was kind of lazy to change 50 SPs so that it can fit in SSRS.
Thx for the feedback.

PBUH

50 Sp's are designed like this? can I ask reason for taking this approach?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Those are all reporting SP's.

PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-18 : 07:07:51
ok i understand that. But why trying to do this in query rather than getting cross tab effect in SSRS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-02-18 : 08:19:34
quote:
Originally posted by visakh16

ok i understand that. But why trying to do this in query rather than getting cross tab effect in SSRS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



quote:

Hi,
We have a set of reports that are displayed on a web pages & on pdf files which are done by some third party pdf writers.All of them work fine.
We are planning to move the reports displayed on the web pages to SSRS.

Because as I said earlier now we are planning to move it to SSRS before we used to display it in web pages & pdf files.Now the client wants it in SSRS.

PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-18 : 08:52:28
oh ok..I got the reason. thanks for making this clear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-02-18 : 11:40:40
quote:
Originally posted by visakh16

oh ok..I got the reason. thanks for making this clear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




I think I should be the one who should say thanks .

PBUH
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-02-19 : 00:53:18
In continuation with the above topic I need to show the values of columnB as '-' if ColumnA is 0.
It always has to be between 1st & 2nd column no matter how many columns are displayed.It was easy in .net as I could easliy loop the datatable or the ListView.But in SSRS for a matrix type I am not to sure.

PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-02-19 : 00:56:12
Are you now bringing the column values row wise from table and doing cross tab in report?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-02-19 : 01:17:08
quote:
Originally posted by visakh16

Are you now bringing the column values row wise from table and doing cross tab in report?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Yes I am doing that.

PBUH
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site