Chart Usage Advice

Post your Gambas programming questions here.
AndyGable
Posts: 390
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Chart Usage Advice

Post by AndyGable »

Hi Everyone,

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

I have been using post 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

    hChart1.Colors.Values = [Color.green, Color.red]
    DrawingArea1.Refresh
    hChart1.YAxe.ShowIntervalLines = False
    hChart1.ShowLabels = True
  
    hChart1.type = ChartType.ColumnsStacked
    hChart1.Legend.Title = "Legend"
    hChart1.Legend.Visible = True
    hChart1.Legend.Position = Align.Right
    '
    hChart1.headers.values = ["Sales", "Refunds"]
    hChart1.CountDataSets = 12
  
    hChart1[0].Text = "Month"
    hChart1[0].values = [12, 0]
    hChart1.FirstColumnAsLabel = True

      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]]
    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]]


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: Select all

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

Image

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
User avatar
cogier
Site Admin
Posts: 1160
Joined: Wednesday 21st September 2016 2:22pm
Location: Guernsey, Channel Islands

Re: Chart Usage Advice

Post by cogier »

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.

DrawingArea1 As DrawingArea

Public Sub Form_Open()
  
  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]
  Dim DataFromDatabase_Refund As Float[] = [0, 0.2, 0.35, 0.47, 0.4, 1, 2, 0.88, 1, 2, 3, 4, 5]
  
  BuildForm
  
  Chart.CountDataSets = 2 
  Chart.Colors.Values = [Color.green, Color.red]
  
  Chart.YAxe.ShowIntervalLines = False
  Chart.ShowLabels = True
  Chart.type = ChartType.ColumnsStacked
  Chart.Legend.Title = "Legend"
  Chart.Legend.Visible = True
  Chart.Legend.Position = Align.Right
  Chart.headers.values = ["Sales", "Refunds"]
  Chart.CountDataSets = 12
  Chart[0].Text = "Month"
  Chart[0].values = [12, 0]
  Chart.FirstColumnAsLabel = True
  
  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]]
  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]]
  
End

Public Sub DrawingArea1_Draw()
  
  Dim iHeight, iWidth As Integer
  
  iHeight = DrawingArea1.Height - 20
  iWidth = DrawingArea1.Width - 10
  If iHeight < 10 Then iHeight = 10
  If iWidth < 10 Then iWidth = 10
  
  Chart.Height = iHeight
  Chart.Width = iWidth
  Chart.Draw
  
End

Public Sub BuildForm()
  
  With Me
    .H = 512
    .W = 1024
    .Arrangement = Arrange.Vertical
    .Padding = 5
  End With
  
  DrawingArea1 = New DrawingArea(Me) As "DrawingArea1"
  DrawingArea1.Expand = True
  
End


Image
AndyGable
Posts: 390
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Re: Chart Usage Advice

Post by AndyGable »

@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.
AndyGable
Posts: 390
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Re: Chart Usage Advice

Post by AndyGable »

cogier wrote: Monday 5th August 2024 2:25pm 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 :(

' Gambas class file

                      Chart As New Chart
      DataFromDatabase_Sale As New Float[13] 
    DataFromDatabase_Refund As New Float[13] 

Public Sub btnReturnToMainMenu_Click()
    If Global.BeepOnKeyPress = "Yes" Then SystemFunctions.PCSpeakerBleep(500)
    Me.Close
    frmSiteFunctionMenu.Close
    frmbackground.Workspace1.Add(frmDashBoard, 0) 
End

Public Sub Button1_Click()
  If Global.BeepOnKeyPress = "Yes" Then SystemFunctions.PCSpeakerBleep(500)
  
  GetChartDAta_1  
   
  Chart.CountDataSets = 2 
  Chart.Colors.Values = [Color.green, Color.red]
   
  Chart.YAxe.ShowIntervalLines = False
  Chart.ShowLabels = True
  Chart.type = ChartType.ColumnsStacked
  Chart.Legend.Title = "Legend"
  Chart.Legend.Visible = True
  Chart.Legend.Position = Align.Right
  Chart.headers.values = ["Sales", "Refunds"]
  Chart.CountDataSets = 12
  Chart[0].Text = "Month"
  Chart[0].values = [12, 0]
  Chart.FirstColumnAsLabel = True
   
  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]]
  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]]
   
End
 
Public Sub DrawingArea1_Draw()
  Dim iHeight, iWidth As Integer
  
    If Chart.Count > 0 Then
        iHeight = DrawingArea1.Height - 20
        iWidth = DrawingArea1.Width - 10
  
        If iHeight < 10 Then iHeight = 10
        If iWidth < 10 Then iWidth = 10
     
        Chart.Height = iHeight
        Chart.Width = iWidth
        Chart.Draw
    End If
End
 
Private Sub GetChartDAta_1()
  Dim DataFromModule As String = Null
  Dim DataToSplit As New String[1] 
  Dim i As Integer

  For i = 1 To 12 
    DataFromModule = BackOffice_Dashboard_Charts.GetMonthlyTakingsFromDatabase(i)
    DataToSplit = Split(DataFromModule, "|")
          DataFromDatabase_Sale[i] = DataToSplit[0]
        DataFromDatabase_Refund[i] = DataToSplit[1]
          DataFromDatabase_Sale[i] = Format((DataFromDatabase_Sale[i] / 100), "0.00")
        DataFromDatabase_Refund[i] = Format((DataFromDatabase_Refund[i] / 100), "0.00")
    Debug DataFromDatabase_Sale[i], DataFromDatabase_Refund[i]
  Next
End


This is the Function form the BackOffice_Dashboard_Charts.GetMonthlyTakingsFromDatabase()

Public Function GetMonthlyTakingsFromDatabase(MonthValue As Integer) As String
     Dim TotalValuesSales As Integer = 0
    Dim TotalValuesRefund As Integer = 0
         Dim RequestedDate As String = Null
      Dim RequestedEndDate As String = Null

              RequestedDate = Format(Now, "yyyy") & "/" & Format(MonthValue, "00") & "/01" 
           RequestedEndDate = Format(Now, "yyyy") & "/" & Format(MonthValue, "00") & "/31" 

     Global.BackOfficeQuery = Null
    Global.BackOfficeQuery &= "Select "
    Global.BackOfficeQuery &= "abs(SUM(cashvalue)) as TotalCash, "
    Global.BackOfficeQuery &= "abs(SUM(chequevalue)) as TotalCheque, "
    Global.BackOfficeQuery &= "abs(SUM(cardvalue)) as TotalCard, "
    Global.BackOfficeQuery &= "abs(SUM(couponvalue)) as TotalCoupon, "
    Global.BackOfficeQuery &= "abs(SUM(giftvouchervalue)) as TotalGiftVoucher, "
    Global.BackOfficeQuery &= "abs(SUM(giftcardvalue)) as TotalGiftCard, "
    Global.BackOfficeQuery &= "abs(SUM(accountvalue)) as TotalAccounts, "
    Global.BackOfficeQuery &= "abs(SUM(safedropvalue)) as TotalSafeDrop, "
    Global.BackOfficeQuery &= "abs(SUM(cashvalue_refund)) as TotalCashRefund, "
    Global.BackOfficeQuery &= "abs(SUM(chequevalue_refund)) as TotalChequeRefund, "
    Global.BackOfficeQuery &= "abs(SUM(cardvalue_refund)) as TotalCardRefund, "
    Global.BackOfficeQuery &= "abs(SUM(couponvalue_refund)) as TotalCouponRefund, "
    Global.BackOfficeQuery &= "abs(SUM(giftvouchervalue_refund)) as TotalGiftVoucherRefund, "
    Global.BackOfficeQuery &= "abs(SUM(giftcardvalue_refund)) as TotalGiftCardRefund, "
    Global.BackOfficeQuery &= "abs(SUM(accountvalue_refund)) as TotalAccountsRefund "
    Global.BackOfficeQuery &= "from posfigures "
    Global.BackOfficeQuery &= "where datecol BETWEEN '" & RequestedDate & "' and '"    
    Global.BackOfficeQuery &= RequestedEndDate & "';"

    BackofficeDatabase.ConnectToBackOfficeDatabase("Open")
    Global.BODataResult = Global.$DBBackOfficeCon.Exec(Global.BackOfficeQuery)  

    If Global.BODataResult.Available = True Then
                     If Global.BODataResult!TotalCash <> "" Then TotalValuesSales += Global.BODataResult!TotalCash
                   If Global.BODataResult!TotalCheque <> "" Then TotalValuesSales += Global.BODataResult!TotalCheque
                     If Global.BODataResult!TotalCard <> "" Then TotalValuesSales += Global.BODataResult!TotalCard
                   If Global.BODataResult!TotalCoupon <> "" Then TotalValuesSales += Global.BODataResult!TotalCoupon
              If Global.BODataResult!TotalGiftVoucher <> "" Then TotalValuesSales += Global.BODataResult!TotalGiftVoucher
                 If Global.BODataResult!TotalGiftCard <> "" Then TotalValuesSales += Global.BODataResult!TotalGiftCard
                 If Global.BODataResult!TotalAccounts <> "" Then TotalValuesSales += Global.BODataResult!TotalAccounts
           
               If Global.BODataResult!TotalCashRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalCashRefund
             If Global.BODataResult!TotalChequeRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalChequeRefund
               If Global.BODataResult!TotalCardRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalCardRefund
             If Global.BODataResult!TotalCouponRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalCouponRefund
        If Global.BODataResult!TotalGiftVoucherRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalGiftVoucherRefund
           If Global.BODataResult!TotalGiftCardRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalGiftCardRefund
           If Global.BODataResult!TotalAccountsRefund <> "" Then TotalValuesRefund += Global.BODataResult!TotalAccountsRefund
    
        Return TotalValuesSales & "|" & TotalValuesRefund
    Else 
        Return TotalValuesSales & "|" & TotalValuesRefund
    End If
End
User avatar
cogier
Site Admin
Posts: 1160
Joined: Wednesday 21st September 2016 2:22pm
Location: Guernsey, Channel Islands

Re: Chart Usage Advice

Post by cogier »

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

I modified the code in your last post as below:-
' Gambas class file

'Chart As New Chart
'DataFromDatabase_Sale As New Float[13] 
'DataFromDatabase_Refund As New Float[13] 

Public Sub Button1_Click()
  
  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]
  Dim DataFromDatabase_Refund As Float[] = [0, 0.2, 0.35, 0.47, 0.4, 1, 2, 0.88, 1, 2, 3, 4, 5]
  
  'If Global.BeepOnKeyPress = "Yes" Then SystemFunctions.PCSpeakerBleep(500)
  
  'GetChartDAta_1  
  
  Chart.CountDataSets = 2 
  Chart.Colors.Values = [Color.green, Color.red]
  
  Chart.YAxe.ShowIntervalLines = False
  Chart.ShowLabels = True
  Chart.type = ChartType.ColumnsStacked
  Chart.Legend.Title = "Legend"
  Chart.Legend.Visible = True
  Chart.Legend.Position = Align.Right
  Chart.headers.values = ["Sales", "Refunds"]
  Chart.CountDataSets = 12
  Chart[0].Text = "Month"
  Chart[0].values = [12, 0]
  Chart.FirstColumnAsLabel = True
  
  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]]
  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]]
  
  DrawingArea1.Refresh ''You seem to need this if the Form is already open
  
End

Public Sub DrawingArea1_Draw()
  
  Dim iHeight, iWidth As Integer
  
  If Chart.Count > 0 Then
    
    iHeight = DrawingArea1.Height - 20
    iWidth = DrawingArea1.Width - 10
    
    If iHeight < 10 Then iHeight = 10
    If iWidth < 10 Then iWidth = 10
    
    Chart.Height = iHeight
    Chart.Width = iWidth
    Chart.Draw
  End If
  
End
AndyGable
Posts: 390
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Re: Chart Usage Advice

Post by AndyGable »

cogier wrote: Thursday 8th August 2024 2:27pm 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
Dim DataFromDatabase_Sale As Float[] = [0, GetChartDAta_1("Sale", 1), GetChartDAta_1("Sale", 2),
    GetChartDAta_1("Sale", 3), GetChartDAta_1("Sale", 4), GetChartDAta_1("Sale", 5), 
    GetChartDAta_1("Sale", 6), GetChartDAta_1("Sale", 7), GetChartDAta_1("Sale", 8), 
    GetChartDAta_1("Sale", 9), GetChartDAta_1("Sale", 10), GetChartDAta_1("Sale", 11), 
    GetChartDAta_1("Sale", 12)]
     
    Dim DataFromDatabase_Refund As Float[] = [0, GetChartDAta_1("Refund", 1), GetChartDAta_1("Refund", 2),
    GetChartDAta_1("Refund", 3), GetChartDAta_1("Refund", 4), GetChartDAta_1("Refund", 5), 
    GetChartDAta_1("Refund", 6), GetChartDAta_1("Refund", 7), GetChartDAta_1("Refund", 8), 
    GetChartDAta_1("Refund", 9), GetChartDAta_1("Refund", 10), GetChartDAta_1("Refund", 11), 
    GetChartDAta_1("Refund", 12)]
 
    Chart.CountDataSets = 2 
    Chart.Colors.Values = [Color.green, Color.red]
   
    Chart.YAxe.ShowIntervalLines = False
    Chart.ShowLabels = True
    Chart.type = ChartType.ColumnsStacked
    Chart.Legend.Title = "Legend"
    Chart.Legend.Visible = True
    Chart.Legend.Position = Align.Right
    Chart.headers.values = ["Sales", "Refunds"]
    Chart.CountDataSets = 12
    Chart[0].Text = "Month"
    Chart[0].values = [12, 0]
    Chart.FirstColumnAsLabel = True
   
    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]]
    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]]
   
    DrawingArea1.Refresh ''You seem to need this if the Form is already open   


I have updated the GetChartData_1 and this is below

Private Sub GetChartData_1(Mode As String, MonthNumber As Integer) As Float
  Dim DataFromModule As String = Null
  Dim DataToSplit As New String[1] 

    DataFromModule = BackOffice_Dashboard_Charts.GetMonthlyTakingsFromDatabase(MonthNumber)
    DataToSplit = Split(DataFromModule, "|")

    Select Mode
        Case "Sale"
            Return DataToSplit[0]
        
        Case "Refund"
            Return DataToSplit[1]
    End Select
End


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

Public Function GetMonthlyTakingsFromDatabase(MonthValue As Integer) As String
     Dim TotalValuesSales As Integer = 0
    Dim TotalValuesRefund As Integer = 0
         Dim RequestedDate As String = Null
      Dim RequestedEndDate As String = Null

              RequestedDate = Format(Now, "yyyy") & "/" & Format(MonthValue, "00") & "/01" 
           RequestedEndDate = Format(Now, "yyyy") & "/" & Format(MonthValue, "00") & "/31" 

     Global.BackOfficeQuery = Null
    Global.BackOfficeQuery &= "Select "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(cashvalue / 100),0)) as DECIMAL(18,2)) as TotalCash, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(chequevalue / 100),0)) as DECIMAL(18,2)) as TotalCheque, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(cardvalue / 100),0)) as DECIMAL(18,2)) as TotalCard, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(couponvalue / 100),0)) as DECIMAL(18,2)) as TotalCoupon, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(giftvouchervalue / 100),0)) as DECIMAL(18,2)) as TotalGiftVoucher, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(giftcardvalue / 100),0)) as DECIMAL(18,2)) as TotalGiftCard, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(accountvalue / 100),0)) as DECIMAL(18,2)) as TotalAccounts, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(safedropvalue / 100),0)) as DECIMAL(18,2)) as TotalSafeDrop, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(cashvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalCashRefund, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(chequevalue_refund / 100),0)) as DECIMAL(18,2)) as TotalChequeRefund, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(cardvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalCardRefund, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(couponvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalCouponRefund, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(giftvouchervalue_refund / 100),0)) as DECIMAL(18,2)) as TotalGiftVoucherRefund, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(giftcardvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalGiftCardRefund, "
    Global.BackOfficeQuery &= "cast(abs(IFNULL(SUM(accountvalue_refund / 100),0)) as DECIMAL(18,2)) as TotalAccountsRefund "
    Global.BackOfficeQuery &= "from posfigures "
    Global.BackOfficeQuery &= "where datecol BETWEEN '" & RequestedDate & "' and '"    
    Global.BackOfficeQuery &= RequestedEndDate & "';"

    BackofficeDatabase.ConnectToBackOfficeDatabase("Open")
    Global.BODataResult = Global.$DBBackOfficeCon.Exec(Global.BackOfficeQuery)  

    If Global.BODataResult.Available = True Then
        TotalValuesSales += Global.BODataResult!TotalCash
        TotalValuesSales += Global.BODataResult!TotalCheque
        TotalValuesSales += Global.BODataResult!TotalCard
        TotalValuesSales += Global.BODataResult!TotalCoupon
        TotalValuesSales += Global.BODataResult!TotalGiftVoucher
        TotalValuesSales += Global.BODataResult!TotalGiftCard
        TotalValuesSales += Global.BODataResult!TotalAccounts
           
        TotalValuesRefund += Global.BODataResult!TotalCashRefund
        TotalValuesRefund += Global.BODataResult!TotalChequeRefund
        TotalValuesRefund += Global.BODataResult!TotalCardRefund
        TotalValuesRefund += Global.BODataResult!TotalCouponRefund
        TotalValuesRefund += Global.BODataResult!TotalGiftVoucherRefund
        TotalValuesRefund += Global.BODataResult!TotalGiftCardRefund
        TotalValuesRefund += Global.BODataResult!TotalAccountsRefund
        Return TotalValuesSales & "|" & TotalValuesRefund
    End If
End 


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)
User avatar
cogier
Site Admin
Posts: 1160
Joined: Wednesday 21st September 2016 2:22pm
Location: Guernsey, Channel Islands

Re: Chart Usage Advice

Post by cogier »

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.

Dim DataFromDatabase_Sale As Float[] = [0, GetChartDAta_1("Sale", 1), GetChartDAta_1("Sale", 2),
    GetChartDAta_1("Sale", 3), GetChartDAta_1("Sale", 4), GetChartDAta_1("Sale", 5), 
    GetChartDAta_1("Sale", 6), GetChartDAta_1("Sale", 7), GetChartDAta_1("Sale", 8), 
    GetChartDAta_1("Sale", 9), GetChartDAta_1("Sale", 10), GetChartDAta_1("Sale", 11), 
    GetChartDAta_1("Sale", 12)]
      
Dim DataFromDatabase_Refund As Float[] = [0, GetChartDAta_1("Refund", 1), GetChartDAta_1("Refund", 2),
    GetChartDAta_1("Refund", 3), GetChartDAta_1("Refund", 4), GetChartDAta_1("Refund", 5), 
    GetChartDAta_1("Refund", 6), GetChartDAta_1("Refund", 7), GetChartDAta_1("Refund", 8), 
    GetChartDAta_1("Refund", 9), GetChartDAta_1("Refund", 10), GetChartDAta_1("Refund", 11), 
    GetChartDAta_1("Refund", 12)]
  
  Stop
User avatar
Got2BeFree
Posts: 112
Joined: Saturday 26th November 2016 2:52am
Location: Lost

Re: Chart Usage Advice

Post by Got2BeFree »

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

You may start a journey with certain goals or objectives, but things don’t always turn out the way you expect them to.
AndyGable
Posts: 390
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Re: Chart Usage Advice

Post by AndyGable »

Got2BeFree wrote: Friday 9th August 2024 3:11pm 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
Image

but I can not get it to work using lines (unless I am using the wrong option)
Image

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)
Image

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)
Last edited by AndyGable on Friday 9th August 2024 9:31pm, edited 1 time in total.
User avatar
Got2BeFree
Posts: 112
Joined: Saturday 26th November 2016 2:52am
Location: Lost

Re: Chart Usage Advice

Post by Got2BeFree »

AndyGable wrote: Friday 9th August 2024 3:15pm
Got2BeFree wrote: Friday 9th August 2024 3:11pm 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

You may start a journey with certain goals or objectives, but things don’t always turn out the way you expect them to.
Post Reply