how to make a dynamic datareport?

[vishwaskothari]

i need to develop a dynamic datareport .
here by the word dynamic i mean that the contents of the report will vary according to the users input.

for eg : i am doing a search operation.and the result of the search will vary according to the input.

so i need to know how to map that varying result with the datareport.

and one more thing that i need to do all these thing with programming, i am not using the Dataenvironment and all those things.

help needed !!!!

thanks in advance
regards
vishwas

[creative1]

hi
It's little tricky. here some tips
1..... you need to create a connection string.
suppose RS in you r connection string.
RS.Open "your query ", Conn, adOpenKeyset, adLockOptimistic

2... Set data source and data members properties of your report
suppose RptInvoice is your report
With RptInvoice
Set .DataSource = Nothing
.DataMember = ""
Set .DataSource = RS.DataSource

3... set controls.
suppose you have lblName, lblAddress as your controls

.Sections("header").Controls("lblName").Caption = RS.Fields("company_name")
.Sections("header").Controls("lblAddress").Caption = RS.Fields("address")
.Sections("header").Controls("lblCity").Caption = RS.Fields("city")
.Sections("header").Controls("lblPostalCode").Capt ion = RS.Fields("postal_code")
.Sections("header").Controls("lblPhoneNo").Caption = RS.Fields("phone_number")

[vishwaskothari]

hi
It's little tricky. here some tips
1..... you need to create a connection string.
suppose RS in you r connection string.
RS.Open "your query ", Conn, adOpenKeyset, adLockOptimistic

2... Set data source and data members properties of your report
suppose RptInvoice is your report
With RptInvoice
Set .DataSource = Nothing
.DataMember = ""
Set .DataSource = RS.DataSource

3... set controls.
suppose you have lblName, lblAddress as your controls

.Sections("header").Controls("lblName").Caption = RS.Fields("company_name")
.Sections("header").Controls("lblAddress").Caption = RS.Fields("address")
.Sections("header").Controls("lblCity").Caption = RS.Fields("city")
.Sections("header").Controls("lblPostalCode").Capt ion = RS.Fields("postal_code")
.Sections("header").Controls("lblPhoneNo").Caption = RS.Fields("phone_number")

thanks a lot
but it is displaying the first record again and again for rs.recordcount times.

wat can be the probable sol for it?

thanks in advance
regards
vishwas

[vishwaskothari]

thanks a lot
but it is displaying the first record again and again for rs.recordcount times.

wat can be the probable sol for it?

thanks in advance
regards
vishwas

help needed!!!

regards
vishwas

[creative1]

Sorry for a late reply. But I hope it will help you a little bit. I usually create dynamic reports using cobination of dataenviornemnt and queries at runtime. Data enviornemnt helps to design layout of report.
Anyways try this to loop displaying records
again suppose Rs is your recordset...Try this to display multiple records.

With RptInvoice
Set .DataSource = Nothing
.DataMember = ""
Set .DataSource = RS.DataSource

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
This section to display information in the header section of the report
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''
.Sections("header").Controls("lblName").Caption = RS.Fields("company_name")
.Sections("header").Controls("lblAddress").Caption = RS.Fields("address")
.Sections("header").Controls("lblCity").Caption = RS.Fields("city")
.Sections("header").Controls("lblPostalCode").Capt ion = RS.Fields("postal_code")
.Sections("header").Controls("lblPhoneNo").Caption = RS.Fields("phone_number")

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
This section to display information in the section1 of the report. A loop is ised to display multiple records.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''


With .Sections("Section1").Controls
For i = 1 To .Count

If TypeOf .Item(i) Is RptTextBox Then
.Item(i).DataMember = ""
.Item(i).DataField = RS.Fields(i + 4).Name
'i+4 means ... leave first 5 in the query and display rest for them

End If
Next i



End With




End With

[jrtox]

hi vishwaskothari , we do have same problem.
but thanks creative1 for giving those idea.

ill try that when i got home.

but i still have a problem if its still working if theres a Parent and child commands

[vishwaskothari]

hi vishwaskothari , we do have same problem.
but thanks creative1 for giving those idea.

ill try that when i got home.

but i still have a problem if its still working if theres a Parent and child commands

thanks a lot Creative1
but how can we know how many controls should be added to control????
is there any sol for it?

well can u plz tell me the exact procedure of using dataenvironment.

thanks in advance
regards
vishwas

[creative1]

hi guys
I will write an article on how we can create dynamic reports using DataEnviornment and Runtime queries. For your knowledge and help I'll work with parent-child commands. It will make the report useful for many other people. I'll try to create a simple example as well.
I hope to post is today.
regards
farhana

[creative1]

Hi
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
here are my suggestions and sequence of steps to create complex dynamic reports

Create 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, custoemerID, 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
Datasource  set to DataEnviornment1
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.

Now we proceed 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.

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.

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

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 complex and dynamic report in VB

I hope it will help you learn something
Farhana

[vishwaskothari]

Hi
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
here are my suggestions and sequence of steps to create complex dynamic reports

Create 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, custoemerID, 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
Datasource  set to DataEnviornment1
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.

Now we proceed 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.

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.

Step-3 Keep properties for child command fields in Sections1
Do not change properties for command2
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 complex and dynamic report in VB

I hope it will help you learn something
Farhana

first of all very very thanks to u for such a nice article.

will u plz elobrate the steps 1,2,3 of generating the dynamic datareport(Bold Part of this reply)
regards
vishwas

[creative1]

Hi
Here is little explanation of the highlighted parts . I hope you will be able to understand it

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

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.

regards
Farhana

[GROG]

Farhana..! u Make my day.. the lengthy write up really works.. thanx a lot..god bless u.. wish could treat u a lunch/ dinner.. he.. hmm .. still need ur help.. how to insert page number in a report..? i.e page # and page # of ##

[GROG]

And also todays date.. as in Date printed < dd/mm/yyyy>

[GROG]

Sorry all.. found it.. the date and the page num.. Thanks

[creative1]

My pleasure ! to help you ... I posted this message in article as well so it may be helpful for others.
regards
farhana

[srbh]

that article helped me a lot....... thanks to farhana....!!

[Vinokbhai r k]

Thanks creative1-farhana for your great work. Your post will help the developers a lot. keep it up. May God bless you.
If I have any query I will surely approach you. You are a good teacher.
regards,
Anjali

[Chege]

Creative1,

Thanks so much for this informative and educative material. I have followed your Dynamic report creation. But I am getting an error.
First i have a form with two TextBoxes that hold From Date(DateFrom) and To Date (DateTo)values.

I have copied the code and pasted on DataReport_Initialize(). When I run the program it says:

Run-time error '-2147217913(80040e07)':

Syntax error in date in query expression 'O.Orderdate BETWEEN ## AND ##'.

It then takes me to
RS.Open query, Conn, adOpenForwardOnly after clicking debug.

I have noticed that my strFromand strTo are both empty but my FromDate and ToDate have values.

While getting dates from Form i am using:
With frmDataEnv
FromDate = .DateFrom.Text
ToDate = .DateTo.Text
End With

NB: it tells me method not supported when i use .DateFrom.Value or .DateTo.Value

Kindly let me know where i am going wrong.

Thanks

Chege

[Chege]

Phew!!!

Creative1, I have nailed it!!! Great....

Thanks a million.

Simon