Data Report

[Akshaya]

I have a report that displays all the information about employees.
Some fields,I don't want to display repeatedly.
For ex,

Empno,Grade.
Now it displays like:

Empno Grade Name Item Received
111 Er xyz frt
111 Er xyz efg

Any option is there not to display those fields repeatedly.
Please help me?

[shakti5385]

I have a report that displays all the information about employees.
Any option is there not to display those fields repeatedly.
Please help me?

Why not using the distinct in the sql query

[leinad31]

You need to either (1) create a heirarchical recordset with a shape query or (2) create a heirarchy of command objects in the dataenvironment GUI. When you have the heirarchy then you create groups/sections in your report. The innermost group will contain the repeating data "xyz frt", for "111 Er"you place those in the higher level group.

[leinad31]

Why not using the distinct in the sql query

Because the records (when all fields are compared) are already distinct

[shakti5385]

Because the records (when all fields are compared) are already distinct

Empno Grade Name Item Received
111 Er xyz frt
111 Er xyz efg

Same employee number 2 times how it is distinct

[leinad31]

The records are distinct cause not all columns are the same, only two columns. He needs to show those 2 records in the report..

He can't accomplish this with just one SELECT query, even with distinct, plain and simple... he needs two queries related in a SHAPE query, or create a heirarchy of command objects (each with its own select query). Your DISTINCT solution is applicable only to the first recordset in the heirarchy (which returns just the first two columns).

For each page you create subgroups
111 Er repeats in group1 (or labels are placed in the page section group1)
xyz frt and xyz efg repeats in group2 which is contained in group1 (or labels are placed in section group2 which is under page section group1)

[irene casper]

possible to use GROUP BY ??

[shakti5385]

The records are distinct cause not all columns are the same, only two columns. He needs to show those 2 records in the report..

in group1 (or labels are placed in section group2 which is under page section group1)

See the code just example you can use the distinct in the query

Private Sub CREPORT_Click()
On Error GoTo ErrorTrap
 
TSQL = ""
TSQL = "SELECT * FROM LEAVE_TABLE WHERE FIRM='" & COMFIRMNAME.Text & "' AND S_DATE BETWEEN #" & DTP1.Value & "# and #" & DTP2.Value & "#"
Set RTMP = Get_Special_Record_Set(TSQL)
 
If RTMP.RecordCount = 0 Then
MsgBox "Record Not Found", vbInformation
Exit Sub
End If
 
 
With FIRM_STATUS
    Set .DataSource = Nothing
    Set .DataSource = RTMP.DataSource
End With
'firm status is datareport name
'don't forget to write all control in section 1 of your report
'coding by banna
With FIRM_STATUS.Sections("Section1").Controls
    
    .Item("LD1").Caption = DTP1.Value
    'label first name ld1
    .Item("LD2").Caption = DTP2.Value
    'label second name ld2
    .Item("TXTFIRM").DataMember = ""
    .Item("TXTFIRM").DataField = RTMP.Fields("FIRM").Name
    'textbox first name txtfirm
    'firm is field firm in the table
    .Item("TXTLOCATION").DataMember = ""
    .Item("TXTLOCATION").DataField = RTMP.Fields("LOCATION").Name
    'txtbox second name txtlocation
    'location is field location in the table
    .Item("LABNOEMP").Caption = EMP
    'label third name labnoemp
    .Item("LSALARY").Caption = Val(SALARY)
    'label lsalary name lsalary
End With
FIRM_STATUS.Refresh
'firm_status is datareport name
 
FIRM_STATUS.Show
ErrorTrap:
If Err.Number <> 0 Then
   MsgBox "Error (" & Err.Number & "): " & Err.Description, vbCritical, "Error"
End If
End Sub

[leinad31]

possible to use GROUP BY ??

I don't think he is using aggregate functions such as Sum()... and the end result would be something similar to DISTINCT.

[leinad31]

See the code just example you can use the distinct in the query

Private Sub CREPORT_Click()
On Error GoTo ErrorTrap
 
TSQL = ""
TSQL = "SELECT * FROM LEAVE_TABLE WHERE FIRM='" & COMFIRMNAME.Text & "' AND S_DATE BETWEEN #" & DTP1.Value & "# and #" & DTP2.Value & "#"
Set RTMP = Get_Special_Record_Set(TSQL)
 
If RTMP.RecordCount = 0 Then
MsgBox "Record Not Found", vbInformation
Exit Sub
End If
 
 
With FIRM_STATUS
    Set .DataSource = Nothing
    Set .DataSource = RTMP.DataSource
End With
'firm status is datareport name
'don't forget to write all control in section 1 of your report
'coding by banna
With FIRM_STATUS.Sections("Section1").Controls
    
    .Item("LD1").Caption = DTP1.Value
    'label first name ld1
    .Item("LD2").Caption = DTP2.Value
    'label second name ld2
    .Item("TXTFIRM").DataMember = ""
    .Item("TXTFIRM").DataField = RTMP.Fields("FIRM").Name
    'textbox first name txtfirm
    'firm is field firm in the table
    .Item("TXTLOCATION").DataMember = ""
    .Item("TXTLOCATION").DataField = RTMP.Fields("LOCATION").Name
    'txtbox second name txtlocation
    'location is field location in the table
    .Item("LABNOEMP").Caption = EMP
    'label third name labnoemp
    .Item("LSALARY").Caption = Val(SALARY)
    'label lsalary name lsalary
End With
FIRM_STATUS.Refresh
'firm_status is datareport name
 
FIRM_STATUS.Show
ErrorTrap:
If Err.Number <> 0 Then
   MsgBox "Error (" & Err.Number & "): " & Err.Description, vbCritical, "Error"
End If
End Sub

I don't see a distinct keyword in your query.

[Akshaya]

Thanks for ur replies. Can u explain how to make group1 & 2.
My code :

Private Sub cmdOk_Click()
    On Error Resume Next
    If txtCode = "" Then
        flag = MsgBox("Display Entire Return Report?", vbYesNo + vbQuestion, "Smart Billing")
        If flag = vbYes Then
            If Rs.State = adStateOpen Then
                Rs.Close
            End If
            Rs.open "Select * from checknew", conn
            Set returnnoReport.DataSource = Rs
            Load returnnoReport
            returnnoReport.Show
            Unload Me
        Else
            Exit Sub
            Txtdate.SetFocus
        End If
    Else
        If Rs.State = adStateOpen Then
            Rs.Close
        End If
        Rs.open "select * from checknew where badgeno= " & txtCode.Text, conn
        Set returnnoReport.DataSource = Rs
        Load returndateReport
        returnnoReport.Show
        Unload Me
    End If
End Sub

[leinad31]

shakti, have you even ever tried making a datareport with nested sections? If not, then that explains why your not getting my point.

For each page you create subgroups
111 Er repeats in group1 (or labels are placed in the page section group1)
xyz frt and xyz efg repeats in group2 which is contained in group1 (or labels are placed in section group2 which is under page section group1)

[leinad31]

Unfortunately, I'm on a public PC with no VB IDE cause my PC won't boot. But I'm sure someone can give you a sample using SHAPE query cause you did this.

Rs.open "Select * from checknew", conn
Set returnnoReport.DataSource = Rs

Best if you posted relevant table structure and how the tables are related (on which fields) to facilitate creation of the query.

[shakti5385]

shakti, have you even ever tried making a datareport with nested sections? If not, then that explains why your not getting my point.

It was past now i not use data report related to my example it was just a demo i tell that you can use the distinct for selecting the field

[Akshaya]

Table Structure(checknew)

Empcode-----N
Empname-----T
Grade--------T
Itemname----T
Date---------T
qty----------N

Empcode,Empname and Grade Should be displayed once.

[shakti5385]

Table Structure(checknew)

Empcode,Empname and Grade Should be displayed once.

Hi did u have crystal report on ur pC just asking

[brucevde]

someone can give you a sample using SHAPE query cause you did this.

You must open the ADO Connection object using the MSDataShape provider and include a Data Provider argument.

Dim cnn As New ADODB.Connection
cnn.Open "provider=msdatashape;data provider=SQLOLEDB...

Does the table have a primary key? You need to link the child command to the header command via the Relate clause. Usually done with the Primary Key. Just make sure the PK is in the Selection List of both Commands.

strSQL = "Shape {Select EmpCode, EmpName, Grade From CheckNew}
Append ({Select * From CheckNew}
Relate EmpName To EmpName) As Details"

As for the Data Report, set the Data Member and Data Field properties for anything in the Details section. Data Member is the name given to the Child command (in this case also called Details).

[MartinLiss]

Moved.