June 25, 2014 at 3:41 pm
The questions apply to using temp tables when executing 5 datasets in the same main ssrs report.
Here is the description of the ssrs 2208 report:
In a new SSRS 2008 report, I am allowing the user to select which report they would like to see generated by selecting the report name from a dropdown list. This is a multi-valued parameter and the parameter name is called 'report'. The default value is for all 5 reports to be selected.
All 5 reports are on the 'main' report. There will be no subreports. Each report has its own unique matrix and the matrix is visibile based upon what is selected in the parameter called 'report'.
The following is how I make all 5 reports show up as a default selection.
1.Right-click the multiple parameter ‘repot’ to open the Properties dialog box.
2.Specify the values below in the Available values:
Label: report1 Value: report1
Label: report2 Value: report2
Label: report3 Value: report3
Label: report4 Value: report4
Label: report5 Value: report5
3.Specify the values below as the Default Values:
report1 report2 report3 report4 report5
4.Right-click the ‘report1’ to open the Tablix Properties dialog box.
5.Select Visibility in the left pane, type the expression below in the “Show or hide based on an expression” textbox:
=iif(InStr(join(Parameters!report.Value,","),"report1")>0,false,true)
6.Use the expressions below to control the visibility of the ‘report2’, ‘report3’, ‘report4’, ’report5’:
=iif(InStr(join(Parameters!report.Value,","),"report2")>0,false,true)
=iif(InStr(join(Parameters!report.Value,","),"report3")>0,false,true)
=iif(InStr(join(Parameters!report.Value,","),"report4")>0,false,true)
=iif(InStr(join(Parameters!report.Value,","),"report5")>0,false,true)
http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
The questions are:
1. Can I use a temp table called '##temp' in the firstdata set and have the other 4 datasets refere to '##temp'? I am asking the question since all 5 datasets use the same stored procedure. If this possible, can you tell me how to setup the code?
2. When I am running visual studio 2008 and create a temp table called '#test'. I get results. However if I change the sql for the dataset and rerun the query, the results from the orginal temp table remains. The column headers remain the same, but the data that is returned is from the original query before the dataset was changed. I found out that I have to stop by visual studio 2008 session, and start a new session for the new values for the dataset to be returned. In the original dataset code I had the statement drop #test and that did not work. Thus can you tell me how to solve this issue?
June 25, 2014 at 3:55 pm
wendy elizabeth (6/25/2014)
The questions are:1. Can I use a temp table called '##temp' in the firstdata set and have the other 4 datasets refere to '##temp'? I am asking the question since all 5 datasets use the same stored procedure. If this possible, can you tell me how to setup the code?
In theory, yes, but reality, no. You have to make sure they run serially and not simultaneously, which I'm not sure you can do from SSRS as unique Data sets. I don't know of way without sub-reports to make a dataset dependent on the completion of a different dataset.
Add to the problem you'd have to make sure that two users wouldn't step on each other with different filters because the ##temp is completely global, not connection global, and could also disappear (which would be good) causing errors on the second user.
End result: Don't do this. It's painful at best, fraught with errors on average.
2. When I am running visual studio 2008 and create a temp table called '#test'. I get results. However if I change the sql for the dataset and rerun the query, the results from the orginal temp table remains. The column headers remain the same, but the data that is returned is from the original query before the dataset was changed. I found out that I have to stop by visual studio 2008 session, and start a new session for the new values for the dataset to be returned. In the original dataset code I had the statement drop #test and that did not work. Thus can you tell me how to solve this issue?
You're generating #Test via in-line SQL, I assume rather than a proc? When you say you change the sql and rerun the query, I assume you mean you're changing the inline SQL in the Dataset with a completely different query (but similar result sets), or are you changing a comment?
However, in short, VS is... quirky. It caches a LOT of things. SSIS packages have a similar problem with configuration loads. My guess is that it runs the query then is cacheing the results for reruns and it's not detecting the change (or it considers the change to not require a rerun). If the parameters or something changed that should also force it to rerun the process.
That it won't DROP TABLE #Test is something I've not come across before, would take me some research. I do all of my dataset control via procedure and just pass parameters down. Makes my life easier in most cases, so I'm not familiar with this issue... yet.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 26, 2014 at 8:57 am
I am wondering if you think this suggestion would work or do you have a better idea? The 5 different dataset will be calling the same stored procedure from the main report at the same time. In each dataset, I am going to put the results into 5 uniquely named temp tables. Would this work for me?
June 26, 2014 at 4:53 pm
wendy elizabeth (6/26/2014)
I am wondering if you think this suggestion would work or do you have a better idea? The 5 different dataset will be calling the same stored procedure from the main report at the same time. In each dataset, I am going to put the results into 5 uniquely named temp tables. Would this work for me?
That... should work.
I wish I had time to dig through your code and see if there are more efficient alternatives, but I don't unfortunately. If you post it all up though someone may be able to spend some time with it and see if there are alternate approaches, however. There's plenty of good folks on this forum.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
