locked
Carry over page totals RRS feed

  • Question

  • I have a field in my subtotal column below my detail line that calls a function to calculate overtime based on some rules

    =Code.getOT(sum(Fields!TotalWeekdayHours.Value), sum(Fields!SaturdayHours.Value), sum(Fields!SundayHours.Value))

     

     

    and returns the overtime for the week. 

     

    I can't wrap the entire code call in sum(), I get the error that I can't have nested aggregates in an aggregate function.

    I can't use the RunningValue function on a reportItem in the body, because I get the error that aggregate functions on reportItems can only be used in the header and footer.

    And I get an error when trying to use Runningvalue in the footer.

     

    OT can't be calculated until all hours for the week are known.

     

    I can sum the reportitems in the footer, and use the global page number & page total to only display the last page

    but I want to carry over the page totals and get a cumulative report total.

     

    What's a good way to go about that.

    Tuesday, July 29, 2008 2:36 PM

Answers

  • The more I find out about OVER, PARTION BY and other features of SQL, the more I see RS as merely a rendering engine with security and subscription facilities.

    I think the longer term goal of MS is for people to have a report model/dsv, against which they use Report Builder from Office/SharePoint



    At least it has proper pivoting, unlike sql where you have to know how many columns there will be!
    Thursday, July 31, 2008 6:26 AM

All replies

  • Use ur code value and place it in some textbox and hide that visibity and u can get those value in your report header or footer elsewhere...
    Wednesday, July 30, 2008 11:26 AM
  •  jonesbeach wrote:

     

    I can sum the reportitems in the footer, and use the global page number & page total to only display the last page

    but I want to carry over the page totals and get a cumulative report total.

     

    Thank you for the reply Bala, but II got that ,   what I am looking for is possibly a way to keep a global report variable to mimic running value since I can't use RunningValue on a report item (other text box) in the body, and can't use RunningValue in the footer.

     

    The big issue is that I have a code call in my group footer, I'm passing in  the sums of RegularHours worked (40 or less), total week over time( any hours over 8), and weekend time.   I have to pass in the sums of the detail records to make a calculation.

    What I get back in the group footer is the calculated overtime.  

     

    I am having a problem getting a grand total for a calculated group footer field at the end of the report.

     

    I can drop a text box in the page footer and use sum(ReportItems!textbox22.value)  and get the sum for the page, but that doesn't do me any good.  I need the Total for calculation from the groups. 

     

     

    Wednesday, July 30, 2008 2:19 PM
  • why can't you do it in the sql?


    Wednesday, July 30, 2008 2:43 PM
  • It actually is 100 times easier to get the result in SQL, I did it in about 5 minutes and added the report totals to the dataset I was passing in.  It seems like that defeats almost all the usefulness of reporting services if it's only used for rendering.    I did manage to find  a workaround.

     

    I dim'd a variable outside the functions in the code window called totalOT. 

    Added a function

     

    public function addValue()

    value += value

    end function

     

    and inside my function that calculates the OT I called addValue

     

    (the actual logic has more rules, but for brevity. . . )

    if regHours > 40

    OT = regHours - 40

    addValue(OT)

    end if

     

    I also added a function

    public function getValue() as double

    return value

    end function

     

     

    I added a second group footer and called code.getValue. and made the field hidden

    Then in the page footer I added a textbox and made it hidden on all pages except the last page and

     used the ReportItems!textbox (<-- the textbox that holds the getValue call)   to display the cumulative value

    from the group footer.

     

     

    I think that the big issue here for me was scope.  If anyone has any other suggestions I'd be glad to hear how others would've solved this.

     

     

     

    Wednesday, July 30, 2008 3:08 PM
  • sorry I meant in the above I dim'd a value named value not totalOT, totalOT was what the value represented.

     

    Wednesday, July 30, 2008 3:11 PM
  • The more I find out about OVER, PARTION BY and other features of SQL, the more I see RS as merely a rendering engine with security and subscription facilities.

    I think the longer term goal of MS is for people to have a report model/dsv, against which they use Report Builder from Office/SharePoint



    At least it has proper pivoting, unlike sql where you have to know how many columns there will be!
    Thursday, July 31, 2008 6:26 AM