Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    82

    Unanswered: Grouping for segmented reporting

    Hi,

    I have a problem getting segmented aggregate results (sum, average, count) by quarter. Here's the issue:

    I have "cycles" defined by month of account activation: Cycle 1 covers the first month of each Quarter (January, April, July, October), Cycle 2 covers the second month, and Cycle 3 covers the third month. This allows me to group activity report by whether accounts were activated in the first month, 2d month, or 3d month of each Quarter, etc and is to enable account review every three months from their activation start month.

    For example: an account that was activated in February would belong to Cycle 2 and would be reviewed in May, August, and November (and again next February). An account that was activated in June would belong to Cycle 3 and would be reviewed in September, December, March, and June.

    Now, I would like to have summary activity (of monthly sales, units sold, average market share, etc) at the end of each review period for each account belonging to specific cycles. In other words, I would like to have monthly account details for each account belonging to Cycle 1, and a summary for the review months (each account within each cycle). Here's how it would conceptually look:

    Cycle/Account#/MonthStart/SaleMonth/Sale$
    1/001/April/May03/$1000
    1/001/April/June03/$500
    1/001/April/July03/$900
    1/001/April/August03/$1000
    Review Month Summary:
    Cycle 1: August: Total Sales: $3400

    etc etc

    I have the first set up (grouping on cycles, sorted by account# and month of activity), but can't get the second aspect (summary reporting of account details for each of the review months (for Cycle 1 with account activity commencing in January, that would be every three months - April, July, October, and back again next January; for Cycle 2 starting in Feb, it would be May, August, November, February; and Cycle 3 in June, September, December, March).

    Any ideas? Thank you in advance.

    Jabo

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Grouping for segmented reporting

    Originally posted by Jablonski
    Hi,

    I have a problem getting segmented aggregate results (sum, average, count) by quarter. Here's the issue:

    I have "cycles" defined by month of account activation: Cycle 1 covers the first month of each Quarter (January, April, July, October), Cycle 2 covers the second month, and Cycle 3 covers the third month. This allows me to group activity report by whether accounts were activated in the first month, 2d month, or 3d month of each Quarter, etc and is to enable account review every three months from their activation start month.

    For example: an account that was activated in February would belong to Cycle 2 and would be reviewed in May, August, and November (and again next February). An account that was activated in June would belong to Cycle 3 and would be reviewed in September, December, March, and June.

    Now, I would like to have summary activity (of monthly sales, units sold, average market share, etc) at the end of each review period for each account belonging to specific cycles. In other words, I would like to have monthly account details for each account belonging to Cycle 1, and a summary for the review months (each account within each cycle). Here's how it would conceptually look:

    Cycle/Account#/MonthStart/SaleMonth/Sale$
    1/001/April/May03/$1000
    1/001/April/June03/$500
    1/001/April/July03/$900
    1/001/April/August03/$1000
    Review Month Summary:
    Cycle 1: August: Total Sales: $3400

    etc etc

    I have the first set up (grouping on cycles, sorted by account# and month of activity), but can't get the second aspect (summary reporting of account details for each of the review months (for Cycle 1 with account activity commencing in January, that would be every three months - April, July, October, and back again next January; for Cycle 2 starting in Feb, it would be May, August, November, February; and Cycle 3 in June, September, December, March).

    Any ideas? Thank you in advance.

    Jabo
    Basically you're doing a tabular report ... I posted code for a way to do this using ADO recently ...

  3. #3
    Join Date
    Mar 2004
    Posts
    82

    Re: Grouping for segmented reporting

    Originally posted by M Owen
    Basically you're doing a tabular report ... I posted code for a way to do this using ADO recently ...

    Hi,
    I went back several screens (about a dozen) and also searched for "ADO", "tabular report" etc. Couldn't find it - could you send me the exact reference or link?
    Thank you.
    Jabo

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Grouping for segmented reporting

    Originally posted by Jablonski
    Hi,
    I went back several screens (about a dozen) and also searched for "ADO", "tabular report" etc. Couldn't find it - could you send me the exact reference or link?
    Thank you.
    Jabo
    How about the code?

    BTW, this is a way you could do it ... What I do is make a table that's used as a template for the report and write the records out then let the report crunch the numbers ...

    Code:
    Function RetrieveBillingDetail() As Integer
        On Error GoTo Err_RBD
        
        Dim BillMonth As Integer, lcv As Integer
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        ' Clear any previous report values
        SQLString = "DELETE FROM " & RptTbl & ";"
        FormsConnect.Execute SQLString, , adCmdText
        
        ' Retrieve the Dacor Billings
        SQLString = "SELECT [Work Orders II].ContactID, CustomerContacts.[First name] & iif(CustomerContacts.MI & '' <> '', ' ' & CustomerContacts.MI & '. ',' ') & CustomerContacts.[Last name] AS CustomerName, [WO Billing Detail].LaborCharged, [WO Billing Detail].PurchasesCharged, [WO Billing Detail].[Billed Cost], [WO Billing Detail].[Billing Date]"
        SQLString = SQLString & " FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN [WO Billing Detail] ON [Work Orders II].WorkOrderNumber = [WO Billing Detail].WorkOrderNumber"
        SQLString = SQLString & " WHERE (CInt(DatePart('yyyy',[WO Billing Detail].[Billing Date]))=" & RptYear & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Contact ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Zero out the record ...
                For lcv = 2 To 49
                    TrgRecSet.Fields(lcv).Value = 0
                Next
                
                BillMonth = DatePart("m", MyRecSet.Fields(5).Value)
                ' Labor Amount
                If MyRecSet.Fields(2).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 1).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(14).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(15).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(16).Value = MyRecSet.Fields(2).Value
                    TrgRecSet.Fields(17).Value = MyRecSet.Fields(2).Value
                End If
                
                ' Purchases Amount
                If MyRecSet.Fields(3).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 17).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(30).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(31).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(32).Value = MyRecSet.Fields(3).Value
                    TrgRecSet.Fields(33).Value = MyRecSet.Fields(3).Value
                End If
                
                ' Total Billed
                If MyRecSet.Fields(4).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 33).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(46).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(47).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(48).Value = MyRecSet.Fields(4).Value
                    TrgRecSet.Fields(49).Value = MyRecSet.Fields(4).Value
                End If
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        
        ' Retrieve the Cash Payments
    'SELECT [Work Orders II].ContactID, CustomerContacts.[First name], CustomerContacts.MI, CustomerContacts.[Last name], Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate
    'FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber;
        SQLString = "SELECT [Work Orders II].ContactID, CustomerContacts.[First name] & iif(CustomerContacts.MI & '' <> '', ' ' & CustomerContacts.MI & '. ',' ') & CustomerContacts.[Last name] AS CustomerName, Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate"
        SQLString = SQLString & " FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber"
        SQLString = SQLString & " WHERE (CInt(DatePart('yyyy',Payments.ReceivedDate))=" & RptYear & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Contact ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Zero out the record ...
                For lcv = 2 To 49
                    TrgRecSet.Fields(lcv).Value = 0
                Next
                
                BillMonth = DatePart("m", MyRecSet.Fields(5).Value)
                ' Labor Amount
                If MyRecSet.Fields(2).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 1).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(14).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(15).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(16).Value = MyRecSet.Fields(2).Value
                    TrgRecSet.Fields(17).Value = MyRecSet.Fields(2).Value
                End If
                
                ' Purchases Amount
                If MyRecSet.Fields(3).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 17).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(30).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(31).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(32).Value = MyRecSet.Fields(3).Value
                    TrgRecSet.Fields(33).Value = MyRecSet.Fields(3).Value
                End If
                
                ' Total Billed
                If MyRecSet.Fields(4).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 33).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(46).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(47).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(48).Value = MyRecSet.Fields(4).Value
                    TrgRecSet.Fields(49).Value = MyRecSet.Fields(4).Value
                End If
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        LoadingDelay
        
        RetrieveBillingDetail = 1
    Exit_RBD:
        Exit Function
        
    Err_RBD:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveBillingDetail = 0
        Resume Exit_RBD
    End Function

  5. #5
    Join Date
    Mar 2004
    Posts
    82

    Re: Grouping for segmented reporting

    Hi again:

    I need hand-holding here - I am a 2-month-old Access user who has rapidly (though not that rapidly enough to understand ADO) matured...

    What do I need to do exactly? I have a report (that I described) that is based off of a query. The query queries two other queries (one has a record per account, the other is a sales history by month/account/product). The two queries in turn are based on several tables each. I could have designed the report off of the two queries directly without another query, but wanted to have a flat table to base on, which the final query is.

    For the report, I have so far: a tiered grouping (by region, account, "cycle"). Works well, gives me the per cycle/per account sales detail. I need a further grouping that will combine the months comprised in a cycle to give me a "Quarter" summary.

    Hope this helps.

    Jabo





    Originally posted by M Owen
    How about the code?

    BTW, this is a way you could do it ... What I do is make a table that's used as a template for the report and write the records out then let the report crunch the numbers ...

    Code:
    Function RetrieveBillingDetail() As Integer
        On Error GoTo Err_RBD
        
        Dim BillMonth As Integer, lcv As Integer
        Dim FormsConnect As ADODB.Connection
        Dim TrgRecSet As ADODB.Recordset
        
        Set FormsConnect = New ADODB.Connection
        Set TrgRecSet = New ADODB.Recordset
        
        FormsConnect.CursorLocation = adUseClient
        FormsConnect.Open "DSN=Billing Forms;"
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        ' Clear any previous report values
        SQLString = "DELETE FROM " & RptTbl & ";"
        FormsConnect.Execute SQLString, , adCmdText
        
        ' Retrieve the Dacor Billings
        SQLString = "SELECT [Work Orders II].ContactID, CustomerContacts.[First name] & iif(CustomerContacts.MI & '' <> '', ' ' & CustomerContacts.MI & '. ',' ') & CustomerContacts.[Last name] AS CustomerName, [WO Billing Detail].LaborCharged, [WO Billing Detail].PurchasesCharged, [WO Billing Detail].[Billed Cost], [WO Billing Detail].[Billing Date]"
        SQLString = SQLString & " FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN [WO Billing Detail] ON [Work Orders II].WorkOrderNumber = [WO Billing Detail].WorkOrderNumber"
        SQLString = SQLString & " WHERE (CInt(DatePart('yyyy',[WO Billing Detail].[Billing Date]))=" & RptYear & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Contact ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Zero out the record ...
                For lcv = 2 To 49
                    TrgRecSet.Fields(lcv).Value = 0
                Next
                
                BillMonth = DatePart("m", MyRecSet.Fields(5).Value)
                ' Labor Amount
                If MyRecSet.Fields(2).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 1).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(14).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(15).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(16).Value = MyRecSet.Fields(2).Value
                    TrgRecSet.Fields(17).Value = MyRecSet.Fields(2).Value
                End If
                
                ' Purchases Amount
                If MyRecSet.Fields(3).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 17).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(30).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(31).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(32).Value = MyRecSet.Fields(3).Value
                    TrgRecSet.Fields(33).Value = MyRecSet.Fields(3).Value
                End If
                
                ' Total Billed
                If MyRecSet.Fields(4).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 33).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(46).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(47).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(48).Value = MyRecSet.Fields(4).Value
                    TrgRecSet.Fields(49).Value = MyRecSet.Fields(4).Value
                End If
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        
        ' Retrieve the Cash Payments
    'SELECT [Work Orders II].ContactID, CustomerContacts.[First name], CustomerContacts.MI, CustomerContacts.[Last name], Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate
    'FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber;
        SQLString = "SELECT [Work Orders II].ContactID, CustomerContacts.[First name] & iif(CustomerContacts.MI & '' <> '', ' ' & CustomerContacts.MI & '. ',' ') & CustomerContacts.[Last name] AS CustomerName, Payments.LaborPortion, Payments.PurchasesPortion, Payments.PaymentAmount, Payments.ReceivedDate"
        SQLString = SQLString & " FROM ([Work Orders II] INNER JOIN CustomerContacts ON [Work Orders II].ContactID = CustomerContacts.ContactID) INNER JOIN Payments ON [Work Orders II].WorkOrderNumber = Payments.WorkOrderNumber"
        SQLString = SQLString & " WHERE (CInt(DatePart('yyyy',Payments.ReceivedDate))=" & RptYear & ");"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, FormsConnect
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Contact ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Customer Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value
                ' Zero out the record ...
                For lcv = 2 To 49
                    TrgRecSet.Fields(lcv).Value = 0
                Next
                
                BillMonth = DatePart("m", MyRecSet.Fields(5).Value)
                ' Labor Amount
                If MyRecSet.Fields(2).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 1).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(14).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(15).Value = MyRecSet.Fields(2).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(16).Value = MyRecSet.Fields(2).Value
                    TrgRecSet.Fields(17).Value = MyRecSet.Fields(2).Value
                End If
                
                ' Purchases Amount
                If MyRecSet.Fields(3).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 17).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(30).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(31).Value = MyRecSet.Fields(3).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(32).Value = MyRecSet.Fields(3).Value
                    TrgRecSet.Fields(33).Value = MyRecSet.Fields(3).Value
                End If
                
                ' Total Billed
                If MyRecSet.Fields(4).Value <> 0 Then
                    TrgRecSet.Fields(BillMonth + 33).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 4 Then TrgRecSet.Fields(46).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 7 Then TrgRecSet.Fields(47).Value = MyRecSet.Fields(4).Value
                    If BillMonth < 10 Then TrgRecSet.Fields(48).Value = MyRecSet.Fields(4).Value
                    TrgRecSet.Fields(49).Value = MyRecSet.Fields(4).Value
                End If
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        FormsConnect.Close
        Set FormsConnect = Nothing
        Set TrgRecSet = Nothing
        LoadingDelay
        
        RetrieveBillingDetail = 1
    Exit_RBD:
        Exit Function
        
    Err_RBD:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveBillingDetail = 0
        Resume Exit_RBD
    End Function

  6. #6
    Join Date
    Feb 2004
    Posts
    142
    Create a public function in a module to group months into quarters

    Public Function getQuarter(dteDateIn as date) as int
    Select Case month(dteDateIn)
    Case 1, 2,3
    getQuarter = 1
    Case 4,5,6
    getQuarter = 2
    Case 7,8,9
    getQuarter = 3
    Case 10,11,12
    getQuarter = 4
    End Select

    Imbed this into your queries as a field
    Qtr: getQuarter(DateFieldName)

    And group by qtr.

    You can change the case values to suit you defined quarters.
    KC

  7. #7
    Join Date
    Mar 2004
    Posts
    82
    Thanks a bunch!



    Originally posted by AZ KC
    Create a public function in a module to group months into quarters

    Public Function getQuarter(dteDateIn as date) as int
    Select Case month(dteDateIn)
    Case 1, 2,3
    getQuarter = 1
    Case 4,5,6
    getQuarter = 2
    Case 7,8,9
    getQuarter = 3
    Case 10,11,12
    getQuarter = 4
    End Select

    Imbed this into your queries as a field
    Qtr: getQuarter(DateFieldName)

    And group by qtr.

    You can change the case values to suit you defined quarters.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •