SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 ssrs 2008 using temp tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

296 Posts

Posted - 06/25/2014 :  17:42:50  Show Profile  Reply with Quote
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)

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?

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 06/25/2014 :  18:03:44  Show Profile  Reply with Quote
1. I don't think datasets are guaranteed to run in a certain order, in my experience they've executed simultaneously. So I don't think that will work.
2. I don't see how you could have been getting data from #test when you were explicitly dropping it, if that's what you're saying, but Visual Studio does annoying and strange things. Did it error out because you should have used DROP TABLE #test?
Go to Top of Page

jassie
Constraint Violating Yak Guru

296 Posts

Posted - 06/26/2014 :  11:06:09  Show Profile  Reply with Quote
visual studio said I do not have permission to drop the table. The dbas here said I do have permission to drop the tables.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000