How to calculate totals on page (not till page) in RDLC?

Isaland
Isaland Member Posts: 52
edited 2012-07-26 in NAV Three Tier
The task below.
MB7-840

Answers

  • Isaland
    Isaland Member Posts: 52
    Or help with another task please.

    I have three TextBox`es in Page Header:

    TextBox1=Last(ReportItems!PagePhysQty.Value)
    TextBox2=First(ReportItems!PagePhysQty.Value)
    TextBox3=First(ReportItems!ItemPhysQty.Value)

    I need to calculate them together in one value: TextBox1 + TextBox3 - TextBox2.
    The classic client error occurs when i put more than one ReportItem in TextBox :|
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    Isaland wrote:
    The classic client error occurs when i put more than one ReportItem in TextBox :|
    Couldn't you calculate the total in the body in order to pass only one value to the header?
    The other way is to use "SetData" and "GetData" functions.
    RDLC allows you to call more than 1 time "GetData" function in one Textbox (remember to convert them to string when passing to SetData and convert them to decimal when summing).

    example: 
    textbox passed to SetData:
...
cstr(Fields!Value1.value) + chr(177) +
cstr(Fields!Value2.value) + chr(177) +
...

    =cdbl(Code.GetData(1,1)) + cdbl(Code.GetData(2,1)) + ...
    ~Rik~
    It works as expected... More or Less...
  • clausl
    clausl Member Posts: 455
    How to do Transfooter and Transheader functionality in RDLC reports is explained here:
    http://blogs.msdn.com/b/nav/archive/2011/06/06/transfooter-and-transheader-functionality-in-rdlc-ssrs-reports-revisited.aspx

    /Claus Lundstrøm
    Claus Lundstrøm | MVP | Senior Product Manager | Continia.com
    I'm blogging here:http://mibuso.com/blogs/clausl and used to blog here: http://blogs.msdn.com/nav
    I'm also offering RDLC Report Training, ping me if you are interested. Thanks to the 700 NAV developers that have now already been at my training. You know you can always call if you have any RDLC report issues :-)
  • Isaland
    Isaland Member Posts: 52
    Troubles In Paradise wrote:
    Isaland wrote:
    The other way is to use "SetData" and "GetData" functions.

    Layout:
    2_500.jpgi.gif

    Result (Error occurs when SetData run):
    1_500.jpgi.gif

    Report Code: 
    Shared HeaderData As Object

Public Function GetData(Num As Integer) As Object
   Return Cstr(Choose(Num, Split(Cstr(HeaderData),Chr(177))))
End Function
 
Public Function SetData(NewData As Object)
    If NewData <> “” Then
        HeaderData = NewData
    End If
End Function

    Where i did mistake? :|
    MB7-840
  • Isaland
    Isaland Member Posts: 52
    clausl wrote:
    How to do Transfooter and Transheader functionality in RDLC reports is explained here:
    http://blogs.msdn.com/b/nav/archive/2011/06/06/transfooter-and-transheader-functionality-in-rdlc-ssrs-reports-revisited.aspx
    /Claus Lundstrøm

    It not exactly what i need. My task is to count totals "on current page", not "till current page". :|
    Thats why i need to calculate those three Values in page header.
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    code seems to be ok.
    I cannot see screenshot could you attach them?
    Did you convert decimals into strings in the textbox you pass to SetData function?
    ~Rik~
    It works as expected... More or Less...
  • Isaland
    Isaland Member Posts: 52
    Troubles In Paradise wrote:
    Did you convert decimals into strings in the textbox you pass to SetData function?
    I used your advice, it does not work yet. But now i am closer to my goal than the last time. :)

    The field that has error contains the expression below: 
    =cdbl(Code.GetData(1)) - cdbl(Code.GetData(2)) + cdbl(Code.GetData(3))

    SetData expressions you can find at the top of mvs.png image.

    And this just one column. I need to calculate whole eight column totals at each page ](*,)
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    I've never seen passing parameters to SetData like are you trying to do.
    I'm wondering if it's possible...
    I explain better:
    I would do as follow: 
    Textbox PagePhysQty:
=cstr(Last(YourField.Value))

Textbox "SetData"
=Code.SetData(ReportItems!PagePhysQty.Value)
    Which is the difference? I prefer to format the value in the PagePhysQty textbox, and then simply pass to setdata the value ready to be managed.
    Try something like this...
    ~Rik~
    It works as expected... More or Less...
  • Isaland
    Isaland Member Posts: 52
    I will try to explain my task in another way.
    On screenshot you can see two fields which i marked with colors.
    They both are in Body.

    My task is to calculate: (Last Green) - (First Green) + (Last Blue) on every page of report, and show this value in header.

    PagePhysQty is blue field
    ItemPhysQty is green field

    I know only one way to take values from that fields: 
    Last(ReportItems!PagePhysQty.Value)
First(ReportItems!PagePhysQty.Value)
First(ReportItems!ItemPhysQty.Value)
    and put them in three different textboxes. But i don`t know what to do next with SetData and GetData functions. :|
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    I would change approach..
    Have you already find a place in the body where you get needed values (maybe in the Table(Group)Footer)? 
    Textbox1: =Last(Fields!GreenField.Value)
Textbox2: =First(Fields!GreenField.Value)
Textbox3: =Last(Fields!BlueField.Value)
    If the BodyLine used to calculate these values is printed on everypage you could call them in the Header with: 
    =Cdbl(ReportItems!Textbox1.Value) - Cdbl(ReportItems!Textbox2.Value) + Cdbl(ReporItems!Textbox3.Value)
    I'm not sure it could work..
    try something like this..

    Edit:
    I'm wrong you can't call many "ReportItems!" on the same textbox.
    So calcualte total on the body and then pass one ReportItems to the header.
    Is it clear?
    ~Rik~
    It works as expected... More or Less...
  • Isaland
    Isaland Member Posts: 52
    I cannot use Last\First (Fields) in body - error occurs. Look at screenshot.

    I have been tried all variants in "Body" to calculate these cursed three values ](*,)
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    Isaland wrote:
    I cannot use Last\First (Fields) in body - error occurs. Look at screenshot.

    I have been tried all variants in "Body" to calculate these cursed three values ](*,)
    :shock: it's impossible...
    I attach a screenshot of the standard report "Budget" (ID: 8 ).
    You can see "First" function in the body ("Last" works as well).
    With this report I'm questioning all my "Report Knowledge"...

    Ps: sorry my VS is in Italian.
    ~Rik~
    It works as expected... More or Less...
  • Isaland
    Isaland Member Posts: 52
    Troubles In Paradise wrote:
    I have been tried all variants in "Body" to calculate these cursed three values ](*,)
    :shock: it's impossible...
    I attach a screenshot of the standard report "Budget" (ID: 8 ).
    You can see "First" function in the body ("Last" works as well).
    With this report I'm questioning all my "Report Knowledge"...
    It seems i did mistake somewhere in my report. I have tried again, and First\Last function works. =D>

    Now i have 3 columns with values, that i need to calculate.
    They contain next expressions: 
    [1] =Last(Code.PagePhysQty(Fields!Item_Document_Line__Qty___Phys__Inventory__.Value))
[2] =First(Code.PagePhysQty(Fields!Item_Document_Line__Qty___Phys__Inventory__.Value))
[3] =First(Fields!Item_Document_Line__Qty___Phys__Inventory__.Value)
    Code function calculate total on each line.

    What is the next step? I feel the solution is close :)
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    Now you only have to pass this values to the header.
    Create a textbox on the same line of those "totals" and then use the SetData function: 
    =Code.SetData(Cstr(Tot1.Value) + chr(177) + Cstr(Tot2.value) + chr(177) + Cstr(Tot3.Value))
    after that in the header: 
    =Cdbl(Code.GetData(1) - Cdbl(Code.GetData(2) + Cdbl(Code.GetData(3)
    Something like this should work..
    ~Rik~
    It works as expected... More or Less...
  • Isaland
    Isaland Member Posts: 52
    I named my total fields like "LastPPQ", "FirstPPQ" and "FirstIPQ" accordingly.
    After that I insert one more column next to last field and put there: 
    =Code.SetData(Fields!LastPPQ.Value + chr(177) + Fields!FirstPPQ.Value + chr(177) + Fields!FirstIPQ.Value)
    Then i put textbox to header and insert: 
    =Cdbl(Code.GetData(1) - Cdbl(Code.GetData(2) + Cdbl(Code.GetData(3)
    But error occurs when i try to save it. :|
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    What about passing to SetData... 
    =Code.SetData(
  cstr(Last(Code.PagePhysQty(Fields!Item_Document_Line__Qty___Phys__Inventory__.Value))) + chr(177) +
  cstr(First(Code.PagePhysQty(Fields!Item_Document_Line__Qty___Phys__Inventory__.Value))) + chr(177) +
  cstr(First(Fields!Item_Document_Line__Qty___Phys__Inventory__.Value))
    ~Rik~
    It works as expected... More or Less...
  • Isaland
    Isaland Member Posts: 52
    It is working not correct :(

    It find First\Last value in whole report, not in current page. Also it use Code functions two times, and in the end i have double total.

    I think Firsl\Last function returns right values only in header or footer
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    Isaland wrote:
    It is working not correct :(

    It find First\Last value in whole report, not in current page. Also it use Code functions two times, and in the end i have double total.

    I think Firsl\Last function returns right values only in header or footer

    Could you provide another screenshot of the layout?
    ~Rik~
    It works as expected... More or Less...
  • Isaland
    Isaland Member Posts: 52
    I will try to write in this post all information. I change code a little, to make my task clearly :wink:

    First is PagePhysQtyFunc. This function will count Sum till current record: 
    Dim PhysQtyOnPage As Integer

Public Function PagePhysQtyFunc(ByVal Value As Integer)
     PhysQtyOnPage = PhysQtyOnPage + Value
     Return PhysQtyOnPage
End Function

    First Red Field is field where i want to find last result from PagePhysQtyFunc on page (not from all report): 
    =Last(Code.PagePhysQtyFunc(Fields!Item_Document_Line__Qty___Phys__Inventory__.Value))

    Second Red Field is field where i want to find first result from PagePhysQtyFunc on page (not from all report): 
    =First(Code.PagePhysQtyFunc(Fields!Item_Document_Line__Qty___Phys__Inventory__.Value))

    Third Red Field is field where i want to find first value of 4 column on page (not from all report): 
    =First(Fields!Item_Document_Line__Qty___Phys__Inventory__.Value)

    Fourth Red Field is field where i use SetData to transfer values in header. It works correct.

    Screenshoots:
    http://isaland.net/Screen-1.png
    http://isaland.net/Screen-2.png
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    Isaland wrote:
    Fourth Red Field is field where i use SetData to transfer values in header. It works correct.
    Does it work now?
    ~Rik~
    It works as expected... More or Less...
  • Isaland
    Isaland Member Posts: 52
    Troubles In Paradise wrote:
    Does it work now?
    Yes. As you see at header - 120 is resul of calculating (120 - 1 + 1) 
    =cdbl(Code.GetData(1)) - cdbl(Code.GetData(2)) + cdbl(Code.GetData(3))
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    Isaland wrote:
    Troubles In Paradise wrote:
    Does it work now?
    Yes. As you see at header - 120 is resul of calculating (120 - 1 + 1) 
    =cdbl(Code.GetData(1)) - cdbl(Code.GetData(2)) + cdbl(Code.GetData(3))

    Does it miss something or your issue is solved?
    ~Rik~
    It works as expected... More or Less...
  • Isaland
    Isaland Member Posts: 52
    Troubles In Paradise wrote:
    Does it miss something or your issue is solved?
    It is not solved, because it is not correct values that i need.
    Yes, SetData works, but the values that pass in it are totals on report, not on page.
    :(
    MB7-840
  • Isaland
    Isaland Member Posts: 52
    Okey, it`s bad idea to use real task for question :mrgreen:
    I will try to show you more simple example.

    I use 21 Table for report and take field "Amount" for calculating.

    Field "Total till record" is value which returns function "TotalFunc":
    Dim Total As Decimal

Public Function TotalFunc(ByVal Value As Decimal)
    Total = Total + Value
    Return Total
End Function

    =Code.TotalFunc(Fields!Cust__Ledger_Entry_Amount.Value)

    As you see on screenshot - http://isaland.net/scrsht1.png - the green column is Sum of all previous "Amount"s include current-record amount.

    It simple to calculate total on page - (Last!Green - First!Green + First!Amount)

    But every time RDLC occurs error when I try to calculate 3 fields :(
    MB7-840
  • Isaland
    Isaland Member Posts: 52
    I finaly found what i need: 
    =SUM(ReportItems!Amount.Value)
    And put it at header or footer in text box.

    Thanks to all for help, especially for Troubles In Paradise =D>
    MB7-840
  • Troubles_In_Paradise
    Troubles_In_Paradise Member Posts: 588
    Glad to see you found a solution :) .
    ~Rik~
    It works as expected... More or Less...

Categories