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

Isaland

The task below.

Isaland

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

Troubles_In_Paradise

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)) + ...

clausl

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

Isaland

Troubles In Paradise wrote:

Isaland wrote:

The other way is to use "SetData" and "GetData" functions.

Layout:


Result (Error occurs when SetData run):


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?

Isaland

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.

Troubles_In_Paradise

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?

Isaland

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 ](*,)

Troubles_In_Paradise

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...

Isaland

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.

Troubles_In_Paradise

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?

Isaland

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 ](*,)

Troubles_In_Paradise

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.

Isaland

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

Troubles_In_Paradise

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..

Isaland

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.

Troubles_In_Paradise

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))

Isaland

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

Troubles_In_Paradise

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?

Isaland

I will try to write in this post all information. I change code a little, to make my task clearly

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

Troubles_In_Paradise

Isaland wrote:

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

Does it work now?

Isaland

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))

Troubles_In_Paradise

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?

Isaland

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.

Isaland

Okey, it`s bad idea to use real task for question
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

Isaland

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>

Troubles_In_Paradise

Glad to see you found a solution .