CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2004
    Posts
    132

    Data Report Help

    I am a bit out of my depth here. I started a simple accounts program that enters information into a MS Access database, so far so good. As well as enter the data I can view records one at a time and edit or delete them.

    Now I need to make a report. My main table has fields holding the following: id, date, category, supplier, amount, notes. Firstly I want to create reports between a user selectable start date and end date. On the report I want to list all the records between these dates, ordered by categories, and show a total of the amounts under each category as well as a final total at the end.

    I have been trying to use Data Reports and can generate a report showing all the fields, but so far it shows all records. I have no idea how to add a start date and end date, or how to add in totals.

    Any advice would be greatly appreciated.

  2. #2
    Join Date
    Jun 2002
    Location
    Clane, Ireland
    Posts
    766

    Re: Data Report Help

    To put in totals / categories, you will need to create group headers / footers in your report.

    You will also need to look at using Shape, to create a recordset which fits the structure of your report.

    You can specify the report source of the report, before openning it i.e. create a record source and then say
    Code:
    Set rpt.DataSource = adrQry
    where rpt is the name of your report, and adrQry is the name of your recordset.

    HTH
    JP

    Please remember to rate all postings.

  3. #3
    Join Date
    Aug 2004
    Posts
    132

    Re: Data Report Help

    Thanks for the help. Now I am getting errors "Report width is larger than the paper width". How would I stop this? I have almost nothing in the report at the moment.

  4. #4
    Join Date
    Aug 2004
    Posts
    132

    Re: Data Report Help

    Oh. I figured that one, I had a line on the report that was stopping me resize it. Guess I better do some readin on the Group Headers / Footers.

  5. #5
    Join Date
    Aug 2004
    Posts
    132

    Re: Data Report Help

    I have no idea how to use the group headers/footers. I drag a field into a group header and I get a "Report sections do not match data source" error. If anyone can give me a few hints on using the group headers / footers I would really appreciate it.

  6. #6
    Join Date
    Jun 2002
    Location
    Clane, Ireland
    Posts
    766

    Re: Data Report Help

    You need to look for help on the SHAPE command. I only ever use it as an SQL statement, so if you're using it like that I can help, but if you use the data environment, I'm can't help you.

    The shape command, basically allows you create different recordsets within the one recordset eg one for each header, and then one for the detail lines.

    Sorry I can't be of more help to you.

    JP
    JP

    Please remember to rate all postings.

  7. #7
    Join Date
    Aug 2004
    Posts
    132

    Re: Data Report Help

    I have hunted everywhere for info on the SHAPE command. I haven't been able find anything that I can understand. In the mean time I have been getting my code to make basic reports. I am hoping you can give me a few hints on using the shape command. I have 2 calendars to choose a start date and an end date for the report, then the comand button has this code in it.
    Code:
    Private Sub cmdShowReport_Click()
            
        Dim Srchflag As Boolean
        Dim i As Integer
        
        'Setting Initial data for recordset
        rs.Close
        rs.Open "Select * from Expenses WHERE Expense_Date>='" & Calendar4.Value & "' AND Expense_Date<='" & Calendar5.Value & "' ORDER BY Category ;", cnAP, adOpenDynamic, adLockOptimistic
        Set DataReport1.DataSource = rs.DataSource
        
        With DataReport1
    
            With .Sections("Section1").Controls
                        For i = 1 To .Count
                            If TypeOf .Item(i) Is RptTextBox Then
                                'The datamember should be always blank while creating dynamic data reports
                                .Item(i).DataMember = ""
                                .Item(i).DataField = rs.Fields(i - 1).Name
                            End If
                        Next i
            End With
            With .Sections("Section2").Controls
                        For i = 1 To .Count
                            If TypeOf .Item(i) Is RptLabel Then
                                'The datamember should be always blank while creating dynamic data reports
                                .Item(i).Caption = "Records for period from " & Calendar4.Value & " to " & Calendar5.Value
                            End If
                        Next i
            End With
            
        End With
        
            If rs.EOF <> True Then
                    DataReport1.Show
                    DataReport1.SetFocus
                    Srchflag = True
                    Exit Sub
            End If
    
        If Srchflag = False Then 'Display msg when search not found
            MsgBox "Search Not Found", vbInformation, "Search Result"
        End If
    
    
    End Sub
    One of the fields is a Currency amount. What I want to do is put a line underneath each category showing a total value of the category, and then at the bottom put a total of everything. At the moment I have no group headers, my report has 1 RptLabel in the Page Header and 6 unbound RptTextBox's in the Detail Section.

    Any hints on how to use the shape command with the above code would be appreciated. Thanks.

  8. #8
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    Re: Data Report Help

    have u tried MSDN?
    this is what i got from MSDN
    Shape APPEND Clause
    The shape command APPEND clause appends a column or columns to a Recordset. Often these columns are chapter columns, which refer to a child Recordset.

    Syntax
    SHAPE [parent-command [[AS] parent-alias]] APPEND column-list
    Description
    The parts of this clause are as follows:

    parent-command
    Zero or one of the following (you may omit the parent-command entirely):
    A provider command within curly braces ("{}") that returns a Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be the SQL language, although ADO does not require any particular query language.
    Another shape command embedded in parentheses.
    The TABLE keyword, followed by the name of a table in the data provider.
    parent-alias
    An optional alias that refers to the parent Recordset.
    column-list
    One or more of the following:
    An aggregate column.
    A calculated column.
    A new column created with the NEW clause.
    A chapter column. A chapter column definition is enclosed in parentheses ("()"). See syntax below:
    SHAPE [parent-command [[AS] parent-alias]]
    APPEND (child-recordset [ [[AS] child-alias]
    RELATE parent-column TO child-column | PARAMETER param-number, ... ])
    [[AS] chapter-alias]
    [, ... ]
    child-recordset
    A provider command within curly braces ("{}") that returns a Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be the SQL language, although ADO does not require any particular query language.
    Another shape command embedded in parentheses.
    The name of an existing shaped Recordset.
    The TABLE keyword, followed by the name of a table in the data provider.
    child-alias
    An alias that refers to the child Recordset.
    parent-column
    A column in the Recordset returned by the parent-command.
    child-column
    A column in the Recordset returned by the child-command.
    param-number
    See Operation of Parameterized Commands.
    chapter-alias
    An alias that refers to the chapter column appended to the parent.
    Note The "parent-column TO child-column" clause is actually a list, where each relation defined is separated by a comma.
    Note The clause after the APPEND keyword is actually a list, where each clause is separated by a comma and defines another column to be appended to the parent.
    sorry can't help u with the code...

    regards

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

  9. #9
    Join Date
    Jun 2002
    Location
    Clane, Ireland
    Posts
    766

    Re: Data Report Help

    The following address may help:

    MSDN and shape

    I'm going to include code here which produces grouped reports. We have a unit stocking system (a garage has cars on their forecourt for sale, we provide the finance for these cars). The garages supply us with orders, we determine if they have available credit etc, and if so we approve the order. They then have to send us an invoice, which we match to the order. Sometimes they decide not to go ahead with the order and forget to tell us, so we never cancel the order. What this program does, is report on orders that don't have invoices, or on orders where the invoice doesn't match the order amount.

    The project has two reports, take a look at the second one (rptOrdersWithoutInvoices), and take a look at the event cmdOrdersNoInvoices_Click from the main menu.

    The report produces a detail on each individual franchise (read garage) - this is done via the group section.

    HTH
    Attached Files Attached Files
    JP

    Please remember to rate all postings.

  10. #10
    Join Date
    Aug 2004
    Posts
    132

    Re: Data Report Help

    Thanks erickwidya for the quote, I did read a lot of that, but it doesn't mean much to me. I have been using a database with VB for about 3 days so far. Try to learn as fast as I can.

    Thanks for the sample jp140768, thats exactly what I need. Hopefully I can work out what I need from this.

  11. #11
    Join Date
    Aug 2004
    Posts
    132

    Re: Data Report Help

    Hi jp140768,

    Sorry to be a pain, when I try to run your code I get an error "Can't find project or library" on this part of the code "Dim objIni As Utilities.clsWinAndIni" What should I do to stop this error?

  12. #12
    Join Date
    Jun 2002
    Location
    Clane, Ireland
    Posts
    766

    Re: Data Report Help

    Sorry the project won't run, it was only meant as an example. To get the project to run I'd have had to send you the database as well as some of the DLLs - definitly can't send you the database.

    If you look at the report and the function that I mentioned yesterday, you should see how it works. If not feel free to ask me about it.
    JP

    Please remember to rate all postings.

  13. #13
    Join Date
    Aug 2004
    Posts
    132

    Re: Data Report Help

    Hi, no problem .. I will continue to study the code. I do want to work out as much as I can myself. Thanks.

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured