Please note:

Comfortability with Lava and SQL is a prerequisate.  This recipe isn't for the faint of heart.  However, if you're persistent and perceptive, I'm sure you'll be able to work it out.  I was a little rusty when writing the example for this Recipe, so it took me an hour to troubleshoot something I forgot, so follow carefully :)

The Objective

In short, this recipe lets you create a block with any filters that can display any data you want (well, whatever you want that you know how to write a query for).  Also, the workflow that I'm detailing here will allow you to save/read user preference.  The specific implementation that I detail will be redirecting the user on page load if the user preference doesn't match.  This effectively runs the query twice (not optimal) but generally, when you link to the page, you can look up that person's User Preferences (following the workflow detailed here) when generating the link to the page, so that when they link to the page, the filter will already be applied.

Sometimes Dynamic Report blocks offer too many options to the user (like you want to limit the group selection to Volunteer groups), or they run too slow on a large data set, or when selecting too many fields.  Other times, you just can't include the data columns that you want to include.

Helpful applications of this Recipe is making financial or attendance reports, and replacing a block that allows you more control as an admin.  In the DD block, you can include all the HTML of an <a> tag to make the text link like any other block in Rock might.

What You'll Find Here (and how to find it)

This Recipe shows you how to make a Dynamic Data block (aided with a Workflow) function very similarly to a Dynamic Report block, or like a filter options grid.

Keep in mind, you may need to do more formatting to other data types (like dates) but treating things like they're strings until they're in the SQL makes implementing this much easier.

At the bottom of this Recipe, you'll see screenshots of the whole configuration.

The Good Stuff (aka, why you're still reading)

The Dynamic Data Block

This recipe uses a workflow to set the user preference for the current person viewing it, so when they go to view the report again, it will automatically pick up the filters that they had set previously.  It handles all of the functions of a Dynamic Report block, but no Data View, and no Report :)

This case uses a FirstName and LastName parameter from the URL.  If the parameter is absent, then the filter for that parameter is ignored.

{%- assign LastName = PageParameter.LastName | WithFallback:'','' | SanitizeSql | Prepend:"'" | Append:"'" -%}
{%- assign FirstName = PageParameter.FirstName | WithFallback:'','' | SanitizeSql | Prepend:"'" | Append:"'" -%}

Declare @FirstName varchar(50) = {{ FirstName }}
Declare @LastName varchar(50) = {{ LastName }}

Select p.Id, p.FirstName, p.LastName, p.Email
From [Person] p
Where (
        p.FirstName = @FirstName    -- Where First Name matches what was searched for.
        or @FirstName = ''          -- or, if First Name wasn't searched for, then return anyone.
    ) and (
        p.LastName = @LastName  -- Where Last Name matches what was searched for.
        or @LastName = ''       -- or, if Last Name wasn't searched for, then return anyone.
    )
    and p.IsDeceased = 0 --Not Deceased
    and p.RecordTypeValueId = 1    

The seemingly unnecessary | WithFallback:'','' at the top makes sure that if the Parameter is missing, that it's returned as a blank string.  In the where statement in the query, you can see "p.FirstName = @FirstName or @FirstName = '' ".  This handles filtering for the parameter if it's defined, or doesn't filter if the parameter isn't defined.  If you're planning on passing an integer, you should parse it from a string to help protect against SQL injection.

The Workflow

This workflow has a few components.  How specifically they're implemented depends on your use case.  Also, the Workflow detailed here has some extra components to compensate for a bug around inconsistent behavior with workflows that aren't automatically persisted.

1. Get the Values

You need to have a User Entry Form that allows the user to set the querystring.  I recommend making your life easier by having your Workflow Attribute Keys, Query and Query Parameters all matching in name so that you can leverage the code editor's multi-line editing (and a future Recipe on that is coming).

The Form Header in this User Entry Form contains some Lava logic to retrieve the user's preference, compare it to the querystring, and redirect them to the desired configuration if the parameters don't match their preference.  Hitting Filter on the User Entry Form will update their preference, and then redirect them to the page that they're currently on, but with the adjusted filtering.

{%- assign prefFirstName = CurrentPerson | GetUserPreference:'workflowtype-23-attribute-5002' | WithFallback:'','' -%}
{%- assign prefLastName = CurrentPerson | GetUserPreference:'workflowtype-23-attribute-4999' | WithFallback:'','' -%}

{%- assign paramFirstName = PageParameter.FirstName | WithFallback:'','' | UrlDecode -%}
{%- assign paramLastName = PageParameter.LastName | WithFallback:'','' | UrlDecode -%}

{%- if prefFirstName != paramFirstName -%}
    {%- assign diffFirstName = true -%}
{%- else -%}
    {%- assign diffFirstName = false -%}
{%- endif -%}
{%- if prefLastName != paramLastName -%}
    {%- assign diffLastName = true -%}
{%- else -%}
    {%- assign diffLastName = false -%}
{%- endif -%}

{%- if diffFirstName == true or diffLastName == true -%}
    {{--}}
    {%- capture querystring -%}
        {{- prefFirstName | WithFallback:'&FirstName=','','prepend' -}}
        {{- prefLastName | WithFallback:'&LastName=','','prepend' -}}
    {%- endcapture -%}
    {%- capture redirectUrl -%}{{- 'Global' | Attribute:'InternalApplicationRoot' -}}page/1542{{ querystring | ReplaceFirst:'&','?' -}}{%- endcapture -%}
    {{- redirectUrl | PageRedirect -}}
{%- endif -%}

The scheme I use for the User Preference Key is "workflow-<workflow type Id>-attribute-<attribute Id>" to help guarantee uniqueness.  Also, almost all of this lava can be written in parallel for each Workflow Attribute if you plan ahead.  So, it can take just about as long to set up 6 attributes as 2 attributes.  Also, make sure that you update your page route appropriately.

2. Conditionally, Clear the Filters

When the user selects "Clear" we want to clear the workflow attributes so that their preferences can be adjusted to match.

3. Update User Preference

Right now, some lava in a Run Lava action won't actually evaluate if you don't have some "throw away" value that it saves to.  I didn't verify if it's an issue that affect this case, I just implemented the precaution just in case.

Here's the Lava

{%- assign FirstNameValue = Workflow | Attribute:'FirstName','RawValue' -%}
{%- assign LastNameValue = Workflow | Attribute:'LastName','RawValue' -%}

{{- CurrentPerson | SetUserPreference:'workflowtype-23-attribute-5002',FirstNameValue -}}
{{- CurrentPerson | SetUserPreference:'workflowtype-23-attribute-4999',LastNameValue -}}

4. Generate the Redirect Url

Again, don't forget to update your Page Route!

Here's the Lava!  Again, the way this is laid out is very easy to extend for multiple attribute values.

{%- assign FirstName = Workflow | Attribute:'FirstName','RawValue' | UrlEncode - %}
{%- assign LastName = Workflow | Attribute:'LastName','RawValue' | UrlEncode - %}
{{--}}
{%- capture querystring -%}
    {{- FirstName | WithFallback:'&FirstName=','','prepend' -}}
    {{- LastName | WithFallback:'&LastName=','','prepend' -}}
{%- endcapture -%}
{{--}}
{{- 'Global' | Attribute:'InternalApplicationRoot' -}}/page/1542{{ querystring | ReplaceFirst:'&','?' -}}

5. Execute Redirect, Delete Workflow

It's really easy to forget to select the Url Attribute.  I forget it, and troubleshoot for it each time I make one of these...

6. (temporary fix) Another action, Delay, then Delete

Full screenshots

Please reach out to me in the Rock Community at @chrisrea if you have any questions!