472,783 Members | 956 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,783 developers and data experts.

Create Dynamic Report : with Parent Child Command

274 100+
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.

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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DataReport_Initialize()
  2. Dim query As String ' this will hold our runtime query
  3. Dim ToDate As String ' This will hold first date
  4. Dim FromDate As String ' this will hold second date
  5. Dim Conn As New ADODB.Connection
  6. Dim RS As New ADODB.Recordset
  8.     Conn.CursorLocation = adUseClient
  9.     Conn.ConnectionString = DataEnvironment1.Connection1
  10.     ' Although connection is already set . But this can be used to set reset connection at runtime and connect to your database.
  11.     Conn.Open
  12.     ' get date values form form and store in string variables.
  14.     With form1
  15.         FromDate = .DateFrom.Value
  16.         ToDate = .DateTo.Value
  17.     End With
  19.     ' 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.
  22.     query = "SHAPE { "
  23.     query = query & "SELECT DISTINCT C.* "
  24.     query = query & "FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID "
  25.     query = query & "WHERE O.Orderdate "
  26.     query = query & "BETWEEN #" & strFrom & "# "
  27.     query = query & "AND #" & strTo & "# "
  28.     query = query & "ORDER BY C.CompanyName "
  29.     query = query & "} AS Command1 "
  30.     query = query & "APPEND ({ "
  31.     query = query & "SELECT Orders.* "
  32.     query = query & "FROM Orders "
  33.     query = query & "WHERE Orderdate "
  34.     query = query & "BETWEEN #" & FromDate & "# "
  35.     query = query & "AND #" & ToDate & "# "
  36.     query = query & "} AS Command2 "
  37.     query = query & "RELATE 'CustomerID' TO 'CustomerID') "
  38.     query = query & "AS Command2 "
  39.     RS.Open query, Conn, adOpenForwardOnly
  40.     Set DataReport1.DataSource = RS
  42. End Sub
This will generate a dynamic report in VB

I hope it will help you learn something

Oct 1 '07 #1
3 18512
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)
Jan 17 '08 #2
274 100+
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
Jan 18 '08 #3
thanks for the dynamic report!!!you're a big help!!!
Nov 22 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

by: RL | last post by:
Hello Perl gurus, 1. I have a web page where I can push a button (dospawn.html). 2. This button calls a CGI script (spawnboss.cgi) 3. spawnboss.cgi calls a forking perl script (forkme.pl) 4....
by: Christian Fowler | last post by:
I have a VERY LARGE pile of geographic data that I am importing into a database (db of choice is postgres, though may hop to oracle if necessary). The data is strictly hierarchical - each node has...
by: Amp Inthalangsy | last post by:
Hi there, Was hoping someone would have an fix for me or some kind or work around. I was able to get my client side dynamic dependent list box working but if my child list data has double...
by: Thelma Lubkin | last post by:
My ColorSet building form/subform now works beautifully, thanks to the help that I've gotten from people in this group. The working form displays the parent ColorSet record with the child records...
by: sandy | last post by:
I need (okay, I want) to make a dynamic array of my class 'Directory', within my class Directory (Can you already smell disaster?) Each Directory can have subdirectories so I thought to put these...
by: max | last post by:
Hello, I am a newbye, and I'm trying to write a simple application. I have five tables with three columns; all tables are identical; I need to change some data in the first table and let VB...
by: jammer | last post by:
Hey All, I have a report based on a query that joins a 'parent' and 'child' table. Each row in the query corresponds to a row in the child table, with a few fields from the Parent table. At...
by: Gurur | last post by:
Hi all, I have a doubt. If I have 2 structures and one is parent of other , ie the child structure is present in the parent one . And if the child structure is declared as dynamic array in the...
by: jmartmem | last post by:
Greetings, I have a nagging problem with client-side dynamic dependent list boxes that perhaps someone can help me troubleshoot. I have a form with a series of dynamic dependent list boxes....
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.