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>
This is not an answer, only my findings...
I recreated your report and got the same results as you did. I then tried various things on the lookup to see what happened (such as converting everything to text in the first two arguments) but this had no effect.
I then tried changing another column and removed the formatting from the original column and still it failed.
After some trial and error it looks like if you have a
LOOKUP()that refences the same dataset anywhere on the row then it shows this issue.To test this, add a new column to the second tablix (the working one) and set the Value expression to the be the return value of your lookup.
i.e.
You will see that the original column is still affected and the return value is returning the data as if it was the data on as shown rather than as it is in the dataset.
I can't help but think that this is something to do with the way
LOOKUP()works and that you don't always have unique values but it is strange behaviour and personally I would think it's worth reporting to MS as a bug.