Qayyum ChauDhary

Qayyum ChauDhary

  • NA
  • 27
  • 2.5k

How to use Parameters in SSRS?

Jan 29 2020 11:53 AM

i need to provide an option in the report so that I can pass a parameter to this for Order By. Right now it is very simple and following things I am willing to achieve..

1 - Add a new Parameter to this report called @OrderBy
2 - If the value of the @OrderBy is null or blank then use the Order By DaysLate DESC (means the current functionality will be as it is)
3 - If the value of @OrderBy is 1 then use Order By RugID ASC (means we will order the rugs by rugid in ascending order instead of days late descending order)  

What I have achieved so far is: 
Declare a parameter called "OrderBy" in report parameters and test these queries:
  1. SELECT RugID, Description, DesignCode, Size, Rate, Price, OrderNo, CONNo, DaysLate, CustomerID, ShippedTo, ShippingAddressIDFK, SizeForReport  
  2. FROM ConDueRugsView  
  3. WHERE (CustomerID = @OrderBy) OR  
  4. (@OrderBy IS NULL)  
  5. ORDER BY DaysLate DESC  
and
  1. SELECT RugID, Description, DesignCode, Size, Rate, Price, OrderNo, CONNo, DaysLate, CustomerID, ShippedTo, ShippingAddressIDFK, SizeForReportFROM ConDueRugsViewWHERE (@OrderBy = 1)  
  2. ORDER BY RugID ASC  
It works good but how to use this in " if - else " statement? 

Answers (1)