Most efficient code for 2 datasets using same tables and fields using SSRS 2008 and SQL Server 2014

0
votes

I have built an SSRS Report with two separate datasets and have a separate table for each dataset. Both datasets access the same database tables in SQL Server.

  • Dataset1 has details that Exclude some Accounts.
  • Dataset2 has details for above Excluded Accounts.

The idea was to have each table in a separate worksheet when exported to Excel.

Instead of writing the same code twice (once for each dataset), Is there a way to write the code once (and hence process only once) and make each dataset access only a part of that code?

My code for the datasets are as follows:

Dataset 1:
===============

SELECT AccountNumber
      ,Field 2
      ,Field 3

INTO #temp_table1

FROM MainTable
WHERE Condition1 AND condition2 AND condition3
-----------------------------------------------------------
SELECT AccountNumber    
INTO #temp_table2

FROM MainTable
WHERE Condition4 AND condition5
-----------------------------------------------------------
SELECT AccountNumber
      ,Field 2
      ,Field 3
FROM #temp_table1

WHERE AccountNumber NOT IN (SELECT AccountNumber FROM #temp_table2)

And then this is the code for Dataset 2:

Dataset 2:
===============

SELECT AccountNumber
      ,Field 2
      ,Field 3

INTO #temp_table1

FROM MainTable
WHERE Condition1 AND condition2 AND condition3
-----------------------------------------------------------
SELECT AccountNumber
      ,Field 2
      ,Field 3

INTO #temp_table2

FROM MainTable
WHERE Condition4 AND condition5
-----------------------------------------------------------
SELECT AccountNumber
      ,Field 2
      ,Field 3
FROM #temp_table1

WHERE AccountNumber IN (SELECT AccountNumber FROM #temp_table2)
-- The only thing that changes in this code is that I have removed the NOT and only kept IN.

Possible Solution :

SELECT AccountNumber
      ,Field 2
      ,Field 3
INTO #temp_dataset1
FROM #temp_table1

WHERE AccountNumber NOT IN (SELECT AccountNumber FROM #temp_table2)

SELECT AccountNumber
      ,Field 2
      ,Field 3
INTO #temp_dataset2
FROM #temp_table1

WHERE AccountNumber IN (SELECT AccountNumber FROM #temp_table2)

Datset 1:

SELECT * FROM #temp_Dataset1

Datset 2:

SELECT * FROM #temp_Dataset2

Any Suggestions ?

2
sql-serverreporting-servicesssrs-2008

2 Answers

1
votes

I would suggest that you could combine both datasets into one dataset, with an extra column that specifies which sub-set the data belongs to.

Then in SSRS, you filter which subset you want for each Tablix (or whatever data-driven object you are using).

1
votes

I agree with Tab Alleman.

You can use subqueries under you SELECT statement or you can create an "Inner Join", I'd recommend the latter. Something like this:

SELECT a.Account Number
,a.Field 2
,a.Field 3
,b.Account Number
,b.Field 2
,b.Field 3

FROM MainTable a
INNER JOIN MainTable b
  ON Condition 1
  AND Condition 2
  AND Condition 3

I'm sure the data you're working with is more complex, but this may give you a good idea.