VB6 Datareport

[Wernerh]

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.Sec tions("section5 ").Controls.Ite m("Function1"). DataField = "Total"

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

Thanks, if anyone could shed light

[jeffstl]

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.Sec tions("section5 ").Controls.Ite m("Function1"). 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

[code=sql]
Select SUM(Allocate) from MyTable where Allocation = 1
[/code]

A query you can re-use if you can pass a parameter in

[code=sql]
Select SUM(Allocate) from MyTable where Allocation = MyParameter
[/code]

[jeffstl]

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.

[Wernerh]

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.

[QVeen72]

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

[Wernerh]

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

[Wernerh]

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.Connecti onString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & App.Path & "\data\adodb1s. mdb;Persist Security Info=False"
Adodc2.CursorLo cation = adUseClient
Adodc2.RecordSo urce = "Select * from Table1"
Adodc2.Refresh

So can I use the same to get query data????
Proposed code:
Adodc2.Connecti onString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & App.Path & "\data\adodb1s. mdb;Persist Security Info=False"
Adodc2.CursorLo cation = adUseClient
Adodc2.RecordSo urce = "Select * from ReportQ"
Adodc2.Refresh

Is that acceptable and should it read the query?

[QVeen72]

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):
[code=oracle]
Select '1' As MyGrp , A, B From MyTable
Union All
Select '2' As MyGrp, A, Sum(B) From MyTable Group By A
Order By MyGrp, A;
[/code]

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

[Wernerh]

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):
[code=oracle]
Select '1' As MyGrp , A, B From MyTable
Union All
Select '2' As MyGrp, A, Sum(B) From MyTable Group By A
Order By MyGrp, A;
[/code]

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