Menu

Show Decimals in Report Writer Only If They Exist

Google+0
LinkedIn0

UPDATE

UPDATE 2021-04/27 – Here’s a simpler version that works in some cases (this is what I started with before settling on the version below as this way didn’t want to work on that invoice). If you need to do calculations on the numbers, stick with the original method as this method converts to text.

Set up a single Quantity field with the code below. You might need to select a text-based field so Report Writer doesn’t try to turn it into a number.

	CASE WHEN CONVERT(int,[Quantity]) = [Quantity] 
				THEN  CONVERT(varchar(10),CONVERT(int,[Quantity])) 
				ELSE  CONVERT(varchar(10),CONVERT(decimal(10,2),[Quantity])) 
	END

Original Post

It’s been way too long since my last Report Writer tip. So, here’s a quick one from my backlog for now. I have a list of future posts I will try and work my way through in the coming weeks. In this post I’ll show you how to show decimals in Report Writer if they exist and display clean integers otherwise.

This one comes from a client’s Report Writer custom invoice template where they wanted the quantities to show without a decimal if none existed. There are various ways to approach this in SQL but Report Writer doesn’t always like the easy way. Here’s a solution I came up with that it likes.

Report Writer Setup

This example comes from an agreement addition subreport on an invoice template. The key is putting two Quantity field versions next to each other (no spaces).

In your Advanced Field Properties for those fields, you are first evaluating whether converting quantity to a decimal-free integer yields the same number as the decimal version (another way to say it ends in “.00”). If so, we can feel free to strip out the “.00”. Here is my code for the Quantity and QuantityDecimal versions. The CASE statement ensures that only the one we want will return a value and the other will be NULL (no space taken on report).

Quantity field:

CASE WHEN CONVERT(int,[Quantity]) <> [Quantity] THEN NULL ELSE [Quantity] END

QuantityDecimal field:

CASE WHEN CONVERT(int,[Quantity]) = [Quantity] THEN NULL ELSE [Quantity] END

Now, change the Format in Report Writer Fields tab to the integer for Quantity and decimal for QuantityDecimal:

The Result

Now the product listing shows a clean integer for typical line items and a decimal for specific quantity items like backup TBs.

Want to do more?

Do you have any other Report Writer formatting issues you’ve been struggling with? Contact us for one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.

Related Posts