To be very clear at the moment. I would like to not use a stored procedure to create my temp table!
I am new to SSRS reports and currently trying to complete a large report. I have multiple datasets all using pretty much the same information in each. I tried creating a temp table in the first dataset and in the last dataset dropping the table however the report is not running. So I have some questions.
Say for example I have the following
----------------- --------------
|Data-set 1 |---------------------------|Data Source |
|===============| | |============|
|Input | | |Use Single |
|===============| | |Transaction |
|@MyDate | | |enable |
|@ProcessorId | | |============|
|===============| |
|Variables | |
|===============| |
|@StartDate | |
|@EndDate | |
|Create #table | |
|---------------| |
|Insert Data | |
|into #table | |
|---------------| |
|return Dataset1| |
|specific data. | |
|===============| |
|
----------------- |
|Data-set 2 |---------------|
|===============| |
|Input | |
|===============| |
|@MyDate | |
|@ProcessorId | |
|===============| |
|Variables | |
|===============| |
|@StartDate | |
|@EndDate | |
|@Larges$Amount | |
|---------------| |
|Using #table | |
|return Dataset2| |
|specific data. | |
|===============| |
|
----------------- |
|Data-set 3 |---------------|
|===============|
|Input |
|===============|
|@MyDate |
|@ProcessorId |
|===============|
|Variables |
|===============|
|@StartDate |
|@EndDate |
|@reasonCode |
|@Amount |
|---------------|
|Using #table |
|return Dataset3|
|specific data. |
|---------------|
|Drop #table |
|===============|
This is how I believed it to work without having to use a stored procedure. Anyone tell me if I am doing something wrong?
Doing this my other datas-ets get an error similar to Query execution failed for data-set 'data-set2' invalid object name '#table'
So after some research I found out that using #temp tables created in one data set cannot be used in another data set. And using ##tables are not the way to go either as this will cause all kinds of issues when people try to run this report at or near the same time.
I was finally able to get what I needed by using a stored procedure to write the data I was going to use in other data sets into a staging table and before each run truncate the table ("TRUNCATE TABLE "). This will allow you to apply indexes and another performance modifications as needed.
Once execute your stored procedure in the first data set. Your data will be populated in the staging table and you can then call it from other data sets.
I hope this helps anyone that was trying to use temp tables in other data sets. Simple answer is its not possible.