11 Aug 2016

ConnectWise comes with some useful built-in reports. However, there are usually a few things MSPs want to tweak so that each report fits their  specific requirements. Report Writer is a great tool for this--if the report is available there, that is.

As it turns out, a lot of standard reports either aren't included in Report Writer or reference stored procedures in the database that you can't access via Report Writer.

Here's how you can make your own customized version of a standard report for use in CW right alongside the built-in ones.

shutterstock_421484053

Example Report: Changing SLA Priority Report to look at Closed Date rather than Entered Date

To illustrate, I will use the SLA Priority Report in ConnectWise. With it, you can select the start/end date based on the date the ticket was created (Date_Entered). We want to make a version that searches based on closed date (Date_Closed).

Figure 1.1

Figure 1.1

Depending on which report you're copying, your steps may vary, but here are the tables you'll be looking at (we'll be adding rows to the highlighted ones in our example):

Figure 2.1

Figure 2.1

3 Steps to a Custom Report

Step One: Create copy of report on report server

In addition to these tables, we'll also need to go into the report server and create a copy. One way to do this is to visit http://localhost/Reports on the ConnectWise server, click Details View in the top right corner, select the folder that matches your CW database name (e.g. "nexnow"), and go to the Service Tickets folder.

Here, we can select the report SLAPriority and choose to edit it in Report Builder.

Figure 3.1

Figure 3.1

Before I do anything else, I'll Save As and rename the report. I renamed this one to SLAPriority_Closed. Now if I accidentally just hit Save, I'm not modifying the built-in report.

Step Two: Find and modify any relevant stored procedures

Once you're in the report, expand the Datasets. The one that looks interesting here is SLAPriorityResults. Going into it Properties page, we see that it references usp_report_SR_SLAPriorityDetail.

Figure 4.1

Figure 4.1

Under cwwebapp>Programmability>dbo.usp_report_SR_SLAPriorityDetail in SQL Management Studio, do a Script Stored Procedure as > CREATE TO > New Query Editor Window and let's take a look at that procedure.

Figure 5.1

Figure 5.1

There's a lot going on here and you may want to enlist the help of a SQL savvy individual. For this particular stored procedure, it turns out there's no reference to Date_Closed. In this example, a simple Find/Replace of Date_Entered for Date_Closed is all that needs to happen. When you're ready to save the modified procedure, append "_Closed" to the procedure name on the CREATE PROCEDURE line then Execute.

Figure 6.1

Figure 6.1

Now that we have our new procedure, we can go back to Dataset Properties (Figure 4.1) and select our new procedure instead. Before hitting Save, you'll probably also want to rename the form field from "Entered Date" to "Closed Date."

Step Three: Adding the database tables for the report

Now that we've got everything set up, we need to get this report to show up in the ConnectWise GUI. The first table you'll want to modify is System_Reports. In that table, look up the existing report by the Report_Name field to find the System_Reports_RecID. You'll want to add an identical row, replacing only:

  • Report_Name (to append "Closed")
  • Report_Link (same title as your new report on the report server)
  • Description
  • Spec_Description
  • Spec_Purpose
  • Last_Update
  • Optional: Updated_By

Now, if you try to look at the report in the GUI, you'll see there are not yet any options to select. To get those, we need to add some rows to the system_report_field and system_report_field_option tables.

Here are the rows we need to copy in system_report_field, substituting the System_Reports_Recid of the existing report (in my case it was 154--yours may be different) to the RecID of our new report.

Last, we need to use the System_Report_Field_Recid of the row with Parameter_Name of "Show" (1398 here) and look it up in the system_report_field_option table.

System Report Field 1

Create similar rows using your new System_Report_Field_Recid (you may find you want to do it slightly different in this case since we're only looking at closed tickets).

System Report Field 2

Now your new "Standard Report" is available in ConnectWise!

 

Do you have any great examples of CW reports you've modified or any that you're stuck on? Let us know in the comments.

Comments

  • Ray
    24 May 2017 Reply

    Nathan,

    Thank you for the article. Questions:

    1) After following these directions, will I be able to see the new
    report under the Service Reports in ConnectWise application?

  • Nathan Dufault
    14 July 2017 Reply

    Hi Ray,

    Sorry for the late reply!

    If I understand your question correctly, yes, you will see the new report in the same spot as the report you based it off of. Just navigate to System > All Reports and find it under whatever name you put for the field, “Report_Name.”

    Thanks,
    Nathan

  • Martin
    17 October 2017 Reply

    How do I get access to my connectwise database in SSMS? My connectwise is hosted.

  • Nathan Dufault
    18 October 2017 Reply

    Hi Martin,

    You’ll need to contact your account manager about setting up CloudConnect on your CW instance. Basically they spin up a new read only database that lags maybe 10 min behind production. unfortunately, you will not be able to directly create any custom procs, views, etc.

    Feel free to send us a note and let us know if there’s something particular you’re trying to do and we’ll see how it might be accomplished within the limits of hosted CW.

  • Martin
    18 October 2017 Reply

    Hi Nathan,

    Thanks for your quick response .

    We are trying to create a dashboard based primarily on Dashboard: Service – Current Report, but we can’t find it in Report Writer to be able to Save As and make our modifications. Is there a reason we can’t see it in All Reports when in Report Writer? We want to create simple gauges that have just a number in them, such as a count of things.

    Also we are finding it difficult/impossible to display data labels on our charts e.g. on the top of bars – a simple feature in Excel and other Report writers we are using. Can Connectwise Report Writer do this? And if so where is the option hidden?

    Martin

    • Nathan Dufault
      24 October 2017 Reply

      Hi Martin,

      I just took a look at a CW instance to see what you’re talking about. You’re right that some reports aren’t in Report Writer. I spoke with ConnectWise about this before and they said that they continue to move reports into Report Writer but some just aren’t available currently. This article has the solution for on-premise partners. For cloud partners, your best bet for a complex report that Report Writer isn’t suited for might be to use the use the CW API to build it out in PowerBI. There are also subscription based tools like BrightGauge to make do-it-yourself dashboard building a lot easier to get started with. Or, we could give you a quote to build a report you’re looking for and show you how to make edits.

      For your questions on the data labels, I would contact CW support. They can access your CW directly and give you help with the exact report you’re looking at. They won’t tell you how to build a report but they can look at the report you’re building and answer questions regarding functionality of the software.

      Hope that helps!

      Nathan

Leave a Comment