locked
Newbie Struggling With Parameters RRS feed

  • Question

  • Hi

    I have a table that I want to generate off of.  It is in Development Studio 2005 and I'm using SQL 2005 and MOSS 2007.

    The reports has "countries" and 'report dates' reports of sales.

    I'm using this query at the moment.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------
    DECLARE @days int; DECLARE @minDate datetime; DECLARE @maxDate datetime; SELECT     @maxDate = max(reportdate)
                                                                                                                                                                                           FROM          importsminmax
                                                                                                                                                                                           WHERE      year(reportdate) BETWEEN (@startdate) AND (@enddate)
    SET              @minDate = '01/01/2008';
    SET              @days = datediff(day, @minDate, @maxDate) + 1;
                              SELECT     *, qty / @days AS bd
                               FROM         IMPORTSminmax
                               WHERE     Year(reportdate) BETWEEN (@startdate) AND (@enddate)
    --------------------------------------------------------------------------------------------------------------------------------------------------------------

    The above query returns everything for 2008 I have used the > 2000 to return allthe data for all the years.
    the section with the division is really important to have the calculation returned for all "reportdate" years in the table.


    --
    OK what I did was create a new Dataset in BIDS in this report called "ReportDate" using this query.
     
    --------------------------------------------------------------------------------------------------
    SELECT DISTINCT reportdate
    FROM crudeimports
    WHERE year(reportdate) < 2008
    --------------------------------------------------------------------------------------------------
    Then I went and set it as a Report Parameter.

    Now I get the drop down list whenI preview the parameter but when I select any one specific date I get the same result set and the parameter does not filter the data.

    I will be "attempting" to deploy this to Share Point.  Will Share Point be filter this and I do not have to do it In BIDS?

    I would really appreciate some enlightenment on this.

    If you need any more info please ask

    (for the filter I would just need to choose a year i.e 2000 - 2009)

    Thanks in Advance

    Ravinder



    Thursday, October 30, 2008 11:36 PM

Answers

  • Ravinder,

     

    I saw you already posted a new thread regarding this. Let me try to understand your problem. You have a report parameter, let say you named them reportdate and the query for this parameter is, as you posted,

    SELECT DISTINCT reportdate
    FROM crudeimports
    WHERE year(reportdate) < 2008

     

    Then, you have a dataset that will return resultset using the query you provided. Is this reportdate parameter (can we call it differently because I don't want it to be confused with reportdate field that was return from the query. Let say we call this parameter varRptDate). Sorry, I got side tarck. So this varRptDate, is it multivalue or a single value parameter? Because from this stmt -> SELECT  @maxDate = max(reportdate) .. you are asking for max of reportdate parameter confused me. It seems that max is finding the latest date from a collection of date. You may want to simplify your query to like so:

     

    select *, bd = qty / (datediff(day, '1/1/2008', @varRptDate)+1)

    from IMPORTSminmax

    where <here you need to put any field from IMPORTSminmax table that you want to evaluate the date

    against the year that you choose from the parameter> = year(@varRptDate)

     

    May be you need to elaborate more of what you want to bring back, like all rows that has the year of a date field matching the parameter year.

     

     

    Friday, October 31, 2008 5:43 PM
    Answerer

All replies

  • If i was missing any data please let me know.

    Thanks
    Friday, October 31, 2008 12:08 PM
  • Ravinder,

     

    I saw you already posted a new thread regarding this. Let me try to understand your problem. You have a report parameter, let say you named them reportdate and the query for this parameter is, as you posted,

    SELECT DISTINCT reportdate
    FROM crudeimports
    WHERE year(reportdate) < 2008

     

    Then, you have a dataset that will return resultset using the query you provided. Is this reportdate parameter (can we call it differently because I don't want it to be confused with reportdate field that was return from the query. Let say we call this parameter varRptDate). Sorry, I got side tarck. So this varRptDate, is it multivalue or a single value parameter? Because from this stmt -> SELECT  @maxDate = max(reportdate) .. you are asking for max of reportdate parameter confused me. It seems that max is finding the latest date from a collection of date. You may want to simplify your query to like so:

     

    select *, bd = qty / (datediff(day, '1/1/2008', @varRptDate)+1)

    from IMPORTSminmax

    where <here you need to put any field from IMPORTSminmax table that you want to evaluate the date

    against the year that you choose from the parameter> = year(@varRptDate)

     

    May be you need to elaborate more of what you want to bring back, like all rows that has the year of a date field matching the parameter year.

     

     

    Friday, October 31, 2008 5:43 PM
    Answerer