locked
Adding filter and braching sql code RRS feed

  • Question

  • I wish to extend ssrs report which currently has four filters. I've added another filter and this is the code in the dataset. The filter basically uses one value from two available values ("All Locations", "UK Only"). For this, I have used an if statement to run code determined by the filter. The if / else contains the same code but with the additional condition (high lighted in bold) I added in the else.  Is this the way you would do this? 

    I  include all locations or just the UK locations in the report. We added a new parameter to the .rdl file. The parameter is called Location.

    The parameter passes one of the two values ‘Y’ or ‘N’ to the sql query. If Y is passed then the report retrieves ‘All Location Zones’ record otherwise if ‘N’ is passed to the sql query only  the ‘UK location Zone’ records are retrieved. The default value is set to Y (All location zones). 

    The location zone field values are uk, au, us, la etc

    uk only = uk

    if @Location = 'Y'
    	begin 
    		SELECT X.*, X.No_of_days * X.new_noofpassengersbookedoncruise AS PBD
    FROM
              (
              SELECT
              FilteredIncident.new_brandidname
              , FilteredIncident.new_shipidname
              , FilteredIncident.new_cruiseidname
              , ( (select SUM(b.new_noofpeopleonbooking)
    					  FROM	FilteredNew_passengerbooking pb
    					  INNER JOIN	FilteredNew_booking b
    					  ON	pb.new_bookingid = b.New_bookingId
    					  AND pb.New_LeadPassenger = 1
    					  WHERE	pb.new_cruiseidName = FilteredIncident.new_cruiseidname
    					  AND	pb.new_brandidName = FilteredIncident.new_brandidname
    					  AND	(pb.statuscodename = 'Active') AND b.statuscodename IN ('Booked', 'Guaranteed') )
              ) AS new_noofpassengersbookedoncruise
              , FilteredNew_cruise.new_returndate
              , FilteredNew_cruise.new_saildate
              , CASE WHEN FilteredNew_cruise.new_saildate IS NULL OR FilteredNew_cruise.new_returndate IS NULL THEN 0
              ELSE DATEDIFF(d, FilteredNew_cruise.new_saildate, FilteredNew_cruise.new_returndate) END AS No_of_days
              , 1 AS counter
              , FilteredIncident.new_caseloggedname
              , SUM(CASE WHEN new_resolutiontypename = 'Cheque' THEN new_value_base ELSE 0 END) AS Cheque_Sum
              , SUM(CASE WHEN new_resolutiontypename = 'Polar Gift' THEN new_value_base ELSE 0 END) AS gift_sum
              , SUM(CASE WHEN new_resolutiontypename = 'FCC' THEN new_value_base ELSE 0 END) AS FCC_sum
              , SUM(CASE WHEN new_resolutiontypename = 'OBC' THEN new_value_base ELSE 0 END) AS OBC_sum
              , SUM(CASE WHEN new_resolutiontypename = 'FCC ODBC' THEN new_value_base ELSE 0 END) AS FCCOBC_sum
              , SUM(CASE WHEN new_resolutiontypename = 'Ship OBC' THEN new_value_base ELSE 0 END) AS ShipOBC_sum
              , SUM(CASE WHEN new_resolutiontypename = 'Third Party Gift' THEN new_value_base ELSE 0 END) AS ThirdGift_sum
              , SUM(ISNULL(R.new_value_base, 0)) AS Totalcomp_sum
              , S.Title AS new_name
              
    
              FROM			FilteredIncident
              INNER JOIN		FilteredNew_cruise ON FilteredIncident.new_cruiseid = FilteredNew_cruise.new_cruiseid
              LEFT OUTER JOIN	FilteredNew_resolution AS R ON R.new_caseid = FilteredIncident.incidentid
              LEFT OUTER JOIN	FilteredSubject AS S ON S.SubjectId = dbo.Panacea_TOPSUBJECT(FilteredIncident.subjectid)
    
              WHERE
    
              (FilteredIncident.new_brandidname IN (@Brand))
              AND (FilteredIncident.new_shipidname IN (@Ship))
              AND (FilteredIncident.new_cruiseidname IN (@Cruise))
              AND (FilteredIncident.new_caseloggedname IN (@caselogged))
    		  
              GROUP BY
              FilteredIncident.new_brandidname
              , FilteredIncident.new_shipidname
              , FilteredIncident.new_cruiseidname
              , FilteredNew_cruise.new_cruiseid
              , FilteredNew_cruise.new_returndate
              , FilteredNew_cruise.new_saildate
              , FilteredNew_cruise.new_brandid
              , FilteredNew_cruise.new_shipid
              , DATEDIFF(d, FilteredNew_cruise.new_saildate, FilteredNew_cruise.new_returndate)
              , CASE WHEN FilteredIncident.CreatedOn < FilteredNew_cruise.New_SailDate THEN 'Pre'
              WHEN FilteredIncident.CreatedOn > FilteredNew_cruise.New_SailDate THEN 'Post'
              END
              , FilteredIncident.new_incidentidname
              , FilteredNew_cruise.new_saildate
              , FilteredNew_cruise.new_noofpassengersbookedoncruise
              , FilteredNew_cruise.new_returndate
              , FilteredIncident.new_caseloggedname
              , S.Title
              , S.SubjectId
              , FilteredIncident.incidentid
              ) AS X
    	end
    else
    	begin
    		SELECT X.*, X.No_of_days * X.new_noofpassengersbookedoncruise AS PBD
    FROM
              (
              SELECT
              FilteredIncident.new_brandidname
              , FilteredIncident.new_shipidname
              , FilteredIncident.new_cruiseidname
              , ( (select SUM(b.new_noofpeopleonbooking)
    					  FROM	FilteredNew_passengerbooking pb
    					  INNER JOIN	FilteredNew_booking b
    					  ON	pb.new_bookingid = b.New_bookingId
    					  AND pb.New_LeadPassenger = 1
    					  WHERE	pb.new_cruiseidName = FilteredIncident.new_cruiseidname
    					  AND	pb.new_brandidName = FilteredIncident.new_brandidname
    					  AND	(pb.statuscodename = 'Active') AND b.statuscodename IN ('Booked', 'Guaranteed') 
    					  AND b.new_locationzone = 'UK')
              ) AS new_noofpassengersbookedoncruise
              , FilteredNew_cruise.new_returndate
              , FilteredNew_cruise.new_saildate
              , CASE WHEN FilteredNew_cruise.new_saildate IS NULL OR FilteredNew_cruise.new_returndate IS NULL THEN 0
              ELSE DATEDIFF(d, FilteredNew_cruise.new_saildate, FilteredNew_cruise.new_returndate) END AS No_of_days
              , 1 AS counter
              , FilteredIncident.new_caseloggedname
              , SUM(CASE WHEN new_resolutiontypename = 'Cheque' THEN new_value_base ELSE 0 END) AS Cheque_Sum
              , SUM(CASE WHEN new_resolutiontypename = 'Polar Gift' THEN new_value_base ELSE 0 END) AS gift_sum
              , SUM(CASE WHEN new_resolutiontypename = 'FCC' THEN new_value_base ELSE 0 END) AS FCC_sum
              , SUM(CASE WHEN new_resolutiontypename = 'OBC' THEN new_value_base ELSE 0 END) AS OBC_sum
              , SUM(CASE WHEN new_resolutiontypename = 'FCC ODBC' THEN new_value_base ELSE 0 END) AS FCCOBC_sum
              , SUM(CASE WHEN new_resolutiontypename = 'Ship OBC' THEN new_value_base ELSE 0 END) AS ShipOBC_sum
              , SUM(CASE WHEN new_resolutiontypename = 'Third Party Gift' THEN new_value_base ELSE 0 END) AS ThirdGift_sum
              , SUM(ISNULL(R.new_value_base, 0)) AS Totalcomp_sum
              , S.Title AS new_name
              
    
              FROM			FilteredIncident
              INNER JOIN		FilteredNew_cruise ON FilteredIncident.new_cruiseid = FilteredNew_cruise.new_cruiseid
              LEFT OUTER JOIN	FilteredNew_resolution AS R ON R.new_caseid = FilteredIncident.incidentid
              LEFT OUTER JOIN	FilteredSubject AS S ON S.SubjectId = dbo.Panacea_TOPSUBJECT(FilteredIncident.subjectid)
    
              WHERE
    
              (FilteredIncident.new_brandidname IN (@Brand))
              AND (FilteredIncident.new_shipidname IN (@Ship))
              AND (FilteredIncident.new_cruiseidname IN (@Cruise))
              AND (FilteredIncident.new_caseloggedname IN (@caselogged))
    		  
              GROUP BY
              FilteredIncident.new_brandidname
              , FilteredIncident.new_shipidname
              , FilteredIncident.new_cruiseidname
              , FilteredNew_cruise.new_cruiseid
              , FilteredNew_cruise.new_returndate
              , FilteredNew_cruise.new_saildate
              , FilteredNew_cruise.new_brandid
              , FilteredNew_cruise.new_shipid
              , DATEDIFF(d, FilteredNew_cruise.new_saildate, FilteredNew_cruise.new_returndate)
              , CASE WHEN FilteredIncident.CreatedOn < FilteredNew_cruise.New_SailDate THEN 'Pre'
              WHEN FilteredIncident.CreatedOn > FilteredNew_cruise.New_SailDate THEN 'Post'
              END
              , FilteredIncident.new_incidentidname
              , FilteredNew_cruise.new_saildate
              , FilteredNew_cruise.new_noofpassengersbookedoncruise
              , FilteredNew_cruise.new_returndate
              , FilteredIncident.new_caseloggedname
              , S.Title
              , S.SubjectId
              , FilteredIncident.incidentid
              
              ) AS X
              where X.new_noofpassengersbookedoncruise is not null
    	end




    Sunday, May 20, 2012 5:12 PM

Answers

  • Hi There

    In that case you just need to pass all location and UK only inside your parameter dataset and for your query you do not need to change anything. I am not in a favour of using the if statement approach as if tomorrow your company has decided to add another location to select inside your parameter values then you need to put another if statement and so on but if you use the above mention approach this will never be a problem for you.

    As far as character limit for SQL in SSRS report if you are talking about the parameter length there is not anymore as SQL server 2005 onward you can declare varchar(max), nvarchar(max) which can accept a wide range of values.

    If you have any question please let me know.

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Syed Qazafi Anjum Monday, May 21, 2012 9:32 PM
    • Marked as answer by Lola Wang Tuesday, May 22, 2012 1:25 AM
    Monday, May 21, 2012 9:32 PM

All replies

  • Hi There

    I think you just need to create another dataset (say Zone) and select all your distinct location and your query might look like this

    Select distinct

                    FilteredNew_booking.new_locationzone  as ZoneId,

                    FilteredNew_booking.new_locationzone  as ZoneDescription

    FROM

                    FilteredNew_booking

    UNION ALL

    SELECT NULL AS ZoneId,

                    'All Locations' as ZoneDescription

                    order by 1

    and in your parameter Location please use that dataset (say Zone) for  getting values

    Now you just need to change your query like this. You do not need to put if condition anymore instead you could write the query like

    AND b.new_locationzone = ISNULL(@Location, b.new_locationzone)

    so when the value passed as null it will return all locations otherwise it will match the location passed from the parameter

    I am putting screenshot for your help

    If you have any question please let me know.

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

    Select distinct FilteredNew_booking.new_locationzone as ZoneId, FilteredNew_booking.new_locationzone as ZoneDescription FROM FilteredNew_booking UNION ALL SELECT NULL AS ZoneId, 'All Locations' as ZoneDescription order by 1

    ---inside your main query please put this condition

    AND b.new_locationzone = ISNULL(@Location, b.new_locationzone)



    Sunday, May 20, 2012 11:59 PM
  • Thanks for that. I actually didn't want values like AU, LA, US etc. I want to show only two values. "All Locations" or "UK Only". The All Location field in CRM will have values such as AU, LA, US, UK etc

    One question though - is there a character limit for sql in ssrs report? I don't know if this is true, but someone told me there is a 4000 character limit therefore anything after that would be trimmed. 

    Monday, May 21, 2012 5:52 PM
  • Hi There

    In that case you just need to pass all location and UK only inside your parameter dataset and for your query you do not need to change anything. I am not in a favour of using the if statement approach as if tomorrow your company has decided to add another location to select inside your parameter values then you need to put another if statement and so on but if you use the above mention approach this will never be a problem for you.

    As far as character limit for SQL in SSRS report if you are talking about the parameter length there is not anymore as SQL server 2005 onward you can declare varchar(max), nvarchar(max) which can accept a wide range of values.

    If you have any question please let me know.

    Many Thanks

    Syed Qazafi Anjum

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    • Proposed as answer by Syed Qazafi Anjum Monday, May 21, 2012 9:32 PM
    • Marked as answer by Lola Wang Tuesday, May 22, 2012 1:25 AM
    Monday, May 21, 2012 9:32 PM
  • HI Pavan !

    According to the below mentioned supprt KB from Microsoft;

    http://support.microsoft.com/kb/2661403

    ASP.NET requests that have lots of form keys, files, or JSON payload receive an error response from the server.

    Resolution : Add the following section in your Report Manager Web.Config file;

    <configuration>
      <appSettings>
        <add key="aspnet:MaxHttpCollectionKeys" value="1000" />
      </appSettings>
    </configuration>

    Set the value to "2000" or bigger according to your requirement.

    Please let me know if this helps. Hopefully i have answered you correctly.

    Thanks, Hasham


    Sunday, July 29, 2012 9:43 PM
    Answerer