Invisible graphs cause report to slow

Question

I have a report with a parameter where the end user chooses a practice name that corresponds to a group of people. Most of these groups have fewer than 10 people, but a small number of them have as many as 150. When there are more than 15 people in a given group, they want separate graphs, each with no more than 15 people. So for most of the groups, we only need one graph. For a few, we need a lot of graphs.

Behind the scenes, I created a graph for each multiple of 15 people, and set them to only be visible if there are actually that many people in the group. This does what I need it to, but it makes the report super slow. As close as I can tell, behind the scenes when an end user runs the report it's still somehow rendering the hidden graphs and slowing it all to heck. (I did find this link which I think suggests this is a known bug.

I need to have one report where the end user selects the practice name, so I can't make two reports, "My practice is normal" and "My practice is ginormous". I thought maybe I could make a conditional sub-report split into those two reports based on the practice name parameter, but that doesn't appear to be possible; you can play around with visibility but I'm guessing that will still cause the invisible graph rendering problem and not help my speed.

Are there any other cool tips I can try to speed up my report, or is this just a case of too many graphs spoiling the broth?

Alan Schofield · Answer

The easiest way would be to generate a group number for every 15 people and then use a list control to repeat the chart for each group.

Here's a very quick example of this in action. I just used some sample data from one of the Adventure Works sample database.

Here's my query that returns every person in each selected department. Note that I have commented out the DELCAREs as these were just in there for testing.

--DECLARE @Department varchar(50) = ''
--DECLARE @chartMax int = 5
SELECT 
    GroupName, v.Department, v.FirstName, v.LastName
    , ChartGroup = (ROW_NUMBER() OVER(PARTITION BY Department ORDER BY LastName, FirstName)-1) / @chartMax -- calc which chart number the person belongs to
    , Salary = ((ABS(CHECKSUM(NewId())) % 100) * 500) + (ABS(CHECKSUM(NewId())) % 1000) + 10000 -- Just some random number to plot
  FROM [HumanResources].[vEmployeeDepartment] v
  WHERE Department IN (@Department) 
  ORDER BY Department

The key bit is the ChartGroup column

ChartGroup = (ROW_NUMBER() OVER(PARTITION BY Department ORDER BY LastName, FirstName)-1) / @chartMax

This will give the first 5 rows in each department a ChartGroup of 0 the next 15 1 and so on. I used 5 rather than 15 just so it's easier to demo.

Here's the dataset results

Now, in your report, add a List, set it's dataset property to your dataset containing your main data (the query above in my case).

Now edit the 'details' rowgroup properties and add a grouping by Practice and ChartGroup (Department and ChartGroup in this example)

In the list box's textbox, right-click then insert a chart. Set the chart up as required, in my example, I used salary as the values on a pie chart and the employee names as the labels.

Here's the final design ..

Note that I set the department as a multi-value parameter and also set the number of persons per chart (chartMax) as a report parameter.

When I preview the report I get this for 'Engineering' which has 6 employees

Sales has 18 employees so we get this

.... and so on, it will generate a new chart for every 15 people or part thereof.

Invisible graphs cause report to slow

1 Answers