Looking for Report Builder hacks, Problem inside
Im looking for a way to have Reportbuilder 3.0 recognize mulitple tables from a single sproc.
I had currently tried to create a parameter and set it from a data set within report builder. EX
if @elementOder = 1
begin select * from yeppers end
if @elementOrder =2
begin select * from nope end
Did not work, only recognized the first set of fields in elementOrder 1 and didnt care about the rest.
I ask because I am using the same function accross multiple sprocs and its a heavy function. This causes my report to hit the 10 minute mark and it is rather frustrating that in BI I can return multiple tables but I am unable to in RB3.0
Any help would be awesome. I was also thinking about an Object Exists with a ##temp but the minor things like this make me wonder what the firing order is for report builder, and if that would be possible.
May 17th, 2012 10:43am
Hi,
I don't think SSRS will be able to work with a procedure that returns different results, it normally uses the first select statement that it sees. If the tables that you are returning have the same columns count and data types, then you could insert the
data into a temporary table, then SSRS can use the temp table. Another option is to break that procedure in multiple procedures, an have a report for each procedure. You may then be able to have a master report with different sub reports.
thanks,
qdtb
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 11:02am
There is always a way. Where there's a will there is a way.
Trying to make a table func right now using the same concept of passing a @variable to return a specific table. So instead of calling the Sproc from the dataset Im going to use the query/text editor with something like select * from tfUsage(@start,@end,1)
Edit: the 1 being my element order.
May 17th, 2012 11:27am