SQL Report Builder Pie Chart Filtering

Question

Hi All,

I have a departments field from a dataset linked to a Pie chart on SQL report Builder. It is displaying all the data, the problem is that there is too much data. The chart is meant to show how many times each department have made a service call. Since there are over 20 departments the pie chart is over crowded and the pie chart legend is only displaying 5 departments out of the 20.

Is there a way to filter the Pie chart so only the top ten departments are displayed? I have tried using the following filters on the graph under chart properties > filters:

Expression: [Department]

Operator: Top N

Value: 10

This filter allowed the report to run but it would only show one record. I then used the filter below:

Expression: [Count(Department)]

Operator: Top N

Value: 10

This did not work as a aggregated functions cannot be used in data set filters.

Does anyone know any other solutions to this problem?

Thanks,

IA

Answer 1

The Top N filter is designed to do what you want. It should be used in conjunction with an appropriate sort. as to why it would return only 1 record when N is set to 10...

What do you mean by 1 record? Are you grouping on Department and showing only one row per department? If so, you must remember that the Top N function works against the dataset, not the tablix. Here is an example:

I have a dataset that returns 100 records. Out of that 100 records, I have 5 distinct Departments with 28 records in Dept. A, 12 in Dept. B, 33 in Dept. C, 11 in Dept. D and the remaining 16 records in Dept. E. In my report I display the data from my dataset in a table that groups by Department and aggregates the data in the detail cells resulting in 1 row per department. In Tablix properties I set a Filter on Department, Top N, 3. I also set sorting on the Department field. This places the data in order by Department leaving all Dept. A records at the top, Dept. B next, etc. When the filter acts it will get the top 3 rows of the dataset. All 3 have a department of Dept. A. So my resulting tablix displays 1 row, not 3.

You should note that the 1 row in the tablix will display the aggregated data from all 28 Dept. A records, not just the top 3. This is not so intuitive. It kind of makes sense when you look at it this way... The filter is restricting the data to Department equals any of the values for Departments returned in the Top N (3) records. Dept. A is returned in all 3 rows so I filter the results to all records where Department = Dept. A. This is based on personal observation. I haven't found any official documentation of the way Top N works.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 2

Hi Tim,

Thanks for your reply. The problem I have is not with the tablix as I can display the results in that. I will give you a further description below:

The dataset requests has a field called "departments". This departments field contains the department associated with the requests they have put in. When put into a pie chart all 20 departments are shown.

What I want to know is...is there a way to show only the top 10 departments. The pie chart currently shows all 20 departments and it is hard to tell the figures appart as there is too much data on the pie chart. What would be ideal is if only the top 10 results were shown.

Is this possible to do? As from the documentation I have seen there is not alot availible to cater for what we want.

Thanks,

Imran

Answer 3

Hi,

Try this - Remove the filter on chart, create the same filter on Category Group of the Pie Chart. I think the category group is already by Department.

filter expression  =Sum(<<fieldname>>)  --> fieldname is the field on which you are deciding the top values. Let us say if you want top 10 departments by sales measure, the field here should be sales.

type - select Float or integer what ever applies.

operator - Top N and value 10.

Good luck

Regards

Srini

Answer 4

Another option would be to group the smaller values into an "Other" slice.  This blog discusses the technique: http://www.erikmonchen.com/2010/06/group-small-values-into-other-category.html

Thanks

Melissa

Answer 5

What I described for Tablix applies to Chart.

You could create a dataset that returns the top 10 departments. ID field = 1 for all records and Department field holds the department name. In the chart filters, add a filter:

Expression: [Department]
Operator: In
Value: =LookUpSet(1,Fields!Id.Value,Fields!Department.Value,"NewDatasetName")

I tested this on a local report and it worked for me.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 6

Hi Tim,

Thanks for your reply. How would I create a dataset to return the top 10 departments? I have tried putting a TOP N filter in with the following:

Expression: [Count(Department)]

Operator: TOP N

Value: 10

But this doesnt work as aggregate functions cannot be used in dataset filters.

Thanks,

Imran

Answer 7

Hi Melissa,

It spilts the results up...but it does not show me the top ten results so its not much use for my problem.

Thanks anyway though!

Imran

Answer 8

I would start with the query you currently use for the dataset that drives your chart. Strip out all fields from the select except Department and the mentioned Id field:

Select Top 10
  1 AS Id,
  Department

Now add or alter the ORDER BY clause so it sorts the departments in the desired order. It may be something like:

ORDER BY COUNT(Field) DESC

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 9

Hi,

Don't know whether you saw my reply above, Top N filter on Category Groups (which Department in your case) will show the topN. Here is the example.

Select 'D1' [Department], 20 [Requests]
union all Select 'D1' [Department], 10 [Requests]
union all Select 'D2' [Department], 10 [Requests]
union all Select 'D3' [Department], 10 [Requests]
union all Select 'D3' [Department], 50 [Requests]
union all Select 'D4' [Department], 40 [Requests]
union all Select 'D5' [Department], 42 [Requests]
union all Select 'D6' [Department], 41 [Requests]
union all Select 'D7' [Department], 38 [Requests]
union all Select 'D8' [Department], 12 [Requests]
union all Select 'D9' [Department], 22 [Requests]
union all Select 'D10' [Department], 20 [Requests]
union all Select 'D11' [Department], 10 [Requests]
union all Select 'D12' [Department], 10 [Requests]
union all Select 'D13' [Department], 10 [Requests]
union all Select 'D13' [Department], 50 [Requests]
union all Select 'D14' [Department], 40 [Requests]
union all Select 'D15' [Department], 42 [Requests]
union all Select 'D16' [Department], 41 [Requests]
union all Select 'D17' [Department], 38 [Requests]
union all Select 'D18' [Department], 12 [Requests]
union all Select 'D19' [Department], 22 [Requests]

It shows all departments. Now set the filter on Category Groups (which is Department)

Expression sum(Requests), Type- Integer, Operator - Top N, Value - 10 (infact value can be Parameter).

If this is not what you are looking for, then sorry for misunderstanding.

Regards

Srini

Answer 10

Hi Srini,

Thanks for your posts they have been helpful. Unfortuently when I try to use a filter in the category groups I get the error:

"Unsupported RPL stream version detected: 101.116.1047292257. Expected version: 10.6.
----------------------------
An error occurred during client rendering."

Any suggestions?

Thanks,

Imran

Answer 11

Hi,

I am not sure what is the root cause of the error. Search in the net for the error, i see lot of resolutions based on the versions and environmental version matching etc. Also let us know what is the issue in your case.

Regards

Srini.

Answer 12

Hi Srini,

The problem only occurs when the filter is put in. If the filter is not put in then the report runs fine meaning to me that the error message is another one of SQL report builders very "helpful" error messages. I dont think its a problem related to the web part or the version as many of the solutions say online, as the report has other filtering and it works fine, its just this particular filter thats causing the problem.

Thanks,

Imran

Answer 13

Hi

I used this type filtering extensively, like top regions, top sellers, top products from same dataset by dynamically creating Category region and Top N filter on category region based on user selection. I am using SSRS 2008 R2 and show reports in report viewer control or report excution on ssrs server. What is the SSRS version you are using?

Regards

Srini

Answer 14

Did you try the second dataset approach? As I said, I successfully implemented a top 10 filter similar to what you are attempting in my environment. It is not the same report but is similar.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 15

Srini,

I am using SSRS 3.0.

Tim,

I have tried the dataset approach. The query for the dataset is:

<RSSharePointList xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xmlns:xsd=http://www.w3.org/2001/XMLSchema>
  <ListName>Service Requests</ListName>
  <ViewFields>
    <FieldRef Name="Department" />
  </ViewFields>
</RSSharePointList>

After the dataset is created to try and use the code in the filter section and i get the same error.

Thanks,
Imran

Answer 16

hI

Is it SQL 2008 R2?

Answer 17

Your query does not include the fixed ID field with the set value "1" per my example. This is essential for the LookupSet function. Please provide the details of your query for the new dataset and the LookupSet function expression.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 18

Hi Srini,

Yes it is SQL 2008 R2.

Hi Tim,

Currently the query in my dataset is:

<RSSharePointList xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xmlns:xsd=http://www.w3.org/2001/XMLSchema>
  <ListName>Service Requests</ListName>
  <ViewFields>
    <FieldRef Name="Department" />
  </ViewFields>
< /RSSharePointList>

I am not sure what ID you are talking about? Could you give me an example with the code I have supplied above? The filter I do currently is when I right click on the chart and goto the chart properties. I then goto filters and have the following:

Expression: [Sum(Department)]

Operator: TOP N

Value: 10

This throws up an error.

Thanks,

Imran

Answer 19

SharePoint list queries are drastically restricted. What is the query that drives your chart? The SharePoint list query defined in you XML only returns a department field. How/where are you getting the value? How are you defining top 10?

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 20

The Chart was created by using the Chart wizard and for the chart data I have pu departments in the series groups. Using that setup it displays all the departments. What I want is the top 10.

How would I do this? I know I have to create a dataset so could you give me an example SQL query instead of the XML I am using? Currently to filter I am just using the filter in the properties but this is not working.

Thanks,

Imran

Answer 21

Sorry to say that SSRS query designer does not support the CAML query elements GroupBy and RowLimit. These must be child elements of View and it does not appear that SSRS 2008 R2 supports View (I cannot speak to 2012, I do not have that available to me to test).

However, I have reproduced the equivalent Chart and Query to what I believe you have and I now better understand what you need to do. The solution is really quite simple. You were on the right track originally with applying a filter to the chart. You were just applying it in the wrong place.

Click the chart in design view, then again to open the Chart Data designer. In the bottom section (Series Groups) you should see a single entry for Department. Click the drop down next to Department and choose Series Group Properties. Select the Filters tab and add a new filter:

Expression: Department
Operator: Top N
Value: 10 (Or whatever number you wish)

Save by clicking OK. Now when you run the report you will see only the number of departments you specified.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 22

Hi,

Using that filter I get the exact same problem that I started the forum post off with. Is there anything else I could try?

Thanks,

Imran

Answer 23

Can you post the report XML. I will try opening it in my designer to see what is different.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 24

Please post the xml that is contained in you report's rdl file. While the extension is rdl, it is actually an xml. If you prefer, you can email it to me at my alamoad dot com email address. The account is timp.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 25

Hi Tim,

I have emailed the .rdl file over to you.

Thanks,

Imran

Answer 26

Thanks, I got it. I will take a look at lunch time. Please post a screenshot of the report showing the problem you are experiencing.

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 27

Hi Tim,

After giving me your email address I have sent you the .rdl file and you have not replied. After you confirmed receipt of the file you have removed all Forum posts giving me your email address and confirming you have it. I take it you could not find a solution?

Thanks,

Imran

Answer 28

No, I didn't remove any forum post. I have the rdl and have looked at it. The screenshot you sent is for a completely different issue than we have been talking about:

This post may address your issue: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/4faba063-be5f-4783-893c-13dbcc2603dc/

Are you using Report Builder? or BIDS/VS?

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.

Answer 29

I am using report builder. I have also tried the solution in the post that you have forwarded on.

Thanks,

Imran

Answer 30

There are numerous other causes for that issue as you can see here:
http://www.google.com/#hl=en&safe=active&sout=1&sclient=psy-ab&q=unsupported+rpl+stream+version+detected&oq=unsupported+rpl+&gs_l=hp.3.0.0j0i22i30l3.1271.8119.0.10240.16.11.0.5.5.3.1282.4485.2j4j1j0j1j1j0j2.11.0...0.0...1c.1.7.psy-ab.PsivPvcZfVI&pbx=1&bav=on.2,or.r_qf.&bvm=bv.44158598,d.b2U&fp=c65f6bf126ebb2dd&biw=1371&bih=690&sout=1&surl=1

http://www.bing.com/search?q=unsupported+rpl+stream+version+detected&qs=n&form=QBLH&pq=unsupported+rpl+stream+version+detected&sc=1-39&sp=-1&sk=

---

"You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
Please Mark posts as answers or helpful so that others may find the fortune they seek.