11/30/2024 5:35:29 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss usage & tips for SalesLogix controls and other 3rd party ActiveX controls. View the code of conduct for posting guidelines.
|
|
|
|
Creating a chart in account detail view SalesLogix v6.2.6
Posted: 15 Feb 08 4:31 PM
|
Hi all.
I would like to add a chart to the account detail view where it shows the total sales for each year, I used the example in the help file.
I want the be a vertical bar graph that shows the total sales by year, I have a table that has the ACCOUNTID, YEAR and TOTALSALES and that's where I am pulling the data.
Right now I have the SQL line hardcoded to look at a specific account, how can I get it to refresh the data everytime I change accounts in the account detail view?
====================================================================
Dim gTopOppRS
Sub LoadChart
BuildTopOppRS
GetTopOppData
chart1.RecordSet = gTopOppRS
chart1.Refresh
End Sub
Sub BuildTopOppRS
Set gTopOppRS = CreateObject("ADODB.Recordset")
With gTopOppRS
.Fields.Append "YEAR", adVarChar, 32
.Fields.Append "AMOUNT",adCurrency, 12
.Open
End With
End sub
Sub GetTopOppData
Dim strSQL
Dim objCon
Dim objRS
Dim I
Dim MAX
strSQL = "SELECT SDI.SUMTOTALSALES As Amount, SDI.SALESDATAYEAR AS Year"
strSQL = strSQL & " FROM SDISALESDATA SDI"
strSQL = strSQL & " WHERE SDI.PRINTMEDIAID = '3-10141'"
' strSQL = strSQL & " WHERE OPP.STATUS = 'Open'"
' strSQL = strSQL & " ORDER BY OPP.SalesPotential DESC"
Set objCon = Application.GetNewConnection
Set objRS = CreateObject("ADODB.Recordset")
objRS.CursorType = 3 'adOpenStatic
objRS.CursorLocation = 3 'adUseClient
objRS.LockType = 3 'adLockOptimistic
objRS.Open strSQL, objCon 'Provider
If objRS.RecordCount > 0 Then
For I = 1 to objRS.RecordCount
'IF( I = gTopMAX+1) Then
' Exit For
'End IF
gTopOppRS.AddNew
gTopOppRS.Fields("YEAR").Value = I
If not(IsNull(objRS.Fields("AMOUNT").Value))Then
gTopOppRS.Fields("AMOUNT").Value = objRS.Fields("AMOUNT").Value
Else
gTopOppRS.Fields("AMOUNT").Value = 0.0
End IF
gTopOppRS.Update
objRS.MoveNext
next
gTopOppRS.MoveFirst
End If
objRS.Close
Set objCon = Nothing
Set objRS = Nothing
End Sub
Sub AXFormOpen(Sender)
LoadChart
End Sub |
|
|
| |
|
Re: Creating a chart in account detail view SalesLogix v6.2.6
Posted: 16 Feb 08 8:48 AM
|
Thanks RJ, I did what you suggested and the chart works great for accounts where there is data, but I go to an acccount that has no data in the SDISALESDATA table (it's not in the table) then I get the following error:
"An error occurred executing active form scrip (System:Account Detail) '' is not a valid floating point value at line 550,char 3"
and line 550,char 3 is: chart1.RecordSet = gTopOppRS
How can I get to: If there is no data to plot on the chart then don't show the chart?
Here is my code again:
Dim gTopOppRS
Sub LoadChart
BuildTopOppRS
GetTopOppData
chart1.RecordSet = gTopOppRS
chart1.Refresh
End Sub
Sub BuildTopOppRS
Set gTopOppRS = CreateObject("ADODB.Recordset")
With gTopOppRS
.Fields.Append "YEAR", adVarChar, 32
.Fields.Append "AMOUNT",adCurrency, 12
.Open
End With
End sub
Sub GetTopOppData
Dim strSQL
Dim objCon
Dim objRS
Dim I
Dim MAX
strSQL = "SELECT SDI.SUMTOTALSALES As Amount, SDI.SALESDATAYEAR AS Year"
strSQL = strSQL & " FROM SDISALESDATA SDI"
strSQL = strSQL & " WHERE SDI.ACCOUNTID = '" & frmAccountDetail.CurrentID & "'"
Set objCon = Application.GetNewConnection
Set objRS = CreateObject("ADODB.Recordset")
objRS.CursorType = 3 'adOpenStatic
objRS.CursorLocation = 3 'adUseClient
objRS.LockType = 3 'adLockOptimistic
objRS.Open strSQL, objCon 'Provider
If objRS.RecordCount > 0 Then
For I = 1 to objRS.RecordCount
gTopOppRS.AddNew
gTopOppRS.Fields("YEAR").Value = I
If not(IsNull(objRS.Fields("YEAR").Value))Then
gTopOppRS.Fields("YEAR").Value = objRS.Fields("YEAR").Value
Else
gTopOppRS.Fields("YEAR").Value = ""
End IF
If not(IsNull(objRS.Fields("AMOUNT").Value))Then
gTopOppRS.Fields("AMOUNT").Value = objRS.Fields("AMOUNT").Value
Else
gTopOppRS.Fields("AMOUNT").Value = 0.0
End IF
gTopOppRS.Update
objRS.MoveNext
next
gTopOppRS.MoveFirst
End If
objRS.Close
Set objCon = Nothing
Set objRS = Nothing
End Sub
|
|
|
| |
|
Re: Creating a chart in account detail view SalesLogix v6.2.6
Posted: 17 Feb 08 1:21 PM
|
How can I get it to to "not plot"...the data is based on customer sales, if there is no sales data for the customer then it's not in the table...I don't see why I would include customer numbers with $0 in the field.
Unless that's the only way then I guess that's what I will have to do...
|
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|