fiogf49gjkf0d Hi all
SLX LAN v7.2.1
I have a datagrid whose data (CustomerID, ItemNumber, ReportYear, Eaches) is obtained from myTableA. The end user selects 2 years from to comboboxes (cmbYear1 and cmbYear2). I can make the datagrid show the data like this:
CustomerID---ItemNumber---ReportedYear---Eaches
ABC---123---2010---50
ABC---123---2011---25
ABC---456---2010---15
ABC---456---2011---75
How can I get it to do this:
CustomerID---ItemNumber---2010---2011
ABC---123---50---25
ABC---456---15---75
It will basically put the eaches for the 2 years as columns. We want to be able to see the eaches for the 2 years they choose side by side. Any idea how I can do this?
Below is the code I used to build the datagrid for the first example.
<p>Dim strSQL_Yearly
Dim col_Yearly
Dim i_Yearly
With Datagrid1
strSQL_Yearly = "SELECT sysdba.myTableA.CustomerID, sysdba.myTableA.ItemNumber, sysdba.myTableA.REPORTEDYEAR , SUM(sysdba.myTableA.EACHES) AS Total_Eaches FROM sysdba.myTableA WHERE (sysdba.myTableA.REPORTEDYEAR = '" & cmbYear1.Text & "') OR (sysdba.myTableA.REPORTEDYEAR = '" & cmbYear2.Text & "') GROUP BY sysdba.myTableA.CustomerID, sysdba.myTableA.ItemNumber, sysdba.myTableA.REPORTEDYEAR"
.SQL.Text = strSQL_Yearly
With .Columns
If (.Count > 0) Then
For i_Yearly = 0 To .Count - 1
.Item(0).Delete
Next
End If
Set col_Yearly = .Add(0)
col_Yearly.FieldName = "CustomerID"
col_Yearly.Caption = "Customer Number"
col_Yearly.Readonly = True
col_Yearly.Width = 90
col_Yearly.Alignment = 2
col_Yearly.Alignment = 2
col_Yearly.HeaderAlignment = 2
Set col_Yearly = .Add(0)
col_Yearly.FieldName = "ItemNumber"
col_Yearly.Caption = "Item Number"
col_Yearly.Readonly = True
col_Yearly.Width = 90
col_Yearly.Alignment = 2
col_Yearly.HeaderAlignment = 2
Set col_Yearly = .Add(0)
col_Yearly.FieldName = "ReportedYear"
col_Yearly.Caption = "Year"
col_Yearly.Readonly = True
col_Yearly.Width = 90
col_Yearly.Alignment = 2
col_Yearly.HeaderAlignment = 2
Set col_Yearly = .Add(0)
col_Yearly.FieldName = "Total_Eaches"
col_Yearly.Caption = "Eaches"
col_Yearly.Readonly = True
col_Yearly.Width = 90
col_Yearly.Alignment = 2
col_Yearly.HeaderAlignment = 2
End With
.Refresh
End With |