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.
| Author |
Topic |
|
Beginer2012
Starting Member
45 Posts |
 Posted - 2012-05-24 : 10:10:43
|
| Hello,I'm building a report in SSRS. I have many datasets in the report.The datasets are built based on a stored procedure that expects a parameter. Depending on the parameter the procedure retruns specific columns. No matter what parameter I pass the return is the same..I have test my procedure it works perfectly..It looks like what ever the first dataset have the other datasets would have as columns even though I'm calling them with different parameters that return different columns.Any ideas ???Thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-24 : 15:14:19
|
| you cant have procedure returning multiple datasets to report. In such cases also it will onlt take metadata (columns) as that of first resultset. Please keep in mind that reports cant change metadata at runtime. So only solution if you want to merge multiple resultsets is to put place holder for all columns and add an additional column to determine the resultset type. Then use a filter based on this column to show correct data in report.To illustrate, consider example where you need to show five columns Col1,Col2,Col3,...Col5 in report for one value of parameter (paramval1) and five other columns Col6,Col7,...Col10 for another value of parameter (paramval2)then in stored procedure make result like thisIF @Param=Paramval1 SELECT col1,Col2,Col3,...Col5,NULL AS Col6,NULL as Col7,...NULL AS Col10,'Result1' AS Category ...IF @Param=paramval2SELECT NULL as col1,NULL as Col2,NULL as Col3,...NULL as Col5,Col6,Col7,...Col10,'Result2' AS Category ...GOthen in report use value to Category and filter to get correct resultsetAlso based on types of Col1,Col2 etc you might have to do cast to make sure it assumes correct datatype when you return NULL valuesie likeCAST(NULL AS int) AS Col1 etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
SSRS and Stored Procedure