Search code examples
ssrs-2014

SSRS: Adding a filter that returns information from entire group

I am trying to create a report in SSRS. Below is a small example of what my dataset looks like.

Example Data Set

So, there are three different stores (A,B,C) and each has a landlord (a,b,c). Landlords can pay via three different methods (1,2,3) and the amounts paid per method are shown.

Right now, I have two filters set up. The first is by Store and the second is by Landlord.

What I am having trouble with is: How can I set up a filter by the Amount that will return information from an entire Store/Landlord?

So for example, if I wanted to filter Amount by 150, I would like to return all the "payment" information for the store(s) that have a payment of 150. Such as the following:

Desired Result

Is it possible to add a filter to return information from the entire group? (Store and Landlord are the group in this case)

I am new to SSRS so any help/insight would be greatly appreciated!

Solution

  • You can use LookUpSet to locate the matching groups, JOIN to put the results in a string and the INSTR function to filter your results.

    =IIF(ISNOTHING(Parameters!AMOUNT.Value) OR INSTR(
Join(LOOKUPSET(Fields!Amount.Value, Fields!Amount.Value, Fields!Store.Value, "DataSet1"), ", ") , 
Fields!Store.Value
) > 0, 1, 0)

    This translates to: If the Store value is found (INSTR > 0) in the list (JOIN) of Stores where the Amount is the current Amount (Lookupset).

    In your filter, put the above expression in the Expression, change the type to INTEGER and the Value to 1.

    [1]