SQL Server report query design and optimization

Question

I have to create a SSRS report which should present both aggregated data and non-aggregated but thankfully not at the same time. I wonder if it's possible to do this in one stored procedure and in one report only.

The report has a dropdown list with values:

Show aggregated
Show non-aggregated

to let user decide which data to show.

Additionally data should be joined by CountryId column.

I have a Finance table which looks like:

FinanceId | CountryId | Year | I | II | III
----------+-----------+------+---+----+-----
1         | 1         | 2016 | 1 | 1  |  1 
2         | 1         | 2016 | 2 | 2  |  2 


//this is how I return grouped and summed data (there are many rows in this table, I left only two for better readability)

WITH CTE_Grouped1 
(
     SELECT 
         FinanceId, Year, I, II, III 
     WHERE 
         FinanceId = 1
)
, CTE_Grouped2
(
      SELECT FinanceId, Year, I, II, III 
      WHERE FinanceId = 2
)
SELECT 
    CTE_Grouped1.FinanceId,
    CTE_Grouped1.Year,
    SUM(Grouped1.I),
    SUM(Grouped1.II),
    SUM(Grouped1.III),
    SUM(Grouped2.I),
    SUM(Grouped2.II),
    SUM(Grouped2.III)
FROM
    CTE_Grouped1 
JOIN 
    CTE_Grouped2 ON CTE_Grouped1.CountryId = CTE_Grouped2.CountryId
GROUP BY 
    CTE_Grouped1.FinanceId, CTE_Grouped1.Year

//this is how I could return not aggregated data
SELECT * FROM Finance

My question is, how to return data the way the user chose.

The query is more complicated and it's not clear to me what should I do.

Should I create two separate reports or maybe there is a smart way, how to achieve this.

Gail Shaw has an excellent blog post about this very type of query. https://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/ — Sean Lange, Aug 23 '18 at 20:02

score 0 · Answer 1 · answered Aug 23 '18 at 19:51

0

Those two queries have different "shapes" so, no you should use two different queries and two different Data Sets. You can toggle the visibility of report elements based on the parameter value, and you can inject the parameter value into the each query to force it to return zero rows when you aren't showing its visual. See eg SSRS conditional execution of dataset

answered Aug 23 '18 at 19:51

David Browne - Microsoft

80,331
6
39
67

Thankfully the shape is the same, I mean number of columns is constant. I thought about splitting this code to two procedures, because there are over 500 lines of code and simply it is hard to understand what is going on there. – tylkonachwile Aug 23 '18 at 19:59
Above code is only sample code, I did not want to rewrite whole procedure here. – tylkonachwile Aug 23 '18 at 20:00

Ronaldo Cano · Answer 2 · 2018-08-23T21:06:33.643

0

you should create 3 stored procedures,

proc1: pulls details data into a Temporary table(truncates the table and inserts every time it execs)

proc2: first execs the first proc and then pulls all details in the table you populate in the fist proc

proc3: pulls from details table and summarize then returns summarized data

*then in SSRS just add 2 datasets

Dataset 1: this execs proc 2

Dataset 2: exec proc 3

hopes this makes sense to you

edited Aug 23 '18 at 21:06

answered Aug 23 '18 at 19:58

Ronaldo Cano

905
8
19

1

This is not a good approach at all. You are suggesting using a persistent table as a temp table. This has concurrency issues like crazy. – Sean Lange Aug 23 '18 at 20:02
do you have any documentation i can read?, i didn't know that, thanks for pointing that out – Ronaldo Cano Aug 23 '18 at 20:07
You don't need documentation. Think about this for a second. Let's say the process takes 4 seconds from start to finish. I start first and you truncate the table that holds the data I want to view. 2 seconds later you start. The table that I am using for MY purpose just got truncated and populated again by your thread. See what I am saying? – Sean Lange Aug 23 '18 at 20:09
well, i dont like that phrase "i dont need documentation" i think documentation is important, however i'll make my own research, and yes what you say makes sense to me – Ronaldo Cano Aug 23 '18 at 20:12
I am not a fan of copying data for the sake of using it momentarily but this kind of approach **could** use a temp table in your first proc. – Sean Lange Aug 23 '18 at 20:12
The point I was making is this isn't documented, not that the documentation for sql server is not needed. If you use a persistent table like that you have created what is known as a "race condition". It will likely work most of the time, it is those times when concurrency causes problems that it does all sorts of wonky stuff. – Sean Lange Aug 23 '18 at 20:14
Not a global temp table. That is no better than a persistent table. Use a temp table. – Sean Lange Aug 23 '18 at 20:21
would a temporary table have visibility in the second proc? i havent try anything like that, thanks for your advice – Ronaldo Cano Aug 23 '18 at 20:24
Yes as long as the temp table is created in the main procedure it will be visible by any procedures called on the same connection. – Sean Lange Aug 23 '18 at 20:51

SQL Server report query design and optimization

2 Answers2