October 4, 2010 at 2:20 pm
Hi,
I wrote a stored procedure that returns two completely different set of columns based on parameters passed in.
Psuedo-
IF parm = 1
Select A,B,C,D,E
Else
Select V,W,X,Y, Z
While setting up my report I create the Dataset that successfully passes the parameter 2 and gets back columns V,W,X,Y,Z but when I go to place fields on the report the field list available only shows A, B, C, D, E.
I was hoping to let one stored procedure support two reports and simply pass a value in to get corresponding results but it looks like SSRS doesn't pay any attention to the actual columns returned from the stored procedure call based on the parameters used.
Surely there must be a way that I'm missing.
Thanks for any info if you can shed light on this.
October 29, 2010 at 6:39 am
I'm not quite sure about your approach, but there's a relative simple solution for your problem;
make sure that the sp always returns the same set of columns, returning NULL in all coumns that are unwanted depending on the parameter.
In your report you use the columns that are neded, and give them a meaningfull header (in the sp it might be a simple col1, col2, etc.)
IF parm = 1
Select A AS col1, B as col2, C as col3, D as col4, E as col5
Else
Select V as col1, W as col2, X as col3, Y as col4, Z as col5
This way the metadata for the dataset coming from the sp is allways the same.
Peter Rijs
BI Consultant, The Netherlands
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply