Reports based on a dynamically filtered slice (as per Dashboard)

[Richard_Brouwer]

I have a dashboard view based on a dynamically filtered slice of my main table, which will display records based on calculations relating to several filter fields. Works perfectly for both single and multiple choices and combinations of each.

If I try to base a report off this filtered slice, a blank table is returned. This is the same behaviour as if I try to view the data of the filtered slice in the Data menu - it seems like the filters are only applied/calculated when the slice is ‘called’ in a view.

My question is how can I set up a filtered report based off a slice - I can get around it by making new slices with all the possible permutations and combinations of what the filter fields are but then I end up with 30+ slices and the same number of reports and templates to edit etc. and it gets very messy to manage.

[TyAlevizos]

“relating to several filter fields”

Where in the data model are these fields located? I think your report doesn’t have knowledge of their existing one way or another.

If you were to create a “global” table with only a single row of data, and then reference fields in it using Any(GlobalTable[aFilterField]), I bet your report would work.

[Richard_Brouwer]

The filter fields are located in a seperate Procedure Filters table so I can have different filters for different users. I currently have a slice based on two filter fields: Year and Hospital and these are all enum lists as they can have one or many values depending on what the user wants to see pulled out of the main data table.

I then use a calculation with AND() and multiple IF() and IN() to select the rows I want the slice to display on a Dashboard View:

AND(
IF(ISBLANK(IN([Hospital], Procedure Filter[fHospital])), TRUE, IN([Hospital], Procedure Filter[fHospital])),
IF(ISBLANK(IN([_Year], Procedure Filter[fYear])), TRUE , IN([_Year], Procedure Filter[fYear]))
)

So you are suggesting I make a virtual column in the main data table which references the filter field values from the Global(in my case Procedure Filter table) and then base the report slice off rows that have data in that field - indicating the have a valid value from the filter table - perhaps a True/False type expression like IF(IN([Hospital], Procedure Filter[fHospital]), TRUE, FALSE) ?

I really appreciate your thoughts and help - I’m new to this sort of expression building.

[Steve · Accepted solution]

Here’s your expression, reformatted to my taste:

AND(
  IF(
    ISBLANK(
      IN(
        [Hospital],
        Procedure Filter[fHospital]
      )
    ),
    TRUE,
    IN(
      [Hospital],
      Procedure Filter[fHospital]
    )
  ),
  IF(
    ISBLANK(
      IN(
        [_Year],
        Procedure Filter[fYear]
      )
    ),
    TRUE,
    IN(
      [_Year],
      Procedure Filter[fYear]
    )
  )
)

Note that these are nonsensical:

ISBLANK(
  IN(
    [Hospital],
    Procedure Filter[fHospital]
  )
),

and:

ISBLANK(
  IN(
    [_Year],
    Procedure Filter[fYear]
  )
),

The result of IN() will never be blank: it will be either TRUE or FALSE.

Typically, when one has a filter table, the goal is to treat an empty filter parameter as “match any”, but if it has a value, to match that value. An expression that should achieve that is:

AND(
  OR(
    ISBLANK(Procedure Filter[fHospital]),
    IN([Hospital], Procedure Filter[fHospital])
  ),
  OR(
    ISBLANK(Procedure Filter[fYear]),
    IN([_Year], Procedure Filter[fYear])
  )
)

See also:

help.appsheet.com

IN()

Is item in list?

[Steve]

Note that reports are run as the app owner, not as any other user of the app. If your filters are user- or device-specific, the report will not use it (unless the user happens to be the app owner).

[Richard_Brouwer]

Thanks Steve I’ve made sure user = app owner at this stage.

From Ty’s suggestion I have made a column to reference the values in the global filter field, but for some reason my IN() expression is returning FALSE even if the value is present in the LIST.

IN([Hospital], Procedure Filter[fHospital])

Is there something simple I’m missing here ? Clearly the value of [Hospital] is present in the List so should it not return Y (true) ? If I can get this working, then the reporting part is fine.

Thanks for any advice.

[Richard_Brouwer]

Got it working. Thanks very much for your time and detailed explanations - I think I finally understand the filtering logic now !