dcsimg
Results 1 to 8 of 8

Thread: [RESOLVED] How to create a report for "GROUP BY" sql statement

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Resolved [RESOLVED] How to create a report for "GROUP BY" sql statement

    Hi there,
    I am back with new problem. Expecting help from all experts as previous.
    I have a access database table like this:

    table name: employee_training
    ------------------------------------------------------
    ID l name l designation l training_title l from l to
    -------------------------------------------------------
    1 l Mr.X l Prog.Managerl Managementl6/2/12l6/8/12
    ---------------------------------------------------------
    2 l Mr.Yl Accountant l Use of Tally l 6/1/12 l 6/3/12
    ---------------------------------------------------------
    3 l Mr.X l Prog.Managerl HR Manag.l3/1/12 l 3/10/12
    ----------------------------------------------------------

    I want to create a datareport in vb6 using following sql statement and ADO connection:
    set rs = con.execute("SELECT * FROM employee_training GROUP BY name")
    set datareport1.datasource = rs

    [nb: I have excluded here all other necessary codes for connection and recordset, but in main program, all are there]


    Now I want a datareport which layout is as like below:

    Designation Training Tilte From TO <--- This is Report Header
    Employee Name: Mr.X
    Prog.Manager Management 6/2/12 6/8/12
    Prog.Manager HR Manag. 3/1/12 l 3/10/12
    ---------------------------------------
    Employee Name: Mr.Y
    Accountant Use of Tally 6/1/12 6/3/12



    i,e I want a report which will show name [employee name] as group data and then his/her details and then draw a horizontal line [I know how to draw horizontal line], again another name and his/her training details and so on.


    I know how to create a simple datareport in vb6 without 'GROUP BY' statement using ADO connection, but I don't know how to create such above stated report.

    I tried to enter group header in datareport1 and then dragged 'name' field from command1 of data environment but that didn't work.

    Any help showing step-by-step procedure will be appreciated.

    Thanks In advance.
    Last edited by infomamun; Jun 23rd, 2012 at 09:46 AM.

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,229

    Re: How to create a report for "GROUP BY" sql statement

    You can't do grouping using an ADO recordset. To do grouping in a DataReport you have to use a MSDataShape or a DataEnvironment. Read this http://www.vbforums.com/showthread.php?t=605172

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: How to create a report for "GROUP BY" sql statement

    Hi wes4dbt, thanks for your useful links. But as I am very new to datareport, I need little bit more details about using datashape.

    What I did before,
    1) added DataEnvironment1 and set Microsoft Jet OLE DB as Provider from Connection1 Properties,
    2) Selected My Access Database from Connection TAB and tested the connection (Connection was successful)
    3) Set SQL Statement (SELECT * FROM employee_training) as Source of Data from Command1 properties of Connection1 from DataEnvironment
    4) Then Added Datareport in design environment. After that I dragged the necessary fields from DataEnvironment to Datareport and Deleted Datamember of each text field of dragged fields.

    Then I set the ADO connection string and set the Datareport1.Datasource = Recordset.

    Upto this all was fine and reports showed the data in runtime.

    I don't know whether this report is made using Dataenvironment or ADO connection, because I used both of these.

    Now, If I want to use MSDataShape, then whether it is necessary to add any Components in project? If yes, from where to add MSDataShape, because I didnot find it in Component list. Or whether there is no necessity to add any component, just have to mention it in ADO Connection String?

    Secondly, do I have to need Group Header and Footer Section (Section6) in my datareport If I want to use DataEnvironment/DataReport together with ADO Connection as stated above?

    Thanks in Advance

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,229

    Re: How to create a report for "GROUP BY" sql statement

    You shouldn't have to add any references, if your using ADO recordsets then you already have a reference to the ActiveX library. There are examples of MSDatashapes in the posts if you follow the links.

    I would suggest starting by designing the report using a dataenvironment. After you have designed the DE then add a new datareport, set the datasource and datamember to the DE. Right click on the report and click on "Retrieve Structure", that will setup the grouping on the report. After you setup the report fields you can remove the datamember property.

    Here's something that should make designing the msdatashape easier,
    Right click on the top command of the DE.
    Select "Hierarchy Info"
    This will show you what the msdatashape should look like.
    I usually copy and paste it into my code. You will probably have to tweak it by put the double quotes in to right places.

  5. #5

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: How to create a report for "GROUP BY" sql statement

    Hi wes4dbt
    I did everything what you said. I inserted DataEnvironment1, Set Connection1, Command1, Moved field 'name' to group box, added Datareport1, inserted Group Header & Footer in DataReport1, Retrieved structure, Got the below Shape command from hierarchy info, Drag necessary fields from DE to DataReport1, Deleted Data Member from all dragged fields and then coded as below:


    Private Sub Command1_Click()
    Dim rsData As ADODB.Recordset
    Dim conData As ADODB.Connection
    Dim SQL As String
    Set rsData = New ADODB.Recordset
    Set conData = New ADODB.Connection
    With conData
    .Provider = "MSDataShape"
    .ConnectionString = "Data Provider = Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source = " & App.Path & "\training.mdb"
    .Open
    End With

    SQL = "SHAPE {SELECT * FROM employee_training} AS Command1 COMPUTE Command1 BY 'name'"
    rsData.Open SQL, conData, adOpenDynamic, adLockOptimistic
    Set DataReport1.DataSource = rsData
    DataReport1.Show
    End Sub

    But after clicking the command1 button, I am seeing this message:
    "Arguments are of wrong type, are out of acceptable range, or are in conflict with one another"

    What did I wrong?
    Last edited by infomamun; Jun 24th, 2012 at 08:51 AM.

  6. #6

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: How to create a report for "GROUP BY" sql statement

    At last, I found the solution. That error was occuring when I deleted both DataMember from Group Header field [which was command1_Grouping] and DataMember of Detail Section (section1) fields. But if I just delete DataMember of Group Header field and keep the DataMember of Detail Section intact [which is 'Command1'] then all goes OK.

    Is this right way or did I do any mistake in designing Datareport1 or DataEnvironment1?

    Expecting reply.

    Thanks

  7. #7
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,229

    Re: How to create a report for "GROUP BY" sql statement

    If your problem is solved then please mark the thread as resolved, from the thread tools menu. If the answer were helpful then rate the post.

  8. #8

    Thread Starter
    Member
    Join Date
    Mar 2012
    Posts
    40

    Re: How to create a report for "GROUP BY" sql statement

    My problem is solved but the way it has been solved is not satisfactory to me. Because you advised me to delete all the DataMember in DataReport1 text box fields. But I had to delete DataMember from only Group Header and kept intact DataMember of Details Section.

    That's why I requested to review the way I have solved the problem and did not marked it as 'Solved'. Please see Post#7

    Expecting help from you.

    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
  •  



Featured


Click Here to Expand Forum to Full Width