Changing Data Source from salesforce reports to SQL Server

Question

We have a report and visuals created using salesforce reports as the source of data. We would like to change the source to an

on-prem SQL server. We have created the SQL Server query to mimic the columns and data from salesforce reports. I added the SQL Server query as a new query to the pbix, I then tried copying the SQL Server query definition from the new query and replacing the salesforce reports "Source" query. This appears to work because after clicking the green checkmark next to the source query box/window, data is returned. We get the error when clicking "Apply" or "Close and Apply" from the Edit Query screen.

Error:

OLE DB or ODBC error: Type mismatch. (Exception from HRESULT:0x80020005 (DISP_E_TYPEMISMATCH)).

Anyone seen this error or tried to make a source change like this?

Thanks in advance,

curtismob

curtismob · Answer

ankitpatira and anyone else that is interested, I was able to successfully make the source change from salesforce reports to SQL Server.&nbsp;- I created a SQL Server query and named each result column exactly the same as the salesforce reports source columns, as well as having the columns in the same sequence (not sure the sequence matters, but did it for good measure)- Added my SQL Server query to the pbix- Once the new&nbsp;SQL Server query was added, I clicked the "Data" icon beneath the "Report" icon on the left ribbon&nbsp;and verified the new SQL Server query column data types all matched the salesforce reports source column data types (I feel like this resolved the issue)- I then&nbsp;went into "Edit Queries", copied&nbsp;the SQL Server query definition from the new query,&nbsp;replacing the salesforce reports "Source" query in source query box/window area&nbsp;I clicked the green check mark, no errors, then clicked Close and Apply with no errors and the visuals stayed&nbsp;in tact.

ankitpatira · Answer

curtismob&nbsp;In my opinion I don't think you will be able to do it that way since Salesforce and SQL Server are both different source connectors. I think easiest option will be to load sql query as a new query (like you have) -&gt; Close&amp;Apply -&gt; then replace fields for all the visuals with fields from sql query and then delete original salesforce query.&nbsp;

curtismob · Answer

Thank you for the response ankitpatira.&nbsp; I will give your suggestion a try and let you know how it goes.&nbsp;Regards,curtismob

curtismob · Answer

ankitpatira,&nbsp;I tried your suggestion of replacing the fields in the visuals with the new query fields, unfortunately it did not work as well as I had hoped.&nbsp; We will basicaly have to recreate the visuals.&nbsp;Any other ideas about possibly changing the source from salesforce reports to SQL Server?&nbsp;Thank you,curtismob

curtismob · Answer

One final note I wanted to share regarding changing this&nbsp;data source.&nbsp;&nbsp;Initially when trying to Close and Apply the source changes in the Query Editor, it would not allow it without signing in to salesforce.&nbsp; At first, I didn't understand why since I had replaced the salesforce reports "= Source" settings with the SQL Server settings (query).&nbsp;In the Query Editor&nbsp;(Edit Queries), I selected the&nbsp;SQL Server dataset/query in the left ribbon, then clicked "Advanced Editor" on the Home tab and copied the "let/in" settings.&nbsp; Next I selected the salesforce report dataset/query in the left ribbon, clicked "Advanced Editor" on the Home tab and pasted the "let/in" settings from the SQL Server dataset/query, replacing the previous salesforce reports settings.&nbsp;Best,curtismob

Forum Discussion

Changing Data Source from salesforce reports to SQL Server

5 Replies

Related content

Salesforce Report Connection

Power BI / Salesforce Report Connector - Does it even work???

Power BI service- salesforce report access

Unable to publish report that contains Salesforce data

Salesforce MFA