If you like or use FetchXML or math, you’ll like today’s post!

When creating a FetchXML SSRS report, applying mathematical calculations from the resulting textbox of an expression can sometimes be a quick way to retrieve values. For example, if you have multiple expressions and you are attempting to simply multiply or divide, instead of combining or in some instances recreating a huge new expression, you can use the actual textbox to perform these calculations instead!

ssrs

Here are a few tips to calculate values:

1. Using the Report Items function will pull the value from an already calculated field and do the math without making additional expressions. Here is an example:

ssrs

2. Report Items values can be Added, Subtracted, Multiplied, Divided, and more:

ssrs

Remember to use parentheses to account for order of operations, as seen in the above screenshot.

3. When using Report Items to calculate values, there are some limitations:

  • SUM/Aggregate of Report Items (Textbox): This can be done, but in a limited capacity – currently you are able to pull the SUM of a textbox, however it needs to be placed in the header or footer. For this reason, remember to omit (SUM) from your expression!
  • Calculating Report Items between two Tablix Datasets: When attempting to calculate values between two datasets, you may receive an error like this: The Value expression for the text box ‘Textbox198’ refers to the report item ‘Textbox194’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

This is a major limitation, but a way to work around this is to just make one large expression – typically pulling two datasets by using the =First function. Here’s an example:

=(CDec(Replace(First(Fields!field1.Value, “Dataset1”), “$”, “”)) +
CDec(Replace(First(Fields!field2.Value, “Dataset2”), “$”, “”))

Adding the CDec function also assisted the values to be converted to a decimal – a frequent requirement with data, so it is recommended that you use it if you encounter an #Error. Also, adding the VAL function can assist with returning numbers as the appropriate type.

In conclusion, textbox field calculations can be a very powerful SSRS tool, allowing you to quickly perform opportunity cost/margin-type calculations on the fly. However, there are some serious limitations to account for. But, if you’re able to utilize them, textbox calculations are a great option!

Don’t forget to subscribe to our blog. Happy Dynamics 365’ing!

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.