VB Data Report Designer 4

[Sam13]

Hi there, I'm relatively new to working with VB and databases. I'm working on a project that is using the Data Environment and the Data Report Designer to create reports. This is my first time using these aspects of VB. I've stumbled my way through setting up some reports but I'm having trouble with a few things.

First, How do you get the report to show in a landscape format rather than portrait? Is this option even available??

My second question Is a bit more involved. Basically when I create the reports, I'm using the data environment connection to create a query of my tables using parent/child commands and SQL statements. The issue is that When my parent item has no data in the child portion then the report still creates a line item for the parent. I want to eliminate this line item and only have the report show parent data that has child data. I hope I'm being clear. I'm a bit lost on how to do this.

My third question is can the Data Report Designer be used with an ADO control? Basically I want to be able to search the database on criteria the user enters and print a report using the recordset returned by the search. Any thoughts on the best way to print such a report would be great.

Thanks in advance for the help.

Sam

 

[Guest_imported]

vb6Access

Thank you for your reply.
Unfortunately your reply doesn't solve my problem.

I know how to use parameters in th 'parent' command. My problem is that I would like to pass the parameters to the child command.
In this way I would be able to use the same commands & report to make different reports depending on the parameters.

The obvious solution is to make more commands. (They will be almost identical. But I will not be able to make 'dynamic' querys like I want.) This is something that I would like to avoid.

Is there anyone that has had the same problem?

 

[Guest_imported]

I could manage to do what I want if I could change the CommandText of the child command. (CommandText~=SQL command)
I know how to set the CommandText of the 'parent', but I cannot do it for the child command.

This is how I do it for the 'parent' command:
DataEnv.Commands("cmdPetOwner&quot.CommandText = "SELECT * FROM PetOwner"

Is there anyone that knows how to find and change the child CommandText and parent relations?

 

[Guest_imported]

I'm trying to set a value in my Report to not Visible from my code, but I cant figure out how to access an Object in the Report......

 

[leh]

i've been using vb datareport, but i found some problems, if there are still group details to be printed on the next page the group header will not appear. I want the group header be printed on the next page if there are still group details left. Anybody can help me, I will appreciate.

Thank you,
leh

 

[Guest_imported]

I am also having huge struggles with datareports. Basically I have managed to print my reports and make it look nice and this all from using the dataenvironment. I use a SQL statement in the dataenvironment which will basically retrieve all the records. For ex; "SELECT * FROM History"
My question is how can I, during runtime, add a WHERE-clause to the statement which will reference to a value in a textbox during runtime for ex; "SELECT * FROM History WHERE Reference = '" & text1 & "'"
I have tried everything but nothing seems to work. My report shows all the records and this is not what I want. Does anyone have any suggestions?

 

[chernandez2000]

hi vbwill

Its kinda early for me, but let me show the syntax I used for such a SQL statement :
"SELECT * FROM History WHERE Reference = " & Chr(39) & text1 & Chr(39) & "
'chr(39) is of course a single quote

Also, would a refresh of some property in the dataenvironment be in order after the SQL statement?

Good luck

 

[chernandez2000]

Also here's a way to get a data report without using the dataenvironment:

Dim cn_ForReport As ADODB.Connection
Dim rs_ForReport As ADODB.Recordset
Dim strSQL As String

Set cn_ForReport = New ADODB.Connection

cn_ForReport.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & whateverpathyourdbisat & ";" & _
"Persist Security Info=False"

cn_ForReport.Open

strSQL = "SELECT * FROM History WHERE Reference = " & Chr(39) & text1 & Chr(39) & "

Set rs_ForReport = cn_ForReport.Execute(strSQL)

' Connect the Recordset to the DataReport.
Set WhateverReportName.DataSource = rs_ForReport

Good luck amigo

 

[Guest_imported]

I AM TRYING TO CREATE A REPORT THAT CAN SHOW PICTURES STORED IN THE DATABASE ALONG WITH OTHER FEILDS. I TRIED USING THE REPORT IMAGE CONTROL BUT AS THE REPORT IMAGE CONTROL DO NOT HAVE DATASOURCE,DATAMEMBER AND DATAFEILD PROPERTIES. I AM WONDERING IF SOME ONE CAN HELP ME ON THIS.

 

[dwray]

CHERNADEZ2000

IS there a way to incorperate grouping levels without using a data environment?

Thanks.

Dom

 

[chernandez2000]

dwray

There may be a way, but I believe I was trying to figure that out once and got a headache LOL.

You may want to look into "hierarchical recordsets". Not sure but that may open up some doors for you.

Good luck.

 

[dwray]

CHERNADEZ,

Thanks, I actully found an example in the MSDN Knowledge base. You can link to it here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q289793

(sorry, I don't know how to post a link)

Here is the text:

HOWTO: Dynamically Populate a Group Data Report in Visual Basic
The information in this article applies to:
Microsoft Visual Basic Enterprise Edition for Windows 6.0, 6.0 SP3, 6.0 SP4
ActiveX Data Objects (ADO) 2.0, 2.1, 2.1 SP2, 2.5, 2.6, 2.7

Summary
This article explains how to create a Group Data Report programmatically without binding it to any data at design time.
More Information
Step-by-Step Example
Open a Standard EXE project in Visual Basic. Form1 is created by default.
Add a Command button to Form1.
On the Project menu, click References, and then add a reference to Microsoft ActiveX Data Objects Library.
On Project menu, click to add DataReport1. If Add Datareport is not on the Project menu, add it from the Designers tab located on the Project menu under Components. (Make sure that you deselect Selected Items Only in the Project menu under Components.)
Open Datareport1 and ensure that the Detail, Page Header, and Page Footer sections are visible. If the Report Header or Footer sections are visible, remove them by right-clicking the report and removing the check mark from Show Report Header/Footer.
Right-click and select Insert Group Header/Footer. A new Section, Section4, is added to the Datareport.
Add one rptLabel control and one rptTextbox control to the new section.
Add two rptLabel controls and two rptTextbox controls to the Detail section of the Datareport.
Copy and paste the following code into the form:
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim rs1 As New ADODB.Recordset

Private Sub Command1_Click()

Dim q As Integer
Dim intCtrl As Integer
Dim x As Integer
Dim z As Integer
x = 0
q = 0
z = 0

With DataReport1
.Hide
Set .DataSource = rs
.DataMember = ""
With .Sections("section4&quot.Controls
For intCtrl = 1 To .Count
If TypeOf .Item(intCtrl) Is RptLabel Then
.Item(intCtrl).Caption = "City" & " :"
q = q + 1
End If
If TypeOf .Item(intCtrl) Is RptTextBox Then
.Item(intCtrl).DataMember = ""
.Item(intCtrl).DataField = "City"
End If
Next
End With

q = 0
With .Sections("Section1&quot.Controls
For intCtrl = 1 To .Count
If TypeOf .Item(intCtrl) Is RptLabel Then
.Item(intCtrl).Caption = rs1.Fields(q).Name & " :"
q = q + 1
End If
If TypeOf .Item(intCtrl) Is RptTextBox Then
.Item(intCtrl).DataMember = "Command1"
.Item(intCtrl).DataField = rs1(z).Name
z = z + 1
End If
Next intCtrl
End With
.Refresh
.Show
End With
End Sub

Private Sub Form_Load()

Command1.Caption = "Show Report"
cn.Open "Provider=MSDATASHAPE; Data Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=D:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;"

With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = " SHAPE {SELECT FirstName,Lastname,City FROM `Employees`} AS Command1 COMPUTE Command1 BY 'City'"
.Execute
End With

With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.Open cmd
End With
Set rs1 = rs(0).Value

End Sub
References
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
Q240019 HOWTO: Dynamically Populate a Data Report in Visual Basic

There is a lot of unecessary code here used to identify text boxes in the different sections and assign a Data Field, but this can be ignored if you already assign the datafield property in the Data Report and set the DataMember to command1.

I'm not sure if I understand all of this but it worked for me.

 

[chernandez2000]

Excellent young Jedi.

 

[Guest_imported]

I was able to get that howto thing microsoft posted but I don't understand it.

This is what I'm doing. I created a data report and added a group. Then, on a form, I have a command button that shows that form. My code works well if I remove the group from the data report but if I dont I get an error message that says something about the data structure doesn't match.

Here is my code. Hope you can help me.


Private Sub cmdProdHist_Click()
Dim ProdChosen As String

ProdChosen = InputBox("Enter Product:", "Product&quot

Call Open_Connection

rsSales.CursorLocation = adUseClient
rsSales.CursorType = adOpenDynamic
rsSales.LockType = adLockPessimistic

rsSales.Open "SELECT Date, [sales invoice number], [product name], Quantity " _
& "FROM SALES WHERE [product name] like '" & ProdChosen & "%' group by [sales invoice number], [product name], date, quantity", cn



With rptProdHist
Set .DataSource = rsSales
.DataMember = ""


With .Sections("Section1&quot.Controls
.Item("txtDate&quot.DataMember = ""
.Item("txtDate&quot.DataField = rsSales.Fields(0).Name
.Item("txtInvNum&quot.DataMember = ""
.Item("txtInvNum&quot.DataField = rsSales.Fields(1).Name
.Item("txtQty&quot.DataMember = ""
.Item("txtQty&quot.DataField = rsSales.Fields(3).Name
.Item("txtloose&quot.DataMember = ""
.Item("txtloose&quot.DataField = rsSales.Fields(2).Name

End With

'With .Sections("section4&quot.Controls
' .Item("Lblprod&quot.Caption = rsSales.Fields(2).Value
'End With

'With .Sections("section7&quot.Controls

' .Item("label9&quot.Caption = "txtQty"

'End With

.Orientation = rptOrientPortrait
.Show
.WindowState = 2

End With

End Sub

 

[BjLim010182]

I was able to get that howto thing microsoft posted but I don't understand it.

This is what I'm doing. I created a data report and added a group. Then, on a form, I have a command button that shows that form. My code works well if I remove the group from the data report but if I dont I get an error message that says something about the data structure doesn't match.

Here is my code. Hope you can help me.


Private Sub cmdProdHist_Click()
Dim ProdChosen As String

ProdChosen = InputBox("Enter Product:", "Product&quot

Call Open_Connection

rsSales.CursorLocation = adUseClient
rsSales.CursorType = adOpenDynamic
rsSales.LockType = adLockPessimistic

rsSales.Open "SELECT Date, [sales invoice number], [product name], Quantity " _
& "FROM SALES WHERE [product name] like '" & ProdChosen & "%' group by [sales invoice number], [product name], date, quantity", cn



With rptProdHist
Set .DataSource = rsSales
.DataMember = ""


With .Sections("Section1&quot.Controls
.Item("txtDate&quot.DataMember = ""
.Item("txtDate&quot.DataField = rsSales.Fields(0).Name
.Item("txtInvNum&quot.DataMember = ""
.Item("txtInvNum&quot.DataField = rsSales.Fields(1).Name
.Item("txtQty&quot.DataMember = ""
.Item("txtQty&quot.DataField = rsSales.Fields(3).Name
.Item("txtloose&quot.DataMember = ""
.Item("txtloose&quot.DataField = rsSales.Fields(2).Name

End With

'With .Sections("section4&quot.Controls
' .Item("Lblprod&quot.Caption = rsSales.Fields(2).Value
'End With

'With .Sections("section7&quot.Controls

' .Item("label9&quot.Caption = "txtQty"

'End With

.Orientation = rptOrientPortrait
.Show
.WindowState = 2

End With

End Sub

 

[Deepak]

How To Change the Forecolor of rptText at runtime at a given condition.
Say
If ROL < 100 Then
Sections(&quot;Section1&quot.Controls(1).ForeColor = vbRed
Else
Sections(&quot;Section1&quot.Controls(1).ForeColor = vbBlue
End IF
Where to write this code.
Thanks in Advance
my email is deepak_adlakha@hotmail.com
Regards
Deepak

 

[nimit]

I am using DataReports with MSaccess.
I have stored the date in the MSAccess as yyyyMMdd format as text,so that i can use this format for comparison purposes.

My Problem is when i generate report ,in the front end the format is again yyyyMMdd format for dates while i need it in
dd/mm/yyyy format.

Please solve this tricky problem.
However Please note i have tried the following
a) Using date format in VB ,unfortunatly it desnt work with
yyyyMMdd format.
b) using stored procedure,it also doesnt seems to work

Please do advice me and if possible give my a handy solution.

 

[nimit]

I am using DataReports with MSaccess.
I have stored the date in the MSAccess as yyyyMMdd format as text,so that i can use this format for comparison purposes.

My Problem is when i generate report ,in the front end the format is again yyyyMMdd format for dates while i need it in
dd/mm/yyyy format.

Please solve this tricky problem.
However Please note i have tried the following
a) Using date format in VB ,unfortunatly it desnt work with
yyyyMMdd format.
b) using stored procedure,it also doesnt seems to work

Please do advice me and if possible give my a handy solution.

 

[bluenoser337]

Sorry, I posted twice (once in wrong forum)...

Have a DB stored procedure (SQLServer 7.0) that gives me columns I'll call A,B,C,D,E,F group by A,B,C,D,E,F order by A,B,C,D,E,F. In Data Environment I set the Database Object/Name as my stored procedure and I can print out the values with A,B,C,D,E,F chosen for grouping, but I can't get the report arranged properly. In other words, I want all of group A to contain subgroup B, to contain subgroup c, etc... divided up acordingly.
City 1(A)
Store 1,2,3,etc (B)
Salesperson 1,2,3,etc (C)
Widgets 1,2,3,etc (D) etc
City 2 (A)
Store 1,2,3,etc (B)
Salesperson 1,2,3,etc (C)
Widgets 1,2,3,etc (D) etc
The report is displaying City 1 many times instead of just group headings for the cities. I've tried everything but I keep getting errors that the report sections do not match the data source. I've done &quot;retrieve structure&quot;. Wondering if the problem is with my stored procedure or report designer. New with this Data Environment business.
Any help greatly appreciated.

 

[tonioJ]

HELLO HOW DO YOU GROUP DATA IN DATA REPORT? M GETTING A ERROR: &quot;REPORT SECTION DO NOT MATCH DATASOURCE&quot; , M USING DATA ENVIRONMENT FOR DATASOURCE.. THANK YOU

 

[bluenoser337]

Microsoft has a tutorial for Data Environment and Report Designer. It's not the greatest but it's something. Good luck. I'm still lost with a lot of this. If you find any samples, let me know.

http://msdn.microsoft.com/library/d...n-us/vbcon98/html/vbconusingdewithyourapp.asp