none
conditional Sums in grouped Tablix wrong ... pls help going crazy

    Question

  • i am using report builder 3.0 with sql server 2008 r2

    about to go crazy spent nearly 2 days trying to figure out why the sums are wrong and still no clue :(

    the report has 2 rowgroups and 1 columngroup and i query the report with date1 and date2

    here is a screen in the editor

    ok i am not allowed to post screens ...

    nothing special imho and i do conditional Sums for the Column "art" that look like this

    =sum(iif(Fields!art.Value = "Pus", Fields!Netto.Value, nothing))

    the strange thing is sometimes the sums are right and sometimes they aren't and i don't know why

    here is a screen how the report looks like and the data displayed

    ok i am not allowed to post screens ...

    the total is always right but the conditional ones give me a headache...

    pls someone help me out of my misery i tried so many things renaming cdbl( and what not different grouping and all i have no idea what i am doing wrong

    thx in advance

    Tuesday, October 29, 2013 12:25 AM

Answers

  • finally i was able to correct my report i needed 3 rowgroups not 2 and i had to make a new report ... i couldn't get the old one to display all the data even tho i added the 3rd group. I guess i screwed around too much with it

    now it displays everything ...

    i wasted 2 days of my life by looking at the wrong spot omg...

    anyways this problem is now solved thx for any help

    ps: how can i verify my account?

    Tuesday, October 29, 2013 4:06 PM

All replies

  • I will recomend you to check whether the IIF condition satisfy the condition or not. I am using CInt function, you may use as appropriate.

     =sum(iif(RTrim(LTrim(Fields!art.Value)) = "Pus", CInt(Fields!Netto.Value), nothing))


    Regards, RSingh

    Tuesday, October 29, 2013 9:09 AM
  • thx for the suggestion but unfortunately its the same output

    but i'm happy for any ideas

    btw how can i verify my account so i can post screenshots?

    Tuesday, October 29, 2013 1:49 PM
  • Hi Nocturnal,
     
    After testing the expressions in my local environment, it always work well. So this issue cannot be reproduced in my own environment, and it is hard to say the exact reason base on the limited information.
     
    In order to trouble shoot this issue more efficiently, could you please post both the report and the dataset with sample data to us by the following E-mail address? It is benefit for us to do further analysis.
    E-mail: sqltnsp@microsoft.com
     
    Thanks,
    Katherine Xiong

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Tuesday, October 29, 2013 2:38 PM
  • OK after some more investigation of the querys in sql management studio it turns out that the sums are correct but the tablix isn't showing everything

    i guess my grouping is incorrect...

    i will make a new report and test a bit

    thf for the offer Katherine i will do so if i am unable to display all data

    Tuesday, October 29, 2013 2:46 PM
  • finally i was able to correct my report i needed 3 rowgroups not 2 and i had to make a new report ... i couldn't get the old one to display all the data even tho i added the 3rd group. I guess i screwed around too much with it

    now it displays everything ...

    i wasted 2 days of my life by looking at the wrong spot omg...

    anyways this problem is now solved thx for any help

    ps: how can i verify my account?

    Tuesday, October 29, 2013 4:06 PM
  • Hi Mr Noctumal,

    Great to hear that the issue was fixed.

    To verify the account, you should contribute to the forums. It means, you should reply/answer some questions in the TechNet/MSDN forums. Once you get some points(If I understand correctly, it is about 20 points), your account will be verified automatically.
    Please understand this mechanism is to avoid SPAM, and to vaoid annoying users in the forums.

    Thanks,
    Jinchun Chen


    Jinchun Chen(JC)
    TechNet Community Support

    Wednesday, October 30, 2013 3:31 AM