about formula property
Printed From: Codejock Forums
Category: Codejock Products
Forum Name: Report Control
Forum Description: Topics Related to Codejock Report Control
URL: http://forum.codejock.com/forum_posts.asp?TID=14837
Printed Date: 01 May 2024 at 8:19pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com
Topic: about formula property
Posted By: dailyblog
Subject: about formula property
Date Posted: 27 July 2009 at 1:55am
|
SUM(R2C3:R8C4)
what does number "2" in "R2" mean?
what does number "3" in "C3" mean?
what does number "8" in "R8" mean?
what does number "4" in "C4" mean?
can anybody tell me?
|
Replies:
Posted By: mdoubson
Date Posted: 27 July 2009 at 2:44pm
|
Like Excel - Row# Col# - e.g. try this:
Private Sub Form_Load()
Dim xColumn As XtremeReportControl.ReportColumn Dim i As Integer wndReport.Columns.Add 0, "Product", 100, True wndReport.Columns.Add 1, "State", 100, True wndReport.Columns.Add 2, "Sales", 100, True wndReport.Columns.Add 3, "", 100, True Dim xRecord As XtremeReportControl.ReportRecord Dim xRecordItem As XtremeReportControl.ReportRecordItem '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem("Pen") Set xRecordItem = xRecord.AddItem("NSW") Set xRecordItem = xRecord.AddItem("20") '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem("paper") Set xRecordItem = xRecord.AddItem("NSW") Set xRecordItem = xRecord.AddItem("10") '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem("Books") Set xRecordItem = xRecord.AddItem("NSW") Set xRecordItem = xRecord.AddItem("10") '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem("Pen") Set xRecordItem = xRecord.AddItem("SA") Set xRecordItem = xRecord.AddItem("20") '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem("paper") Set xRecordItem = xRecord.AddItem("SA") Set xRecordItem = xRecord.AddItem("10") '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem("Books") Set xRecordItem = xRecord.AddItem("SA") Set xRecordItem = xRecord.AddItem("10") '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem("Pen") Set xRecordItem = xRecord.AddItem("WA") Set xRecordItem = xRecord.AddItem("20") '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem("paper") Set xRecordItem = xRecord.AddItem("WA") Set xRecordItem = xRecord.AddItem("10") '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem("Books") Set xRecordItem = xRecord.AddItem("WA") Set xRecordItem = xRecord.AddItem("10") '------------------------------------------- Set xRecord = wndReport.Records.Add Set xRecordItem = xRecord.AddItem(" ") Set xRecordItem = xRecord.AddItem("Total") Set xRecordItem = xRecord.AddItem("x") xRecordItem.Format = "%d" xRecordItem.Caption = "x" xRecordItem.Formula = "SUM(R*C1:R*C8)" xRecordItem.Editable = False With Me.wndReport .PaintManager.ColumnStyle = xtpColumnOffice2007 .AllowColumnSort = True .AllowColumnReorder = False .AllowEdit = True .FocusSubItems = True End With wndReport.PaintManager.RecOrRowNumber = False wndReport.PaintManager.StartRecOrRowNumber = 1 wndReport.PaintManager.TreeIndent = 40 wndReport.PaintManager.GroupIndentColor = RGB(255, 0, 0)
wndReport.Populate myerror: If Err.Number > 0 Then MsgBox Err.Description End If End Sub Press run-time Shift+F5 (same after user changed some values in cells) ------------- Mark Doubson, Ph.D. |
Posted By: dailyblog
Date Posted: 29 July 2009 at 8:47am
 hi,sir
i'm not clear what the numbers in formula "sum(R0*C0:R3*C1)" mean.
i marked the item in my opinion in green color,but it is not correct.
|
Posted By: mdoubson
Date Posted: 29 July 2009 at 9:28am
|
C1 and R3 is strict "< " boundary - so [R0*C0:R3*C1] set = {R0C0, R1C0,R2C0} - 3 cells ------------- Mark Doubson, Ph.D. |
Posted By: dailyblog
Date Posted: 29 July 2009 at 10:48am
|
great,i see
thank u
 |
Posted By: mdoubson
Date Posted: 30 July 2009 at 7:52pm
|
There is another function - SUMSUB - where only child records used
and full row or full column notation e.g. SUMSUB(R*C2:R*C3) - total column 2 (all rows used) ------------- Mark Doubson, Ph.D. |
Posted By: dailyblog
Date Posted: 31 July 2009 at 12:18am
|
great. and i have 2 additional questions.
1. Just SUM function is supported? can it support AVG,COUNT,MAX,MIN functions?
2.i got grouped records from database, for example,SQL="Select name,amount,sex from t_user Group By sex", then how to use SUM function to get the total amount in each "male" or "female" ?
|
Posted By: joeliner
Date Posted: 31 July 2009 at 4:59am
|
This is really nice addon. How can i change the forecolor to blue or black? ------------- Product: Xtreme SuitePro (ActiveX) version 13.1 Platform: Windows XP SP 3 Language: Visual Basic 6 SP6 |
Posted By: joeliner
Date Posted: 31 July 2009 at 7:41am
|
I have tried item.forecolor = vbblack but that daint work
Secondly, how do i apply a format like "$ %.20s" onto the generated result?
I understand the function still requires refinement to calculate formated data like currency.
------------- Product: Xtreme SuitePro (ActiveX) version 13.1 Platform: Windows XP SP 3 Language: Visual Basic 6 SP6 |
Posted By: joeliner
Date Posted: 31 July 2009 at 7:42am
|
What's the purpose of these two properties
wndReport.PaintManager.RecOrRowNumber = False
wndReport.PaintManager.StartRecOrRowNumber = 1 I cant find them on the Help. :) ------------- Product: Xtreme SuitePro (ActiveX) version 13.1 Platform: Windows XP SP 3 Language: Visual Basic 6 SP6 |
Posted By: mdoubson
Date Posted: 31 July 2009 at 9:05am
|
No relation with Formula at all. It's about using service-column used for icon-view mode also in report-mode and show row # (or rec #) based on StartRecOrRowNumber as initial # ------------- Mark Doubson, Ph.D. |
Posted By: dailyblog
Date Posted: 31 July 2009 at 9:17am
| Just like Ms Excel ,right? |
Posted By: mdoubson
Date Posted: 31 July 2009 at 9:43am
|
Similar - excel does not recognize "Records" - only "Rows" - we do.
About user-defined Colors for Formula - SetTextColor(RGB(0,0,255)); for Childs (SUMSUB) SetTextColor(RGB(255,0,0)); for SUM Get fresh upgrade https://forum.codejock.com/uploads/DemoVersion/ReportControlUpdated.rar - https://forum.codejock.com/uploads/DemoVersion/ReportControlUpdated.rar
Now you can use new settings:
ReportControl.PaintManager.FormulaSumColor
ReportControl.PaintManager.FormulaSubSumColor  ------------- Mark Doubson, Ph.D. |
Posted By: joeliner
Date Posted: 03 August 2009 at 7:20am
Kudos Mark 
One more thing, formatting into currency. This is still a not possible.
I am using the xRecordItem.Format = "$ %.4s"
regards,
Joel ------------- Product: Xtreme SuitePro (ActiveX) version 13.1 Platform: Windows XP SP 3 Language: Visual Basic 6 SP6 |
Posted By: mdoubson
Date Posted: 03 August 2009 at 9:51am
|
Looks like you can - see end of Calc loop - result already ready - just need to present it in formatted way:
CString sFmt = GetFormatString(); if (sFmt.IsEmpty() || sFmt == _T("%s"))sFmt = _T( "%f"); else if (sFmt == _T("%d"))sFmt = _T( "%.0f");strCaption.Format(sFmt, d); SetTextColor(clrSum ); e.g. xRecordItem.Format = "$ %04.2f"
 ------------- Mark Doubson, Ph.D. |
Posted By: mdoubson
Date Posted: 13 August 2009 at 10:33pm
|
see Dynamic Formula in Group Rows - https://forum.codejock.com/forum_posts.asp?TID=14968&KW=Formula - https://forum.codejock.com/forum_posts.asp?TID=14968&KW=Formula ------------- Mark Doubson, Ph.D. |
Posted By: dailyblog
Date Posted: 14 August 2009 at 4:31am
|
does the records above filtered from database?
i use Vsflexgrid,and it can get result i need,but it does NOT surport skin style.
 |
Posted By: mdoubson
Date Posted: 14 August 2009 at 8:40am
|
How it relate to skin? ReportControl support skins ------------- Mark Doubson, Ph.D. |
Posted By: dailyblog
Date Posted: 14 August 2009 at 9:55am
|
sorry,my wrong.vsflexgrid does not surport skin.
can reportcontrol meet my needs?(my English is poor)
waiting on line...
|
Posted By: mdoubson
Date Posted: 14 August 2009 at 10:06am
|
I guess so. Try it ------------- Mark Doubson, Ph.D. |
Posted By: dailyblog
Date Posted: 14 August 2009 at 10:11am
| it is pity that it does not meet my needs. thanks anyway! |
Posted By: mdoubson
Date Posted: 14 August 2009 at 10:16am
|
RC SUPPORTS skin but it does not mean RC NEED to use skin ------------- Mark Doubson, Ph.D. |
Posted By: dailyblog
Date Posted: 14 August 2009 at 10:41am
|
misunderstanding.
please look at the reply on 7th Floor.
|
Posted By: jpbro
Date Posted: 14 August 2009 at 12:26pm
|
If I may interject - I think what daily blog is looking for is subtotals & groups to appear *below* the list of grouped items. If you look closely at the screenshot, you will see that the groups open upwards instead of downwards. Also, I know that the VSFlexGrid allows for multiple columns to be subtotalled onto a row, so this may be another requirement (in dailyblog's screenshot, there are 3 subtotalled columns per row). The point about skins is irrelevant except in the sense that dailyblog would prefer to use the CodeJock ReportControl over the VsFlexGrid if it supports his grouping/subtotalling requirements because the RC supports skins (and will therefore look better). But functionality trumps aesthetics. Maybe dailyblog confirm if my interpretation is correct? ------------- Product: Xtreme SuitePro (ActiveX) version 16.2.6 Platform: Windows XP - SP3 Language: Visual Basic 6.0 SP6 |
Posted By: mdoubson
Date Posted: 14 August 2009 at 12:41pm
As all you know our group concept - childs follows the group - so group as parent should be above - no way to change it
btw - Formula in Group Row is very fresh code I am working right now - e.g. need to extend it for cascade case (several columns in Group Box)
You can see on snapshot today's version result with 2 columns in GroupBox - and subtotal now show in cascade way
Get fresh ocx https://forum.codejock.com/uploads/DemoVersion/ReportControlUpdated.rar - https://forum.codejock.com/uploads/DemoVersion/ReportControlUpdated.rar
and your can use same VB sample I attached yesterday ------------- Mark Doubson, Ph.D. |
Posted By: dailyblog
Date Posted: 15 August 2009 at 12:09am
|
member_profile.asp?PF=2676&FID=124 - jpbro :ur reply not completely correct,but thank u anyway ,because u have explained the differences between RC and VSflexgrid,and these are all my needs.
My point is :i get grouped records from database but do not know the counts of child rows in each group(except using "select count(*)" in SQL),so how can i get the number 4 in formula SUM(r1*c2:r4*c3).
i think RC can meet my needs,but it needs more Codes than Vsflexgrid.
if you do not understand me ,i have to translate the reply above into Chinese:
你说的不完全正确。但还是要谢谢你的回复，因为你说出了RC跟Vsflexgrid差异点。而这些也是我想在RC中实现的。
我的主要意思是：我从数据库是得到分组记录，但我却不知道每一组记录里包含多少子记录，所以在我用公式 SUM(r1*c2:r4*c3) 时，如何知道 4 是多少？
我想RC是可以做到的，但可能会更多的代码才能实现我的需求。
  |
Posted By: mdoubson
Date Posted: 15 August 2009 at 1:34am
|
You don't need it at all - see my comment from code to recalc for GroupRow:
//CODE FOR RECALC - pattern SUMSUB(R#C#:R#C#) or * instead of # - means all row or column //ROW range ignored as used dynamic based on group row childs so you can use formula as SUMSUB(R*C2:R*C3)------------- Mark Doubson, Ph.D. |