Data Connection Broken? ConnectWise Made Changes To Webreport
If you use URLs created in the ConnectWise Manage Web Report URL Creator tool to feed data to Excel, Power BI, etc, you may need to make some changes to get your data connections working again. ConnectWise changed the formatting of the password so it shows up quasi-encrypted rather than in clear text within the URL.
Getting the updated URL string
Unfortunately, though the old URL no longer provides any data, the URL still resolves so it’s not immediately apparent that anything is broken. You’ll need to disregard that URL and instead follow their updated documentation to go to the new URL creator address. For NA cloud partners, that is https://api-na.myconnectwise.net/v4_6_release/webreport/urlcreator/?companyId=YourCompany, replacing YourCompany with your company ID.
Log in to the tool and create a report for any table–it doesn’t matter which as we are just copying down the new URL string with the obfuscated password.
You now have the new URL formatting. If you’re using Excel, you’ll need to go to Data > Queries and Connections > double-click your query and go to Advanced Editor. The pieces you will be replacing are the version number and the password (the text after p= and before &r). See the screenshot below for an example. You may want to consider replacing the version number with the classic v4_6_release. This seems to automatically forward to the latest version, as of this writing–v2018_3.
Background/Thoughts
We discovered this after our client’s live Excel spreadsheet stopped updating and threw a data source error after a ConnectWise update. After discovering the changes, I initially thought that ConnectWise was enhancing security by generating a unique password for every report you create with the tool. Since that’s not the case, I’m not sure exactly what the purpose of the change is. The account used for URL creation is already only an API account so it’s not able to log into ConnectWise. You are still required to give the account access to all reporting in ConnectWise so theoretically anyone you share a data view with would be able to transform the URL and report on any other data in your system available to the reporting API.
If you have any insights, please let me know in the comments.
Want to do more?
Do you want to do more of your own ConnectWise reporting in Report Writer, Power BI, or another reporting tool? Contact usfor one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.