IF/THEN CASE Statements in ConnectWise Report Writer
A few years ago a world of possibilities opened up for me in ConnectWise Report Writer when I discovered the ability to add SQL expressions to report fields. Now, if you read carefully through every bit of documentation on RW, you may have come across some basic mention of this. Here’s an introductory post for those who are like me and RTFM is not always their thing.
Creating a CASE statement
One of the most useful custom SQL expressions you can do in Report Writer are CASE statements. A good example report for this is included by default at Report Writer > Standard Reports > Time and Expense > Hours By Staff – Work Role. Open the Fields section of this report and you’ll notice that they have the hours_actual field listed several times. In these instances, they are using CASE statements to slice the data.
Click the gear icon next to % Non-Billable and you’ll see the CASE statement entered in the Expression box.
As you can see, the Expression box can reference other fields in the Data Source. They don’t have to be fields that you’ve selected in the fields tab. You can build your CASE statement using standard CASE / WHEN / THEN / ELSE / END. For more information on building CASE statements, see this Microsoft article.
Syntax
Apart from the standard syntax to build your SQL statement, you’ll need to ensure that each of your field references are within square brackets: [ ]. Also note that you are indicating the actual field name in the SQL database (usually with an underscore between words), as opposed to the name in the Description field on report writer (defaults to the same as the field name but with underscores replaced by spaces).
Other SQL statements
I have found this functionality to be useful for several other SQL expressions, including text formatting and date functions. However, I have noticed that some SQL expressions are not able to be parsed by Report Writer. This is due to software the ConnectWise white-labels as Report Writer (created by Izenda) being on an older version. Here are some other available expressions from Izenda’s documentation.
Have you created something unique using SQL statements in Report Writer? Need help getting yours to work? Let us know in the comments or reach out to us at http://www.nexnow.net/contact.