none
Conditional SUM - Sum everything except the fist row RRS feed

  • Question

  • Hi,

    As the title says I want to sum a table with the exception of the first field/value. How can I do it?

    Thanks!

    Can I do this without modifying query?

    Conditional SUM - Sum everything except the fist value

    Tuesday, June 25, 2019 2:41 PM

All replies

  • Hi,

    As the title says I want to sum a table with the exception of the first field/value. How can I do it?

    Thanks!

    Conditional SUM - Sum everything except the fist value

    Monday, June 17, 2019 3:42 PM
  • =Sum(Fields!XXX.Value, scope)  - First(Fields!XXX.Value, scope)

    A Fan of SSIS, SSRS and SSAS



    • Edited by Guoxiong Monday, June 17, 2019 4:01 PM
    Monday, June 17, 2019 3:54 PM
  • Table1 (values below)

    2019-06-06

           1

           2

           3

    Can you give me an example of how to sum the 1,2,3 except the fist one?

    Monday, June 17, 2019 3:59 PM
  • Is this for SSRS or T-SQL?

    A Fan of SSIS, SSRS and SSAS

    Monday, June 17, 2019 4:18 PM
  • Hi,

    Are you trying to do this in report design or with sql query?


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 18, 2019 2:35 AM
  • Guoxiong and Lukas

    It's in SSRS

    Tuesday, June 18, 2019 1:03 PM
  • How about this?

    =Sum(IIF(IsNumeric(Fields!XXX.Value) = 0, 0 Fields!XXX.Value))


    A Fan of SSIS, SSRS and SSAS

    Tuesday, June 18, 2019 2:06 PM
  • No, gives me error...
    Tuesday, June 18, 2019 2:32 PM
  • What is the error?


    A Fan of SSIS, SSRS and SSAS

    Tuesday, June 18, 2019 2:37 PM
  • Screenshots:

    imgur.com/ECkuhAv

    imgur.com/8OMlApn


    • Edited by Vadim SFF Tuesday, June 18, 2019 2:59 PM
    Tuesday, June 18, 2019 2:59 PM
  • I missed a comma between 0 and Fields!XXX.Value. You need to change XXX to your feild name.

    =Sum(IIF(IsNumeric(Fields!XXX.Value) = 0, 0, Fields!XXX.Value))


    A Fan of SSIS, SSRS and SSAS

    Tuesday, June 18, 2019 3:31 PM
  • I know the missing comma, I added it before executing, that's when it gives me error.
    Tuesday, June 18, 2019 3:36 PM
  • Cannot see the error images. If you have multiple datasets, try this:

    =Sum(
     IIF(
      IsNumeric(Fields!XXX.Value) = 0,
      0,
      Fields!XXX.Value
     ),
     DATASET_NAME
    )


    A Fan of SSIS, SSRS and SSAS

    Tuesday, June 18, 2019 3:47 PM
  • Only one dataset,

    =Sum(IIF(IsNumeric(Fields!ID1.Value) = 0, 0, Fields!ID1.Value),"DataSet")

    The report preview shows "#Error"

    imgur . com / ECkuhAv

    imgur . com / 8OMlApn

    • Edited by Vadim SFF Tuesday, June 18, 2019 4:02 PM
    Tuesday, June 18, 2019 3:53 PM
  • Try this:

    =Sum(IIF(IsNumeric(Fields!ID1.Value), Fields!ID1.Value, 0), "DataSet")


    A Fan of SSIS, SSRS and SSAS

    Tuesday, June 18, 2019 6:03 PM
  • I don't know Guoxiong, this one doesn't work either, nothing seems to work...
    Wednesday, June 19, 2019 1:51 PM
  • If your fields is pulled from a database, I suspect that other values are also varchar/string type as your first row.

    If so:

    Maybe you can try to modify your sql query as 

    SELECT *,ROW_NUMBER() as NumId over (ORDER BY yourOrderColumn) FROM yourTable

    Then 

    The summation you want could be expression

    =IIF(Fields!NumID.value>1,sum(cint(Fields!Youfield.value)),nothing)

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, June 20, 2019 5:52 AM
  • Can I do this without modifying query, it's a stored procedure also... Late response cause was doing other work.
    Tuesday, June 25, 2019 2:40 PM
  • Ok, I assume the field is string format, right?

    Try this:

    =SUM(CINT(IIF(LEN(Fields!YourField.value)>9,0,Fields!YourFiled,value)))

    Regards,

    Lukas

     

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 26, 2019 2:21 AM