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

 

[bsarbaugh]

I am new to Visual Basic and am having a problem with the Data Report Designer. I can get the reports to refresh etc, but either the entire detail section of the report is blank or certain peices of data are missing. When I check the database, they are there. Here the other twist. When I run the code in debug mode and step through it, the reports appear fine.

What is happening in Run mode to cause this? I am using the following code to view the report.

Load deReport
With deReport
If .rscomList.State <> 0 Then
.rscomList.Close
End If
End With

If rptList.Visible = False Then
rptList.Refresh
rptList.Show
Else
rptList.Refresh
rptList.SetFocus
End If

 

[bluenoser337]

Reply to chernandez2000 from July 23, 2002...thank you...thank you...for this simple code to get results from an SQL statement into my report. I just have to work on how to properly display the grouping now...so I stop seeing that &quot;report sections do not match...&quot; message.

 

[EnS]

Hi All
I am having problems with my data report. But first, let me thank all of you for your postings. I am working on my first vb project and all of your suggestions have help me get furthur faster then if I were to figure it out myself. Thanks again.

However, I have run in to a bit of a snag! I have a vb project connected to an access db. I am using the Jet 4.0 OLD DB Provider for this connection. Just some background info. Anyway, I have created a data report using a parent command and am having trouble with the passing of a referenced variable to the sql while keeping the grouping option viable! When I exclude the WHERE clause, or if I hard code the condition of the WHERE clause, the report works fine. But when I change the condition to WHERE <table_name.field> = ?, I lose my grouping option. Can anyone explain to me why?

**************************************************************
My sql is as follows:

TRANSFORM
SUM(SCORING_INFO.SCORE) AS CAT_SCORE
SELECT
GENERAL_INFO.RECORD_NO,
GENERAL_INFO.AUDIT_DATE,
(EMP_F_NAME &' '& EMP_L_NAME) AS EMP_NAME,
GENERAL_INFO.AUDITOR,
GENERAL_INFO.RESOLVED_OUTAGE_COUNT,
GENERAL_INFO.OUTAGE_REVIEWED_COUNT,
GENERAL_INFO.REVIEWED_PERCENT,
GENERAL_INFO.OVERAL_PERCENTAGE,
SCORING_INFO.FC_NO,
SCORING_INFO.ATM_NO,
SCORING_INFO.WORK_OF_DATE,
SCORING_INFO.RESOLUTION_DATE,
OUTAGE_INFO.OUTAGE_DESC,
GENERAL_INFO.COMMENTS,
SUM(SCORING_INFO.SCORE) AS LINE_SCORE
FROM
(EMPLOYEE_INFO INNER JOIN GENERAL_INFO
ON EMPLOYEE_INFO.EMP_NO = GENERAL_INFO.EMP_NO)
INNER JOIN (AUDIT_INFO INNER JOIN (OUTAGE_INFO INNER JOIN
SCORING_INFO ON OUTAGE_INFO.OUTAGE_CODE =
SCORING_INFO.OUTAGE_CODE) ON AUDIT_INFO.AUDIT_CODE =
SCORING_INFO.AUDIT_CODE) ON GENERAL_INFO.RECORD_NO =
SCORING_INFO.RECORD_NO
WHERE
GENERAL_INFO.RECORD_NO = ?
GROUP BY
GENERAL_INFO.RECORD_NO,
GENERAL_INFO.AUDIT_DATE,
(EMP_F_NAME &' '& EMP_L_NAME),
GENERAL_INFO.AUDITOR,
GENERAL_INFO.RESOLVED_OUTAGE_COUNT,
GENERAL_INFO.OUTAGE_REVIEWED_COUNT,
GENERAL_INFO.REVIEWED_PERCENT,
GENERAL_INFO.OVERAL_PERCENTAGE,
SCORING_INFO.FC_NO,
SCORING_INFO.ATM_NO,
SCORING_INFO.WORK_OF_DATE,
SCORING_INFO.RESOLUTION_DATE,
OUTAGE_INFO.OUTAGE_DESC,
GENERAL_INFO.COMMENTS
ORDER BY
GENERAL_INFO.RECORD_NO,
AUDIT_INFO.AUDIT_DESC
PIVOT
AUDIT_INFO.AUDIT_DESC

my click event is as follows:

Private Sub Command5_Click()

auditDataEnvironment.Command1_Grouping txtRecord_No
DataReport1.Show

End Sub

my Parameter info is as follows:
Parameter Name: Param1
Direction : Input
Data Type: asInteger
Precision: 0
Scale: 0
Size: 4
Host Data Type: Long(VT_14)
Required: True
**************************************************************

As I explained earlier, if I exclude the WHERE clause or set the WHERE clause to say,
WHERE GENERAL_INFO.RECORD_NO = 1,
then my grouping option will work and the report will print fine. But if I place the WHERE clause as shown above and select the Parameters tab in the property dialog and set all the needed parameter properties, I lose my grouping!

Any help would be greatly appreciated!
Thanx ERM

 

[chernandez2000]

Bluenoser - glad it helped you out.

If you are still working on the Report Sections Do Not Match problem, here's a link in MSDN (IF you dont happen to have a copy, got to MSDN.MICROSOFT.COM) - Do a search for:

PRB: 8570 Report Sections Do Not Match DataSource

 

[EnS]

F.Y.I.

After several brain splitting and frustrating hours, I found an answer.

Instead of creating a parent command in my DE, I placed a SHAPE
statement in the data report initialization sub.

I first had to changed my connection string form
provider=Microsoft.Jet.OLEDB.4.0;Password=&quot;&quot;;User ID=Admin;
Data Source=<path\db.mdb>
to
provider=MSDataShape.1;Data Source=<path\db.mdb>;
Data Provider=Mirosoft.Jet.OLEDB.4.0

I then placed the following code in DataReport_Initializate()

Dim rs as Recordset

With DE.Connection
Set rs = .Execute _
(&quot;SHAPE {TRANSFORM SUM(SCORING_INFO.SCORE) AS CAT_SCORE &quot; _
& &quot;SELECT &quot; _
& &quot; GENERAL_INFO.RECORD_NO, &quot; _
& &quot;GENERAL_INFO.AUDIT_DATE, &quot; _
& &quot;(EMP_F_NAME &' '& EMP_L_NAME) AS EMP_NAME, &quot; _
& &quot;GENERAL_INFO.AUDITOR, &quot; _
& &quot;GENERAL_INFO.RESOLVED_OUTAGE_COUNT, &quot; _
& &quot;GENERAL_INFO.OUTAGE_REVIEWED_COUNT, &quot; _
& &quot;GENERAL_INFO.REVIEWED_PERCENT, &quot; _
& &quot;GENERAL_INFO.OVERAL_PERCENTAGE, &quot; _
& &quot;SCORING_INFO.FC_NO, &quot; _
& &quot;SCORING_INFO.ATM_NO, &quot; _
& &quot;SCORING_INFO.WORK_OF_DATE, &quot; _
& &quot;SCORING_INFO.RESOLUTION_DATE, &quot; _
& &quot;OUTAGE_INFO.OUTAGE_DESC, &quot; _
& &quot;GENERAL_INFO.COMMENTS, &quot; _
& &quot;SUM(SCORING_INFO.SCORE) AS LINE_SCORE &quot; _
& &quot;FROM &quot; _
& &quot;(EMPLOYEE_INFO INNER JOIN GENERAL_INFO &quot; _
& &quot;ON EMPLOYEE_INFO.EMP_NO = GENERAL_INFO.EMP_NO) &quot; _
& &quot;INNER JOIN (AUDIT_INFO INNER JOIN (OUTAGE_INFO INNER JOIN &quot; _
& &quot;SCORING_INFO ON OUTAGE_INFO.OUTAGE_CODE = &quot; _
& &quot;SCORING_INFO.OUTAGE_CODE) ON AUDIT_INFO.AUDIT_CODE = &quot; _
& &quot;SCORING_INFO.AUDIT_CODE) ON GENERAL_INFO.RECORD_NO = &quot; _
& &quot;SCORING_INFO.RECORD_NO &quot; _
& &quot;WHERE &quot; _
& &quot;GENERAL_INFO.RECORD_NO = &quot;& <entered value> &&quot; &quot; _
& &quot;GROUP BY &quot; _
& &quot;GENERAL_INFO.RECORD_NO, &quot; _
& &quot;GENERAL_INFO.AUDIT_DATE, &quot; _
& &quot;(EMP_F_NAME &' '& EMP_L_NAME), &quot; _
& &quot;GENERAL_INFO.AUDITOR, &quot; _
& &quot;GENERAL_INFO.RESOLVED_OUTAGE_COUNT, &quot; _
& &quot;GENERAL_INFO.OUTAGE_REVIEWED_COUNT, &quot; _
& &quot;GENERAL_INFO.REVIEWED_PERCENT, &quot; _
& &quot;GENERAL_INFO.OVERAL_PERCENTAGE, &quot; _
& &quot;SCORING_INFO.FC_NO, &quot; _
& &quot;SCORING_INFO.ATM_NO, &quot; _
& &quot;SCORING_INFO.WORK_OF_DATE, &quot; _
& &quot;SCORING_INFO.RESOLUTION_DATE, &quot; _
& &quot;OUTAGE_INFO.OUTAGE_DESC, &quot; _
& &quot;GENERAL_INFO.COMMENTS &quot; _
& &quot;PIVOT &quot; _
& &quot;AUDIT_INFO.AUDIT_DESC} AS Commad1 &quot; _
& &quot;COMPUTE Command1 BY 'RECORD_NO', &quot; _
& &quot;'AUDIT_DATE','EMP_NAME','AUDITOR', &quot; _
& &quot;'RESOLVED_OUTAGE_COUNT', &quot; _
& &quot;'OUTAGE_REVIEWED_COUNT', &quot; _
& &quot;'REVIEW_PERCENT', &quot; _
& &quot;'OVERALL_PERCENTAGE','COMMENTS'&quot
End With

Set Me.DataSource = rs

Me.Refresh

End Sub

I then went and set the datamember property for all textboxes
contained in the detail section of the report to reflect <Command1>
and left all the datamember property for all the textboxes contained
within the header and footer as blank. The report now runs like a dream.

Hope this helps out somebody.
ERM

 

[Schmendrick]

I am building a report using Datareport Designer basically just for formating. (I have a bunch of other reports and want this one to look the same.) Anyway. I placed a bunch of labels in the report and filled them in with code like this:
EmployeeBalance2.Sections(&quot;Section1&quot.Controls(&quot;label10&quot.Caption = Ctotal
EmployeeBalance2.Sections(&quot;Section1&quot.Controls(&quot;label11&quot.Caption = CHtotal

It all works great... except that it prints it all twice!

label 10 Ctotal
label 11 CHtotal

label 10 Ctotal
label 11 CHtotal

Why is that???

Thanks in advance!

 

[bluenoser337]

Schmendrick...can't answer your question but it makes me ask another one...where do you place the code for changing label captions, etc. Does it go in the report itself? In what kind of sub? (new to me) THANKS!

 

[alanf]

Schmendrick...Have you checked the results of your query before it goes into the report? Maybe everything is coming up twice in the query because of your join condition.

 

[Schmendrick]

bluenoser337... You can place the code anywhere in your program itself... I havent tried putting anything in the report (new to the designer)

alanf... Thats not it (unfortunatly). I had the same query printing to a listbox before and it worked great (just looked ugly)

Here is part of the sub that creates/calls the report:

SQLtext = &quot;SELECT * FROM INVOICE WHERE SOLDBY = '&quot; & User & &quot;';&quot;
Set rst = Login.db.OpenRecordset(SQLtext)
If rst.RecordCount = 0 Then
Msgbox &quot;No Transactions for this user on this date&quot;
Exit Sub
End If
rst.MoveLast: rst.MoveFirst
Do Until rst.EOF
If rst.Fields(&quot;HOWPAID&quot = &quot;MC &quot; Then
Mtotal = Mtotal + Val(rst.Fields(&quot;TOTALAMOUNT&quot)
MCount = MCount + 1
ElseIf rst.Fields(&quot;HOWPAID&quot = &quot;Visa &quot; Then
Vtotal = Vtotal + Val(rst.Fields(&quot;TOTALAMOUNT&quot)
VCount = VCount + 1
ElseIf rst.Fields(&quot;HOWPAID&quot = &quot;Discover&quot; Then
Dtotal = Dtotal + Val(rst.Fields(&quot;TOTALAMOUNT&quot)
DCount = DCount + 1
ElseIf rst.Fields(&quot;HOWPAID&quot = &quot;Check&quot; Then
CHtotal = CHtotal + Val(rst.Fields(&quot;TOTALAMOUNT&quot)
CHCount = CHCount + 1
ElseIf rst.Fields(&quot;HOWPAID&quot = &quot;Cash&quot; Then
Ctotal = Ctotal + Val(rst.Fields(&quot;TOTALAMOUNT&quot)
CCount = CCount + 1
ElseIf rst.Fields(&quot;HOWPAID&quot = &quot;Amex&quot; Then
Atotal = Atotal + Val(rst.Fields(&quot;TOTALAMOUNT&quot)
ACount = ACount + 1
End If
rst.MoveNext
Loop

EmployeeBalance2.Sections(&quot;Section4&quot.Controls(&quot;label1&quot.Caption = &quot;Employee Balance Sheet for &quot; & frmChooseEmp.EmpList.Text
EmployeeBalance2.Sections(&quot;Section1&quot.Controls(&quot;label10&quot.Caption = Ctotal
EmployeeBalance2.Sections(&quot;Section1&quot.Controls(&quot;label11&quot.Caption = CHtotal
EmployeeBalance2.Sections(&quot;Section1&quot.Controls(&quot;label12&quot.Caption = Mtotal
EmployeeBalance2.Sections(&quot;Section1&quot.Controls(&quot;label13&quot.Caption = Vtotal
EmployeeBalance2.Sections(&quot;Section1&quot.Controls(&quot;label14&quot.Caption = Dtotal
EmployeeBalance2.Sections(&quot;Section1&quot.Controls(&quot;label15&quot.Caption = Atotal
RunningTotal = Ctotal + CHtotal + Mtotal + Vtotal + Dtotal + Atotal
EmployeeBalance2.Sections(&quot;Section1&quot.Controls(&quot;label16&quot.Caption = RunningTotal

EmployeeBalance2.Show

 

[mdmonir]

Hi ,

I just downloaded visual studio service pack 5.Now my forms are working fine when I try to print them in landscape mode but my Data report which has grouping feature is not working.I use different ways such as

Printer.Orientation = vbPRORLandscape
DataEnvironment1.cmReports_Grouping DataCombo1
DataReport1.PrintReport
This time I got a printout with portrait mode but when I use
Printer.Orientation = vbPRORLandscape

DataEnvironment1.cmReports_Grouping DataCombo1
DataReport1.PrintForm
I did not even get a printout.Please send me some comments regarding this problem.Thanks in advance.

 

[jrvbweenie]

Can anyone tell me how to change the printer's paper source (bin) for printing a Data Report Item in VB? Any suggestions would be helpful.

 

[jpinto]

Is there a way to make a report without the Data Environment and using DAO connection instead of ADO?

Thanks,

João Pinto

 

[johnwm]

Read faq222-2244 for guidance on forum usage - it's generally better to ask a new question in a new thread rather than tagging on the end of an old one.

For this question:
Yes - you can set the DataReport object Datasource property to any recordset directly in code

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts