1

I am using Microsoft report builder. I have columns in a column group that are grouped by weeks. One of the field of this scope should be subtraction of one column value from current week minus column value from another week. Is this possible in ssrs? enter image description here

rough dataset:

Create table #Test
(
JobNum [nvarchar](20) 
,YearNumber int
,WeekNumber int
,Column1 int
)

insert into #Test 
VALUES
('job1',2022,1,10),
('job2',2022,1,50),
('job1',2022,2,15),
('job2',2022,2,60),
('job1',2022,3,20),
('job2',2022,3,70)

select * from #Test

drop table #Test

and groups in builder

enter image description here

Ekaterina Chistyakova
  • 39
  • 5

1 Answers1

1

Onestly I don't know if this is possible (I don't think is possbile to access the scope of the "previous group") but maybe you can use this workaroud.

0. Start dataset

I've started from this dataset, somehow similar to yours

enter image description here

And this is the tablix object

enter image description here

1. Create a calculated field on your dataset

Create a new simple filed in your dataset to have for each week number the previous week number

enter image description here

=Fields!Week.Value - 1

2. Add a custom function to sum the result of a LookupSet

Follow this guide to add this custom function to the report enabling us to sum the result of a LookupSet function (many thanks to the author!). We will use this in the next point.

Function SumLookup(ByVal items As Object()) As Decimal  
If items Is Nothing Then  
Return Nothing  
End If  
Dim suma As Decimal = New Decimal()  
Dim ct as Integer = New Integer()  
suma = 0  
ct = 0  
For Each item As Object In items  
suma += Convert.ToDecimal(item)  
ct += 1  
Next  
If (ct = 0) Then return 0 else return suma   
End Function

3. Add a costum expression for the column 2

= 
Sum(Fields!Value.Value) -
Code.SumLookup(LookupSet(Fields!PreviousWeekNumber.Value,Fields!Week.Value,Fields!Value.Value, "DataSet1"))

The LookupSet function retrive the set of values in the selected table/scope (Dataset1 in the example) wich have the week number equal to the previuos week number (in our the default scope, the column group scope). The custom function "SumLookup" enable us to sum the VariantArray (or Nothing if there is no match) returned by the LookupSet function.

4. Results

This is the result:

enter image description here

if you need a different result for the first week just add a condition to the custom expression for the column2

EDIT

If you have also a row group like this:

enter image description here

You can modify the previuos expression to this:

= 

Sum(Fields!Value.Value) -
Code.SumLookup(LookupSet(Fields!JobNum.Value & Fields!PreviousWeekNumber.Value,Fields!JobNum.Value & Fields!Week.Value,Fields!Value.Value, "DataSet1"))

Achivieng a LookupSet based on multiple conditions.

Gam
  • 318
  • 2
  • 9
  • That is an amazing work around! Thank you very much! The problem I am dealing now is that I also have row group, and as I calculated some logic from my numbers, it doing WEEK2 COLUMN1 - SUM OF job1 and job2 COLMN1 – Ekaterina Chistyakova May 26 '22 at 09:54
  • https://imgur.com/zOT5jyj – Ekaterina Chistyakova May 26 '22 at 10:01
  • Setting the scope of the lookupset function to the "dataset1" completly remove the filtering operated by both the column and row group and return ALL the values on wich the weekNumber is equal to the previousWeekNumber. This is the reason why you receive WEEK2 COLUMN1 - SUM OF job1 and job2 COLMN1. Let me think a bit for a solution :) – Gam May 26 '22 at 10:02
  • 1
    Ok, it's simple, we just need a LookupSet that takes multiple columns for the join. And this is achivable through concatenation give a try to [this](https://stackoverflow.com/questions/35699458/ssrs-lookup-based-on-multiple-conditions) excellent Q&A. I add a little EDIT fot this in the response – Gam May 26 '22 at 10:15
  • Oh my god, It actually works, and implemented in real thing doing exactly what it needed to do. THANK you very very much!!! – Ekaterina Chistyakova May 26 '22 at 10:22