Create Dynamic Report : with Parent Child Command

[creative1]

Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however, working with complex reports is tricky

Assumption: Reader of this article have basic knowledge of creating data reports.

Creating a Parent-Child Command and create a DataReport
Suppose we have a database called company with two tables

Customers (customerID, company, contacttitle, address, city)
Orders (ordered, customerID, orderdate)


Create a report using Parent / Child relationship between queries.

Step 1: Add Data Environment in the project. In the project Explorer windows Right click on Project name -> Add -> DataEnvironment

You will see DataEnviornemnt1 under folder Designer in Project Explorer Windows. Double click DataEnviornment1 to get it displayed in the screen

Step 2: Set connection to the database
Right Click on connection1-> select Provider tab-> select your provider name ( for Access select Microsoft Jet OLE DB Provider) -> select Next-> Select your database name(suppose your database name is company)-> Username and password -> Test connection
Once connection is successful go to step 3

Step 3: Add a command (Parent Command)
Suppose we have table names customer
Right click connection1 -> select Add Command -> Right click on command1 -> select properties -> select radio button SQL statement -> type in your query(select * from customer) -> click Ok
You will see your fields under you command name (command1 in this case)

Step 4: Add a command (Child Command)
Right click on command1-> add child command-> It will add a new command under command1-> right click on Command2 -> Click on SQL statement radio button -> type in your query (suppose you have a table orders to select record from: select * from orders)

Step-5 Relate Command2 with command1
Right click command2 -> select tab Relation-> select parent command (in this case Command1). Set relation definition by relating fields from both tables’ parent table (customer) and child table (customer).
Suppose customerID is relating both tables.

Step-6 View hierarchy information of your command
Right click on Command1 -> select Hierarchy info -> It will display relation of both commands(parent and child command) -> copy this all we are going to use this command in future to change it at runtime.

Step-7 Create a data report
Right click on Project1->select data report ->this will add DataReport1 under designer folder.

Step-8 Set Datareport1’s properties
Click on report scale under report tile to display report properties -> Right click on report -> select Add group header and footer -> It will add two more partitions in report group header (Section4) and group footer (Section5)

Step-9 Set Data source and Data members
Select you report then in the properties windows set following properties of data report
set to DataEnviornment1Datasource
Datamember Set to Command1

Step-10 Design Report Layout
Open you datareport1 and drag contents from command1 into Group Header (Section4)) and fields of Command2 into section1. To design a better interface you can drag fields of command2 in Section4. You can organize your report here. Set Report titles etc in report Header Section (Section2). Set page number and date in the footer section (Section3)
Tip: Group Footer section is used to display aggregates of the Section4 of you have any set in the hierarchy.

Step-11 Design a form to access data report
Add a form and add some commands buttons etc.
Connect a button (CommandButton1) to display/Print your report. If you execute you project now you will get a report that displays record from a database with parent chaild commands queries.

Next step is to make the report Dynamic

Make the Report Dynamic

Use your previous knowledge to do this. Create a report then following steps

Step-1 Set DataReport properties
Remove DataSource and DataMember for report that you previously used to design report.

Explanation: when we design our report structure by dragging command1 and command2 fields in out report. We set two properties of out report

1... Data member 2... DataSource:
we don't need to reset these properties if we are working on static reports. If we are using parent-child command queries, it is very important to remove these properties in the property window. Otherwise report won't work. This is because when we use runtime queries, we set RS as DataSource.

Step-2 Reset Properties for Header Section
For group header section (Section4) delete DataMember property. Make sure that this property is empty for all fields in this section. Simply let it empty but do not remove DataFieds.

Explanation:
Generally speaking, group header section contains the parent part of query. for every fields in this section, remove value of DataMemeber property. However, it important to keep dataFields property so query can identify which fields goes where.


Step-3 Keep properties for child command fields in Sections1
Do not change properties for command2

Explanation: For Section1, where we keep fields of out child query. Do not remove properties of this section let them same as you were using while designing layout.

Step-4 This is final and most important step in creating dynamic reports.

Here is a sample code to display an order details for all customers for a date range specified on form in datefrom.text and dateto.text

Code this for initialize event of dataeport.

Private Sub DataReport_Initialize()
Dim query As String ' this will hold our runtime query
Dim ToDate As String ' This will hold first date
Dim FromDate As String ' this will hold second date
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
 
    Conn.CursorLocation = adUseClient
    Conn.ConnectionString = DataEnvironment1.Connection1
    ' Although connection is already set . But this can be used to set reset connection at runtime and connect to your database.
    Conn.Open
    ' get date values form form and store in string variables.
 
    With form1
        FromDate = .DateFrom.Value
        ToDate = .DateTo.Value
    End With
 
    ' As you copied text from hierarchy of command1, paste it here and do necessary changes in it. Such as in my example I am trying to specify my date requirements at runtime.
 
 
    query = "SHAPE { "
    query = query & "SELECT DISTINCT C.* "
    query = query & "FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID "
    query = query & "WHERE O.Orderdate "
    query = query & "BETWEEN #" & strFrom & "# "
    query = query & "AND #" & strTo & "# "
    query = query & "ORDER BY C.CompanyName "
    query = query & "} AS Command1 "
    query = query & "APPEND ({ "
    query = query & "SELECT Orders.* "
    query = query & "FROM Orders "
    query = query & "WHERE Orderdate "
    query = query & "BETWEEN #" & FromDate & "# "
    query = query & "AND #" & ToDate & "# "
    query = query & "} AS Command2 "
    query = query & "RELATE 'CustomerID' TO 'CustomerID') "
    query = query & "AS Command2 "
    RS.Open query, Conn, adOpenForwardOnly
    Set DataReport1.DataSource = RS
 
End Sub
 

This will generate a dynamic report in VB

I hope it will help you learn something

Farhana

[VaTravo]

Thanks farhana

i have tried using ur code on dynamic report generation but an error is encounted during execution its a run time error '-2147217904(80040e10).
And i dont understand the use of strTo and strFrom.

thank you your code was helpful. but i would be happy if help me tackle that problem again


Trevor Mataranyika (vaTravo)

[creative1]

Thanks farhana

i have tried using ur code on dynamic report generation but an error is

Trevor Mataranyika (vaTravo)

strTo and strFrom are two dynamic variables to store starting and ending dates of the the reprot to be printed.
If you couldl tell where you get error '-2147217904(80040e10) it may possible to help you fix it.
Kind Regards

[ein fumiko]

thanks for the dynamic report!!!you're a big help!!!