0

Using SQL Server 2014

I have a report, that runs a stored procedure, the resulting set brings back all records. For example all cars sold and not sold for the last 6 months.

Based on the initial data set I have created a Matrix, that shows by month how many cars were sold and not sold.

When the Report is run the matrix shows sold and not sold but after the report has been run I would like to filter only the matrix part of the report between sold, not sold and both, is this possible?

Thanks

PJD
  • 743
  • 2
  • 12
  • 38
  • I'd create a report parameter and filter the query and not the matrix. – aduguid Aug 20 '18 at 00:06
  • Hi aduguid, unfortunately this is not an option, I have a table in the same report that run's of the same data set that I still want to show all details. There was a second response to this query that seems to have been deleted, was this added by yourself? Thanks, Phil – PJD Aug 20 '18 at 07:38
  • @PJD There was an answer from me. I sometimes delete my ansers when I find that not even the questioners care. Well, ok, this time I might have been too quick, sorry. It's available again. – Wolfgang Kais Aug 20 '18 at 12:12

1 Answers1

0

Yes, that is possible.

Create a Parameter named Sold in your report and define 3 Available Values:

Available Values

On the Default Values page, define 3 as the default value. Then, on the Filters page in the Tablix Properties window, define a filter for a boolean expression comparing to true:

Filter

You can use something like this as the Expression:

=(Fields!IsSold.Value=True And Parameters!Sold.Value<>2) 
Or (Fields!IsSold.Value=False And Parameters!Sold.Value<>1)

Through the definition of the default value, by default all records are shown, and you can decide later that you just want to see sold or unsold cars (click on "View Report" after changing the parameter selection).

Wolfgang Kais
  • 4,010
  • 2
  • 10
  • 17
  • Hi, thanks for re-posting your answer. What you have described above is what I have done though this does require the user to refresh the entire report before seeing the dataset based on the selected parameter. What I was hoping to do though was to be able to alter a filter within the matrix without having to rerun the report. – PJD Aug 22 '18 at 10:18
  • Are you sure that the stored procedure is run again when clicking on "view report" to apply the filter? – Wolfgang Kais Aug 22 '18 at 14:41
  • I would of though it would have to rerun the SP one you have clicked on the "view report" button. Based on the time it takes alone to run it's running everything again as I am having to import data from Excel and then do some matching against the data afterwards. What I was looking for was something like you have within an Excel Pivot where you can filter on a specific column header. – PJD Aug 22 '18 at 14:46