interactive sort and filter in a graph

Question

I am very new to SQL Server Report Builder. I'm using Microsoft SQL Server 2014.

My purpose for using SQL Server Report Builder is to publish up-to-date graphs for my SharePoint list. I am using SharePoint 2013.

I figured out how to create my first graph in SQL Server Report Builder and I displayed it using the SQL Server Reporting Service web part in SharePoint. But I would like interactive sorting and filtering (similar to how easy it is in Excel using pivot charts).

So for example, I want a bar graph to show all Project Statuses (Open, Closed, On Hold, etc). But there should be a drop down where the user can filter the graph to only show Open and Closed. Or I have a bar graph showing number of projects by employee. The user should be able to sort the graph ascending or descending by # of Projects.

I have searched but can't find a way to let the user have drop downs where they can filter and sort the charts.

Thanks

Answer 1

Hi,

You may set a parameter when design the report.

Here is a tutorial for you, you could also use the parameter as a filter in the chart.

https://learn.microsoft.com/en-us/sql/reporting-services/tutorial-add-a-parameter-to-your-report-report-builder?view=sql-server-ver15#Query

You could also add filters like shown in the article.

https://learn.microsoft.com/en-us/sql/reporting-services/report-design/add-a-filter-report-builder-and-ssrs?view=sql-server-ver15

And it seems that we could not use interactive sort in a chart.

We could sort the data points in ascending or descending order on a chart when designing the report.

For reference:

https://learn.microsoft.com/en-us/sql/reporting-services/report-design/sort-data-in-a-data-region-report-builder-and-ssrs?view=sql-server-ver15

Regards,

Zoe

Answer 2

Thanks for writing. I wasn't able to get it to work for 2 reasons: with a parameter, the chart is blank until the user manually types something in. I want the chart to show all values but have a drop down to show the filter choices from the SharePoint list (ex: Open, Closed, On Hold).

Thanks

Answer 3

Hi，

You could set the properties of the parameter "allow multiple values" and set the default value of the parameter.

When a parameter is configured to allow multiple values, we can specify one or more default values.

Here is a video tutorial for you,hope it can be useful.

watch

And then the chart will show all values when we review it, we could also drop down to show the filter choices.

Answer 4

Thanks so much for writing. Sorry for the delay. I signed up to follow this thread but never got an email notification.

I read the article you provided and watched the video but I wasn't able to make it work.

I used Query Designer so my query looks like:
'<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

'<ListName>SPTableProjects</ListName>

'<ViewFields>

' <FieldRef Name="Title" />

' <FieldRef Name="Status" />

' <FieldRef Name="ID" />

' </ViewFields>

'</RSSharePointList>

I tried to remove that and overwrite it with the Select statement below but I got the error: "Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."

Select
[Title]
,[ID]
,[Status]
from SPTableProjects

Thanks