Report with Formulas

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   I added Formula support to ReportRecordItem. If presented - Item Caption will show result of calculations based on given Formula. 

Examples - 

SUM(R2C3:R8C4) - given range

SUM(R*C3:R*C4) - means all rows (parent rows only in case of hierarhy)

SUM(R2C*:R8C*) - means all columns

SUM(R*C*:R*C*) - means all
given item always excluded from the loop - no circular refs

similar for child records

SUMSUB(R2C3:R8C4) - given range for childs

SUMSUB(R*C3:R*C4) - means all childs rows 
SUMSUB(R2C*:R8C*) - means all columns

SUMSUB(R*C*:R*C*) - means all

so parent item chan show subtotal
and bottom item can show total by all

It also works in VB

If user is doing data entry run-time in interactive way - he need to press F5 to call Recalc in the end and will see results

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
mdoubson Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 17 November 2008 Status: Offline Points: 1705	Post Options Post Reply Quote mdoubson Report Post Thanks(0) Quote Reply Topic: Report with Formulas Posted: 10 April 2009 at 2:00pm
	I added Formula support to ReportRecordItem. If presented - Item Caption will show result of calculations based on given Formula. Examples - SUM(R2C3:R8C4) - given range SUM(RC3:RC4) - means all rows (parent rows only in case of hierarhy) SUM(R2C:R8C) - means all columns SUM(RC:RC) - means all given item always excluded from the loop - no circular refs similar for child records SUMSUB(R2C3:R8C4) - given range for childs SUMSUB(RC3:RC4) - means all childs rows SUMSUB(R2C:R8C) - means all columns SUMSUB(RC:RC) - means all so parent item chan show subtotal and bottom item can show total by all It also works in VB If user is doing data entry run-time in interactive way - he need to press F5 to call Recalc in the end and will see results

mdoubson Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 17 November 2008 Status: Offline Points: 1705	Post Options Post Reply Quote mdoubson Report Post Thanks(0) Quote Reply Posted: 13 August 2009 at 10:39pm
	Now Report also supported dynamic formulas in Group Rows (good for subtotlals by different criteria) - https://forum.codejock.com/forum_posts.asp?TID=14968&KW=Formula

mdoubson Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 17 November 2008 Status: Offline Points: 1705	Post Options Post Reply Quote mdoubson Report Post Thanks(0) Quote Reply Posted: 14 August 2009 at 8:41pm
	Added Cascade Group Row formula support - http://forum.codejock.com/forum_posts.asp?TID=14837&PN=1 (SubTotal by internal SubTotals)

mdoubson Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 17 November 2008 Status: Offline Points: 1705	Post Options Post Reply Quote mdoubson Report Post Thanks(0) Quote Reply Posted: 02 January 2010 at 12:26pm
	Added multiple subtotals in group rows with dynamic formula - you can try it in action in https://forum.codejock.com/uploads/BetaOCX/ReportControlBeta13-2-2.rar where are VB6 sample - you can use same idea and make simple MFC sample with same feature using https://forum.codejock.com/uploads/DemoVersion/ReportControlMFCUpdated.rar

mdoubson Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 17 November 2008 Status: Offline Points: 1705	Post Options Post Reply Quote mdoubson Report Post Thanks(0) Quote Reply Posted: 04 January 2010 at 3:56pm
	see snapshot of new feature - https://support.codejock.com/Attachments/20091228-140707_GroupRowsWithMultipleSubtotals.jpg

Algae Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 08 January 2007 Location: United States Status: Offline Points: 217	Post Options Post Reply Quote Algae Report Post Thanks(0) Quote Reply Posted: 05 January 2010 at 9:12pm
	Looks pretty interesting and useful. If there was a group "footer" totals could display on the end of group line so you could do sub-reports. something like: Group Header ------- Item 1,1 Item 1,2 Item 1.3 Item 2.1 Item 2.2 Item 2.3 .... etc ______ Group Footer Total Total

znakeeye Members Profile Send Private Message Find Members Posts Add to Buddy List Senior Member Joined: 26 July 2006 Status: Offline Points: 1672	Post Options Post Reply Quote znakeeye Report Post Thanks(0) Quote Reply Posted: 06 January 2010 at 8:18pm
	Though this is a nice add-on, I still think this kind of functionality belongs in a separate Grid-control. You will most likely end up having to refactor this code later on... :(
	PokerMemento - http://www.pokermemento.com/

blackeagle Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 24 May 2009 Location: Turkey Status: Offline Points: 1	Post Options Post Reply Quote blackeagle Report Post Thanks(0) Quote Reply Posted: 11 February 2010 at 12:48pm
	Hi Is it possible to print grouprow item's count with formula at group caption?
	eagles fly higher...

davidbeane Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 12 June 2007 Status: Offline Points: 5	Post Options Post Reply Quote davidbeane Report Post Thanks(0) Quote Reply Posted: 08 June 2010 at 4:09pm
	Could you possibly give a sample of the code used to insert the records into the control as in the screenshot displayed? (The original screenshot in the first post) Not the formulas, just how to insert the grouped rows. I cannot figure out how to insert data in a hierarchical format like that.

davidbeane Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 12 June 2007 Status: Offline Points: 5	Post Options Post Reply Quote davidbeane Report Post Thanks(0) Quote Reply Posted: 09 June 2010 at 2:12pm
	I finally discovered CXTPReportColumn::SetTreeColumn()

jequita2009 Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 29 July 2010 Location: Peru Status: Offline Points: 3	Post Options Post Reply Quote jequita2009 Report Post Thanks(0) Quote Reply Posted: 02 September 2010 at 1:49pm
	''establishing the following function to generate a string: Private Function SumSub() As String Dim SumSubf As String Dim cadena As String cadena = " " For x = 0 To RC.Columns.Count - 1 RC.Columns(x).Tag = G_Header(1, x) If RC.Columns(x).Tag = "N" Then SumSubf = "SUMSUB(C" & x & ":C" & x + 1 & ")" cadena = cadena + SumSubf End If Next SumSub = cadena End Function

jequita2009 Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 29 July 2010 Location: Peru Status: Offline Points: 3	Post Options Post Reply Quote jequita2009 Report Post Thanks(0) Quote Reply Posted: 02 September 2010 at 1:50pm
	''Espero que les sirva ''Esto es en caso no se sabe que columna es num�rica o no. ''I hope you learn ''This is in case you do not know which column is numeric or not. Private Sub RC_GroupOrderChangedEx(ByVal Column As XtremeReportControl.IReportColumn, ByVal Reason As XtremeReportControl.XTPReportColumnOrderChangedReason) Dim i As Integer Dim j As Integer Dim Row As ReportRow Dim groupRow As ReportGroupRow Dim xRecord As XtremeReportControl.ReportRecord Dim xRecordItem As XtremeReportControl.ReportRecordItem Dim CadenaSumSub As String CadenaSumSub = SumSub If Reason & xtpReportColumnAddedToGroupby Then For j = 0 To RC.Columns.Count - 1 If RC.Columns(j).Tag = "N" Then ''If numeric For i = 0 To RC.Rows.Count - 1 Set Row = RC.Rows(i) If Row.groupRow Then Set groupRow = Row 'sumSub = "SUMSUB(C" & j + 1 & ":C" & j + 1 + 1 & ")" groupRow.GroupFormat = " Subtotal =%.02f" groupRow.GroupFormula = CadenaSumSub groupRow.GroupCaption = "x" End If Next End If Next End If End Sub

Ajit Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 06 September 2010 Location: India Status: Offline Points: 1	Post Options Post Reply Quote Ajit Report Post Thanks(0) Quote Reply Posted: 14 September 2010 at 1:07pm
	Hi Can somebody help me to display total in group rows. as shown in following screenshot. available example in this forum is in VB. what will be the equivalent code in VC++. Thanx in advance.

RJAMIN Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 29 September 2010 Location: Houston, TX Status: Offline Points: 5	Post Options Post Reply Quote RJAMIN Report Post Thanks(0) Quote Reply Posted: 05 October 2010 at 4:32pm
	You can just put the item count in the caption. Example: Private Sub wndReportControl_GroupOrderChangedEx(ByVal sender As Object, ByVal e As AxXtremeReportControl._DReportControlEvents_GroupOrderChangedExEvent) Handles wndReportControl.GroupOrderChangedEx Dim row As Integer Dim sumSub As String Dim groupRow As XtremeReportControl.ReportGroupRow If ((e.reason And XTPReportColumnOrderChangedReason.xtpReportColumnAddedToGroupby) = XTPReportColumnOrderChangedReason.xtpReportColumnAddedToGroupby) Or _ ((e.reason And XTPReportColumnOrderChangedReason.xtpReportColumnRemovedFromGroupby) = XTPReportColumnOrderChangedReason.xtpReportColumnRemovedFromGroupby) Then sumSub = "SUMSUB(C6:C7)" For row = 0 To (wndReportControl.Rows.Count - 1) If (wndReportControl.Rows(row).GroupRow) Then groupRow = wndReportControl.Rows(row) groupRow.GroupFormula = sumSub groupRow.GroupFormat = "%0.2f" groupRow.GroupCaption = (" (" + groupRow.Childs().Count.ToString() + " items)") End If Next End If End Sub