Chart Usage Advice

Post

Posted
Rating:
#1 (In Topic #1307)
AndyGable
Enthusiast
AndyGable is in the usergroup ‘Enthusiast’
Hi Everyone,

I hope someone could explain to me what I am doing wrong

I have been using post http://forum.gambas.one/viewtopic.php?p=1193 as a base for what I am trying to do

I am trying to use a Chart to show taking for the 12 months (both sales and Refunds) I have the following code that I have come up with after reading a lot
of posts on here

Code (gambas)

  1.     hChart1.Colors.Values = [Color.green, Color.red]
  2.     DrawingArea1.Refresh
  3.     hChart1.YAxe.ShowIntervalLines = False
  4.     hChart1.ShowLabels = True
  5.  
  6.     hChart1.type = ChartType.ColumnsStacked
  7.     hChart1.Legend.Title = "Legend"
  8.     hChart1.Legend.Visible = True
  9.     hChart1.Legend.Position = Align.Right
  10.     '
  11.     hChart1.headers.values = ["Sales", "Refunds"]
  12.     hChart1.CountDataSets = 12
  13.  
  14.     hChart1[0].Text = "Month"
  15.     hChart1[0].values = [12, 0]
  16.     hChart1.FirstColumnAsLabel = True
  17.  
  18.       hChart1[0].Values = [DataFromDatabase_Sale[1], DataFromDatabase_Sale[2], DataFromDatabase_Sale[3], DataFromDatabase_Sale[4], DataFromDatabase_Sale[5], DataFromDatabase_Sale[6], DataFromDatabase_Sale[7], DataFromDatabase_Sale[8], DataFromDatabase_Sale[9], DataFromDatabase_Sale[10], DataFromDatabase_Sale[11], DataFromDatabase_Sale[12]]
  19.     hChart1[1].Values = [DataFromDatabase_Refund[1], DataFromDatabase_Refund[2], DataFromDatabase_Refund[3], DataFromDatabase_Refund[4], DataFromDatabase_Refund[5], DataFromDatabase_Refund[6], DataFromDatabase_Refund[7], DataFromDatabase_Refund[8], DataFromDatabase_Refund[9], DataFromDatabase_Refund[10], DataFromDatabase_Refund[11], DataFromDatabase_Refund[12]]
  20.  

But I am not getting anything on the screen.

The DataFromDatabase_Sale[1] - DataFromDatabase_Sale[12] is the sale data loaded from the database
The DataFromDatabase_Refund[1] - DataFromDatabase_Refund[12] is the Refund data loaded from the database

below is a Debug print out of the data from the database

Code

0.00   0.00
0.00   0.00
0.00   0.00
0.00   0.00
5357.61        238.96
7962.02        161.00
11448.19       145.98
1415.19        5.99
0.00   0.00
0.00   0.00
0.00   0.00
0.00   0.00

**Added 05/08/2024**
This is the result i am getting on screen

<IMG src="https://support.algpos.co.uk/images_help/blankChart.png"> </IMG>

Could someone show me what I am doing wrong and if possible show me how I can get the months across the bottom I would appreciated that a lot
Online now: No Back to the top

Post

Posted
Rating:
#2
cogier
Avatar
Guru
cogier is in the usergroup ‘Guru’
I have taken as much of your code as I can to get this working. Some of the code has been taken from my ChartExample with is on the Gambas Farm.
Run the code in a new graphical program.

Code (gambas)

  1. DrawingArea1 As DrawingArea
  2.  
  3. Public Sub Form_Open()
  4.  
  5.   Dim DataFromDatabase_Sale As Float[] = [0, 2.25, 7.52, 4.25, 78.87, 93.21, 5.2, 5.9, 5.87, 4.7, 11, 12, 4.5]
  6.   Dim DataFromDatabase_Refund As Float[] = [0, 0.2, 0.35, 0.47, 0.4, 1, 2, 0.88, 1, 2, 3, 4, 5]
  7.  
  8.   BuildForm
  9.  
  10.   Chart.CountDataSets = 2
  11.   Chart.Colors.Values = [Color.green, Color.red]
  12.  
  13.   Chart.YAxe.ShowIntervalLines = False
  14.   Chart.ShowLabels = True
  15.   Chart.type = ChartType.ColumnsStacked
  16.   Chart.Legend.Title = "Legend"
  17.   Chart.Legend.Visible = True
  18.   Chart.Legend.Position = Align.Right
  19.   Chart.headers.values = ["Sales", "Refunds"]
  20.   Chart.CountDataSets = 12
  21.   Chart[0].Text = "Month"
  22.   Chart[0].values = [12, 0]
  23.   Chart.FirstColumnAsLabel = True
  24.  
  25.   Chart[0].Values = [DataFromDatabase_Sale[1], DataFromDatabase_Sale[2], DataFromDatabase_Sale[3], DataFromDatabase_Sale[4], DataFromDatabase_Sale[5], DataFromDatabase_Sale[6], DataFromDatabase_Sale[7], DataFromDatabase_Sale[8], DataFromDatabase_Sale[9], DataFromDatabase_Sale[10], DataFromDatabase_Sale[11], DataFromDatabase_Sale[12]]
  26.   Chart[1].Values = [DataFromDatabase_Refund[2], DataFromDatabase_Refund[3], DataFromDatabase_Refund[4], DataFromDatabase_Refund[5], DataFromDatabase_Refund[6], DataFromDatabase_Refund[7], DataFromDatabase_Refund[8], DataFromDatabase_Refund[9], DataFromDatabase_Refund[10], DataFromDatabase_Refund[11], DataFromDatabase_Refund[12]]
  27.  
  29.  
  30. Public Sub DrawingArea1_Draw()
  31.  
  32.   Dim iHeight, iWidth As Integer
  33.  
  34.   iHeight = DrawingArea1.Height - 20
  35.   iWidth = DrawingArea1.Width - 10
  36.   If iHeight < 10 Then iHeight = 10
  37.   If iWidth < 10 Then iWidth = 10
  38.  
  39.   Chart.Height = iHeight
  40.   Chart.Width = iWidth
  42.  
  44.  
  45. Public Sub BuildForm()
  46.  
  47.   With Me
  48.     .H = 512
  49.     .W = 1024
  50.     .Arrangement = Arrange.Vertical
  51.     .Padding = 5
  53.  
  54.   DrawingArea1 = New DrawingArea(Me) As "DrawingArea1"
  55.   DrawingArea1.Expand = True
  56.  
  58.  

<IMG src="https://www.cogier.com/gambas/ChartEx.png"> </IMG>
Online now: No Back to the top

Post

Posted
Rating:
#3
AndyGable
Enthusiast
AndyGable is in the usergroup ‘Enthusiast’
  cogier

Once I am back at my development computer I shall post the complete code I am using to generate the chart.

In the mean time I shall study what you have written to see where I'm going wrong.
Online now: No Back to the top

Post

Posted
Rating:
#4
AndyGable
Enthusiast
AndyGable is in the usergroup ‘Enthusiast’

cogier said

I have taken as much of your code as I can to get this working. Some of the code has been taken from my ChartExample with is on the Gambas Farm.
Run the code in a new graphical program.

Hi Cogier,

Please see below the code I am using to generate the Chart. I have tired to integrate you code (Even though your code is a lot more cleaner then mine)
but I am still getting a blank chart.

I have a drawingarea1 already placed on the screen where I want the chart to show (this is why It is not declared anywhere)

for some reason when my form loads the drawingarea1_draw is called and if I don't have If Chart.Count > 0 Then then I get a error saying Chart null. (and it would be null as nothing has been loaded into the chart yet lol)

I MAY be recalling the data wrong from the database to start with I am am not sure anymore :(

Code (gambas)

  1. ' Gambas class file
  2.  
  3.                       Chart As New Chart
  4.       DataFromDatabase_Sale As New Float[13]
  5.     DataFromDatabase_Refund As New Float[13]
  6.  
  7. Public Sub btnReturnToMainMenu_Click()
  8.     If Global.BeepOnKeyPress = "Yes" Then SystemFunctions.PCSpeakerBleep(500)
  9.     Me.Close
  10.     frmSiteFunctionMenu.Close
  11.     frmbackground.Workspace1.Add(frmDashBoard, 0)
  13.  
  14. Public Sub Button1_Click()
  15.   If Global.BeepOnKeyPress = "Yes" Then SystemFunctions.PCSpeakerBleep(500)
  16.  
  17.   GetChartDAta_1  
  18.    
  19.   Chart.CountDataSets = 2
  20.   Chart.Colors.Values = [Color.green, Color.red]
  21.    
  22.   Chart.YAxe.ShowIntervalLines = False
  23.   Chart.ShowLabels = True
  24.   Chart.type = ChartType.ColumnsStacked
  25.   Chart.Legend.Title = "Legend"
  26.   Chart.Legend.Visible = True
  27.   Chart.Legend.Position = Align.Right
  28.   Chart.headers.values = ["Sales", "Refunds"]
  29.   Chart.CountDataSets = 12
  30.   Chart[0].Text = "Month"
  31.   Chart[0].values = [12, 0]
  32.   Chart.FirstColumnAsLabel = True
  33.    
  34.   Chart[0].Values = [DataFromDatabase_Sale[1], DataFromDatabase_Sale[2], DataFromDatabase_Sale[3], DataFromDatabase_Sale[4], DataFromDatabase_Sale[5], DataFromDatabase_Sale[6], DataFromDatabase_Sale[7], DataFromDatabase_Sale[8], DataFromDatabase_Sale[9], DataFromDatabase_Sale[10], DataFromDatabase_Sale[11], DataFromDatabase_Sale[12]]
  35.   Chart[1].Values = [DataFromDatabase_Refund[2], DataFromDatabase_Refund[3], DataFromDatabase_Refund[4], DataFromDatabase_Refund[5], DataFromDatabase_Refund[6], DataFromDatabase_Refund[7], DataFromDatabase_Refund[8], DataFromDatabase_Refund[9], DataFromDatabase_Refund[10], DataFromDatabase_Refund[11], DataFromDatabase_Refund[12]]
  36.    
  38.  
  39. Public Sub DrawingArea1_Draw()
  40.   Dim iHeight, iWidth As Integer
  41.  
  42.     If Chart.Count > 0 Then
  43.         iHeight = DrawingArea1.Height - 20
  44.         iWidth = DrawingArea1.Width - 10
  45.  
  46.         If iHeight < 10 Then iHeight = 10
  47.         If iWidth < 10 Then iWidth = 10
  48.      
  49.         Chart.Height = iHeight
  50.         Chart.Width = iWidth
  51.         Chart.Draw
  52.     End If
  54.  
  55. Private Sub GetChartDAta_1()
  56.   Dim DataFromModule As String = Null
  57.   Dim DataToSplit As New String[1]
  59.  
  60.   For i = 1 To 12
  61.     DataFromModule = BackOffice_Dashboard_Charts.GetMonthlyTakingsFromDatabase(i)
  62.     DataToSplit = Split(DataFromModule, "|")
  63.           DataFromDatabase_Sale[i] = DataToSplit[0]
  64.         DataFromDatabase_Refund[i] = DataToSplit[1]
  65.           DataFromDatabase_Sale[i] = Format((DataFromDatabase_Sale[i] / 100), "0.00")
  66.         DataFromDatabase_Refund[i] = Format((DataFromDatabase_Refund[i] / 100), "0.00")
  67.     Debug DataFromDatabase_Sale[i], DataFromDatabase_Refund[i]
  68.   Next

This is the Function form the BackOffice_Dashboard_Charts.GetMonthlyTakingsFromDatabase()

Code (gambas)

  1. Public Function GetMonthlyTakingsFromDatabase(MonthValue As Integer) As String
  2.      Dim TotalValuesSales As Integer = 0
  3.     Dim TotalValuesRefund As Integer = 0
  4.          Dim RequestedDate As String = Null
  5.       Dim RequestedEndDate As String = Null
  6.  
  7.               RequestedDate = Format(Now, "yyyy") & "/" & Format(MonthValue, "00") & "/01"
  8.            RequestedEndDate = Format(Now, "yyyy") & "/" & Format(MonthValue, "00") & "/31"
  9.  
  10.      Global.BackOfficeQuery = Null
  11.     Global.BackOfficeQuery &= "Select "
  12.     Global.BackOfficeQuery &= "abs(SUM(cashvalue)) as TotalCash, "
  13.     Global.BackOfficeQuery &= "abs(SUM(chequevalue)) as TotalCheque, "
  14.     Global.BackOfficeQuery &= "abs(SUM(cardvalue)) as TotalCard, "
  15.     Global.BackOfficeQuery &= "abs(SUM(couponvalue)) as TotalCoupon, "
  16.     Global.BackOfficeQuery &= "abs(SUM(giftvouchervalue)) as TotalGiftVoucher, "
  17.     Global.BackOfficeQuery &= "abs(SUM(giftcardvalue)) as TotalGiftCard, "
  18.     Global.BackOfficeQuery &= "abs(SUM(accountvalue)) as TotalAccounts, "
  19.     Global.BackOfficeQuery &= "abs(SUM(safedropvalue)) as TotalSafeDrop, "
  20.     Global.BackOfficeQuery &= "abs(SUM(cashvalue_refund)) as TotalCashRefund, "
  21.     Global.BackOfficeQuery &= "abs(SUM(chequevalue_refund)) as TotalChequeRefund, "
  22.     Global.BackOfficeQuery &= "abs(SUM(cardvalue_refund)) as TotalCardRefund, "
  23.     Global.BackOfficeQuery &= "abs(SUM(couponvalue_refund)) as TotalCouponRefund, "
  24.     Global.BackOfficeQuery &= "abs(SUM(giftvouchervalue_refund)) as TotalGiftVoucherRefund, "
  25.     Global.BackOfficeQuery &= "abs(SUM(giftcardvalue_refund)) as TotalGiftCardRefund, "
  26.     Global.BackOfficeQuery &= "abs(SUM(accountvalue_refund)) as TotalAccountsRefund "
  27.     Global.BackOfficeQuery &= "from posfigures "
  28.     Global.BackOfficeQuery &= "where datecol BETWEEN '" & RequestedDate & "' and '"    
  29.     Global.BackOfficeQuery &= RequestedEndDate & "';"
  30.  
  31.     BackofficeDatabase.ConnectToBackOfficeDatabase("Open")
  32.     Global.BODataResult = Global.$DBBackOfficeCon.Exec(Global.BackOfficeQuery)  
  33.  
  34.     If Global.BODataResult.Available = True Then
  35.                      If Global.BODataResult!TotalCash <> "" Then TotalValuesSales += Global.BODataResult!TotalCash
  36.                    If Global.BODataResult!TotalCheque <> "" Then TotalValuesSales += Global.BODataResult!TotalCheque
  37.                      If Global.BODataResult!TotalCard <> "" Then TotalValuesSales += Global.BODataResult!TotalCard
  38.                    If Global.BODataResult!TotalCoupon <> "" Then TotalValuesSales += Global.BODataResult!TotalCoupon
  39.               If Global.BODataResult!TotalGiftVoucher <> "" Then TotalValuesSales += Global.BODataResult!TotalGiftVoucher
  40.                  If Global.BODataResult!TotalGiftCard <> "" Then TotalValuesSales += Global.BODataResult!TotalGiftCard
  41.                  If Global.BODataResult!TotalAccounts <> "" Then TotalValuesSales += Global.BODataResult!TotalAccounts
  42.            
  43.                If Global.BODataResult!TotalCashRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalCashRefund
  44.              If Global.BODataResult!TotalChequeRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalChequeRefund
  45.                If Global.BODataResult!TotalCardRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalCardRefund
  46.              If Global.BODataResult!TotalCouponRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalCouponRefund
  47.         If Global.BODataResult!TotalGiftVoucherRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalGiftVoucherRefund
  48.            If Global.BODataResult!TotalGiftCardRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalGiftCardRefund
  49.            If Global.BODataResult!TotalAccountsRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalAccountsRefund
  50.    
  51.         Return TotalValuesSales & "|" & TotalValuesRefund
  52.     Else
  53.         Return TotalValuesSales & "|" & TotalValuesRefund
  54.     End If
Online now: No Back to the top

Post

Posted
Rating:
#5
cogier
Avatar
Guru
cogier is in the usergroup ‘Guru’
I discovered that you need DrawingArea1.Refresh if the Form is already open.

I modified the code in your last post as below:-

Code (gambas)

  1. ' Gambas class file
  2.  
  3. 'Chart As New Chart
  4. 'DataFromDatabase_Sale As New Float[13]
  5. 'DataFromDatabase_Refund As New Float[13]
  6.  
  7. Public Sub Button1_Click()
  8.  
  9.   Dim DataFromDatabase_Sale As Float[] = [0, 2.25, 7.52, 4.25, 78.87, 93.21, 5.2, 5.9, 5.87, 4.7, 11, 12, 4.5]
  10.   Dim DataFromDatabase_Refund As Float[] = [0, 0.2, 0.35, 0.47, 0.4, 1, 2, 0.88, 1, 2, 3, 4, 5]
  11.  
  12.   'If Global.BeepOnKeyPress = "Yes" Then SystemFunctions.PCSpeakerBleep(500)
  13.  
  14.   'GetChartDAta_1  
  15.  
  16.   Chart.CountDataSets = 2
  17.   Chart.Colors.Values = [Color.green, Color.red]
  18.  
  19.   Chart.YAxe.ShowIntervalLines = False
  20.   Chart.ShowLabels = True
  21.   Chart.type = ChartType.ColumnsStacked
  22.   Chart.Legend.Title = "Legend"
  23.   Chart.Legend.Visible = True
  24.   Chart.Legend.Position = Align.Right
  25.   Chart.headers.values = ["Sales", "Refunds"]
  26.   Chart.CountDataSets = 12
  27.   Chart[0].Text = "Month"
  28.   Chart[0].values = [12, 0]
  29.   Chart.FirstColumnAsLabel = True
  30.  
  31.   Chart[0].Values = [DataFromDatabase_Sale[1], DataFromDatabase_Sale[2], DataFromDatabase_Sale[3], DataFromDatabase_Sale[4], DataFromDatabase_Sale[5], DataFromDatabase_Sale[6], DataFromDatabase_Sale[7], DataFromDatabase_Sale[8], DataFromDatabase_Sale[9], DataFromDatabase_Sale[10], DataFromDatabase_Sale[11], DataFromDatabase_Sale[12]]
  32.   Chart[1].Values = [DataFromDatabase_Refund[2], DataFromDatabase_Refund[3], DataFromDatabase_Refund[4], DataFromDatabase_Refund[5], DataFromDatabase_Refund[6], DataFromDatabase_Refund[7], DataFromDatabase_Refund[8], DataFromDatabase_Refund[9], DataFromDatabase_Refund[10], DataFromDatabase_Refund[11], DataFromDatabase_Refund[12]]
  33.  
  34.   DrawingArea1.Refresh ''You seem to need this if the Form is already open
  35.  
  37.  
  38. Public Sub DrawingArea1_Draw()
  39.  
  40.   Dim iHeight, iWidth As Integer
  41.  
  42.   If Chart.Count > 0 Then
  43.    
  44.     iHeight = DrawingArea1.Height - 20
  45.     iWidth = DrawingArea1.Width - 10
  46.    
  47.     If iHeight < 10 Then iHeight = 10
  48.     If iWidth < 10 Then iWidth = 10
  49.    
  50.     Chart.Height = iHeight
  51.     Chart.Width = iWidth
  52.     Chart.Draw
  53.   End If
  54.  
Online now: No Back to the top

Post

Posted
Rating:
#6
AndyGable
Enthusiast
AndyGable is in the usergroup ‘Enthusiast’

cogier said

I discovered that you need DrawingArea1.Refresh if the Form is already open.

I modified the code in your last post as below:-

Hi Cogier,

I update my code and if I  use your example data it works perfectly but If I try to use data from my database I just a blank chart.

below is the updated code that I use to generate the chart

Code (gambas)

  1. Dim DataFromDatabase_Sale As Float[] = [0, GetChartDAta_1("Sale", 1), GetChartDAta_1("Sale", 2),
  2.     GetChartDAta_1("Sale", 3), GetChartDAta_1("Sale", 4), GetChartDAta_1("Sale", 5),
  3.     GetChartDAta_1("Sale", 6), GetChartDAta_1("Sale", 7), GetChartDAta_1("Sale", 8),
  4.     GetChartDAta_1("Sale", 9), GetChartDAta_1("Sale", 10), GetChartDAta_1("Sale", 11),
  5.     GetChartDAta_1("Sale", 12)]
  6.      
  7.     Dim DataFromDatabase_Refund As Float[] = [0, GetChartDAta_1("Refund", 1), GetChartDAta_1("Refund", 2),
  8.     GetChartDAta_1("Refund", 3), GetChartDAta_1("Refund", 4), GetChartDAta_1("Refund", 5),
  9.     GetChartDAta_1("Refund", 6), GetChartDAta_1("Refund", 7), GetChartDAta_1("Refund", 8),
  10.     GetChartDAta_1("Refund", 9), GetChartDAta_1("Refund", 10), GetChartDAta_1("Refund", 11),
  11.     GetChartDAta_1("Refund", 12)]
  12.  
  13.     Chart.CountDataSets = 2
  14.     Chart.Colors.Values = [Color.green, Color.red]
  15.    
  16.     Chart.YAxe.ShowIntervalLines = False
  17.     Chart.ShowLabels = True
  18.     Chart.type = ChartType.ColumnsStacked
  19.     Chart.Legend.Title = "Legend"
  20.     Chart.Legend.Visible = True
  21.     Chart.Legend.Position = Align.Right
  22.     Chart.headers.values = ["Sales", "Refunds"]
  23.     Chart.CountDataSets = 12
  24.     Chart[0].Text = "Month"
  25.     Chart[0].values = [12, 0]
  26.     Chart.FirstColumnAsLabel = True
  27.    
  28.     Chart[0].Values = [DataFromDatabase_Sale[1], DataFromDatabase_Sale[2], DataFromDatabase_Sale[3], DataFromDatabase_Sale[4], DataFromDatabase_Sale[5], DataFromDatabase_Sale[6], DataFromDatabase_Sale[7], DataFromDatabase_Sale[8], DataFromDatabase_Sale[9], DataFromDatabase_Sale[10], DataFromDatabase_Sale[11], DataFromDatabase_Sale[12]]
  29.     Chart[1].Values = [DataFromDatabase_Refund[2], DataFromDatabase_Refund[3], DataFromDatabase_Refund[4], DataFromDatabase_Refund[5], DataFromDatabase_Refund[6], DataFromDatabase_Refund[7], DataFromDatabase_Refund[8], DataFromDatabase_Refund[9], DataFromDatabase_Refund[10], DataFromDatabase_Refund[11], DataFromDatabase_Refund[12]]
  30.    
  31.     DrawingArea1.Refresh ''You seem to need this if the Form is already open  
  32.  

I have updated the GetChartData_1 and this is below

Code (gambas)

  1. Private Sub GetChartData_1(Mode As String, MonthNumber As Integer) As Float
  2.   Dim DataFromModule As String = Null
  3.   Dim DataToSplit As New String[1]
  4.  
  5.     DataFromModule = BackOffice_Dashboard_Charts.GetMonthlyTakingsFromDatabase(MonthNumber)
  6.     DataToSplit = Split(DataFromModule, "|")
  7.  
  8.     Select Mode
  9.         Case "Sale"
  10.             Return DataToSplit[0]
  11.        
  12.         Case "Refund"
  13.             Return DataToSplit[1]
  14.     End Select

I have also updted the Sub for getting the data from the Database

Code (gambas)

  1. Public Function GetMonthlyTakingsFromDatabase(MonthValue As Integer) As String
  2.      Dim TotalValuesSales As Integer = 0
  3.     Dim TotalValuesRefund As Integer = 0
  4.          Dim RequestedDate As String = Null
  5.       Dim RequestedEndDate As String = Null
  6.  
  7.               RequestedDate = Format(Now, "yyyy") & "/" & Format(MonthValue, "00") & "/01"
  8.            RequestedEndDate = Format(Now, "yyyy") & "/" & Format(MonthValue, "00") & "/31"
  9.  
  10.      Global.BackOfficeQuery = Null
  11.     Global.BackOfficeQuery &= "Select "
  12.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(cashvalue / 100),0)) as DECIMAL(18,2)) as TotalCash, "
  13.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(chequevalue / 100),0)) as DECIMAL(18,2)) as TotalCheque, "
  14.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(cardvalue / 100),0)) as DECIMAL(18,2)) as TotalCard, "
  15.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(couponvalue / 100),0)) as DECIMAL(18,2)) as TotalCoupon, "
  16.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(giftvouchervalue / 100),0)) as DECIMAL(18,2)) as TotalGiftVoucher, "
  17.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(giftcardvalue / 100),0)) as DECIMAL(18,2)) as TotalGiftCard, "
  18.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(accountvalue / 100),0)) as DECIMAL(18,2)) as TotalAccounts, "
  19.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(safedropvalue / 100),0)) as DECIMAL(18,2)) as TotalSafeDrop, "
  20.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(cashvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalCashRefund, "
  21.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(chequevalue_refund / 100),0)) as DECIMAL(18,2)) as TotalChequeRefund, "
  22.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(cardvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalCardRefund, "
  23.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(couponvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalCouponRefund, "
  24.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(giftvouchervalue_refund / 100),0)) as DECIMAL(18,2)) as TotalGiftVoucherRefund, "
  25.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(giftcardvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalGiftCardRefund, "
  26.     Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(accountvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalAccountsRefund "
  27.     Global.BackOfficeQuery &= "from posfigures "
  28.     Global.BackOfficeQuery &= "where datecol BETWEEN '" & RequestedDate & "' and '"    
  29.     Global.BackOfficeQuery &= RequestedEndDate & "';"
  30.  
  31.     BackofficeDatabase.ConnectToBackOfficeDatabase("Open")
  32.     Global.BODataResult = Global.$DBBackOfficeCon.Exec(Global.BackOfficeQuery)  
  33.  
  34.     If Global.BODataResult.Available = True Then
  35.         TotalValuesSales += Global.BODataResult!TotalCash
  36.         TotalValuesSales += Global.BODataResult!TotalCheque
  37.         TotalValuesSales += Global.BODataResult!TotalCard
  38.         TotalValuesSales += Global.BODataResult!TotalCoupon
  39.         TotalValuesSales += Global.BODataResult!TotalGiftVoucher
  40.         TotalValuesSales += Global.BODataResult!TotalGiftCard
  41.         TotalValuesSales += Global.BODataResult!TotalAccounts
  42.            
  43.         TotalValuesRefund += Global.BODataResult!TotalCashRefund
  44.         TotalValuesRefund += Global.BODataResult!TotalChequeRefund
  45.         TotalValuesRefund += Global.BODataResult!TotalCardRefund
  46.         TotalValuesRefund += Global.BODataResult!TotalCouponRefund
  47.         TotalValuesRefund += Global.BODataResult!TotalGiftVoucherRefund
  48.         TotalValuesRefund += Global.BODataResult!TotalGiftCardRefund
  49.         TotalValuesRefund += Global.BODataResult!TotalAccountsRefund
  50.         Return TotalValuesSales & "|" & TotalValuesRefund
  51.     End If

I can not see where the issue is with the retrieval of the data (the data is store in the database as string but the sql request converts it into float for use on the system)
Online now: No Back to the top

Post

Posted
Rating:
#7
cogier
Avatar
Guru
cogier is in the usergroup ‘Guru’
I'm sorry, but I am no expert on Databases. I can only assume that the data received is not to the liking of Gambas. I suggest you put a 'Stop' as below and then highlight the variable name and see what's in there.

Code (gambas)

  1. Dim DataFromDatabase_Sale As Float[] = [0, GetChartDAta_1("Sale", 1), GetChartDAta_1("Sale", 2),
  2.     GetChartDAta_1("Sale", 3), GetChartDAta_1("Sale", 4), GetChartDAta_1("Sale", 5),
  3.     GetChartDAta_1("Sale", 6), GetChartDAta_1("Sale", 7), GetChartDAta_1("Sale", 8),
  4.     GetChartDAta_1("Sale", 9), GetChartDAta_1("Sale", 10), GetChartDAta_1("Sale", 11),
  5.     GetChartDAta_1("Sale", 12)]
  6.      
  7. Dim DataFromDatabase_Refund As Float[] = [0, GetChartDAta_1("Refund", 1), GetChartDAta_1("Refund", 2),
  8.     GetChartDAta_1("Refund", 3), GetChartDAta_1("Refund", 4), GetChartDAta_1("Refund", 5),
  9.     GetChartDAta_1("Refund", 6), GetChartDAta_1("Refund", 7), GetChartDAta_1("Refund", 8),
  10.     GetChartDAta_1("Refund", 9), GetChartDAta_1("Refund", 10), GetChartDAta_1("Refund", 11),
  11.     GetChartDAta_1("Refund", 12)]
  12.  
  13.   Stop
Online now: No Back to the top

Post

Posted
Rating:
#8
sholzy
Avatar
Administrator
sholzy is in the usergroup ‘unknown’
 Anytime I'm dealing with database issues,  I use 'db.Debug = True' to watch any database query.
sholzy
Gambas One Site Director

To report bugs in the Gambas IDE:
Official Gambas Bug Tracker
Online now: No Back to the top

Post

Posted
Rating:
#9
AndyGable
Enthusiast
AndyGable is in the usergroup ‘Enthusiast’

Got2BeFree said

Anytime I'm dealing with database issues,  I use 'db.Debug = True' to watch any database query.

A couple of updates that I though you all would like to know

Fist off @Gpt2BeFree I did not know that db.debug option that is a much quicker way to view the SQL Commands I am send then saving them to a text file and reading it in latter

I found that I was using Integer in the database recall function so I changed that to float and now as you can see from the image below i have it working using columns
<IMG src="https://support.algpos.co.uk/images_help/Graph1.png"> </IMG>

but I can not get it to work using lines (unless I am using the wrong option)
<IMG src="https://support.algpos.co.uk/images_help/Graph2.png"> </IMG>

I would like it to look like this (if this option is in the charts then yay if not then I would have to settle for something close)
<IMG src="https://support.algpos.co.uk/images_help/Graph3.png"> </IMG>

Also does anyone know how I can get the values along the side to show up? (example my biggest value at the moment is £1447.98 and I would like to show that on the side)

Also another quick question if i Have already displayed a chart on screen how would i display another ones (example I have now a customer count chart but if I have the tender chart and then I click the Customer count chart i get under "chart.CountDataSets = 2"

Type mismatch: wanted number, got object[] instead (frmChartReport:98) (and chart.CountDataSets = 2 is on line 98)
Online now: No Back to the top

Post

Posted
Rating:
#10
sholzy
Avatar
Administrator
sholzy is in the usergroup ‘unknown’

AndyGable said

Got2BeFree said

Anytime I'm dealing with database issues,  I use 'db.Debug = True' to watch any database query.

A couple of updates that I though you all would like to know

Fist off @Gpt2BeFree I did not know that db.debug option that is a much quicker way to view the SQL Commands I am send then saving them to a text file and reading it in latter

I found that I was using Integer in the database recall function so I changed that to float and now as you can see from the image below i have it working using columns
…
[snipped]
…
Also does anyone know how I can get the values along the side to show up? (example my biggest value at the moment is £1447.98 and I would like to show that on the side)

Glad that was of some help. I usually have 'db.Debug = True' at the beginning of a block I need to watch and 'db.Debug = False' at the end and I'll usually just comment 'db.Debug = True' out when not needed. Most of my projects have it buried somewhere in at least a few different places.

Can't help out on the chart problem since I've never had an occasion to use it.
sholzy
Gambas One Site Director

To report bugs in the Gambas IDE:
Official Gambas Bug Tracker
Online now: No Back to the top

Post

Posted
Rating:
#11
AndyGable
Enthusiast
AndyGable is in the usergroup ‘Enthusiast’

Got2BeFree said

AndyGable said

Got2BeFree said

Anytime I'm dealing with database issues,  I use 'db.Debug = True' to watch any database query.

A couple of updates that I though you all would like to know

Fist off @Gpt2BeFree I did not know that db.debug option that is a much quicker way to view the SQL Commands I am send then saving them to a text file and reading it in latter

I found that I was using Integer in the database recall function so I changed that to float and now as you can see from the image below i have it working using columns
…
[snipped]
…
Also does anyone know how I can get the values along the side to show up? (example my biggest value at the moment is £1447.98 and I would like to show that on the side)

Glad that was of some help. I usually have 'db.Debug = True' at the beginning of a block I need to watch and 'db.Debug = False' at the end and I'll usually just comment 'db.Debug = True' out when not needed. Most of my projects have it buried somewhere in at least a few different places.

Can't help out on the chart problem since I've never had an occasion to use it.


You can be sure I will be using that more often now so I can make sure my SQL Commands are correct :D
Online now: No Back to the top
1 guest and 0 members have just viewed this.