Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Grouping On Multiple Columns In Data Reports


hi,
anybody help me out this problem i want a report where i should get report on two columns like this
 column1
       column2
                 -----------
                 -----------
                 -----------
       column2
                 -----------
                 -----------
 column1
              follows

this is the format i want i tried this with putting child command but i can't do that please help me out of this problem
thank you

 




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Grouping And Sub-Grouping In Data Reports
How can I achieve having one group and inside this, another sub-group information in data reports?

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

Grouping Records In Data Reports / Crystal Reports
Hi

I want to group records in my Data Report based on 2 particular fields. Just see the example below.

Below is the Table Structure and its values

UnitID Part Quantity
1 xx 5
1 xy 10
2 xx 5
2 xy 10


Now I want to result to show the summation of quantities based on the part only.

so the result should be

Part Quantity
xx 10
xy 20

It would be fine if someone can tell me how to do this in Crystal report/ VB data reports


Thanks
Kumaran

Grouping With Data Reports
I have been trying to generate a report with the Visual Basic Data Reports and every time I try to run a report that is grouped by certain fields and has group headers and footers in the report layout I get the error "Report Sections do not match data source". I would appreciate any suggestions on how to fix the error

Grouping With Data Reports
Hi i'm using ADO with MS SQL Server Stored Procedures, how i can shape the data into a hierarchical recordset???

The reason i use stored procedures is that the different users have different data returned to them based on their rights.

Grouping In Data Reports
is it possible to add grouping levels in data reports in VB?
if so, whta is the syntax of the code for it?

Grouping In Data Reports, How?
I've worked with data reports in Access for a long time. Now, my boss asks me data reports in Visual Basic 6. However, the migration seems impossible. The data report in Access has 4 grouping headers to show data hierarquicaly. I've just added a grouping to the Data Environment and a header to the Data Report. However, I can't add more groupings. It seems impossible to me, since I've never worked with these data designers in VB. How can I make a report with many groupings with little effort?

Thanks in advance.

To boldly code, where no programmer has compiled before!

Grouping With Data Reports And Using Shaped SQL.
I want to do a report like this:

Group Header section: (Section 6) - From tbl_Payments
****************
NO NAME
****************
OR Number : 00001
OR Date : 11/19/2007
Payor Name: Joe Schmo
Sum(Amount Paid): Php 250.00

Details Section: (Section 1) - From tbl_Transactions
*******************
Invoices Due Amount
*******************
01 Php50.00
01 Php250.00

Here's my code.


Code:
Private Sub ShowORReport(InvoiceNo As String)
Dim sql As String
Dim i As Integer

sql = "SHAPE {SELECT tbl_Payments.ORId, tbl_Payments.DatePaid, " _
& "tbl_Payments.PayorName, SUM(tbl_Payments.AmountPaid) " _
& "FROM tbl_Payments " _
& "GROUP BY tbl_Payments.ORId, tbl_Payments.DatePaid, tbl_Payments.PayorName " _
& "HAVING tbl_Payments.ORId = " & InvoiceNo & "} " _
& "APPEND ({SELECT tbl_Transactions.InvoiceID, tbl_Transactions.DueAmount " _
& "FROM tbl_Transactions " _
& "WHERE tbl_Transactions.InvoiceID = " & InvoiceNo & "" _
& "ORDER BY tbl_Transactions.TransID} AS SECONDQUERY " _
& "RELATE tbl_Payments.TransID TO tbl_Transactions.TransID)"

rs.Open sql, sConn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not rs.EOF And Not rs.BOF Then
Set rptOfficialReceipt2.DataSource = rs
'GROUP HEADER
With rptOfficialReceipt2.Sections("Section6")
'Access all the textboxes in the detail section
For i = 1 To .Controls.Count
If TypeOf .Controls(i) Is RptTextBox Then
.Controls(i).DataField = rs.Fields(i - 1).Name
End If
Next
End With

'DETAILS
With rptOfficialReceipt2.Sections("Section1")
For i = 1 To .Controls.Count
If TypeOf .Controls(i) Is RptTextBox Then
.Controls(i).DataField = rs.Fields(i - 1).Name
End If
Next
End With

rptOfficialReceipt2.ReportWidth = 9000 'set the size of report
rptOfficialReceipt2.Refresh
rptOfficialReceipt2.WindowState = vbMaximized
rptOfficialReceipt2.Show vbModal
Else
MsgBox "Sorry, no Official Receipt available."
End If

rs.Close
Sub

Error message:
Datashape command text contains a syntax error at or near position 402 in the command. The text near the error is "RELATE tbl_Payments.TransID TO tbl_Transactions.TransID}"

What am I doing wrong?

The table structure for tbl_Payments and tbl_Transaction are as follows:

tbl_Payments:
---------------
PaymentID
ORId
DatePaid
TransID (tbl_Transactions is linked with this)
PayorName
AmountPaid

tbl_Transactions:
----------------
TransID (tbl_Payments is linked with this)
InvoiceID
ClientName
ClientAddress
DateSold
ItemID
Quantity
DueAmount
IsSettled

Thanks.

Grouping Information On Data Reports
Can anyone tell me why there is a function on data reports which doesn't work???
I cannot understand it's existence!!!
You can make by your own a self-test:
Create a simple data report (using dataenvironment). Set the data report's datamember property to the command that you have specified on the dataenvironment and then right click on the report and select 'Retrieve Structure'. Then drag & drop all the desired fields on the report. This work well!!!
But the following doesn't:
If you try to right click on the report and select: 'Insert Group Header', then you will get a message: "Data report sections do not match with data source" when you run the program.
Only if you remove this group header the program will work again...
And I am asking, where is based the existence of 'Insert Group Header' while it doesn't work???

Michael Vlastos
Automation Engineer
Company SouthGate Hellas SA
Development Department
Athens, Greece

Grouping Records In Data Reports
Hi!

I'd like to know how to group records in data reports
without data environment.

How do I proceed to group records in a structure like this one:

Table: Clients

Field: Cli_Id
Field: Cli_Name

Table: Orders

Field: Cli_Id
Field: Product_Id
Field: Qty

I'd like to use ADO programatically, without data environment.

Simple reports are easy. I just have to set the .DataSource
property with a Recordset but it doesn't work when I use
SQL with "Group By" clause even if I put the group field in the
data report.

Can anyone help me please?

Thanx!

Sorting And Grouping Data In Reports (access)
hi everybody!
i was wondering what i should do to sort and group data in a given report (access) from a macro or vb code.
thanks in advance!

Data Report Multiple Grouping
i am creating a cash/cheque collection report. i haved added a queried command from a single foxpro2.5 table. I want to create more than one grouping in a data report so that the output will be in the following order

    currency code
      location code
         payment mode


 i have done it in foxpro2.5 but i want it in visual basic. please advise me.

*** Resolved-somewhat *** Grouping Data In Data Reports
I am trying to print out a report using data reports. Thanks to some awesome members here I have now got a working report that prints out the data from my database based on a chosen date.

My problem is that I need to do some grouping of the data based on one of the fields in the database. When I followed the tutorial in the MSDN I ended with the error that the report is wider than the paper. I am not sure how this is happening because I can display this report without the grouping and it fits on the paper. I also find that when I change things in the data environment and then try to add a new header to do the grouping by I cannot drag my field from the new grouping command onto my new header.

Attached is a zip file with my project and database. In it's present form it works great but it doesn't do any grouping. I have included an excel file and a Word file that shows the type of format I am looking for. I will try to head off a couple of questions here:

1. Yes I do want 2 or 3 extra spaces between each of the pickup time groupings. That way they can write in additions to the schedule which sometimes happens.

2. I can have pickup times starting as early as 8 AM and ending at 4 PM Normally they only occur on the hour but that is set up in my actual program and database.

3. If I have too many appointments in one day to fit on one page I want to split it onto 2 pages and place the AM on page one and the PM on page two.

If there are other questions I will be back on to check this post in about 12 hours from now.

Thanks to everyone who even reads this post because you at least are willing to help. I really do appreciate any help given and if someone just wants to guide me to the resources I am willing to try to solve this myself. Please understand that I do research and try many things before I post here but when I post here I am usually quite stumped at that point.

Thanks again everyone. Later,


Cliff R.
Mesa, AZ
Check out my website at http://crogers.org
Being an organ donor is giving the greatest gift that someone can give.
It is giving the gift of life. Please register to be an organ donor.


Edited by - crogers on 9/24/2005 8:10:35 PM

Crystal Reports : Total Of A Field In Multiple Columns
Hi

I have created one report with multiple columns (Right click on Details section -> Select 'Format Section' from popup menu -> Check mark the field 'Format with Multiple Columns').

'Weight' field is in both columns. Now i want the total of Weight in each column on every page.

Please help me with this issue

Display Multiple Records In Columns Using Data Report
i know how to display records and grouping it but im having trouble creating a report with mutiple records in columns arrange in some like this way

Boys: Girls:

name1 name2
name3 name4
name5

where either the boys column or the girls column would exceed the other

can anyone help me?

Drawing Vertical Lines Between Columns In Data Report / Crystal Reports
Hi Guys,

I want to draw VERTICAL lines between columns of my report made using DATA REPORT of CRYSTAL REPORTS. The Text boxes can grow but the lines & image control cant.

How do i do it ???????

Thanks,
Joe

Grouping The Values In Two Columns
I have a excel file with two columns

Col1 Col2
A Red
A Red
A Green
A Red
A Yellow
A Black
A Red
A Green
A Red
A Red
A Green
A Red
A Green
A Red
B Red
B Green
B Green
B Red
B Red
B Green
B Green
C Black
C Green
C Green
C Red
C Red
C Red
D Red
D Green
D Green
D Black
E Yellow
E Green
E Red
E Red

I want this output

Red Green Yellow Black
A 8 4 1 1
B 3 4 0 0
C 4 2 0 1
D 1 2 0 1
E 2 1 1 0

What would be the best way to achieve the same?
There can be as many distinct values in Col1 and Col2

Sending Data To Populate Multiple Columns In Database Table
Hi all! Hope ya had a good christmas!!

Im trying to send data from one textbox to an access database.

For example, the textbox will have information displayed as follows:

firstname1 lastname1 age1
firstname2 lastname2 age2
firstname3 lastname3 age3

etc....

The data has to go to corresponding table columns in the database.

Does anyone know how I can do this?

Thanks a lot all!

Multiple Data Reports
I have searched this forum and learned much about Data Reports. I have a problem that I hope someone can help with. Is it possible to have more than one Datareport in a DataEnvironment or DEConnection?
     I ask this because I have a project where I have one Data report that is working fine. When I add a second one and add a Decommand to it, it defaults to adCmdStoredProc. When I try to edit/change it to cmdText or cmdTable, I get the wonderful "Visual Basic has encountered a problem......Do I want to send this error report to Microsoft?" VB then crashes back to desktop!
     At this point I can't even edit the first DeCommand Type or cmdText, which still works fine. I have tried adding another environment, another connection. Nothing seems to work. Has anyone else encountered this problem? Does anyone know of a good Tutorial or VB manual that concentrates on DataEnvironments/DataReports?

REPORTS FROM MULTIPLE DATA SOURCE?
How do you do this: Create a report from multiple database sources. Can this be done using VB? Or do you have to pull out information manually and then do it that way?

Please advise! I just need ideas, and possible solutions.
Does crystal reports writer allow for access to multiple db's CONCURRENTLY??

Printing Multiple Data Reports
How do I print multiple data reports using data report designer.

Dynamically Reference Multiple Data Reports
Does anybody know if it is possible to reference data reports using a variable?
Eg, I have more that one data report so I would like to use a variable to establish which report I am referencing.

I need something like me.controls("text1") but for a data report instead of a control....

Thanks

HowTo Do Multiple Groups In Data Reports?
How do you design multiple groups in data reports at design time. I am able to do this report in access2k but my problem is not all of my clients have access2k.
if more info is needed ask.

example

Report Title
Report Date

Month Year
     Department
          name    Department    Position   hire date
          name    Department    Position   hire date
          name    Department    Position   hire date
          name    Department    Position   hire date
     Department
          name    Department    Position   hire date
          name    Department    Position   hire date
{new page}
Month Year
   Department
          name    Department    Position   hire date


Jason H
Database Admin & Program Developer
www.LuckyEagleCasino.com

Grouping In Reports
Hey everyone,

I'm using the Microsoft Data Report Designer for reports. My question is this. Is there any way to use the grouping sections w/o using a Data Environment. The only way that I've seen it done are with Data Environments.

I'm using ADO to query the database.

Thanks alot,

jayceepoo

Grouping Reports, Anyone ?
How can i make report grouping ?? It's like when we using crystal report, by selecting field from different table in our DB and displayed all of the selected field in the report. I have extract the tables in my DB and showing Table, fields etc. But now i'm stuck when building the query for my reports. The question is :

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1. How can i read is it must be INNER JOIN, LEFT JOIN, Etc ???
2. How to read the primary key from each table ( Link with No. 1 ) or there's
no index at all ???
++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Data Type has been solved
For the query i have array variables to manages.Now on i'm using access DB
for my test.
Thank's in advance for any help . . .

Throught the river of steel we'll go, when the dragon lies bleeding . . .

Crystal Reports Grouping
i'm trying to create an invoice report with crystal reports, so that it has all the order information (order ID, address, etc.) at the top, the order details (product, quantity, etc.) in the middle, and a summary at the bottom.

in the details section, i need to have the sum of the product price * the quantity for each item (the total), at the bottom of the report i need the sum of the total (the subtotal), the surcharge, and the sum of the subtotal and surcharge.

problem is, i keep getting a group by error due to those aggregates. at the moment, i have all the order information in a group header (grouped by the order ID), the order details in the details section, and the summary in the group footer.

if that makes sense, has anyone got any idea how to do this?

Help!!! Crystal Reports .. Grouping
I want to be able to group the data in my report by a specific field. But when I choose Insert | Group Selection the field I wish to group by does not appear in the 'Sort By' combobox. There is no problem placing this field in any of the default groups. Can anyone help?? This is driving me mad.

Thanks,
Mel.

Crystal Reports New Page Grouping
How do I make Crystal report start each new group on a new page. I am creating a report that groups by name. But I only want one name per page

Grouping With Crystal Reports And Exporting The RTF Into DOC Using VB
hello!
this is my first post here. i have a problem in crystal and vb as well. first in crystal reports. in the report i have two tables that are link together by a common fld say QNum and ExNum. When i group qnum which is of text type, it is not sorted accordingly e.g. 1,10,11,2,3. And exnum contains duplicate values eg. 1,1,2,3,4,5,2. what i want for crytal is to sort the qnum and group the ExNum. the value of Qnum must also be like ExNum.

Regarding the RTF to DOC here is my problem.
I want to write to an Rich Text Box using my code then later on saving it as an RTF or DOC. the rich text box will have a multiple lines. please do help me.

Grouping From 3 Tables (Crystal Reports)
Imagine the three tables

1. farmers
2. equipment
3. animals

Would it be possible to group these into one report?

For example, one record might be:


Code:
Farmer Bob
- Equipment:
- Tractor
- Combine Harvester

- Animals:
- Pigs
- Sheep
Using Crystal's grouping, I can get farmers plus equipment, or farmers plus animals, but not all three at once. Perhaps there is some special way of doing that?

Crystal Reports Grouping/Sorting
I am grouping by one field in my Crystal Report but need to be able to sort by a different field within that group.

Any crystal gurus out there?

Crystal Reports Grouping && Sorting
Can a Crystal report (version 4) be grouped by one field and sorted by another?

When you go into Insert...Group Section...a drop down box permits you to group and sort by a selected field. I would like to group and sort according to a "Name" field but for some reason the drop-down list does not include the "Name" field. Therefore, I chose to group according to a "Name#" identifier. This allows me to group correctly but then sorts wrong because it is sorting according to a number and not the name.

Does this make any sense. Anyone experience something similar? Help Please.

Crystal Reports 9 Grouping Through Code.
I have been able to come to the point where I can print directly via code, the Report Header, Page Header , details, but cannot do the grouping. Does anyone have any code samples or ideas? Can we do sort within code right here, without having to resort to Data environment,
or other means. Thanks...

Multiple Records New Grouping.
Hi All,

Tried over in DD but nobody wants to talk . Any ideas anybody?

I'll try to explain this the best I can. Im using Access2000 and I am reading in my proxy logs(plain text) into the database and have it split up into IP address, loginname, URL, File Size etc etc etc.
I have about 26K lines per log. Any given user will have approx between 200 - 500 entries per log.
How can I (in Access) build a query/report/form to give me the totals per user. E.g. Adding up all the File Size fields for Dermodyr and giving me the total.
Thanks to all,
Ray.

Datareport Multiple Grouping For 1 Table
Hi,
I am creating a multiple grouping datareport without parent and child replationship. I am trying to use dataenvironment But it only gives me one grouping level.
For example, I want the report to group by country, type_of_sales and productCode. Besides, I also need to get the subtotal for each group. Therefore, there will be 3 grouping levels that is followed by this sequence country, type_of_sales and productCode. All information will be extract from one table called SALES.
Thanks in advance.

Belle

Crystal Reports 10 With VB6.0 -> Sorting, Grouping And Formula Fields
I have a Vb6.0 application calling crystal reports developed in 4.6. The new requirement is to get these reports in CR10 and accordingly update the VB code.

The VB code typically uses
Report1.SortFields(0) = "+{mn_contact.lastname} "
Report1.GroupCondition(2) = "GROUP3;{mn_company.customerid};ANYCHANGE;A"
Report1.SelectionFormula = {V.PROSPECT} = 0
etc etc

I require equivalent code that would be comaptible with CR10.

I tried grouping using the below code

Private Appl As New CRAXDRT.Application
Private Report As New CRAXDRT.Report
Set Report = Appl.OpenReport("C:Documents and Settingsprashanth_d01Desktopproject1Prjt1.rpt")
Report.Application.LogOnServer "crdb_oracle.dll", "vpcs.ad.infosys.com", , "MN06U", "GANESH"
Report.GroupNameFields(1).GroupNameConditionFormula = "GROUP1;{MN_COMPANY.STATE};ANYCHANGE;D"

With CrystalActiveXReportViewer1
.ReportSource = Report
.RefreshEx True
.Refresh
.Visible = True
.viewreport
End With

This returns me the report in the ascending order. The override of descending order given above in the Vb code is not being considered. The grouping in the report has it in ascending order and its returning the same.

CR gurus please help me!!

User Control Container For Grouping Multiple Controls... Yar!
Ahoy there!

Simple one.. but problematic for me..
I need some direction regarding a control container. I'm wanting to group a number of controls together, textboxes/labels/commandbuttons/etc., in a container, so that I can hide and reveal a group of controls simultaneously and with little code. The issue is I can't use a Picturebox or a Frame, because they're not translucent. I decided that I would make a UserControl, but that flopped for some reason.. Even at design time when I place the controls into the UserControl, they become invisible, and remain that way at runtime.. I set the UserControl properties to translucent and container to accomodate.. What should I do?

Needs to be translucent, and a container... Any feedback/direction muuuuch appreciated

Autofilter With Multiple Columns And Multiple Criteria
Hi

I have a sheet of data, which I need to clear up. What I want to do is use the autofilter function to filter out the information that I need, leaving what I don't need visible and then delete the visible rows.

First filter is on column D, where I need to select all rows that are in "Unit1" and then filter on column C to select all rows that are NOT "Team1" or "Team2".

Code I have is:

vb/
Dim CheckRange As Range, DeleteRange As Range

Set CheckRange = Worksheets("Sheet1").Columns(7)
CheckRange.AutoFilter Field:=4, Criteria1:="Unit1"
CheckRange.AutoFilter Field:=3, Criteria1:="<>Team1", Operator:=xlOr, Criteria2:="<>Team2"

Set DeleteRange = CheckRange.SpecialCells(xlCellTypeVisible).EntireRow
DeleteRange.Delete
/vb

However this does not work. It filters out all the non "Unit1" rows fine, but then applied a filter to Column C that does nothing!

Can someone help put me out of my misery?

Grouping A Data Report Programically (without Data Environment)
Does anyone know how to do this? I can set up a simple report using a
connection and recordset object but I can't figure out how to add grouping
levels.

Dominic Wray

Data Report Grouping
Hi,
I am using the data report designer of VB...I want to group a record...

my record is like this...

i have a product...within this i have several subproducts...

i want to display the product, then details of the subproduct, then sum up the details of the subproducts...i guess i need to create a recordset within a recordset...

how do i do this plz help me..

thanq
praveen

Grouping In Data Report
I'm getting an error that says "data field networkadequacyrpt_grouping.ccpn not found". ccpn is the field that I'm grouping on and networkadequacyrpt_grouping is the grouping command name on the grouping tab of the DE properties. The ccpn field is in the detail, group header and group footer sections of my report. My data source and data member appear to be set correctly, what am I missing? Can any one help?

Data Report Grouping
I have a data report problem. Basically I need to create a report of players grouped by various positions. These players need to be grouped by QB, WR, RB, etc....

I am using the data environment to set this up, however I am not sure how I could arrange the recordset to come out like the data report below. The data report only seems to take one grouping, however I believe that I need more than one grouping.

I also need to run this report on one page.

I am stuck, how do I go about grouping multiple players in one data report.
All the information that I need is in one Access table.

The query would look something like this:
SELECT plyName, plyPosition FROM players GROUP BY plyPosition, plyName


So the report should look like this


<left> <middle> <right>
QB RB WR
player 1 player 2 player 3
player 4 player 5 player 6

THe above report does not seem to format correctly in this post. So basically this report should display 3 columns of data.

ANY HELP WOULD BE APPRECIATED. HAVE BEEN TRYING TO FIGURE OUT FOR WEEKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!

Data Report Grouping
I made a report using ADO but I am having problems in showing it in a way I want. The way I want is in the attatchment and the way I have it now also in the attatchemnt. I am so stuck with this thing.The code I am using is:

VB Code:
Dim MyCon As New ADODB.ConnectionDim MyRs As New ADODB.Recordset   Private Sub Command4_Click()Dim rs As New ADODB.RecordsetDim cmd As New ADODB.CommandDim cn As New ADODB.ConnectionDim strSQL As StringDim a As DateDim b As StringDim c As Stringcn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c: emp;Extended Properties=dBASE IV;Persist Security Info=False"  strSQL = "SELECT EXAMINER,APPLN,SUBCLASS,SECTION1,ACT_DATE FROM NEWLAW1.DBF WHERE ACT_DATE>= #" & Me.txtactdate.Text & "# AND ACT_TYPE='DED' AND SECTION1= '" & Me.txtSection.Text & "' And Examiner= '" & Me.txtexaminer.Text & "' AND EX_DATE1 IS NOT NULL ORDER BY EXAMINER;"With cmd    .ActiveConnection = cn    .CommandType = adCmdText    .CommandText = strSQL    .ExecuteEnd With With rs    .ActiveConnection = cn    .CursorLocation = adUseClient    .Open cmdEnd With With DataReport3    Set .DataSource = rs    .DataMember = ""     With .Sections("Section1").Controls        .Item("Text2").DataField = "Examiner"        .Item("Text3").DataField = "Act_date"        .Item("Text1").DataField = "Subclass"        .Item("Text4").DataField = "Section1"    End With    .Refresh    .ShowEnd With  End Sub

Data Reporter Sub-Grouping....
Here is a question that I know that no one will probably try to help figure out..

What I need to do is to make a sub-group in my visual basic data reporter.

This is a small example of what I need.


020 - Agriculture '<==== Grouping Header'
1856 - Mr Smith - 162.02
1856 - Mr Jones - 50.00
Sub-Total ---> 212.02
2011 - Mrs Smith - 20.36
Sub-Total ---> 20.36
2589 - Mr Smith - 25.25
2589 - Mrs Smith - 10.25
2589 - Mr Jones - 20.25
Sub-Total ---> 55.75

Grand-Total ---> 288.13




I can get the 1st grouping down but the sub-grouping I can not figure out.


This is how I do the grouping in a Visual Basic module.


Cn.Open "Provider=MSDATASHAPE;Password=****;Persist Security Info=True;User ID=sa;Data Source=dynamics;Initial Catalog=HOUSE"

With cmd
.ActiveConnection = Cn
.CommandType = adCmdText
.CommandText = " SHAPE {SELECT GL00100.ACTNUMBR_3, GL20000.REFRENCE,GL20000.TRXDATE, GL20000.DEBITAMT, GL20000.ORMSTRNM,BIBUDINFO.DEPARTMNT, BIBUDINFO.DESCRIPTN, BIBUDINFO.BUDGETAMT FROM GL00100 INNER JOIN GL20000 ON GL00100.ACTINDX = GL20000.ACTINDX INNER JOIN BIBUDINFO ON BIBUDINFO.DEPARTMNT = GL00100.ACTNUMBR_2 WHERE GL00100.ACTNUMBR_3 > '2000' AND BIBUDINFO.DEPARTMNT > '019' AND YEAR(GL20000.TRXDATE) = '2002' AND GL20000.DEBITAMT <> '0' AND GL00100.ACTNUMBR_2 <> '026' AND GL00100.ACTNUMBR_2 <> '031' AND GL00100.ACTNUMBR_2 <> '041' AND GL00100.ACTNUMBR_2 <> '102' AND GL00100.ACTNUMBR_2 <> '123' Union All SELECT GL00100.ACTNUMBR_3, GL30000.REFRENCE, GL30000.TRXDATE, GL30000.DEBITAMT, GL30000.ORMSTRNM,BIBUDINFO.DESCRIPTN, BIBUDINFO.DEPARTMNT, BIBUDINFO.BUDGETAMT FROM GL00100 INNER JOIN GL30000 ON GL00100.ACTINDX = GL30000.ACTINDX INNER JOIN BIBUDINFO ON BIBUDINFO.DEPARTMNT = GL00100.ACTNUMBR_2 WHERE GL00100.ACTNUMBR_3 > '2000' AND BIBUDINFO.DEPARTMNT > '019' AND YEAR(GL30000.TRXDATE) = '2002' AND GL30000.DEBITAMT <> '0' AND GL00100.ACTNUMBR_2 <> '026' AND GL00100.ACTNUMBR_2 <> '031' AND GL00100.ACTNUMBR_2 <> '041' AND GL00100.ACTNUMBR_2 <> '102' AND GL00100.ACTNUMBR_2 <> '123' ORDER BY BIBUDINFO.DEPARTMNT, GL00100.ACTNUMBR_3, GL20000.TRXDATE} AS Command2 COMPUTE Command2 BY 'DEPARTMNT','DESCRIPTN','BUDGETAMT'
.Execute
End With

With Rs
.ActiveConnection = Cn
.CursorLocation = adUseClient
.Open cmd
End With
Set Rs1 = Rs(0).Value

With DBiennialReport2001
.Hide
Set .DataSource = Rs
.DataMember = ""
.Sections("Section2").Controls("lblYear1").Caption = Trim(txtSYear.Text)
.Sections("Section2").Controls("lblYear2").Caption = Trim(txtEYear.Text)
'This with statement is the grouping area.'
With .Sections("section4").Controls
For intCtrl = 1 To .Count
aryGroup(1) = "DEPARTMNT"
aryGroup(2) = "DESCRIPTN"

If TypeOf .Item(intCtrl) Is RptTextBox Then
.Item(intCtrl).DataMember = ""
.Item(intCtrl).DataField = aryGroup(intCtrl)
End If
Next
End With

'The rest of the with statements are the details and summary'
With .Sections("Section1").Controls
For intCtrl = 1 To .Count
If TypeOf .Item(intCtrl) Is RptTextBox Then
.Item(intCtrl).DataMember = "Command2"
.Item(intCtrl).DataField = Rs1(z).Name
z = z + 1
End If
Next intCtrl
End With
With .Sections("Section5").Controls
For intCtrl = 1 To .Count
If TypeOf .Item(intCtrl) Is RptFunction Then
.Item(intCtrl).DataMember = "Command2"
.Item(intCtrl).DataField = "DEBITAMT"
End If
Next
End With
With .Sections("Section5").Controls
For intCtrl = 1 To .Count
If TypeOf .Item(intCtrl) Is RptTextBox Then
.Item(intCtrl).DataMember = ""
.Item(intCtrl).DataField = "BUDGETAMT"
End If
Next
End With
.Refresh
.Show
End With

Grouping In The Data Reporter...
Is there a way to do a SQL Grouping command in the VB module and then use it for the DATA REPORTER?

Data Report Grouping
Hie VB Gurus

I am doing a data report with 3 groupings. I have only been able to do one
group. Now I am stuck because I dont know how to do another grouping.
Below is the groups that I want in the report

Department(First Level Group)
Role Name(Second level group)
Individual Names (Detail)



Waiting to hear from you

Regards

Lackson

Data Report Grouping + ADO
VB6 + Access 2000 mdb

I have created a report using an ADO Shape to produce grouping and it works well.

However, my report shows contact events for each person in my database and currently it shows all people, even if there are no contacts for them. There could be hundreds of these during a project.

How do I hide the group header records if there are no details for that group?

TIA for any help with this
MargV

Data Report Grouping
Hi!!! friends I have got another Q related to data report pls help.

I retrieve the data using the following query in a forms command click event

Ssql = "select a.emp_code, b.emp_name, a.br_code, b.account_no, b.bank_code, c.bank_name, a.total_sal from emp_earnded a, emp_mast b, bank_mast c where a.emp_code = b.emp_code and b.bank_code = c.bank_code and b.pay_mode = 0 and a.Month = " & cbomonth.Text & " and a.Year = " & cboyear.Text & " and a.posted = 1 order by b.bank_code, a.br_code, a.emp_code"
DataEnvironment1.Commands(11).CommandText = ssql
dtrbankstmt.Sections("pqr").Controls("lblmonth").Caption = strmonth & " " & cboyear.Text
dtrbankstmt.Show

I need to print the report as per individual bank_code in separate pages

The report should be displayed as follows -

Bank_code 555 Bank_name xyz

Header
Emp_code, emp_name, br_code, account_no, total_sal

Detail
Emp_code, emp_name, br_code, account_no, total_sal

Report Footer
No of record 50            Total Sal   555555


Pls Help!! Data Report Grouping
i have ben trying really hard to group on two fields in data reports in vb. but not working out!!!. will someone be kind enough to help???
this is my prob.
i have a table called bookregister with the following fields
category, author, bookname, stock
  i want to group by category first and then by author.
the format of the  data report  that i want is given below
computer(category)
                diana(author)   
                ffffff(bookname)
                fgfghgf  "
                fgfgfggh  "
         
                walter(author)
                dfgdfgfg(bookname)
                dfdgfghg   "
economics(category)

          siam (author)
                 gghgh  (bookname)
                  fgfgh    "
i hope my prob is understood: ie grouping first on the basis  of ategory and then on author
it is so easy in access but it's driving me nuts in vb!!!!

i tried group by but u see, the headers(category, author is repeating

to make it more clear
suppose in access we were to use the report wizard
we would first soecify the table,
then add two grouping levels
first : category
second: author

and we would get a report.

i want this same form of report in vb using data report and data environment

i am relatively new   to this

pls help!!!!!!! i really need it.
elizabeth_c123@rediffmail.com
                    
     

VB Data Report Grouping
I made a Data report using grouping tab. In this way a detail section gets created
. How can I print the resultset of this detail section in accross then down direction?

Copyright 2005-08 www.BigResource.com, All rights reserved