Just a quick post on something that I recently had to remember how to do and thought would be useful to write down and share.
The Problem
I have a dataset which duplicates some values, something like this:
Here I’ve got two product lines that each have two narratives associated with them. I am required to SUM the price column for a totals rows in a table displaying the product lines, so that I get an output in my report something like this:
By default, SSRS will use a SUM expression for the ‘Price’ column (SUM(Fields!Price.Value)), but in this instance it will give the wrong answers (21.00, 41.98, 62.98) because the product lines are being doubled up by their narratives. Ordinarily you might break the narratives out into their own data set, but sometimes that is not possible. So how do I get the correct answer?
The Solution
The expressions in the ‘Price’ column should be set to:
=SUM(IIF(Fields!NarrativeNum.Value = 1, Fields!Price.Value, 0))
This expression checks for the value of NarrativeNum on the current row and only SUMs the Price value if NarrativeNum == 1, otherwise we substitute a 0. In effect, the SUM sees our dataset like this:
Summary
We’ve seen how we can use a combination of functions to prevent SSRS from counting duplicate values. This is a simple technique but can be very useful.
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar