Add multiple Complex Default Values to a Number type Variable

Hello!

Could really use some help trying to figure this one out. The database we're querying is Postgres 9.6.x.

I'm trying to add a 'Number' type filter so that I'm able to look up the information I want by an account's id number (or multiple account id's at the same time), which I know is possible by something like WHERE id IN ({{account_id}}), the problem I have however, is that I also want to set up a series of default values, so as to save a "view" of the table data that I want to display to users when they first open the report. I was able to get the default values to show up accordingly by doing something like WHERE id IN ([[{{account_id}}#]](SELECT id FROM accounts WHERE id IN (id1, id2, id3, ..., idn))), but then as soon as I try to look for an individual account by using the number filter, I get the following error message: "ERROR: more than one row returned by a subquery used as an expression" (see attached screenshots - some info. on the table is blurred out for customer privacy).


Can you share the whole query? I just want to see what you are doing and if i am able to replicate

Hey @TonyC , thank you for getting back to me. Sure thing! Here's the original query I was working with originally for this post:

WITH user_count AS (
    SELECT 
        account_id, 
        COUNT(account_id) AS count
    FROM users
    GROUP BY account_id
), admin_count AS (
    SELECT 
        account_id, 
        COUNT(role) AS count
    FROM users
    WHERE role = 0
    GROUP BY account_id
), editor_count AS (
    SELECT 
        account_id, 
        COUNT(role) AS count
    FROM users
    WHERE role = 1
    GROUP BY account_id
), viewer_count AS (
    SELECT 
        account_id, 
        COUNT(role) AS count
    FROM users
    WHERE role = 2
    GROUP BY account_id
), manual_paid_seats AS (
    SELECT 
        ms.account_id,
        ms.state,
        ms.period_end,
        CONCAT_WS('/', COUNT(u.role), ms.paid_seat_count) AS "Paid Seat Usage"
    FROM manual_subscriptions ms
    LEFT JOIN users u
        ON u.account_id = ms.account_id
    WHERE ms.state = 'active' AND u.role IN (0, 1)
    GROUP BY ms.account_id, ms.state, ms.paid_seat_count, ms.period_end
), stripe_paid_seats AS (
    SELECT *
    FROM (
        SELECT 
            ps.account_id,
            ps.state,
            ps.current_period_end,
            CONCAT_WS('/', COUNT(u.role), sa.quantity) AS "Paid Seat Usage",
            ROW_NUMBER() OVER (
                PARTITION BY ps.id ORDER BY sa.starts_at DESC
    ) AS row_number
    FROM subscription_agreements sa
    LEFT JOIN payola_subscriptions ps
        ON ps.id = sa.payola_subscription_id 
    LEFT JOIN users u
        ON u.account_id = ps.account_id
    WHERE ps.state = 'active' AND u.role IN (0, 1)
    GROUP BY ps.account_id, ps.state, sa.quantity, ps.current_period_end, sa.starts_at, ps.id) temp where row_number=1
), free_seat_count AS (
    SELECT 
        a.id,
        CONCAT_WS('/', COUNT(role), '3') AS "Paid Seat Usage"
    FROM accounts a
    LEFT JOIN users u
        ON u.account_id = a.id
    WHERE role IN (0,1) AND a.plan_unique_id = 'free'
    GROUP BY a.id
), legacy_plans_seat_count AS (
    SELECT 
        a.id,
        CONCAT_WS('/', COUNT(role), '∞') AS "Paid Seat Usage"
    FROM accounts a
    LEFT JOIN users u
        ON u.account_id = a.id
    WHERE role IN (0,1) AND a.plan_unique_id IN ('pro_legacy_annual')
    GROUP BY a.id
), credit_usage AS (
    SELECT 
        account_id,
        ABS(SUM(karma_points)) AS "Credit Usage"
    FROM point_transactions
    WHERE 
        cause IN (18, 19)
        [[AND {{date_range}}]]
    GROUP BY account_id
)

SELECT a.id AS "Account ID",
    COALESCE(clearbit_data.company_name, a.organization_name) AS "Company Name",
    a.contact_name AS "Main Contact",
    CASE
        WHEN u.account_owner = true THEN u.name
    END AS "Account Owner",
    u.email AS "Account Owner Email",
    CASE
        WHEN a.manual_subscription = true THEN 'Manual Billing'
        ELSE 'Self Served'
    END AS "Billing Cadence",
    CASE a.plan_unique_id
        WHEN 'agency' THEN 'Agency (Monthly)'
        WHEN 'agency_annual' THEN 'Agency (Annual)'
        WHEN 'annual' THEN 'Annual'
        WHEN 'basic' THEN 'Basic (Monthly)'
        WHEN 'basic_annual' THEN 'Basic (Annual)'
        WHEN 'basic_v3' THEN 'Basic (V3, Monthly)'
        WHEN 'basic_v3_annual' THEN 'Basic (V3, Annual)'
        WHEN 'enterprise' THEN 'Enterprise'
        WHEN 'free' THEN 'Free'
        WHEN 'pro_legacy' THEN 'Pro Legacy (Monthly)'
        WHEN 'pro_legacy_annual' THEN 'Pro Legacy (Annual)'
        WHEN 'pro_v2' THEN 'Pro (V2, Monthly)'
        WHEN 'pro_v2_annual' THEN 'Pro (V2, Annual)'
        WHEN 'pro_v3' THEN 'Pro (V3, Monthly)'
        WHEN 'pro_v3_annual' THEN 'Pro (V3, Annual)'
        WHEN 'solo' THEN 'Solo (Monthly)'
        WHEN 'solo_annual' THEN 'Solo (Annual)'
        WHEN 'studio' THEN 'Studio (Monthly)'
        WHEN 'studio_annual' THEN 'Studio (Annual)'
        WHEN 'team' THEN 'Team (Monthly)'
        WHEN 'team_annual' THEN 'Team (Annual)'
        WHEN 'team_v2' THEN 'Team (V2, Monthly)'
        WHEN 'team_v2_annual' THEN 'Team (V2, Annual)'
    END AS "Plan Type",
    CASE
        WHEN a.manual_subscription = true THEN mps.period_end
        WHEN a.manual_subscription = false THEN sps.current_period_end
    END AS "Renewal Date",     
    uc.count AS "Total Number of Users",
    CASE
        WHEN ac.count IS NULL THEN 0
        ELSE ac.count
    END AS "Number of Admins",
    CASE
        WHEN ec.count IS NULL THEN 0
        ELSE ec.count
    END AS "Number of Editors",
    CASE
        WHEN vc.count IS NULL THEN 0
        ELSE vc.count
    END AS "Number of Viewers",
    CASE 
        WHEN a.manual_subscription = true THEN mps."Paid Seat Usage"
        WHEN a.manual_subscription = false AND a.plan_unique_id NOT IN ('free', 'pro_legacy_annual') THEN sps."Paid Seat Usage"
        WHEN a.manual_subscription = false AND a.plan_unique_id IN ('pro_legacy_annual') THEN lpsc."Paid Seat Usage"
        ELSE fsc."Paid Seat Usage"
    END AS "Paid Seat Count",
    a.karma_points AS "Remaining Credit Balance",
    cu."Credit Usage" AS "Credits Used",
    alvv.amount/100 AS "Total LTV (USD)"
    
FROM accounts a

LEFT JOIN users u 
    ON u.account_id = a.id
LEFT JOIN user_count uc 
    ON uc.account_id = a.id
LEFT JOIN admin_count ac
    ON ac.account_id = a.id
LEFT JOIN editor_count ec
    ON ec.account_id = a.id
LEFT JOIN viewer_count vc
    ON vc.account_id = a.id
LEFT JOIN manual_paid_seats mps
    ON mps.account_id = a.id
LEFT JOIN stripe_paid_seats sps
    ON sps.account_id = a.id
LEFT JOIN free_seat_count fsc
    ON fsc.id = a.id
LEFT JOIN legacy_plans_seat_count lpsc
    ON lpsc.id = a.id
LEFT JOIN account_lifetime_value_views alvv
    ON alvv.account_id = a.id
LEFT JOIN credit_usage cu
    ON cu.account_id = a.id
LEFT JOIN (
    SELECT
      account_id,
      data->'person'->'employment'->>'role' as person_role,
      data->'company'->>'name' as company_name,
      data->'company'->'category'->>'sector' as company_sector
    FROM clearbit_lookups
    GROUP BY
      account_id,
      person_role,
      company_name,
      company_sector
) AS clearbit_data ON (clearbit_data.account_id = a.id)


WHERE u.account_owner IS NOT false
    AND a.id IN ([[{{account_id}} #]](SELECT id FROM accounts WHERE id IN (146228, 577087, 365462, 319722, 277400, 453612, 862474, 16853, 193684, 505523, 328911, 343558, 548767, 631554, 375521, 245385, 311824, 185398, 118614, 707419, 382942, 23480, 27506, 412194, 455297, 577087, 433524, 454597)))
    --(SELECT id FROM accounts WHERE id IN (146228, 577087, 365462, 319722, 277400, 453612, 862474, 16853, 193684, 505523, 328911, 343558, 548767, 631554, 375521, 245385, 311824, 185398, 118614, 707419, 382942, 23480, 27506, 412194, 455297, 577087, 433524, 454597))

GROUP BY 
    a.id, 
    clearbit_data.company_name, 
    u.account_owner, 
    u.name, 
    u.email, 
    uc.count, 
    ac.count, 
    ec.count, 
    vc.count, 
    mps."Paid Seat Usage", 
    sps."Paid Seat Usage", 
    fsc."Paid Seat Usage", 
    lpsc."Paid Seat Usage", 
    mps.period_end, 
    sps.current_period_end, 
    alvv.amount,
    cu."Credit Usage"
ORDER BY alvv.amount DESC

I was able to get it to work by replacing Complex Default Values on the query to a "Field Filter" that links directly to the account Id's and then setting up the default value for the ID's that I want as such:

WITH user_count AS (
    SELECT 
        account_id, 
        COUNT(account_id) AS count
    FROM users
    GROUP BY account_id
), admin_count AS (
    SELECT 
        account_id, 
        COUNT(role) AS count
    FROM users
    WHERE role = 0
    GROUP BY account_id
), editor_count AS (
    SELECT 
        account_id, 
        COUNT(role) AS count
    FROM users
    WHERE role = 1
    GROUP BY account_id
), viewer_count AS (
    SELECT 
        account_id, 
        COUNT(role) AS count
    FROM users
    WHERE role = 2
    GROUP BY account_id
), manual_paid_seats AS (
    SELECT 
        ms.account_id,
        ms.state,
        ms.period_end,
        CONCAT_WS('/', COUNT(u.role), ms.paid_seat_count) AS "Paid Seat Usage"
    FROM manual_subscriptions ms
    LEFT JOIN users u
        ON u.account_id = ms.account_id
    WHERE ms.state = 'active' AND u.role IN (0, 1)
    GROUP BY ms.account_id, ms.state, ms.paid_seat_count, ms.period_end
), stripe_paid_seats AS (
    SELECT *
    FROM (
        SELECT 
            ps.account_id,
            ps.state,
            ps.current_period_end,
            CONCAT_WS('/', COUNT(u.role), sa.quantity) AS "Paid Seat Usage",
            ROW_NUMBER() OVER (
                PARTITION BY ps.id ORDER BY sa.starts_at DESC
    ) AS row_number
    FROM subscription_agreements sa
    LEFT JOIN payola_subscriptions ps
        ON ps.id = sa.payola_subscription_id 
    LEFT JOIN users u
        ON u.account_id = ps.account_id
    WHERE ps.state = 'active' AND u.role IN (0, 1)
    GROUP BY ps.account_id, ps.state, sa.quantity, ps.current_period_end, sa.starts_at, ps.id) temp where row_number=1
), free_seat_count AS (
    SELECT 
        a.id,
        CONCAT_WS('/', COUNT(role), '3') AS "Paid Seat Usage"
    FROM accounts a
    LEFT JOIN users u
        ON u.account_id = a.id
    WHERE role IN (0,1) AND a.plan_unique_id = 'free'
    GROUP BY a.id
), legacy_plans_seat_count AS (
    SELECT 
        a.id,
        CONCAT_WS('/', COUNT(role), '∞') AS "Paid Seat Usage"
    FROM accounts a
    LEFT JOIN users u
        ON u.account_id = a.id
    WHERE role IN (0,1) AND a.plan_unique_id IN ('pro_legacy_annual')
    GROUP BY a.id
), credit_usage AS (
    SELECT 
        account_id,
        ABS(SUM(karma_points)) AS "Credit Usage"
    FROM point_transactions
    WHERE 
        cause IN (18, 19)
        [[AND {{date_range}}]]
    GROUP BY account_id
)

SELECT accounts.id AS "Account ID",
    COALESCE(clearbit_data.company_name, accounts.organization_name) AS "Company Name",
    accounts.contact_name AS "Main Contact",
    CASE
        WHEN u.account_owner = true THEN u.name
    END AS "Account Owner",
    u.email AS "Account Owner Email",
    CASE
        WHEN accounts.manual_subscription = true THEN 'Manual Billing'
        ELSE 'Self Served'
    END AS "Billing Cadence",
    CASE accounts.plan_unique_id
        WHEN 'agency' THEN 'Agency (Monthly)'
        WHEN 'agency_annual' THEN 'Agency (Annual)'
        WHEN 'annual' THEN 'Annual'
        WHEN 'basic' THEN 'Basic (Monthly)'
        WHEN 'basic_annual' THEN 'Basic (Annual)'
        WHEN 'basic_v3' THEN 'Basic (V3, Monthly)'
        WHEN 'basic_v3_annual' THEN 'Basic (V3, Annual)'
        WHEN 'enterprise' THEN 'Enterprise'
        WHEN 'free' THEN 'Free'
        WHEN 'pro_legacy' THEN 'Pro Legacy (Monthly)'
        WHEN 'pro_legacy_annual' THEN 'Pro Legacy (Annual)'
        WHEN 'pro_v2' THEN 'Pro (V2, Monthly)'
        WHEN 'pro_v2_annual' THEN 'Pro (V2, Annual)'
        WHEN 'pro_v3' THEN 'Pro (V3, Monthly)'
        WHEN 'pro_v3_annual' THEN 'Pro (V3, Annual)'
        WHEN 'solo' THEN 'Solo (Monthly)'
        WHEN 'solo_annual' THEN 'Solo (Annual)'
        WHEN 'studio' THEN 'Studio (Monthly)'
        WHEN 'studio_annual' THEN 'Studio (Annual)'
        WHEN 'team' THEN 'Team (Monthly)'
        WHEN 'team_annual' THEN 'Team (Annual)'
        WHEN 'team_v2' THEN 'Team (V2, Monthly)'
        WHEN 'team_v2_annual' THEN 'Team (V2, Annual)'
    END AS "Plan Type",
    CASE
        WHEN accounts.manual_subscription = true THEN mps.period_end
        WHEN accounts.manual_subscription = false THEN sps.current_period_end
    END AS "Renewal Date",     
    uc.count AS "Total Number of Users",
    CASE
        WHEN ac.count IS NULL THEN 0
        ELSE ac.count
    END AS "Number of Admins",
    CASE
        WHEN ec.count IS NULL THEN 0
        ELSE ec.count
    END AS "Number of Editors",
    CASE
        WHEN vc.count IS NULL THEN 0
        ELSE vc.count
    END AS "Number of Viewers",
    CASE 
        WHEN accounts.manual_subscription = true THEN mps."Paid Seat Usage"
        WHEN accounts.manual_subscription = false AND accounts.plan_unique_id NOT IN ('free', 'pro_legacy_annual') THEN sps."Paid Seat Usage"
        WHEN accounts.manual_subscription = false AND accounts.plan_unique_id IN ('pro_legacy_annual') THEN lpsc."Paid Seat Usage"
        ELSE fsc."Paid Seat Usage"
    END AS "Paid Seat Count",
    accounts.karma_points AS "Remaining Credit Balance",
    cu."Credit Usage" AS "Credits Used",
    alvv.amount/100 AS "Total LTV (USD)"
    
FROM accounts --a

LEFT JOIN users u 
    ON u.account_id = accounts.id
LEFT JOIN user_count uc 
    ON uc.account_id = accounts.id
LEFT JOIN admin_count ac
    ON ac.account_id = accounts.id
LEFT JOIN editor_count ec
    ON ec.account_id = accounts.id
LEFT JOIN viewer_count vc
    ON vc.account_id = accounts.id
LEFT JOIN manual_paid_seats mps
    ON mps.account_id = accounts.id
LEFT JOIN stripe_paid_seats sps
    ON sps.account_id = accounts.id
LEFT JOIN free_seat_count fsc
    ON fsc.id = accounts.id
LEFT JOIN legacy_plans_seat_count lpsc
    ON lpsc.id = accounts.id
LEFT JOIN account_lifetime_value_views alvv
    ON alvv.account_id = accounts.id
LEFT JOIN credit_usage cu
    ON cu.account_id = accounts.id
LEFT JOIN (
    SELECT
      account_id,
      data->'person'->'employment'->>'role' as person_role,
      data->'company'->>'name' as company_name,
      data->'company'->'category'->>'sector' as company_sector
    FROM clearbit_lookups
    GROUP BY
      account_id,
      person_role,
      company_name,
      company_sector
) AS clearbit_data ON (clearbit_data.account_id = accounts.id)


WHERE u.account_owner IS NOT false
    [[AND {{account_id}}]]
    [[AND {{plan_type}}]]
    [[AND {{manual_billing}}]]

GROUP BY 
    accounts.id, 
    clearbit_data.company_name, 
    u.account_owner, 
    u.name, 
    u.email, 
    uc.count, 
    ac.count, 
    ec.count, 
    vc.count, 
    mps."Paid Seat Usage", 
    sps."Paid Seat Usage", 
    fsc."Paid Seat Usage", 
    lpsc."Paid Seat Usage", 
    mps.period_end, 
    sps.current_period_end, 
    alvv.amount,
    cu."Credit Usage"
ORDER BY alvv.amount DESC

However, this limits me to only be able to update the filters for those 27 selections only. It would be nice to have something where perhaps I could add a "tag option" for "Key Accounts" in the field filter's dropdown menu that has the ID values I want stored in it [for example {{key_accounts}} = account_id IN (id1, id2, ..., idn)], so that when you select it and run the query it shows the results for those particular selections, but if you remove that option from the filter, it returns a full database query instead, but it looks like it's a feature request at this stage based on other threads I've seen.

Let me know if you have any suggestions, cheers! :slightly_smiling_face:

@DCamps Your optional clauses are wrong. Something like this should work:

AND a.id IN [[({{account_id}}) --]] (146228, 577087, 365462, 319722, 277400, 453612, 862474, 16853, 193684, 505523, 328911, 343558, 548767, 631554, 375521, 245385, 311824, 185398, 118614, 707419, 382942, 23480, 27506, 412194, 455297, 577087, 433524, 454597)

I'm not sure why you can only select 27 options with Field Filter, you should be able to select as many as you need (though there's some upper technical limits when reaching like a thousand).

Hello @flamber, thank you for getting back to me. I've modified the optional clauses to follow your syntax, and it does render the default values accordingly. However, when I try to type an account ID in the number filter and rerun the query, I get the following message: ERROR: operator does not exist: boolean # record Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 6067.

Regarding the 27 options in the field filter, I am able to query more than 27, it's just that those are the 27 ID's I chose as default values for this particular case. I guess what I would like to do ideally is add a dropdown option that if you click on it, it returns those 27 values named 'Key Accounts' for example, but if you remove the option from the filter it's just a full database query that should work for any values outside the ones I included as the default values... Not sure if that makes sense? But it looks like it's not something that's currently supported based on other threads I've seen, but please let me know if you know of a workaround for this.

Cheers! :slightly_smiling_face:

@DCamps You are using Postgres, which uses -- as comment syntax, not #.
Copy the line I provided.

1 Like

Hey @flamber apologies I never got back to you. I can confirm that this now works, thanks for all the help! :slight_smile: