Calculate the next value using VB code in SSRS report

Mayuran Thiru 31 Reputation points
2022-09-07T12:49:26.657+00:00

Hello community,
I require some help to calculate the next row value using VB if possible for the transaction posting date. This transaction posting from the next row will be used to populated the "To" column (minus -1 Date). I don't have access to the underlying sources to do a query using the LAG/Lead functions.

Problem:
I have a ssrs report where I would like to determine autonomously the "To" date. The "To" date is determined based on the next transaction posting date.
For example, I have a transaction posting date of 02-10-2021 and the subsequent transaction for this same entity on 05-03-2021. Therefore "To" date for this transaction should be 02-28-2021

Subsequently, the transaction posting date from 05-03-2021, the "To" date will depend on the next transaction posting date. If I have another transaction within the same month, for example occurring on 05-10-2021. Then the "To" date for the subsequent transaction will be 05-09-2021.

Here is original post I created for further information: https://learn.microsoft.com/en-us/answers/questions/994917/how-to-dynamic-calculate-the-to-date-based-on-the.html?childToView=997527#answer-997527

I am referencing a website where they added the Row field in the Query: https://stackoverflow.com/questions/39385975/get-previous-current-and-next-record-in-ssrs-report-in-a-page
I am not able to do this either.

238618-image.png

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,727 questions
0 comments No comments
{count} vote

1 answer

Sort by: Most helpful
  1. Mayuran Thiru 31 Reputation points
    2022-09-12T13:49:57.383+00:00

    1st table - will contain the set function in a textbox expression passing in the row number and value. This table can be hidden.

    =setValue(RowNumber(Nothing),myfield.value)

    2nd table - will contain the get function passing in the row number.

    =getValue(RowNumber(Nothing)

    Code Below

    public dictLookup as new System.Collections.Generic.Dictionary(Of String, String)

    Function setValue(value as object, value2 as object) as object

    dictLookup.Add(value, value2)

    return value

    End Function

    Function getValue(value as object) as object

    If dictLookup.ContainsKey(value)

    return dictLookup(value)

    else

    return value

    end if

    End Function

    1 person found this answer helpful.