September 2, 2012 at 3:46 pm
Hey everyone, I am seeking assistance with creating a VB expression in SSRS that subtracts SUMS. I have a dataset that has accounts and corresponding money/amount values. I need help creating an expression that sums up the money/amount values from one group of the accounts in a specified range, and then subtracts it from the money/amount total of another range. Specifically:
(Sum(amt) where acct between 40000 and 49999) -
(Sum(amt) where (acct between 50000 and 59999) or (acct between 66000 and 69999)) -
(Sum(amt) where acct between 76000 and 79825) -
(Sum(amt) where acct between 89000 and 90399)
Really could use some help translating this SQL logic into a VB expression to be used for a textbox in SSRS. Any advice would be extremely helpful! Thanks!
September 3, 2012 at 11:37 am
Wanted to update this thread with my solution should anyone else come across this issue. The following is the code that was ultimately used, which also applies some conversion and aggregation functions for business rules.
= FormatCurrency(
iif(
Cint(Fields!acct.Value >= 40000 and
Fields!acct.Value <= 49999),
Sum(Sum(CDec(Fields!amt.Value))),NOTHING
)
-
iif(
Cint(Fields!acct.Value>=50000 and Fields!acct.Value<=59999)
or Cint(Fields!acct.Value>=66000 and Fields!acct.Value<= 69999),
Sum(Sum(CDec(Fields!amt.Value))),NOTHING
)
-
iif(
Cint(Fields!acct.Value >= 76000 and
Fields!acct.Value <= 79825),
Sum(Sum(CDec(Fields!amt.Value))),NOTHING
)
-
iif(
Cint(Fields!acct.Value >= 89000 and
Fields!acct.Value <= 90399),
Sum(Sum(CDec(Fields!amt.Value))),NOTHING
)
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply