Codejock Forums Homepage
Forum Home Forum Home > Codejock Products > ActiveX COM > Report Control
  New Posts New Posts RSS Feed - about formula property
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

about formula property
 Post Reply Post Reply
Author
Message
  Topic Search Topic Search  Topic Options Topic Options
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post Topic: about formula property
    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?
 
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post 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)
Back to Top
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post 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.  
 
 
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post Posted: 29 July 2009 at 9:28am
C1 and R3 is strict "< " boundary - so [R0*C0:R3*C1] set = {R0C0, R1C0,R2C0} - 3 cells
Back to Top
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post Posted: 29 July 2009 at 10:48am
great,i see
thank u
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post 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)
Back to Top
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post 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" ?
Back to Top
joeliner View Drop Down
Senior Member
Senior Member
Avatar

Joined: 09 June 2006
Status: Offline
Points: 273 		Post Options Post Options   Thanks (0) Thanks(0)   Quote joeliner Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
joeliner View Drop Down
Senior Member
Senior Member
Avatar

Joined: 09 June 2006
Status: Offline
Points: 273 		Post Options Post Options   Thanks (0) Thanks(0)   Quote joeliner Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
joeliner View Drop Down
Senior Member
Senior Member
Avatar

Joined: 09 June 2006
Status: Offline
Points: 273 		Post Options Post Options   Thanks (0) Thanks(0)   Quote joeliner Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post 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 #
Back to Top
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post Posted: 31 July 2009 at 9:17am
Just like Ms Excel ,right?
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post Posted: 31 July 2009 at 9:43am
Similar - excel does not recognize "Records" - only "Rows" - we do.
About user-defined Colors for Formula - will be in next upgrade - now this is hardcoded -

SetTextColor(RGB(0,0,255)); for Childs (SUMSUB)

SetTextColor(RGB(255,0,0)); for SUM

 
Now you can use new settings:
ReportControl.PaintManager.FormulaSumColor
ReportControl.PaintManager.FormulaSubSumColor
 
Back to Top
joeliner View Drop Down
Senior Member
Senior Member
Avatar

Joined: 09 June 2006
Status: Offline
Points: 273 		Post Options Post Options   Thanks (0) Thanks(0)   Quote joeliner Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post 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"
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post Posted: 13 August 2009 at 10:33pm
see Dynamic Formula in Group Rows - https://forum.codejock.com/forum_posts.asp?TID=14968&KW=Formula
Back to Top
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post Posted: 14 August 2009 at 8:40am
How it relate to skin? ReportControl support skins
Back to Top
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post 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...
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post Posted: 14 August 2009 at 10:06am
I guess so. Try it
Back to Top
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post Posted: 14 August 2009 at 10:11am
it is pity that it does not meet my needs. thanks anyway!
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post Posted: 14 August 2009 at 10:16am
RC SUPPORTS skin but it does not mean RC NEED to use skin
Back to Top
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post Posted: 14 August 2009 at 10:41am
misunderstanding.
please look at the reply on 7th Floor.
 
Back to Top
jpbro View Drop Down
Senior Member
Senior Member
Avatar

Joined: 12 January 2007
Status: Offline
Points: 1354 		Post Options Post Options   Thanks (0) Thanks(0)   Quote jpbro Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post 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
and your can use same VB sample I attached yesterday
Back to Top
dailyblog View Drop Down
Groupie
Groupie


Joined: 11 July 2009
Status: Offline
Points: 27 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dailyblog Quote  Post ReplyReply Direct Link To This Post Posted: 15 August 2009 at 12:09am
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是可以做到的，但可能会更多的代码才能实现我的需求。
Back to Top
mdoubson View Drop Down
Senior Member
Senior Member
Avatar

Joined: 17 November 2008
Status: Offline
Points: 1705 		Post Options Post Options   Thanks (0) Thanks(0)   Quote mdoubson Quote  Post ReplyReply Direct Link To This Post 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)
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.04
Copyright ©2001-2021 Web Wiz Ltd.

This page was generated in 0.172 seconds.