AJ0718
asked on
What Is the Best Way to Create This Report?
I need some expert help to get started on this report. It will eventually look something like this (with checkboxes in all).The query parameters will make both the trip list across the top and the species list on the side vary in both counts and content. I then want to check the checkbox if that species was recorded on that particular trip. I'm just not sure what is the most efficient way to approach this task and would really appreciate someone pointing me in the right direction.
you have a list of species the list of species points to a list of trips. the list of trips will have the particulars of the trip i.e. date/location/users and a list of species ID's.. What you want is database design and not report design
ASKER
Yep, that's pretty much what I've been playing with for the last few hours. I've been trying to do it through a temp table but am struggling with how to dynamically add columns using a select. There has to be an easier way to do this.
If the source data is correct, a grid view can spit out something that looks like that with no trouble at all.
Perhaps the best thing to do first is to structure the source data so it'll automatically come out the way you want. What does the source data look like? Where does it come from?
Perhaps the best thing to do first is to structure the source data so it'll automatically come out the way you want. What does the source data look like? Where does it come from?
ASKER
... and the trip data?
ASKER
Yep, that's what I'm after. I did manage to create a temp table for the structure (SpeciesID, Species, and columns for locations) and bind that to the gridview. I'm hoping now to set the field values by looping through the trip data dataset OnRowDataBound. Does that make sense? If I can figure out the syntax I should be able to set the 1's in your sample as "X" and leave the others as null.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. I'm going to play with this because it no doubt does things a lot more efficiently than what I just now got finished - http://dfobirds.org/FieldT rips/Repor ts/Monthly Trips.aspx . I built the structure from a stored procedure using a temp table and bound it to the gridview. I then used the dataset I create on the button click (my sample above) to loop through the ds and put an X where appropriate.
Protected Sub GridView1_RowDataBound(sender As Object, e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
If dsRecords.Tables.Count > 0 Then
For i As Integer = 0 To dsRecords.Tables(0).Rows.Count - 1
Dim SpeciesID As Double = dsRecords.Tables(0).Rows(i).Item(0)
Dim Location As String = dsRecords.Tables(0).Rows(i).Item(2)
Dim rowView As DataRowView = DirectCast(e.Row.DataItem, DataRowView)
Dim myDataKey As Double = rowView("SpeciesID")
If SpeciesID = myDataKey Then
e.Row.Cells(GetColumnIndexByName(e.Row, Location)).Text = "X"
End If
Next
End If
End If
e.Row.Cells(0).Visible = False
End Sub
Protected Function GetColumnIndexByName(row As GridViewRow, SearchColumnName As String) As Integer
Dim columnIndex As Integer = 0
For Each cell As DataControlFieldCell In row.Cells
If TypeOf cell.ContainingField Is BoundField Then
If DirectCast(cell.ContainingField, BoundField).DataField.Equals(SearchColumnName) Then
Exit For
End If
End If
columnIndex += 1
Next
Return columnIndex
End Function
You are most welcome. Don't forget that only developers care how the data gets displayed. :) Your solution works nicely.
ASKER
Thanks.