Parameter not initializing in report (SSRS 2005) RRS feed

  • Question

  • User1275445422 posted

    So I have a pretty simply report. I pull info from 2 tables and put into a temp table. I pull info from a third table and put into a second temp table (and looking back I can probably rewrite this without the temp tables..force of habit).

    And then I use a simply IF/ELSE to determine which select to use.
    The report itself only has one parameter, rmorg, which is in another dataset that simply pulls a list of 10 names out of a table union-ed togther with '** All' so I can use that to select 'all results'.



    declare @rmorg varchar(200)
    create table #incdata (
      inc_id                   smallint,
      inc_fac                 varchar(100),
      inc_number         varchar(20),
      inc_type              varchar(200),
      inc_sig                 smallint,
      inc_date              datetime,
      inc_daterm          datetime,
      inc_daystorm       int
    create table #includedfacs (
      fac                        smallint,
      fac_desc               varchar(100),
      fac_rm                  varchar(100)
    INSERT INTO #incdata
      SELECT facilityid,facility,number,type,sig,incident_date,incident_daterm,incident_daystorm
      FROM tblIncidents_M a INNER JOIN tblIncidents_M_daystorm b
          ON a.number = b.incident_id
      ORDER BY facility,type,number
    INSERT INTO #includedfacs
      SELECT facilityid,facilityname,rm_div
      FROM tblFacmaster
      WHERE fac_precompany='new'
    if left(@rmorg,2) <> '**'
        SELECT inc_fac,inc_number,inc_type, inc_sig, inc_date,inc_daterm,inc_daystorm,fac_rm
        FROM #incdata a INNER JOIN #includedfacs b ON a.inc_id = b.fac
        WHERE fac_rm = @rmorg
        SELECT inc_fac,inc_number,inc_type, inc_sig,inc_date,inc_daterm,inc_daystorm,fac_rm
        FROM #incdata a INNER JOIN #includedfacs b ON a.inc_id = b.fac
    drop table #incdata
    drop table #includedfacs

    So if I simply execute this in the query window, it runs without asking me to enter the @rmorg info.
    By default it seems to be running the ELSE part of the IF statement. If I add a where fac_rm = 'name' (where 'name' of course is one of the names from the other dataset), it pulls up the info it should.

    I've done stuff like this a bunch but Ive never had it not initialize the @parm. It's something silly I'm sure but whatever is causing it, I'm overlooking it.

    Thanks all =)

    Thursday, January 19, 2012 8:27 AM


  • User1275445422 posted

    *forehead slap*

    It's because I decladed the parameter in the query. As soon as I took that out, it is prompting like it should.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 19, 2012 1:21 PM