none
Comparing single-value parameter against multiple fields in SSRS.

    Question

  • I am building a report off of a poorly-designed table and need to compare a single parameter against 4 possible values, each stored in a separate field in the database (hence, poorly designed).

    The values I have are N, L, R or C. Each of their respective columns contains either the letter after which the column is named, or null.

    I've been tasked with adding a parameter for selecting the values individually. so far, there has been no request for a multi-value selection.

    I've built the parameter and hard-coded the available values. The default is set for null, but doesn't default to "All" as the other parms in the report do currently.

    I've added a clause in the where of the query that if the parameter is = to FieldL or Field R, etc. that it should be included in the report.

    When the report runs, it brings back data for all of the 4 values, not just the one selected.

    How can I resolve this issue?

    Thanks!

    Thursday, October 17, 2013 7:02 PM

All replies

  • Can you post your query? In the meantime, perhaps something like this would work:

    --setup sample data
    DECLARE @paramType char(1);
    
    SET @paramType = 'L';
    
    DECLARE @data TABLE (
     	 N char(1)
    	,L char(1)
    	,R char(1)
    	,C char(1)
    	,Rowdata int
    	);
    		
    INSERT @data(N,L,R,C,Rowdata) VALUES
     (NULL,NULL,NULL,'C',345)
    ,(NULL,NULL,'R',NULL,123)
    ,(NULL,'L',NULL,NULL,234)
    ,('N',NULL,NULL,NULL,789)
    ,(NULL,'L',NULL,NULL,999);
    
    --query with COALESCE
    SELECT Rowdata
    FROM @data
    WHERE COALESCE(N,L,R,C) = @paramType


    Jason

    Thursday, October 17, 2013 8:31 PM
  • This is the current version...

    select

      Customer."CustomerNumber", Customer."CustomerOrganizationName", Agency."AgencyNumber",

      Agency."AgencyLegalName", AgencyIn."EORenewalDate", AgencyIn."FidelityBondInsuranceRenewalDate",

      Location."LocationNumber", Location."LocationAddress1", Location."LocationCityName",

      Location."LocationStateCode", Location."IsRemittingOffice", vwOtherU."OtherUW_List", Customr2."StateCode",

      Customr2."AgentIdentificationNo", Region."RegionName", vwSalesR."RepName", rptWatch."StartHistory",

      rptWatch."LastAuditDate", rptWatch."ExcludeNewAgent", rptWatch."AgentSplit", rptWatch."MTDGross",

      rptWatch."MTDNet", rptWatch."YTDGross", rptWatch."YTDNet", isnull(rptWatch."N",'') as N,

      isnull(rptWatch."R",'') as R ,isnull(rptWatch."L",'') as L,isnull(rptWatch."C",'') as C,

      rptWatch.WatchCodes", rptWatch."YTDNetPerLiab", CustSTyp."CustSubTypeCode"

     from

    dbo.Customer Customer inner join dbo.Agency Agency on Customer."CustomerID" = Agency."CustomerID"

    left join "dbo"."AgencyInsurance" AgencyIn on Agency."AgencyID" = AgencyIn."AgencyId"

    inner join dbo.Location Location on Agency."AgencyID" = Location."AgencyID"

    left join "dbo"."vwOtherUWCode" vwOtherU on Agency."AgencyID" = vwOtherU."AgencyID"

    inner join dbo.CustomerAccount Customr2 on Location."LocationID" = Customr2."LocationID"

    inner join dbo.Region Region on  Customr2."RegionID" = Region."RegionID"

    inner join dbo.vwSalesReps vwSalesR on Customr2."AgencyRepID" = vwSalesR."RepID"

    inner join "dbo"."rptWatchCodes" rptWatch on Customr2."CAID" = rptWatch."CAID"

    inner join "dbo"."CustomerSubType" CustSTyp  on Customr2."CustomerSubTypeID" = CustSTyp."CustSubTypeID"

    where

    ((rptWatch."SaveAs" = @month  and Customr2."CustomerTypeID" <> 4 and Customer."CustomerNumber" <> '10102'

    and Customr2."RegionID" not in (10,11) and Customr2."AgencyStatusID" = 1))

    and not (n is null and r is null and l is null and c is null) and excludenewagent=@excludenewagent

    and region.RegionID=coalesce(@Region,region.RegionID)

    and vwSalesR.repid=coalesce(@SalesRep,vwSalesR.repid)

    and customr2.statecode=coalesce(@StateCode,customr2.statecode)

    and agency.AgencyID=coalesce(@AgencyID,agency.agencyID)

    and customer.CustomerID = Coalesce(@CustomerNumber, customer.CustomerID)

    and @WatchCode = rptWatch.R or @WatchCode = rptWatch.L or @WatchCode = rptWatch.N or @WatchCode = rptWatch.C

    order by

      Region."RegionName", Customer."CustomerOrganizationName", Agency."AgencyNumber", Location."LocationCityName",

      Customr2."StateCode"

    The line in bold print is how I was trying to compare the parameter to the values in the individual fields. There is another field in the database (WatchCodes) which is a concatenation of all of the values by record (for example: N,L,C or R,L. That would place the values into one field for comparison, but then it is an issue of parsing the values.

    What is the best way for me to jump? (off the bridge is not the right answer....)

    Thursday, October 17, 2013 8:57 PM
  • I'm curious to know if making that where clause part of the join statement instead of putting it in the where clause would make a difference:

    inner join "dbo"."rptWatchCodes" rptWatch on (Customr2."CAID" = rptWatch."CAID" AND (rptWatch.N = @WatchCode OR rptWatch.L = @WatchCode OR rptWatch.R = @Watchcode OR rtpWatch.C = @WatchCode))


    Jason

    Thursday, October 17, 2013 9:37 PM
  • Ok, I tried putting the code in the join clause, but I'm getting an error about the variable already being declared.

    I removed the parameter from the report and verified no other reference to the parameter in the query, but the error persists.

    Am I missing something?

    Thursday, October 17, 2013 10:37 PM