report builder 3.0 count paramters

Question

New to SSRS.

I have created some parameters. however...

I am trying to create a count parameter / filter for a report in report builder 3.0.

I have the view of customers orders

ID, OrderID, Department, Date, delivered, Cancelled

columns delivered and Cancelled, are represented by 1, or 0

I want to have a parameter that the user of the report can select, the number of times an ID cancels a orders.

this can be 1, 2,3 or even 4 times.

thus when a user selects e.g 4, it filters the table to IDs that have cancelled orders 4 times

I thought of creating a count, and group on the ID in the SQL view.

however I need the date of each of the orders in the view. to see what dates the cancellations was occurring. thus just getting 1 per row.

So I can't create a parameters based on distinct number of cancelations, I would just return 1

any ideas please

You could use OUTER APPLY in your query to apply the total number of cancellations per ID. Then it's easy to add logic to filter it out. If you post your query we can help further. — BJones
this is correct, cant mark as correct but bjones, thank you x — pete

pete pete · Accepted Answer · 2018-05-14T12:45:45

0

votes

Thank you bjones, that outer apply worked (see comment)

Thank you x

thanks team