How to Implement Multi-Parameter Queries in SSRS Report Builder with Databricks ODBC?

Question

I'm currently working on a report in SSRS (SQL Server Reporting Services) where my data source is Databricks, accessed via ODBC. The report requires implementing a multi-select parameter for filtering results based on office names.

I have a SQL query that works perfectly with a single-select parameter using a question mark ? as a placeholder for dynamic input. However, I'm struggling to modify the query to support multi-select parameters. The query is supposed to filter results based on multiple office names, but I'm unable to pass a list or array to the single placeholder used for the IN clause. Here is the part of the query causing issues:

SELECT DISTINCT
       dimpractice.PracticeId,
       dimpractice.PracticeName AS Practice
FROM curated.dimprojectmaster
    INNER JOIN curated.dimorganization
        ON dimprojectmaster.OrganizationID = dimorganization.OrganizationID
    INNER JOIN curated.dimpractice
        ON dimorganization.PracticeID = dimpractice.PracticeID
    INNER JOIN curated.dimprofitcenter
        ON dimorganization.ProfitCenterID = dimprofitcenter.ProfitCenterID
    INNER JOIN curated.dimoffice
        ON dimprofitcenter.OfficeID = dimoffice.OfficeID
      AND dimprojectmaster.ChargeType IN ('Regular', 'Promotional')
AND dimoffice.OfficeName IN (?) 
ORDER BY Practice

dimoffice.OfficeName IN (?) -- This is where the problem occurs with multi-select

Directly pass the parameter just like below example then it will work SELECT TOP (1000) [SERIAL_NUMBER] FROM [OperationalReports].[dbo].[GB193010-BB] WHERE [SERIAL_NUMBER] IN (@ReportParameter1) — Arpit Sharma
– Arpit Sharma, Commented Sep 4, 2024 at 11:13

Bikerdad955 · Accepted Answer · 2025-02-21 01:14:56Z

0

Use a CTE. This is roughly what it should look like.

with cteParams as 
(select explode(split(?, ',')) as MyParamValue)
SELECT DISTINCT
       dimpractice.PracticeId,
       dimpractice.PracticeName AS Practice
FROM curated.dimprojectmaster
    INNER JOIN curated.dimorganization
        ON dimprojectmaster.OrganizationID = dimorganization.OrganizationID
    INNER JOIN curated.dimpractice
        ON dimorganization.PracticeID = dimpractice.PracticeID
    INNER JOIN curated.dimprofitcenter
        ON dimorganization.ProfitCenterID = dimprofitcenter.ProfitCenterID
    INNER JOIN curated.dimoffice
        ON dimprofitcenter.OfficeID = dimoffice.OfficeID
      AND dimprojectmaster.ChargeType IN ('Regular', 'Promotional')
    inner join cteParams on dimoffice.OfficeName = cteParams.MyParamValue
ORDER BY Practice

answered Feb 21 at 1:14

Bikerdad955

1

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremy Caney Feb 21 at 2:20

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

Collectives™ on Stack Overflow

How to Implement Multi-Parameter Queries in SSRS Report Builder with Databricks ODBC?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related