SQL Server Performance

Adding a SQL statement to a parameter ?!

Discussion in 'SQL Server Reporting Services' started by dougwood, Feb 1, 2004.

  1. dougwood New Member

    Hi again,
    I seem to be the most popular member in this forum !!
    Anyway, I'm wondering the best way to go about this...

    I want to have a report in which a user chooses a Month from a dropdown list. The dropdown list will be populated with fields Jan, Feb, March, April, May and so on...

    When a user selects a month, I then want to create the SQL statement, for example, if a user chooses October I want to run:
    SELECT body, eMail, dateEntered
    FROM event
    WHERE (dateEntered >= '10/1/2003') AND (dateEntered < '11/1/2003')

    I initially created a Dataset which returned all events (SELECT * FROM events), then I created a Report Parameter. In the 'available values' section I clicked 'Non-queried', and then in the 'Label' field I put January, Feb, etc, and the Value field I would put the SQL statement like this:
    (for January)
    ="SELECT body, eMail, dateEntered
    FROM event
    WHERE (dateEntered >= '1/1/2003') AND (dateEntered < '2/1/2003')"

    But I just got a lot of errors.
    So what's the best way to go about this ? DO I have to create 12 different datasets for each month.
    I'm sort of stumped ?!

    Thanks again !

  2. jasper_smith New Member

    If this is a SQL datasource I would create a stored procedure that takes a month as the parameter (as an integer) and use that to generate the start and end dates. So for your non-queried parameter list use the Month Names as the labels and the month as an integer as the Value and pass it to a procedure like
    create proc GetEvents @month int,@year int = 2003
    set nocount on

    declare @start datetime
    declare @end datetime
    set @start = CONVERT(datetime,(CAST(@year as char(4)) + RIGHT('0'+CAST(@month as varchar(2)),2) + '01'))
    set @end = DATEADD(m,1,@start)

    SELECT body, eMail, dateEntered
    FROM event
    WHERE dateEntered >= @start AND dateEntered < @end
    This would also give the option to add another parameter for year at a later date


    Jasper Smith
  3. dougwood New Member

    damn, this is great for SQL Server, thanks. Unfortunately, I am using Oracle (no sprocs). I am setting variables in my SQL statements like so:
    SELECT * FROM myTable
    WHERE myField = ?

    Any suggestions ? I've never worked with Oracle before!

  4. jasper_smith New Member

    I'm afraid I know next to nothing about Oracle programming. As a workaround you could use two parameters. Set up the first one (in this example I have called it d1) to have the labels for the months and the values as the first of each month. Set up the second to allow Null values and set its default to Non Queried and its value to
    Then deploy the report and click on View Details and then Properties for the report. Click on Parameters and uncheck the Prompt User checkbox. This will hide the second parameter from the users but because of the default we have set, it will always be the first of the next month. This assumes you can use a query like
    SELECT body, eMail, dateEntered
    FROM event
    WHERE dateEntered >= ? AND dateEntered < ?


    Jasper Smith

Share This Page