reporting-servicesreportbuilder3.0group

ssrs column group subtraction from previous value

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

Solution

  • 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.