Migrate Report Writer Dataset to Power BI

Good news! Whether your ConnectWise Manage is self-hosted or in the cloud with the Cloud Data Access add-on ($99/month for 11-50 users as of this writing), you can now establish a live Direct Query SQL connection to your ConnectWise data without having to purchase NexNow’s custom Power BI connector. This has dramatically reduced our fee for an On-premises Data Gateway implementation and kept me busy with gateway setups and report migrations from Report Writer and BrightGauge to Power BI.

Here’s one handy trick I’ve been using to help folks convert their sluggish Report Writer reports to Power BI. The best part about it is it doesn’t require you to know SQL. If you know how to join together Data Sources and add filters in Report Writer, you can turn that into a SQL query you can use directly in Power BI Desktop or Power BI Service/Apps (gateway setup required).

ConnectWise Report Writer Setup

First, go to your report you want to migrate or create a dataset in Report Writer. The key pieces here are 1) the Data Sources tab:

And 2) the Filters tab:

The Data Sources will be your SELECT/JOINS in the query, while your Filters tab will be the WHERE clause. Once you’ve created/opened your Report Writer report, click on the yellow SQL button to create an export of the SQL query. In the example above (admittedly probably not the most efficient of joins but it will do for an example), here is the output of the SQL export:

-- Detail
SELECT TOP 100000
[dbo].[v_rpt_AgreementList].[Company_Name] AS 'Company Name', [dbo].[v_rpt_Product].[item_desc] AS 'Item Desc', [dbo].[v_rpt_AgreementAdditions].[Total_Quantity] AS 'Total Quantity', [dbo].[v_rpt_AgreementAdditions].[AGR_Name] AS 'AGR Name', [dbo].[v_rpt_AgreementAdditions].[Unit_Cost] AS 'Unit Cost', [dbo].[v_rpt_AgreementAdditions].[Unit_Price] AS 'Unit Price', [dbo].[v_rpt_AgreementList].[agr_type_desc] AS 'Agr Type Desc', [dbo].[v_rpt_AgreementAdditions].[Ext_Cost] AS 'Ext Cost', [dbo].[v_rpt_AgreementList].[Agreement_Status] AS 'Agreement Status', [dbo].[v_rpt_AgreementAdditions].[Ext_Price] AS 'Ext Price', [dbo].[v_rpt_AgreementAdditions].[QuantityToBill] AS 'Quantity To Bill', [dbo].[v_rpt_AgreementAdditions].[Addition_Status] AS 'Addition Status', [dbo].[v_rpt_AgreementAdditions].[Bill_Customer] AS 'Bill Customer', [dbo].[v_rpt_AgreementAdditions].[Effective_Date] AS 'Effective Date', [dbo].[v_rpt_AgreementAdditions].[Last_Update] AS 'Last Update', [dbo].[v_rpt_AgreementAdditions].[Less_Included] AS 'Less Included', [dbo].[v_rpt_AgreementAdditions].[Margin] AS 'Margin', [dbo].[v_rpt_AgreementAdditions].[Product_Description] AS 'Product Description', [dbo].[v_rpt_AgreementAdditions].[Product_RecId] AS 'Product Rec Id', [dbo].[v_rpt_AgreementAdditions].[Sequence_Number] AS 'Sequence Number', [dbo].[v_rpt_AgreementAdditions].[Serial_Number] AS 'Serial Number', [dbo].[v_rpt_AgreementAdditions].[Taxable] AS 'Taxable', [dbo].[v_rpt_AgreementList].[BalAvailable] AS 'Bal Available', [dbo].[v_rpt_AgreementList].[BalRemaining] AS 'Bal Remaining', [dbo].[v_rpt_AgreementList].[Bill_Start_Date] AS 'Bill Start Date', [dbo].[v_rpt_AgreementList].[Billing_Amount] AS 'Billing Amount', [dbo].[v_rpt_AgreementList].[Billing_Cycle_Desc] AS 'Billing Cycle Desc', [dbo].[v_rpt_AgreementList].[Billing_CycleOption_RecID] AS 'Billing Cycle Option Rec ID', [dbo].[v_rpt_AgreementList].[Billing_Unit_Name] AS 'Billing Unit Name', [dbo].[v_rpt_AgreementList].[billing_unit_recid] AS 'Billing Unit Recid', [dbo].[v_rpt_AgreementList].[company_recid] AS 'Company Recid', [dbo].[v_rpt_Product].[item_id] AS 'Item Id', [dbo].[v_rpt_Product].[item_subcategory] AS 'Item Subcategory', [dbo].[v_rpt_Product].[IV_Class_ID] AS 'IV Class ID', [dbo].[v_rpt_Product].[uom] AS 'Uom', [dbo].[v_rpt_Product].[Line_Desc] AS 'Line Desc', [dbo].[v_rpt_Product].[Location] AS 'Location'
FROM [dbo].[v_rpt_AgreementList] WITH(NOLOCK)
INNER JOIN [dbo].[v_rpt_AgreementAdditions] WITH(NOLOCK) ON [dbo].[v_rpt_AgreementAdditions].[AGR_RecID]=[dbo].[v_rpt_AgreementList].[AGR_Header_RecID]
INNER JOIN [dbo].[v_rpt_Product] WITH(NOLOCK) ON [dbo].[v_rpt_Product].[iv_product_RecID]=[dbo].[v_rpt_AgreementAdditions].[Product_RecId]
WHERE ([dbo].[v_rpt_AgreementList].[agr_type_desc] = 'MS Gold') AND ([dbo].[v_rpt_AgreementList].[Agreement_Status] = N'Active') ;

 

In the above example, you’ll need to remove the comment “– Detail” as Power BI seems to choke on those. You also may want to remove the “Top 100000” so you are including all results. Once you’ve copied the code, paste it into your SQL query in Power BI Desktop and hit OK. Now you’ve got your dataset loaded into Power BI. All you need to do is rebuild your table/visualization.

Important Note

If you have anything in the Summary tab or you have subtotal calculations going on, Report Writer will export those as separate queries. You should only keep the main query and discard those. In Power BI, you can rebuild any summary or subtotal calculations.

Want to do more?

Do you want to set up a Power BI Gateway so you can collaborate on reports and view them on your mobile device? Contact us for one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.