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!

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:

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

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!