Report in Grid or Grid type control

Hi,

I have a form which searches database on user critera and displays the result in FlexiGrid and if user wants to export the result to excel they can. Everything is working fine. But now I want to modify this program a little.

The result in Grid is display like this:

Year-----Field1-----Field2-----Field3
2000----- A ------- A -------- A
2001----- B ------- B -------- C
2002----- C ------- C -------- C

But I want this to be displayed in Grid like this:

Year----2000-----2001----2002
Filed1----A--------B-------C
Field2----A--------B-------C
Filed3----A--------B-------C

How can I do this? Any help?

Thanks

Re: Report in Grid or Grid type control

can you post your program and/or its sourcecode? then someone can see how its written and offer advice.

Quote:

Originally Posted by sillylady

Hi,

I have a form which searches database on user critera and displays the result in FlexiGrid and if user wants to export the result to excel they can. Everything is working fine. But now I want to modify this program a little.

The result in Grid is display like this:

Year-----Field1-----Field2-----Field3
2000----- A ------- A -------- A
2001----- B ------- B -------- C
2002----- C ------- C -------- C

But I want this to be displayed in Grid like this:

Year----2000-----2001----2002
Filed1----A--------B-------C
Field2----A--------B-------C
Filed3----A--------B-------C

How can I do this? Any help?

Thanks

Re: Report in Grid or Grid type control

Here is code.

VB Code:

lngRow = adoRS.RecordCount
lngCol = adoRS.Fields.Count
 
With ManuRatioGrid
 
.Cols = 2
.Rows = 2
 
Do While Not adoRS.EOF
 
For j = 0 To lngRow
For i = 0 To lngCol
 
.TextMatrix(i, j) = adoRS.Fields(i).Value
 
Next i
 
adoPrimaryRS.MoveNext
Next j
 
Loop
End With

I think its working but the problem is if any cell of the Grid is empty, the for loop comes out.

Re: Report in Grid or Grid type control

how many records supposed to be retrieved and how many have been displayed in the grid...i cudnt see any problem with this code, but how do u open the recordset ....

Re: Report in Grid or Grid type control

Quote:

Originally Posted by ganeshmoorthy

how many records supposed to be retrieved and how many have been displayed in the grid...i cudnt see any problem with this code, but how do u open the recordset ....

It can retrieve any number of records and it displays the fetched records in the grid. I think the problem is when the inner for look encounter 'blank' cell in the Grid the loop exits.

Re: Report in Grid or Grid type control

i hope you are filling only the blank cells with the resultset...
may be instead of using
VB Code:
.TextMatrix(i, j) = adoRS.Fields(i).Value
try this
.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")

Re: Report in Grid or Grid type control

Quote:
Originally Posted by ganeshmoorthy
i hope you are filling only the blank cells with the resultset...
may be instead of using
VB Code:
.TextMatrix(i, j) = adoRS.Fields(i).Value
try this
.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")
I think i am not clear in my question. I am retrieving records in the ADO Recordset and trying to transfer those records in a GRID in this manner:
Year-----2000
Field1-----1
Field2-----1
Field3-----1

instead of the usual display like:
Year----Field1----Field2----Field3
2000-----1--------1--------1

the code which i have given here works fine the only problem is if any field in the row contains null value the for loop exits.

Re: Report in Grid or Grid type control

this will take care of the null values of the recordset, have you trid it...
VB Code:
.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")

Re: Report in Grid or Grid type control

Quote:
Originally Posted by ganeshmoorthy
this will take care of the null values of the recordset, have you trid it...
VB Code:
.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")
I tried it but the problem is same..........for statement comes out after i=3

Do you have anyother idea?

Re: Report in Grid or Grid type control

is it displaying any error or how do u say if it is null it comes out of for loop...have you checked the recordcount, how much it returns...

Re: Report in Grid or Grid type control

Quote:

Originally Posted by ganeshmoorthy

is it displaying any error or how do u say if it is null it comes out of for loop...have you checked the recordcount, how much it returns...

I have tested it by just setting the Grid Datasource to Ado recordset and it displays 6 records and 31 fields of which some have blank value.

Re: Report in Grid or Grid type control

i asked the value of lngRow after you assign lngRow = adoRS.RecordCount

Re: Report in Grid or Grid type control

Quote:

Originally Posted by ganeshmoorthy

i asked the value of lngRow after you assign lngRow = adoRS.RecordCount

lngRow=6 and lngCol=31

Re: Report in Grid or Grid type control

i guess i is for your column and j is for your row values
VB Code:
.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")
TextMatrix takes row, column, but you have given col, row check it and then in the MoveNext statement you have given some different recordset name and you have checked in the Do while some different recordset name... Do While adoRs.EOF and adoPrimaryRS.MoveNext

Re: Report in Grid or Grid type control

Quote:
Originally Posted by ganeshmoorthy
i guess i is for your column and j is for your row values
VB Code:
.TextMatrix(i, j) = Trim(adoRS.Fields(i).Value & "")
TextMatrix takes row, column, but you have given col, row check it and then in the MoveNext statement you have given some different recordset name and you have checked in the Do while some different recordset name... Do While adoRs.EOF and adoPrimaryRS.MoveNext
Don't be confused about the recordset name. I have used Col, Row to make the Row as Col and Col as Row in Grid display.

Re: Report in Grid or Grid type control

VB Code:

For j = 0 To adoRs.RecordCount -1
        For i = 0 To adoRs.Fields.Count - 1
            .TextMatrix(j+1, i+1) = adoRS.Fields(i).Value
        Next i
       adoRs.MoveNext
    Next j

Re: Report in Grid or Grid type control

Quote:
Originally Posted by ganeshmoorthy
VB Code:
For j = 0 To adoRs.RecordCount -1
        For i = 0 To adoRs.Fields.Count - 1
            .TextMatrix(j+1, i+1) = adoRS.Fields(i).Value
        Next i
       adoRs.MoveNext
    Next j
Thank you for your continuous help but sadly it didnot solve my problem. I think I will have find another control so something that display the data in GRID as I want.

Re: Report in Grid or Grid type control

The code below gives the result close to my desire. It dispays the 1st record vertically in GRID but after that nothing happens though there are 5 rows. Means 'For i' statement is working but after displaying the 1st record it doesn't display the next record mean 'For j' not working.
VB Code:
With ManuRatioGrid
 
Do While Not adoPrimaryRS.EOF
 
For j = 0 To adoPrimaryRS.RecordCount - 1
 
For i = 0 To lngCol
 
.TextMatrix(i + 1, j) = Trim(adoPrimaryRS.Fields(i).Value & "")
 
Next i
 
adoPrimaryRS.MoveNext
 
Next j
 
Loop
End With

Re: Report in Grid or Grid type control

Any help on this plssssssss.

Or can any one suggest any grid where I can display records in the following format.

Year----2000
Field1---100
Field2---100
Field3---100

Thanks

Re: Report in Grid or Grid type control

You just need to set the number of rows to the number of fields, and the number of columns to the number of records + 1 (field names). Then you fill the cells using (FieldNo,RecordNo), as the Textmatrix has parameters of (Row,Column).

Here's the code:

VB Code:

Dim lngRow As Long, lngCol As Long
 
  If adoPrimaryRS.EOF Then     'check we have data
    MsgBox "no data!"
    ManuRatioGrid.Rows = 0
  Else
    With ManuRatioGrid
      .Rows = adoPrimaryRS.Fields.Count
      .Cols = 2
      .FixedRows = 0
      .FixedCols = 1
                      'add field names
      For lngRow = 0 To adoPrimaryRS.Fields.Count - 1
        .TextMatrix(lngRow, 0) = adoPrimaryRS.Fields(lngRow).Name
      Next lngRow
      
      lngCol = 1
      Do While Not adoPrimaryRS.EOF
                      'fill a column with a record
        For lngRow = 0 To adoPrimaryRS.Fields.Count - 1
          .TextMatrix(lngRow, lngCol) = (adoPrimaryRS.Fields(lngRow).Value & "")
        Next lngRow
                      'move on to next record
        adoPrimaryRS.MoveNext
        If Not adoPrimaryRS.EOF Then
          .Cols = .Cols + 1
          lngCol = lngCol + 1
        End If
      Loop
    End With
  End If

Re: Report in Grid or Grid type control

Thanks Geek. It is working. Thank you very much.

Quote:

Originally Posted by si_the_geek

You just need to set the number of rows to the number of fields, and the number of columns to the number of records + 1 (field names). Then you fill the cells using (FieldNo,RecordNo), as the Textmatrix has parameters of (Row,Column).

Here's the code:

VB Code:

Dim lngRow As Long, lngCol As Long
 
  If adoPrimaryRS.EOF Then     'check we have data
    MsgBox "no data!"
    ManuRatioGrid.Rows = 0
  Else
    With ManuRatioGrid
      .Rows = adoPrimaryRS.Fields.Count
      .Cols = 2
      .FixedRows = 0
      .FixedCols = 1
                      'add field names
      For lngRow = 0 To adoPrimaryRS.Fields.Count - 1
        .TextMatrix(lngRow, 0) = adoPrimaryRS.Fields(lngRow).Name
      Next lngRow
      
      lngCol = 1
      Do While Not adoPrimaryRS.EOF
                      'fill a column with a record
        For lngRow = 0 To adoPrimaryRS.Fields.Count - 1
          .TextMatrix(lngRow, lngCol) = (adoPrimaryRS.Fields(lngRow).Value & "")
        Next lngRow
                      'move on to next record
        adoPrimaryRS.MoveNext
        If Not adoPrimaryRS.EOF Then
          .Cols = .Cols + 1
          lngCol = lngCol + 1
        End If
      Loop
    End With
  End If