topic Re: Table Direct in Query Designer is not Available in Report Server

Table Direct in Query Designer is not Available

gfross — Mon, 17 Oct 2022 21:31:17 GMT

I didn't see a location for Power BI Report Builder so I created this under Report Server. I'm having issues being able to use the Table Direct option in Query Designer. I'm trying to connect to a MySQL database and I'm using the ODBC connection that I have setup on my machine. I didn't see a option to select MySQL when setting up the dataset, even though there's one available in Power BI Desktop. I'm using a 64-bit System DSN which is using the MySQL ODBC 8.0 ANSI Driver.

I'm able to write SQL statements to retrieve data using the Text Based Query, but I have users that do not know SQL. So, having them to be able to use Table Direct is important. When I try and select the Table Direct option, I get the following error.

Any help is greatly appreciated. Thank you!

Re: Table Direct in Query Designer is not Available

v-luwang-msft — Tue, 18 Oct 2022 06:20:28 GMT

Hi gfross ，

1. In Power BI report builder, create a data source which uses Oracle data source connector.

2. Create a dataset based on the data source created in above step, you can write query to retrieve data.

3. To create parameters follow this document: https://docs.microsoft.com/en-us/power-bi/report-builder-parameters

And about text_based query ,pls refer:

https://learn.microsoft.com/en-us/sql/reporting-services/report-data/text-based-query-designer-user-interface-report-builder?view=sql-server-ver16

The following may be helpful for you:

https://stackoverflow.com/questions/37978344/ssdt-for-ssrs-unable-to-connect-to-data-source-datasource1-the-selected-data

Best Regards

Lucien

Re: Table Direct in Query Designer is not Available

gfross — Tue, 18 Oct 2022 12:27:41 GMT

Thank you for the reply! But at the end of the day, I need my users to be able to select 'Table' and not 'Text' for the query type. This way they can use the Query Designer. My users don't know SQL.

Re: Table Direct in Query Designer is not Available

gfross — Tue, 18 Oct 2022 12:55:44 GMT

I found a solution. I was able to create a datasource in Power BI Desktop, then I published it, and I was able to select that published datasource in Report Builder.