Solved

What Is the Best Way to Create This Report?

Posted on 2016-08-13
11
58 Views
Last Modified: 2016-08-18
I need some expert help to get started on this report. It will eventually look something like this (with checkboxes in all).Layout of reportThe 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.
0
Comment
Question by:AJ0718
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 41754962
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
0
 

Author Comment

by:AJ0718
ID: 41754998
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.
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41755040
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?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:AJ0718
ID: 41755070
I'm pulling my data from a couple of views which looks like this:
Data Sample from QueryI'm just struggling with how to flip things so that the unique locations are column headers, each species is listed only once, and if there is a match in my data the field shows either an X or a checkmark.
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41755171
... and the trip data?
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41755179
To be clear, you want something like species.png?
1
 

Author Comment

by:AJ0718
ID: 41755197
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.
0
 
LVL 12

Accepted Solution

by:
funwithdotnet earned 500 total points
ID: 41755646
I used the output from your sample to create a database to mimic the underlying structure.

Three tables ...
Species (SpeciesId, SpeciesName)
Locations (LocationId, LocationName)
SpeciesLocation (RecordId, SpeciesId, LocationId)

... and one view, SpeciesLocationView (SpeciesId, SpeciesName, LocationId, LocationName)

Then the tables were populated with data from your sample. Your database probably has similar structures.

The view I posted came from here:

SELECT s.SpeciesId, s.SpeciesName as Species

, (CASE WHEN  ISNULL(
 (SELECT top (1) LocationName from dbo.SpeciesLocationView l WHERE l.LocationId = 4 AND l.SpeciesId = s.SpeciesId)
 , '') = '' THEN CAST(0 as Bit) ELSE CAST(1 as Bit) END) AS [Beebe]

 , (CASE WHEN  ISNULL(
 (SELECT top (1) LocationName from dbo.SpeciesLocationView l WHERE l.LocationId = 2 AND l.SpeciesId = s.SpeciesId)
 , '') = '' THEN CAST(0 as Bit) ELSE CAST(1 as Bit) END) AS [Chase]

 , (CASE WHEN  ISNULL(
 (SELECT top (1) LocationName from dbo.SpeciesLocationView l WHERE l.LocationId = 6 AND l.SpeciesId = s.SpeciesId)
 , '') = '' THEN CAST(0 as Bit) ELSE CAST(1 as Bit) END)  AS [Cherry Creek]

, (CASE WHEN  ISNULL(
 (SELECT top (1) LocationName from dbo.SpeciesLocationView l WHERE l.LocationId = 1 AND l.SpeciesId = s.SpeciesId)
 , '') = '' THEN CAST(0 as Bit) ELSE CAST(1 as Bit) END)  AS [NE Raptor]
 
, (CASE WHEN  ISNULL(
 (SELECT top (1) LocationName from dbo.SpeciesLocationView l WHERE l.LocationId = 5 AND l.SpeciesId = s.SpeciesId)
 , '') = '' THEN CAST(0 as Bit) ELSE CAST(1 as Bit) END)  AS [Prospect Park]
  
, (CASE WHEN  ISNULL(
 (SELECT top (1) LocationName from dbo.SpeciesLocationView l WHERE l.LocationId = 3 AND l.SpeciesId = s.SpeciesId)
 , '') = '' THEN CAST(0 as Bit) ELSE CAST(1 as Bit) END)  AS [S Platte]

 FROM Species s

Open in new window


Personally, I'd use .NET to dynamically build the report SQL query. Hopefully your database already includes the needed structures for the three tables. For the location columns in the report, I'd loop through a list of unique locations and build the applicable part of the report query from that.  Make sense?

If you feed the results of that query to a gridview, I believe it'll automatically put checkboxes in the cells, because the columns are SQL Bit type (.NET Boolean).
1
 

Author Comment

by:AJ0718
ID: 41755698
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/FieldTrips/Reports/MonthlyTrips.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

Open in new window

0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41755703
You are most welcome. Don't forget that only developers care how the data gets displayed. :) Your solution works nicely.
2
 

Author Closing Comment

by:AJ0718
ID: 41761371
Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question