topic Re: Using Postgres as a data source and create mutli value selection in power builder in Developer

Using Postgres as a data source and create mutli value selection in power builder

gantwanc — Fri, 02 Feb 2024 16:36:11 GMT

Hello,

I am working on creating a paginated report using Power Builder to give our end users the ability to filter datasets and schedule Excel files to their inbox using the Power BI service. Right now, I can use SQL Server to create a parameter with multi-select capabilities. When I try to use our Postgres database I get the following error.

Cannot add multi value query parameter '?' for dataset 'ds_con_events' because it is not supported by the data extension.
----------------------------
An error has occurred during report processing.
----------------------------
An error occurred during local report processing.

What is the step-by-step process to connect to a Postgres database and create a parameter that allows mutli select filtering options in a paginated report?

Re: Using Postgres as a data source and create mutli value selection in power builder

Anonymous — Mon, 05 Feb 2024 02:43:02 GMT

Hi @gantwanc,
Can you please share some more detail information about this? They should help us clarify your scenario and test to troubleshoot.

How to Get Your Question Answered Quickly

In addition, I'd like to suggest you take a look the following document about add multi-value parameter in paginated report and the limitations:

Add a multi-value parameter to a paginated report - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

Re: Using Postgres as a data source and create mutli value selection in power builder

gantwanc — Mon, 05 Feb 2024 15:14:52 GMT

@Anonymous Hello, Please see more details below:

1. I am connecting to an ODBC connection type using an Amazon Redshift driver as a connection string

2. Within the query I use a ? next to the where clause to be able to use the parameter. This was the only way I was able to get the parameters to work using a ODBC connection type. for SQL server I use @ and I am able to select multiple filters.

The query is select multiple fields from table where market in ? (This is the parameter symbol)
3. The parameter properties are the same as the below screenshot:

4. when I try to test the report I am trying to select multiple years or markets. The below case is the markets:

5. When I run the view I get the below error message:

What is the best way to allow multiple selections using a ODBC connection?

Re: Using Postgres as a data source and create mutli value selection in power builder

gantwanc — Mon, 05 Feb 2024 15:54:27 GMT

@Anonymous The documentation seems to be geared towards SQL Server. I was wondering how to do the same thing connecting to ODBC. Is there any documentation that shows how to connect to other databases besides SQL Server?

Re: Using Postgres as a data source and create mutli value selection in power builder

gantwanc — Mon, 05 Feb 2024 15:57:26 GMT

@Anonymous Here is another screenshot:

Re: Using Postgres as a data source and create mutli value selection in power builder

Anonymous — Tue, 06 Feb 2024 06:32:26 GMT

HI @gantwanc,

How did you define the parameter and query string? Can you please share some more detail?

BTW, you can also take a look at the following link about the similar issues:

SSRS Error: Cannot add multi value query parameter '@CountryId' for dataset 'ds_GetPractice' because it is not supported by the data extension. (rsErrorAddingMultiValueQueryParameter) - Microsoft Q&A
Regards,

Xiaoxin Sheng

Re: Using Postgres as a data source and create mutli value selection in power builder

gantwanc — Tue, 06 Feb 2024 14:41:58 GMT

@Anonymous Please see a couple a screenshots related to the parameters below:

Does this help?