locked
SSRS SUM produces wierd results when summing with three nested ROW groups, why? RRS feed

  • Question

  • I have three nested ROW groups:-

    The first one is a depended on a wether a field is true or false in the dataset, for each case. This is the where the error is worst. The second is nested on the first and is based on a group variable in the cases (1 to many), the third is the ref number of the cases.

    The sums don't work for a cloumn that is produced by a join, depending on the ID of the second group. It seems to pull the right value, but multiplies by the number of cases. I can divide by the case numbers here, inside the last nested group(ref#) to get the right value. Tried using "Count" , Blank, Add total after..

    If I try to sum the column with "=Sum(ReportItems!Textbox231.Value)"  Produces:-

    The Value expression for the textrun 'Textbox232.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item.  Aggregate functions can be used only on report items contained in page headers and footers.

    The sums work fine for the non joined values..in all three nested row groups. But for the joined values they are out by an order of magnitude. Why is this?

    • Edited by AltFire Wednesday, January 10, 2018 1:54 PM
    Wednesday, January 10, 2018 12:55 PM

All replies

  • Hi AltFire,

    As I know, the reportitem can't be used in an aggregate function, so you will get above error information. And I am not very clear about your requirement. If possible, could you please inform me more detailed information (such as your dataset structure, your current design structure, and your expecting output) with screenshots or drawings, then I will help you more correctly.

    Please do mask sensitive data before uploading. 

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    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, January 11, 2018 2:07 AM
  • Going back to the actual problem I have, I have to estimate the difference between estimated and actual values over a month period for each Company, given the estimated values.

    I have made a table to hold four estimated Key Perfomance Indicator's that can be entered in to a table, for each month of the year, for each Company by ID. This table has Company ID , which it has in common with the table that contains a list of cases, from which the actual KPI's can be extracted, by summing the values for each Company. Tried various things. but nothing working, tried join on dates, join on Company, creating a view with a UNION. Any advise would be much appreciated.

    Thursday, January 11, 2018 4:22 PM
  • Going back to the actual problem I have, I have to estimate the difference between estimated and actual values over a month period for each Company, given the estimated values.

    I have made a table to hold four estimated Key Perfomance Indicator's that can be entered in to a table, for each month of the year, for each Company by ID. This table has Company ID , which it has in common with the table that contains a list of cases, from which the actual KPI's can be extracted, by summing the values for each Company. Tried various things. but nothing working, tried join on dates, join on Company, creating a view with a UNION. Any advise would be much appreciated.

    why?

    Looks like a straightforward scenario to me

    Can you provide a sample of your estimated value vs actual data for 2-3 months of a year

    I shall show you the query to calculate the variance between them


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 11, 2018 4:34 PM
  • Sorry I don't know how to do that, as the actual figures are contained inside the database, and the estimated figures are just random values at this stage. I can't post any pictures either.

    I read that UNION removes duplicates but it didn't work this, but I may be doing it wrong:-

    Group by won't work, as I need to calculate actual figures from single variables.


    Is there a way to UNION a group by and another query. So group by Company on the joined table so only one value per company?

    thanks in advance.

    Thursday, January 11, 2018 5:24 PM
  • no need of actual data

    Just post some sample of how you have them in your tables 

    For random value you need to put it in a table before you do the comparison


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 11, 2018 5:28 PM
  • ID Company DateInstructed DateStarted DateFinished DateSignificant Rate Additions Name
    1 GSP 1/1/2017 1/5//2017 1/6/2017 2/7/2017 5 3

    Billl

    ID Company DateEffectiveFrom DateEffectiveTo EstimatedNoInstructions EstimatedNoStarts EstimatedNoSignificant EstimatedValue
    1 GSP 1/1/2017 2/2//2017 5 2 1 45

    I need to work out the actual no instructions from the date instructed, the actual, no starts from the date started, the actual no of significance from datesignificant, the actual value from the rate times the duration of the period between the two dates. The join on the other table gives the estimated values for the period it's effective from.


    • Edited by AltFire Thursday, January 11, 2018 6:52 PM
    Thursday, January 11, 2018 6:44 PM
  • ID Company DateInstructed DateStarted DateFinished DateSignificant Rate Additions Name
    1 GSP 1/1/2017 1/5//2017 1/6/2017 2/7/2017 5 3

    Billl

    ID Company DateEffectiveFrom DateEffectiveTo EstimatedNoInstructions EstimatedNoStarts EstimatedNoSignificant EstimatedValue
    1 GSP 1/1/2017 2/2//2017 5 2 1 45

    I need to work out the actual no instructions from the date instructed, the actual, no starts from the date started, the actual no of significance from datesignificant, the actual value from the rate times the duration of the period between the two dates. The join on the other table gives the estimated values for the period it's effective from.


    Looks like this is what you're after based on your limited data

    set dateformat mdy go ---sample tables for illustration based on data you provided declare @actuals table ( ID int, Company varchar(10), DateInstructed datetime, DateStarted datetime, DateFinished datetime, DateSignificant datetime, Rate int, Additions int, Name varchar(10) ) declare @estimates table ( ID int, Company varchar(10), DateEffectiveFrom datetime, DateEffectiveTo datetime, EstimatedNoInstructions int, EstimatedNoStarts int, EstimatedNoSignificant int, EstimatedValue int ) insert @actuals values(1,'GSP','1/1/2017','1/5/2017','1/6/2017','2/7/2017',5, 3 ,'Billl'), (1,'GSP','1/11/2017','1/15/2017','1/20/2017','2/1/2017',51, 3 ,'Harry'), (1,'GSP','12/31/2016','1/7/2017','2/12/2017','2/18/2017',18, 3 ,'John') insert @estimates values (1,'GSP','1/1/2017','2/2/2017',5, 2, 1, 45)

    -- this is your actual solution. you need to replace the sample tables with your actual ones and also column names accordingly select e.ID, e.Company,e.DateEffectiveFrom,e.DateEffectiveTo,e.EstimatedNoInstructions,e.EstimatedNoSignificant,e.EstimatedNoStarts,e.EstimatedValue, sum(ActualNoInstn) AS ActualNoInstn, sum(ActualNoStart) AS ActualNoStart, sum(ActualNoSigni) AS ActualNoSigni, sum(ActualValue) AS Rate from @estimates e cross apply ( select case when DateInstructed between e.DateEffectiveFrom and e.DateEffectiveTo then 1 else 0 end as ActualNoInstn, case when DateStarted between e.DateEffectiveFrom and e.DateEffectiveTo then 1 else 0 end as ActualNoStart, case when DateSignificant between e.DateEffectiveFrom and e.DateEffectiveTo then 1 else 0 end as ActualNoSigni, Rate as ActualValue from @actuals where ID = e.ID AND Company = e.Company )a group by e.ID, e.Company,e.DateEffectiveFrom,e.DateEffectiveTo,e.EstimatedNoInstructions,e.EstimatedNoSignificant,e.EstimatedNoStarts,e.EstimatedValue ' /* output ----------------------------------------------------- ID Company DateEffectiveFrom DateEffectiveTo EstimatedNoInstructions EstimatedNoSignificant EstimatedNoStarts EstimatedValue ActualNoInstn ActualNoStart ActualNoSigni Rate ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1 GSP 2017-01-01 00:00:00.000 2017-02-02 00:00:00.000 5 1 2 45 2 3 1 74 */


    I'm assuming calculation for Rate etc as you've given only very limited data (1 row!!!) and the business rule is therefore not clear for me

    This should still give you the base idea to work on

    If you need more help,post back

    Itts always desirable to give atleast 5 - 10 lines of sample data covering your various scenarios as create/ insert statements just like what I've shown above. that makes it easier for anyone to run it and get your scenario which will help you to get quick solution. 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Thursday, January 11, 2018 7:26 PM
    Thursday, January 11, 2018 7:24 PM
  • Thanks, this helped no end, but I made an error in the naming of the tables ID cloumns,I shouldn't have named them both the same, The "actuals" table ID, should be PKID, referencing each individual case, different to the "estimates" table column ID. In Report builder 3, I have to drill in to the report from, high to low, 3 level nested row groups, true or false for a Company ID>Company Name>PKID. The output I have has no COUNT of PKID returned. I mean i don't have each instance of a case in the dataset.

    • Edited by AltFire Friday, January 12, 2018 3:12 PM
    Friday, January 12, 2018 1:20 PM
  • Hi AltFire,

    I am sorry for late reply, I am still not clear about your requirement.

    I wonder the below one is your expecting output? I want to know  how you get the 5,2,1,45 ? If possible, could you please inform me in details? I don't need your actual sample, you could use other field and values to replace your actual values. If possible, could you please inform me your current design and your expecting output with screenshot or drawings? Then I will help you more correctly.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

    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, January 16, 2018 6:59 AM