Skip to main content

Lesson 13: Parameters session 1 in SSRS

How to Add Static value parameter in SSRS.

  • After creating the datasource, while creating the dataset add a where clause to display that value as a parameter, consider the Salesperson column in the image below.
  • Now to go to parameter properties, in available values and click on specify values and required values to be displayed in the output for selection.

How to Add Single Value dynamic parameter in SSRS.

  • In the dataset add a where clause which accepts a value and display the results based on it. In the first above consider EnglishProductCategoryName as example.
  • Now to in order to make it dynamic go to available values in Parameter properties and select get values from a query.
  • Before that we need to create a dataset to supply values to that parameter. Select the dataset and assign the value field and label field.

How to Add Multi Value dynamic parameter in SSRS.

  • In order add a Multi value parameter we need to select one additional option compared to single value parameter creation.

  • In the pic below we can see a multiple values parameter sub category which allows us to select multiple values for it.

How to Add Cascading parameters in SSRS.

  • In the pic1 we can see there are three parameters in where clause, consider the case where we can display the results based on category name and sub category name.
  • Now first create a two datasets , one dataset to select distinct categories available in the table and assign to the category parameter.
  • Second create a dataset to select subcategory based on the category selected, in this case create the dataset as below.
  • Now preview the report where you need to first select category and then subcategory to view the results.

Comments

Post a Comment

Popular posts from this blog

Lesson 57: Auto refresh the report in SSRS

                     How to Auto refresh the report in SSRS. Firstly create a datasource,dataset and select the required fields into the table/matrix. If you want to refresh the report frequently for every second you can change the Auto refresh as 1 in the properties of the report. If you want to change the color of a field after given period of time to change the expression of the color in properties as a follows and set the Auto refresh time to the required period of time in the report properties.
Post a Comment
Read more

12th Day

Conditional statement in SSIS: same as ternary operator in C ** <condition>? <true>:<false> Eg: Gender=="M" ? "Male": "Female" Multicast Transformation : This t/r creates multiple copies of i/p data. it is useful because every time we need not to go to source. ** This will improve task performance. Aggregate Transformation : It is used to convert the detailed values into aggregated data by using Aggregate functions like sum,avg,max,min,count etc. ** It is Asynchronous transformation. Example: to the i/p data from source group by deptno and perform aggregate funcs. Conversion in SSIS ; * Conversion is very important because data from flat file, excel comes to transformations in the form of string(varchar) , so we cannot perform required operations so it require to convert data. 1) Directly at source level : ** Select flat file source, right click--> show advanced editor. ** Select i/p & o/p properties tab, ...
Post a Comment
Read more

Lesson 67: Join two datasets in SSRS by using Lookup function

How to join two datasets in SSRS by using Lookup function First create two datasets each one with a different datasource. Next insert a table in the report body and insert the required columns from the dataset1. Next in order to insert the columns from 2nd dataset, you need to write a look up function where you need to give the matching columns from both the datasets and the required column from dataset and scope as 2 datset Here since we are inserting the columns of dataset1 first, we are writing exp with scope as dataset 2.
Post a Comment
Read more