I have a dataset that contains balances for customer accounts by month:
| CustomerID | Month | Balance |
|---|---|---|
| 1 | 1/1/2022 | 100.00 |
| 1 | 2/1/2022 | -100.00 |
| 2 | 1/1/2022 | 100.00 |
| 2 | 2/1/2022 | 100.00 |
I am then simply displaying this dataset in a table, so essentially just as it looks above.
I am trying to change the background color for balances which are canceled out by a negative balance of the exact same amount. So for Customer 1 for example, I would want to change the background color of the Balance cells for 1/1/2022 and 2/1/2022, since those cancel out. For my purposes, I am not worried about matching multiple times - if this customer also had a +-100.00 balance for 3/1/2022, it is fine for all three of those to be changed.
My expected output:
| CustomerID | Month | Balance |
|---|---|---|
| 1 | 1/1/2022 | 100.00 |
| 1 | 2/1/2022 | -100.00 |
| 2 | 1/1/2022 | 100.00 |
| 2 | 2/1/2022 | 100.00 |
I am using the following expression in the Background Color property to accomplish this:
=IIf(
IsNothing(
Lookup(
(-1 * Fields!Balance.Value) & Fields!CustomerID.Value
,Fields!Balance.Value & Fields!CustomerID.Value
,Fields!Balance.Value
,"BalancesDS"
)
)
,"No Color"
,"Yellow"
)
The issue I am having with this is that when the expression finds a match, it changes the data displayed for that row. The actual output I am getting is this:
| CustomerID | Month | Balance |
|---|---|---|
| 1 | 1/1/2022 | 100.00 |
| 1 | 1/1/2022 | 100.00 |
| 2 | 1/1/2022 | 100.00 |
| 2 | 2/1/2022 | 100.00 |
Note how instead of showing 2/1/2022 with a balance of -100.00, it is being changed to essentially a duplicate of the 1/1/2022 row.
I do not have any other expressions on this report at the moment, all of the fields displayed in the table are just straight from the dataset, so I'm at a bit of a loss. Even more strangely, this only seems to happen when the cancelling rows are adjacent in the dataset. I have the dataset sorted by CustomerID, then Month. I was only able to find one occurrence of this in my dataset, but when there was a different month in between the matching months, the data was displayed correctly:
| CustomerID | Month | Balance |
|---|---|---|
| 1 | 1/1/2022 | 100.00 |
| 1 | 1/1/2022 | 100.00 |
| 2 | 1/1/2022 | 100.00 |
| 2 | 2/1/2022 | 100.00 |
| 3 | 1/1/2022 | 100.00 |
| 3 | 2/1/2022 | 200.00 |
| 3 | 3/1/2022 | -100.00 |
See Customer 3 above. 1/1/2022 and 3/1/2022 cancel each other out, and with the extra month in between, the data is correctly displayed in this instance for whatever reason. Customer 1 is still displaying incorrectly though.
If I create an exact duplicate of the dataset with a different name and have the lookup look in that duplicate instead of in the tablix dataset, then I do get my expected output. So that is a workaround that I can use, but I would definitely prefer not to double the amount of data/datasets unless there is no alternative.
Does this seem to be working as intended? Is this even reproducible in a different environment? I'm at a bit of a loss, the expression and the report as a whole are pretty basic so I'm not really seeing any way to fix this other than by using a duplicate dataset.
Edit: Below is a sample RDL code, in case anyone feels like trying it. Had to remove some things like style properties for the tablixes to fit the character limit.
<?xml version="1.0" encoding="utf-8"?>
<Report MustUnderstand="df" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily">
<df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily>
<AutoRefresh>0</AutoRefresh>
<DataSources>
<DataSource Name="DataSource1">
<DataSourceReference>/Data Sources/Test</DataSourceReference>
<rd:SecurityType>None</rd:SecurityType>
<rd:DataSourceID>4e7acbfa-3ce2-4d8a-abdc-9d7414cafb67</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="BalancesDS">
<Query>
<DataSourceName>DataSource1</DataSourceName>
<CommandText>select
cast(1 as int) as CustomerID
,cast('2022-01-01' as date) as MonthPeriod
,cast(100.00 as decimal(18, 2)) as Balance
union
select
cast(1 as int) as CustomerID
,cast('2022-02-01' as date) as MonthPeriod
,cast(-100.00 as decimal(18, 2)) as Balance
union
select
cast(2 as int) as CustomerID
,cast('2022-01-01' as date) as MonthPeriod
,cast(100.00 as decimal(18, 2)) as Balance
union
select
cast(2 as int) as CustomerID
,cast('2022-02-01' as date) as MonthPeriod
,cast(100.00 as decimal(18, 2)) as Balance
union
select
cast(3 as int) as CustomerID
,cast('2022-01-01' as date) as MonthPeriod
,cast(100.00 as decimal(18, 2)) as Balance
union
select
cast(3 as int) as CustomerID
,cast('2022-02-01' as date) as MonthPeriod
,cast(200.00 as decimal(18, 2)) as Balance
union
select
cast(3 as int) as CustomerID
,cast('2022-03-01' as date) as MonthPeriod
,cast(-100.00 as decimal(18, 2)) as Balance</CommandText>
</Query>
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="MonthPeriod">
<DataField>MonthPeriod</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Balance">
<DataField>Balance</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
</DataSet>
</DataSets>
<ReportSections>
<ReportSection>
<Body>
<ReportItems>
<Tablix Name="Tablix1">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox15">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>With BackgroundColor Expression</Value>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox15</rd:DefaultName>
</Textbox>
<ColSpan>3</ColSpan>
</CellContents>
</TablixCell>
<TablixCell />
<TablixCell />
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox6">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Customer ID</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox6</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox8">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Month Period</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox8</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox10">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Balance</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox10</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="CustomerID">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!CustomerID.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>CustomerID</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="MonthPeriod">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!MonthPeriod.Value</Value>
<Style>
<Format>d</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>MonthPeriod</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Balance">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Balance.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Balance</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<BackgroundColor>=IIf(
IsNothing(
Lookup(
(-1 * Fields!Balance.Value) & Fields!CustomerID.Value
,Fields!Balance.Value & Fields!CustomerID.Value
,Fields!Balance.Value
,"BalancesDS"
)
)
,"No Color"
,"Yellow"
)</BackgroundColor>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
<TablixMember />
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<KeepWithGroup>After</KeepWithGroup>
</TablixMember>
<TablixMember>
<KeepWithGroup>After</KeepWithGroup>
</TablixMember>
<TablixMember>
<Group Name="Details" />
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>BalancesDS</DataSetName>
<Top>0.25in</Top>
<Left>0.25in</Left>
<Height>0.75in</Height>
<Width>3in</Width>
</Tablix>
<Tablix Name="Tablix2">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox16">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Without BackgroundColor Expression</Value>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox15</rd:DefaultName>
</Textbox>
<ColSpan>3</ColSpan>
</CellContents>
</TablixCell>
<TablixCell />
<TablixCell />
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox7">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Customer ID</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox6</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox9">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Month Period</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox8</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox11">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Balance</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox10</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="CustomerID2">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!CustomerID.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>CustomerID</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="MonthPeriod2">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!MonthPeriod.Value</Value>
<Style>
<Format>d</Format>
</Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>MonthPeriod</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Balance2">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!Balance.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Balance</rd:DefaultName>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
<TablixMember />
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<KeepWithGroup>After</KeepWithGroup>
</TablixMember>
<TablixMember>
<KeepWithGroup>After</KeepWithGroup>
</TablixMember>
<TablixMember>
<Group Name="Details2" />
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>BalancesDS</DataSetName>
<Top>1.5in</Top>
<Left>0.25in</Left>
<Height>0.75in</Height>
<Width>3in</Width>
<ZIndex>1</ZIndex>
</Tablix>
</ReportItems>
<Height>4.52083in</Height>
</Body>
<Width>7.5in</Width>
<Page>
<InteractiveHeight>0in</InteractiveHeight>
<InteractiveWidth>0in</InteractiveWidth>
<Style />
</Page>
</ReportSection>
</ReportSections>
<ReportParametersLayout>
<GridLayoutDefinition>
<NumberOfColumns>4</NumberOfColumns>
<NumberOfRows>2</NumberOfRows>
</GridLayoutDefinition>
</ReportParametersLayout>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
<rd:ReportServerUrl></rd:ReportServerUrl>
<rd:ReportID>427a9b7a-0570-45e3-912e-f62036630d2a</rd:ReportID>
</Report>
