Sunday, July 18, 2010

Report Parameters in SSRS 2008

What is Report Parameters in SSRS?
In SSRS, parameters are used to specify the data to use in a report, narrow down your report data for better analysis, connect related reports together, and vary report presentation. Report parameters can be Single-Valued and Multivalued.

Using Parameters in Reporting Services:
The most common use of parameters is to vary report data retrieved by dataset queries. In this scenario, users are prompted for a value or values when they run the report, and the dataset query retrieves only the data that is requested. You can change the report parameter properties in the report design tools to include a valid values list that displays at run time.

You can also create cascading parameters, which retrieve hierarchical data from a data source. I'll explain cascading parameters in my nect post. First I want to explain creating a report parameter in SSRS 2008.

Below are the uses of report parameters:
  • To Control Report Data - by selecting required parameters values to filter the report data.
  • To Control Report Appearance - use parameters to change report appearance using expression-based properties, including conditionally hiding report items and conditionally changing text color.
  • To Connect to Other Reports - use parameters to link to drillthrough reports, subreports, and linked reports.
  • To Select Specific Data Columns - parameters can be used to select specific columns of a table/matrix at run time.
How to create Report Parameters:
Query parameters are added to a dataset query by way of the query designers or the Dataset Properties dialog box. After you create a query with parameters, Reporting Services automatically links query parameters to report parameters with the same name. Below are the steps to create parameters:

STEP 1:
To begin, start a new Report Server Project project in Visual Studio 2008 by clicking on Start --> All Programs --> SQL Server 2008 --> SQL Server Business Intelligence Development Studio. Then, from the menu, select File --> New --> Project to open New Project wizard. Now select Report Server Project from Visual studio installed templates and specify Name, Location and Solution Name.

I'll use the solution ctreated in my previous article. I'll use FirstReport.rdl for this example and I'll create a parameter Designation to filter report data for selected designations.

STEP 2:
Create a new Data Set for the report parameter. I will add a dataset dsDesignation for Employee's Designation using query
SELECT DISTINCT Desg AS Designation FROM Employee
as shown below:



















STEP 3:
Now I will add a parameter Designation. In Report Data section, right click on Parameters node and click Add Parameter... as shown below:























STEP 4:
In Report Parameter Properties window, enter Name and Prompt of the parameter and select the Data type from dropdown box. For our example, enter Designation in Name and Prompt text boxes and select Text as data type.



















STEP 4:
Now click on Avaliable Values to set available values for the parameter. Select Get values from a query. Select dsDesignation as Dataset, Designation as Value field and Label field.



















STEP 5: Click on Default Values, Select Get values from a query. Select dsDesignation as Dataset, Designation as Value field. Click OK to save changes.



















STEP 6:
Double click on your main dataset (dsMain) to open Dataset Properties. Now Select  Parameters and click on Add button to map Designation parameter.

STEP 7:
Now final step, just add parameter in your query as shown in below snapshot:
WHERE [Desg] = @Designation





















Now preview the report to check the action of parameter used:
















Note: This article describes onlu about single valued parameter. I'll explain multi-valued parameter in my next article.

75 comments:

  1. I am having trouble with ssrs multivalued parameters. MY question is how can I pass multivalued parametes in using ssrs expression

    ReplyDelete
  2. Your question is a bit unlcear. Do you want to pass the MV parameter in a stored procedure or just want to display the values in report?

    If you just want to display the values then use following expression:

    =Join(Parameters!MyParameter.Value,",")

    ReplyDelete
  3. Is the process of creating a drillthrough report off of a SQL query the same as creating a drillthrough report off of an MDX query? I am unable to populate the parameter in my subreport when I click in the chart to drillthrough. I went into Series Properties, Actions and checked Go To Report, selected the correct report and parameter to be passed. When I click on the report, it sends me to the sub report but the parameter on the sub report is blank.

    ReplyDelete
  4. Thanks for sharing such useful information on the blog and refer the link Android Training in Chennai

    ReplyDelete
  5. Your blog is really useful for me. Thanks for sharing this useful blog..Suppose if anyone interested to learn Android Course in Chennai please visit fita academy which offers best Android Training in Chennai at reasonable cost.

    ReplyDelete
  6. Where is your next post? I am looking for the multivalued one. Thanks

    ReplyDelete
  7. Could we load whole data first then filter with report parameter . please revert earlier.

    ReplyDelete
  8. Oracle Training in chennai
    I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly..

    ReplyDelete
  9. Oracle Training in chennai
    Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    ReplyDelete
  10. Informatica Training in chennai
    Thanks for sharing such a great information..Its really nice and informative..

    ReplyDelete
  11. Pega Training in Chennai
    This post is really nice and informative. The explanation given is really comprehensive and informative..

    ReplyDelete
  12. There are lots of information about latest technology and how to get trained in them, like Hadoop Training in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this blogs..

    ReplyDelete
  13. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    QTP Training in Chennai

    ReplyDelete
  14. SAS Training in Chennai
    Thanks for sharing this informative blog. I did SAS Certification in Greens Technology at Adyar. This is really useful for me to make a bright career..

    ReplyDelete
  15. This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic
    Greens Technologies In Chennai

    ReplyDelete
  16. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    Greens Technologies In Chennai

    ReplyDelete
  17. You have stated definite points about the technology that is discussed above. The content published here derives a valuable inspiration to technology geeks like me. Moreover you are running a great blog. Many thanks for sharing this in here.

    Salesforce Training in Chennai
    Salesforce Certification
    Salesforce Training

    ReplyDelete
  18. Thanks for sharing amazing information about pega Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with pega. Pega Training in Chennai

    ReplyDelete
  19. Who wants to learn Informatica with real-time corporate professionals. We are providing practical oriented best Informatica training institute in Chennai. Informatica Training in chennai

    ReplyDelete
  20. QTP is a software Testing Tool which helps in Functional and Regression testing of an application. If you are interested in QTP training, our real time working. QTP Training in Chennai

    ReplyDelete
  21. Looking for real-time training institue.Get details now may if share this link visit Oracle Training in chennai

    ReplyDelete
  22. Hey, nice site you have here!We provide world-class Oracle certification and placement training course as i wondered Keep up the excellent work experience!Please visit Greens Technologies located at Chennai Adyar Oracle Training in chennai

    ReplyDelete
  23. Nice site....Please refer this site also Our vision succes!Training are focused on perfect improvement of technical skills for Freshers and working professional. Our Training classes are sure to help the trainee with COMPLETE PRACTICAL TRAINING and Realtime methodologies. Green Technologies In Chennai

    ReplyDelete
  24. Job oriented Hadoop training in Chennai is offered by our institute. Our training is mainly focused on real time and industry oriented. We provide training from beginner’s level to advanced level techniques thought by our experts. Hadoop Training in Chennai

    ReplyDelete
  25. let's Jump Start Your Career & Get Ahead. Choose sas training method that works for you. This course is designed for professionals looking to move to a role as a business analyst, and students looking to pursue business analytics as a career. SAS Training in Chennai

    ReplyDelete
  26. It is really very helpful for us and I have gathered some important information from this blog.Oracle Training In Chennai

    ReplyDelete
  27. Oracle Training in Chennai is one of the best oracle training institute in Chennai which offers complete Oracle training in Chennai by well experienced Oracle Consultants having more than 12+ years of IT experience.

    ReplyDelete
  28. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this.

    ReplyDelete
  29. Great post and informative blog.it was awesome to read, thanks for sharing this great content to my vision.Informatica Training In Chennai

    ReplyDelete
  30. A Best Pega Training course that is exclusively designed with Basics through Advanced Pega Concepts.With our Pega Training in Chennai you’ll learn concepts in expert level with practical manner.We help the trainees with guidance for Pega System Architect Certification and also provide guidance to get placed in Pega jobs in the industry.

    ReplyDelete
  31. Our HP Quick Test Professional course includes basic to advanced level and our QTP course is designed to get the placement in good MNC companies in chennai as quickly as once you complete the QTP certification training course.

    ReplyDelete
  32. Thanks for sharing this nice useful informative post to our knowledge, Actually SAS used in many companies for their day to day business activities it has great scope in future.

    ReplyDelete
  33. Greens Technologies Training In Chennai
    Excellent information with unique content and it is very useful to know about the information based on blogs.

    ReplyDelete
  34. Greens Technology offer a wide range of training from ASP.NET , SharePoint, Cognos, OBIEE, Websphere, Oracle, DataStage, Datawarehousing, Tibco, SAS, Sap- all Modules, Database Administration, Java and Core Java, C#, VB.NET, SQL Server and Informatica, Bigdata, Unix Shell, Perl scripting, SalesForce , RedHat Linux and Many more.

    ReplyDelete
  35. The best kits online trainings,thanks for sharing these articles.

    Selenium

    Solaris

    SQL and Plsql

    ReplyDelete
  36. have to learned to lot of information about java Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with java.
    Java Training in Chennai

    ReplyDelete
  37. Looking for real-time training institue.Get details now may if share this link visit
    Spring Training in chennai
    oraclechennai.in:

    ReplyDelete
  38. hybernet is a framework Tool. If you are interested in hybernet training, our real time working.
    Hibernate Training in Chennai,hibernate training in Chennai

    ReplyDelete

  39. Awesome blog if our training additional way as an SQL and PL/SQL trained as individual, you will be able to understand other applications more quickly and continue to build your skill set which will assist you in getting hi-tech industry jobs as possible in future courese of action..visit this blog
    plsql in Chennai
    greenstechnologies.in:

    ReplyDelete
  40. Job oriented form_reports training in Chennai is offered by our institue is mainly focused on real time and industry oriented. We provide training from beginner’s level to advanced level techniques thought by our experts.
    forms-reports Training in Chennai

    ReplyDelete
  41. hai you have to learned to lot of information about c# .net Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with c#.net.
    C-Net-training-in-chennai

    ReplyDelete

  42. hai If you are interested in asp.net training, our real time working.
    asp.net Training in Chennai.
    Asp-Net-training-in-chennai.html

    ReplyDelete
  43. Amazing blog if our training additional way as an silverlight training trained as individual, you will be able to understand other applications more quickly and continue to build your skill set which will assist you in getting hi-tech industry jobs as possible in future courese of action..visit this blog
    silverlight-training.html
    greenstechnologies.in:

    ReplyDelete

  44. Hai if our training additional way as (IT) trained as individual,you will be able to understand other applications more quickly and continue to build your skll set
    which will assist you in getting hi-tech industry jobs as possible in future courese of action..
    visit this blog webMethods-training in chennai



    ReplyDelete
  45. great article!!!!!This is very importent information for us.I like all content and information.I have read it.You know more about this please visit again.
    QTP Training in Chennai

    ReplyDelete
  46. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    QTP Training in Chennai

    ReplyDelete
  47. Hai you have to learned to lot of information about selenium Gain the knowledge and hands-on experience you need to successfully design,so you have more details visit this site..
    selenium Training in Chennai

    ReplyDelete
  48. Looking for real-time training institue.Get details now may if share this link visit
    oracle training in chennai
    oraclechennai.com:

    ReplyDelete
  49. hai If you are interested in hyperion training, our real time working. so visit this site and get more details....
    hyperion Training in Chennai.
    hyperion training in chennai

    ReplyDelete
  50. great article!!!!!This is very importent information for us.I like all content and information.I have read it.You know more about this please visit again.
    Best Oracle Training in Chennai

    ReplyDelete
  51. Thank you for the fantastic service provided and helping me find a niche home at an affordable price. Appreciate your web designing team.

    QTP Training in chennai

    ReplyDelete
  52. Order food in online !!! online restaurant aggregate unavu wats more of its customers to order their food through its,app not its website.Am working in https://www.unavu.co/lp/fully-branded-app-chennai.php best restaurants app

    ReplyDelete
  53. Your blog is much effective and thanks for sharing information.. Here we are providing training & materilas and dumps for those who are preparing for cca 175 spark and hadoop developer certification exam and in the related fields of cloudera hadoop developer certification exam.
    cca 175 certification
    cloudera hadoop developer certification
    cca 175 spark and hadoop developer certification
    hadoop developer certification
    cca 500 certification

    ReplyDelete
  54. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    Python Training in Chennai | Python Course in Chennai

    ReplyDelete
  55. very helpful for my site. I always follow your tips....
    weblogic training in chennai

    ReplyDelete
  56. I found Lot of informative things in CALL360, 24 HOURS MEDICAL SHOP, Hospitals, Call Taxi, Many more, More than 200 categories,
    1 Million Business listings. Keep your city in finger tip currently CALL360 serve Chennai, Kanchipuram & Thiruvallur District Very shortly They expand to entire South India.

    ReplyDelete
  57. Green Ladies hostel is specifically the safest women's hostel in Chennai near Solinganallur. Surrounded by good hotels, hospitals, bus stands and shopping malls.Ladies hostel Adyar

    ReplyDelete
  58. brilliant article that I was searching for. Helps me a lot
    call360 is Fastest local search Engine we have 12 years of experience in online industery, in our Search Engine we offer,
    more than 220 categories and 1 Million Business Listing most frequently search categories
    are Money exchange Chennai and Bike mechanic Chennai,
    we deliver 100% accure data to users & 100% Verified leads to our
    registered business vendors and our most popular categories are
    AC mechanic chennai,
    Advertising agencies chennai
    catering services chennai

    ReplyDelete
  59. brilliant article that I was searching for. Helps me a lot.
    We are one of the Finest ladies hostel near OMR and our
    womens hostel in adyar is secure place for working womens
    we provide home based food with hi quality, our hostel located very near to Adyar bus depot.
    womens hostel near Adyar bus depot, we are one of the best and experienced
    womens hostel near omr

    ReplyDelete
  60. Thanks for sharing this information article, Hi We at Colan Infotech Private Limited a
    Mobile application development company in chennai,
    is Situated in US and India, will provide you best service in
    enterprise mobile app development company .
    and Colan Infotech has a group of exceedingly dedicated, inventive and creative experts with an energy for delivering exciting , helpful and stylish Web and Mobile Applications, We work with customers in a wide variety of sectors.
    We design all of our websites and applications using the responsive web design approach. Our talented team can handle all the aspects of mobility so we are rated as best service provider in
    Mobile apps development companies in chennai.

    We solidly trust that our customers start things out and there is not a viable alternative for quality of service.
    We offer custom services to a wide range of industries by exceeding our client’s expectations. You can even interact directly with the team regarding your project, just as you would with your in-house team. we always desire to solicit our customer's fruitful experience with us, we are the top notch
    Mobile App Development Company in chennai
    and mobile app development companies in Bangalore. We can provide best
    mobile app development chennai .
    We can provide cutting edge technology services in
    Mobile application development in chennai.
    Reach us for mobile app development chennai or just call us for best
    mobile app developers in chennai

    ReplyDelete

  61. Thanks for the awesome share
    Hi we at Colan Infotech Private Limited , a company which is Situated in US and India, will provide you best service and our talented team will assure you best result and we are familiar with international markets, We work with customers in a wide variety of sectors. Our talented team can handle all the aspects of custom application development, we are the best among the dot net development companies in Chennai asp .net web development company
    We have quite an extensive experience working with asp .net development services. we are the only asp.net web development company which offer custom services to a wide range of industries by exceeding our client’s expectations. You can even interact directly with the team regarding your project, just as you would with your in-house team, to achieve your dream product.Custom application development company, asp.net development companies,Hire asp .net programmers,asp.net web development services,dot net development companies in chennai Hire asp .net programmers. Here is a good resource if anyone in need of asp.net web development services
    dot net development companies in chennai

    ReplyDelete
  62. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharng this information,this is useful to me...
    Android training in chennai
    Ios training in chennai

    ReplyDelete


  63. Thanks for posting useful information.You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read..please sharing like this information......
    Web Design Development Company
    Web Design Development Company


    ReplyDelete
  64. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Web Design Company
    Web Development Company

    ReplyDelete
  65. Pretty section of content. I simply stumbled upon your site and in accession capital to say that I get actually loved to account your blog posts.
    Python Training in Chennai

    ReplyDelete
  66. I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.

    Android App Development Company

    ReplyDelete
  67. This information really worth saying, i think you are master of the content and thank you so much sharing that valuable information and get new skills after refer that post.
    SQL Server Training in Chennai

    ReplyDelete
  68. great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome...Thank you very much for this one.
    web design Company
    web development Company
    web design Company in chennai
    web development Company in chennai
    web design Company in India
    web development Company in India

    ReplyDelete
  69. it is really amazing...thanks for sharing....provide more useful information...
    Mobile app development company

    ReplyDelete

Here are few FREE resources you may find helpful.