Creating a report based on form entries

[pwinters]

I'm not sure if I'm posting this in the right forum, but I think this could be done using sql..just not sure how.

Is it possible to create a report (displayed on a website) based on criteria selected from a form? (3 or 4 drop down boxes)

i.e.
User selects Month: April, Team: 1 and 2.
Can I produce a report after the user hits submit with information based on these selections?

I'm currently using java & jsp to display the sql results.

Thanks!

 

[jlsmithhartfiel]

I'm doing this currently, though with .asp.

I get the recordset back from sql & do some calculations with asp variables & then display everything.

Works okay if you don't have to worry too much about pagination. Jessica

 

[SQLSister]

Make the input on the page into input variables for a stored procedure which then runs a select statement based on those variables. Dynamically create the where clause using those variables. This can get quite complex depending on how many inputs you allow on the form and whether they are all And conditions or Or conditions or a combination. One of ours has 30 variables and a combination of and, or in and full text search. It's also 15 or 16 pages long. We take care of pagination through our client interface (.NET based, but I'm sure you can do something similar in Java) and we sometimes limit the total number of records which can be returned to save from returning too many records in the SELECT Statements.

A simplified sample of our search from a web form stored procedure is below:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Create PROCEDURE USP_SELECT_Search (@AirState as nvarchar(2) = NULL,
@AirCity as nvarchar(50) = NULL)
AS


Declare @SQLWhere nvarchar(500)

Set @SQLWhere = ''

---Build Query

If @AirState <> '' and @AirState is not Null
Set @SQLWhere = 'State = &quot;' + @AirState+'&quot;'

If @AirCity <>'' and @AirCity is not Null
begin
If @SQLWhere <> ''
Set @SQLWhere =@SQLWhere+ ' and City = &quot;' +@AirCity+'&quot;'
else
Set @SQLWhere = 'City = &quot;' +@AirCity+'&quot;'
End

If @SQLWhere <> ''
Begin
Set @SQLWhere = 'Where ' + @SQLWhere
exec(&quot;SELECT AirportName, Airport.AirportID, City, State, AirportType, FacilityUse, Country, SATSID
FROM Airport LEFT OUTER JOIN
SATSAirports ON SATSAirports.AirportID = Airport.AirportID &quot; + @SQLWhere)
End
ELse
Begin
SELECT AirportName, Airport.AirportID, City, State, AirportType, FacilityUse, Country, SATSID
FROM Airport LEFT OUTER JOIN
SATSAirports ON SATSAirports.AirportID = Airport.AirportID
End

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO