SSRS - Dynamic expression based on group values

Question

Question: How do i create a dynamic expression in SSRS that references a cell value in a column before?

Table: The table is built using column groupings by date.

Software Used: Using Visual Studio 2015 Report Server Project.

Example: See screenshots.

This is the design view in SSRS:

[![enter image description here][1]][1]

This is the result:

This an example of the dataset. Note the starting balance is on the first day and then I would like it to carry over to next day minis the sum of the remaining ids. [3

there may be a way to do this in SSRS expressions but my instinct would be to do this in your dataset so you would end up with two new 'areas' 'beginning' and 'total'. If you need help doing this post a sample of your data or your current dataset query. — Alan Schofield
I don't think this is doable in SSRS at all. The best shot is to figure this out in SQL side. — ViKiNG
Link to datafile..dropbox.com/s/5uyas1xhb8s3nr4/Dataset.xlsx?dl=0 — mkalan44

Hamza Rabah Hamza Rabah · Accepted Answer · 2017-08-03T23:36:17

maybe this can help you

DECLARE @TestData TABLE
    (
        DateColumn DATE ,
        ID INT ,
        Type NVARCHAR(100) ,
        Ammount DECIMAL(18, 2)
    );

INSERT INTO @TestData (   DateColumn ,
                          ID ,
                          Type ,
                          Ammount
                      )
VALUES ( '8/2/2017', 1, N'1_BeginningCash', 1797791 ) ,
    ( '8/4/2017', 2, N'2_AR', 41526.23 ) ,
    ( '8/4/2017', 5, N'5_PaymentOther', -19863.21 ) ,
    ( '8/5/2017', 3, N'3_Vendor_Rebates', 1200 ) ,
    ( '8/7/2017', 2, N'2_AR', 12163.94 ) ,
    ( '8/9/2017', 2, N'2_AR', 179236.8 ) ,
    ( '8/9/2017', 4, N'4_Payment_Supplier', -1066266.32 );

WITH FirstLevel
AS ( SELECT DateColumn ,
            ID ,
            Type ,
            Ammount ,
            SUM(Ammount) OVER ( PARTITION BY DateColumn ) AS DateTotal ,
            SUM(Ammount) OVER ( ORDER BY DateColumn ,
                                         ID
                              ) AS RunningTotal
     FROM   @TestData
   ) ,
     SecondLevel
AS ( SELECT FirstLevel.DateColumn ,
            FirstLevel.ID ,
            FirstLevel.Type ,
            FirstLevel.Ammount ,
            FirstLevel.DateTotal ,
            FirstLevel.RunningTotal ,
            LAG(FirstLevel.RunningTotal, 1, 0) OVER ( ORDER BY FirstLevel.DateColumn ,
                                                               FirstLevel.ID
                                                    ) LastRunningTotal
     FROM   FirstLevel
   ) ,
     ThirdLevel
AS ( SELECT SecondLevel.DateColumn ,
            SecondLevel.ID ,
            SecondLevel.Type ,
            SecondLevel.Ammount ,
            SecondLevel.DateTotal
            + FIRST_VALUE(SecondLevel.LastRunningTotal) OVER ( PARTITION BY SecondLevel.DateColumn
                                                               ORDER BY SecondLevel.DateColumn ,
                                                                        SecondLevel.ID
                                                             ) AS DateTotal ,
            FIRST_VALUE(SecondLevel.LastRunningTotal) OVER ( PARTITION BY SecondLevel.DateColumn
                                                             ORDER BY SecondLevel.DateColumn ,
                                                                      SecondLevel.ID
                                                           ) AS Beginning
     FROM   SecondLevel
   )
SELECT *
FROM   ThirdLevel;

SSRS - Dynamic expression based on group values

1 Answers