Table Direct in Query Designer is not Available

[gfross]

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!

[gfross · Accepted solution]

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.

[gfross]

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.

[v-luwang-msft]

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-... 

The following may be helpful for you:

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

Best Regards

Lucien