about formula property |
Post Reply |
Author | |
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
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?
|
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
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)
|
|
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
|
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.
|
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
C1 and R3 is strict "< " boundary - so [R0*C0:R3*C1] set = {R0C0, R1C0,R2C0} - 3 cells
|
|
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
|
great,i see
thank u
|
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
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)
|
|
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
|
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" ?
|
|
joeliner
Senior Member Joined: 09 June 2006 Status: Offline Points: 273 |
Post Options
Thanks(0)
|
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 |
|
joeliner
Senior Member Joined: 09 June 2006 Status: Offline Points: 273 |
Post Options
Thanks(0)
|
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 |
|
joeliner
Senior Member Joined: 09 June 2006 Status: Offline Points: 273 |
Post Options
Thanks(0)
|
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 |
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
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 # |
|
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
|
Just like Ms Excel ,right?
|
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
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
Now you can use new settings:
ReportControl.PaintManager.FormulaSumColor
ReportControl.PaintManager.FormulaSubSumColor |
|
joeliner
Senior Member Joined: 09 June 2006 Status: Offline Points: 273 |
Post Options
Thanks(0)
|
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 |
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
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"
|
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
see Dynamic Formula in Group Rows - https://forum.codejock.com/forum_posts.asp?TID=14968&KW=Formula
|
|
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
|
does the records above filtered from database?
i use Vsflexgrid,and it can get result i need,but it does NOT surport skin style.
|
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
How it relate to skin? ReportControl support skins
|
|
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
|
sorry,my wrong.vsflexgrid does not surport skin.
can reportcontrol meet my needs?(my English is poor)
waiting on line...
|
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
I guess so. Try it
|
|
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
|
it is pity that it does not meet my needs. thanks anyway!
|
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
RC SUPPORTS skin but it does not mean RC NEED to use skin
|
|
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
|
misunderstanding.
please look at the reply on 7th Floor.
|
|
jpbro
Senior Member Joined: 12 January 2007 Status: Offline Points: 1355 |
Post Options
Thanks(0)
|
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 |
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
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
and your can use same VB sample I attached yesterday
|
|
dailyblog
Groupie Joined: 11 July 2009 Status: Offline Points: 27 |
Post Options
Thanks(0)
|
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是可以做到的,但可能会更多的代码才能实现我的需求。
|
|
mdoubson
Senior Member Joined: 17 November 2008 Status: Offline Points: 1705 |
Post Options
Thanks(0)
|
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) |
|
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |