By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,116 Members | 1,319 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,116 IT Pros & Developers. It's quick & easy.

VB6 Datareport

100+
P: 104
I have a datareport that is generated from mdb, it reports each line of the database as required in the report. There are records that have the same allocation number, but with diff prices in the database. I want the report to also list the sum of the allocation number at the bottom of the report (Section 5 of the report) To explain : My table (simplified) has these records.

Allocation Amount
1 100
2 50
1 75
3 34
1 20
etc etc

My report list as above, but would want a sum of example allocation "1" beneath in section5

1 195 (this is the sum of all the 1's)
2 50
etc

This is the code i have but it just totals the whole database.

DataReport1.Sections("section5").Controls.Item("Fu nction1").DataField = "Total"

Not sure if I have to ref the control (funtion1) to a field in the db?

Thanks, if anyone could shed light
May 13 '08 #1
Share this Question
Share on Google+
8 Replies


jeffstl
Expert 100+
P: 432
I have a datareport that is generated from mdb, it reports each line of the database as required in the report. There are records that have the same allocation number, but with diff prices in the database. I want the report to also list the sum of the allocation number at the bottom of the report (Section 5 of the report) To explain : My table (simplified) has these records.

Allocation Amount
1 100
2 50
1 75
3 34
1 20
etc etc

My report list as above, but would want a sum of example allocation "1" beneath in section5

1 195 (this is the sum of all the 1's)
2 50
etc

This is the code i have but it just totals the whole database.

DataReport1.Sections("section5").Controls.Item("Fu nction1").DataField = "Total"

Not sure if I have to ref the control (funtion1) to a field in the db?

Thanks, if anyone could shed light

Not sure exactly what you have available to work with but you would need a query like this to get that data into a report

Expand|Select|Wrap|Line Numbers
  1. Select SUM(Allocate) from MyTable where Allocation = 1
  2.  
A query you can re-use if you can pass a parameter in

Expand|Select|Wrap|Line Numbers
  1. Select SUM(Allocate) from MyTable where Allocation = MyParameter
  2.  
May 13 '08 #2

jeffstl
Expert 100+
P: 432
Usually data reports are based on a returned record set or query of some kind. You would need to modify your query to also return sums basically.
May 13 '08 #3

100+
P: 104
Thank you for the response. I have tried the code you suggested, but cannot get it to work. I have now written a query in mdb called ReportQ. So maybe it would be easier to read from that query in the report via code. The problem is I have no dea what the syntax would be to call that data from the query into the report with code? Is there any place i could go look it up? Any suggestions would be greatly appreciated.
May 14 '08 #4

QVeen72
Expert 100+
P: 1,445
Hi,

I guess, it would be a better option to show the Group wise report in a different report.
as you can have a Grand Total or show the subtotal (by grouping on that particular field).

Regards
Veena
May 14 '08 #5

100+
P: 104
Hi,

I guess, it would be a better option to show the Group wise report in a different report.
as you can have a Grand Total or show the subtotal (by grouping on that particular field).

Regards
Veena
Hi Veena,

Yes, that is what I was thinking, but to execute thatis another ball game all together :-> (for me anyway)

I need to do this via code as I have 28 tables that need to be reported on, so don't want to create 28 different reports to link to all the tables. If I code it, then it will be much easier. I assume you are referring to section5 in vb6 report (grouping). If so, where can I get an example of code that would read a query and list it in a text or label within that datareport? I have tried vb6 help, but it does not give me "how to" there. I have the report linked up to the mdb (all in code) and reading the tables in already done, so all that is working, just the step to read the query is a problem, I am a newbie, so therefore the "stupid" questions. Sorry...

Thanks
Werner
May 14 '08 #6

100+
P: 104
I am trying to solve my own problem.

Here is the code i am using to connect to the table to get the data in the report:

Code:
Adodc2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\adodb1s.mdb;Persist Security Info=False"
Adodc2.CursorLocation = adUseClient
Adodc2.RecordSource = "Select * from Table1"
Adodc2.Refresh

So can I use the same to get query data????
Proposed code:
Adodc2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\adodb1s.mdb;Persist Security Info=False"
Adodc2.CursorLocation = adUseClient
Adodc2.RecordSource = "Select * from ReportQ"
Adodc2.Refresh

Is that acceptable and should it read the query?
May 14 '08 #7

QVeen72
Expert 100+
P: 1,445
Hi,

I Guess there is no way out.. but to print 2 seperate reports.
I have tried this with CR, there also it is not possible.
In CR we can do with Sub-reports..
Not sure about datareports..
Your requirement is to print Set of 2 details(Records)

There is one way round..

Say you have a MyTable (A, B)
You want to print Details of A, B
and In Summary you want to print:
A, Sum(B) Group by A
Change the Command Query(On which the Data Report is based):
Expand|Select|Wrap|Line Numbers
  1. Select  '1' As MyGrp , A, B From MyTable 
  2. Union All
  3. Select  '2' As MyGrp, A, Sum(B) From MyTable Group By A
  4. Order By MyGrp, A;
  5.  
With this Code,Your Data is retreived along with the Group Info..
No in Data Report Create a Group on field "MyGrp"
First Group prints all the details and second group prints the Grouping Info..

Tedious... but works..

Regards
Veena
May 14 '08 #8

100+
P: 104
Hi,

I Guess there is no way out.. but to print 2 seperate reports.
I have tried this with CR, there also it is not possible.
In CR we can do with Sub-reports..
Not sure about datareports..
Your requirement is to print Set of 2 details(Records)

There is one way round..

Say you have a MyTable (A, B)
You want to print Details of A, B
and In Summary you want to print:
A, Sum(B) Group by A
Change the Command Query(On which the Data Report is based):
Expand|Select|Wrap|Line Numbers
  1. Select  '1' As MyGrp , A, B From MyTable 
  2. Union All
  3. Select  '2' As MyGrp, A, Sum(B) From MyTable Group By A
  4. Order By MyGrp, A;
  5.  
With this Code,Your Data is retreived along with the Group Info..
No in Data Report Create a Group on field "MyGrp"
First Group prints all the details and second group prints the Grouping Info..

Tedious... but works..

Regards
Veena
Thanks Veena,

I will give it a go and hopefully get ot to work.

I have it working already by exporting the query as a .snp file and then opening it with snapshot viewer, but for obvious reasons the client requires Snapshot which is not ideal. I can bundle snapshot with my software, but was trying to get rid of it all together. I will try your suggestions and hopefully wil be able to do it that way. Thanks again.
Cheers
Werner
May 14 '08 #9

Post your reply

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