Friends,
Parameters are used to specify the data to use in a report. There are two types of parameters in a report –
- Query Parameters – When you define a dataset query that includes variables, Reporting Services creates corresponding query parameters. Query parameters are used to limit data retrieved from the data source to just the data needed for the report.
- Report Parameters – Report parameters appear on the report toolbar and allow report readers to select or enter values that are used when a report is processed. In Report Designer, report parameters are created automatically from query parameters. In Report Builder, report parameters are created when you set a prompt on a filter clause. You can also define report parameters that are not tied to query parameters.
Now let’s see how to add Parameters to a Report. Here in this post we are gonna discuss about Single Valued Parameters and for that I selected OUR Adventure Works Database as Data source. First I created a report(Tabular) using the following query –
SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName, FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight FROM DimProduct INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey
and pfb the report screenshot.
At this point of time there are no Parameters defined and hence you will get all the data from the database and will be displayed in the report. PFB the steps to be followed to ADD PARAMETERS to your report –
- Right click on Datasource and select “Add Dataset” option.
- Select the data source and provide the query which pulls values for parameter and as I am adding “Category” as parameter I am using the following query.
- When you click on OK by providing the query, you can see new data set added with TWO columns as shown in below screenshot.
- Now Right Click on “Parameters” and select “Add Parameters” option. The wizard will get open and Chnage the default value given for NAME and PROMPT if required.
- Now select the “Available Values” tab which is on the Left hand side of the wizard and select the option “Get Values From a Query”. Provide the Dataset name,Value Field and Label Field as shown below –
- Click on OK and with this our First parameter is added to report.
As we are gonna design cascading parameters, the remaining parameters creation changes a bit as those are depended on other parameters and here let us see how to define the parameter for Sub Categories.
- Right click on Datasource and select “Add Dataset” option.
- Select the data source and provide the query which pulls values for parameter and as I am adding “Sub Category” as parameter I am using the following query.
select ProductSubCategoryKey,EnglishProductSubCategoryName as “SubCategories” from dimproductSubcategory
Where ProductCategoryKey = @ReportParameter1
- The above query has a WHERE clause and @ReportParameter1 as varaible. This is because the values of SubCategory parameter is based on the selection of Categories parameter.
- Now select parameters tab on left hand side and select the “Parameter Value” from the list of available parameters as shown below.
- When you click on OK by providing the query, you can see new data set added with TWO columns as shown in below screenshot.
- Right click on “Parameters” and select “Add New Parameter”.
- When the wizard opens, Change the Prompt and Name if you wish to provide proper names.
- Go to the “Available Values” tab and select the option “Get Values from Query” and select the values as shown below.
- Select OK and you can see your first cascading parameter got created.
- Now repeat the same step to create one more parameter “Products” whose value is depended on selection of “SubCategories” and PFB the query for the same.
select ProductKey,EnglishProductName as “Products” from dimproduct
Where ProductSubCategoryKey = @ReportParameter2
Now all the report parameters are ready. Here the sequence of parameters are as follows – Category –> Sub Category–> Product and you can see the same in the below given screenshot.
If you see the above screenshot the Parameters looks like working fine i.e when u Sub Category is listing only those records which belongs to the category selected and the same for Products too but if you see the chart area the data doesn’t look correct. This is because we added parameters but not linked those parameters to the Main dataset from which we are getting data and filling the chart. Now I will modify the Main dataset in order to fetch data by applying parameter values. Edit the main dataset and update the query as shown below –
SELECT DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName,FactInternetSales.SalesAmount, FactInternetSales.TotalProductCost, FactInternetSales.TaxAmt, FactInternetSales.Freight FROM DimProduct INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey Where DimProduct.ProductKey = @ReportParameter3 and DimProductSubCategory.ProductSubCategoryKey = @ReportParameter2 and DimProductCategory.ProductCategoryKey = @ReportParameter1
Now Select Parameters tab and map the parameters as shown below.
Click on OK and your report is ready. Go to preview tab and test you report. You can see Parameters cascading and the Report data get filtered as shown below –
That’s it .. Your Report with Parameters is Ready .. Hope you understood the concepts .. Happy Coding !!
Regards,
Roopesh Babu V
Nasal lavage biomarkers: effect of water damage and microbial growth in an office building.
Make sure when you get your tadalafil 5mg goodrx , an effective treatment, at low prices
People with depression are at higher risk for suicide, and certainly those with untreated depression might take their own life.
Осуществим мечту о доставке цветов в Нижнем Новгороде, надежно и качественно.
Доставка цветов в Нижнем Новгороде откроет мир нежности и красоты, порадуйте себя и других прекрасными цветами.
Красота и свежесть цветов на доставку в Нижнем Новгороде, быстрая доставка гарантирована.
Очаруйте своих близких уникальной доставкой цветов в Нижнем Новгороде, доставляем красоту прямо к вам домой.
Устроим цветочный феерверк в Нижнем Новгороде, берите только лучшее.
Цветочное настроение с доставкой в Нижнем Новгороде, выбирайте только лучшие букиеты у нас.
Удивите близких невероятной доставкой цветов в Нижнем Новгороде, заказывайте цветы прямо сейчас.
Подарите радость с помощью доставки цветов в Нижнем Новгороде, покажите свою заботу и внимание.
Осуществим мечту о цветах в Нижнем Новгороде, быстрая доставка качественных цветов.
цветы с доставкой в нижнем новгороде цветы с доставкой в нижнем новгороде .
по франшизе по франшизе .
Laboratory studies revealed an elevated total serum alkaline phosphatase level of 350 U L reference range 40 125 buy generic priligy The way Clenbuterol works inside the human body is by stimulating the beta 2 receptor
срочный вывод из запоя ростов http://www.domsadremont.ukrbb.net/viewtopic.php?f=3&t=903 .
вывод из запоя ростов на дону на дому [url=https://www.family2.quadrobb.me/viewtopic.php?id=1836#p6861]https://www.family2.quadrobb.me/viewtopic.php?id=1836#p6861[/url] .
нарколог на дом круглосуточно [url=ideya.forums.party/viewtopic.php?id=655]нарколог на дом круглосуточно[/url] .
вывод из запоя краснодар наркология [url=http://setter.borda.ru/?1-7-0-00000674-000-0-0-1730745092]вывод из запоя краснодар наркология[/url] .
выведение из запоя врачом наркологом [url=www.internetmoney.bestbb.ru/viewtopic.php?id=31701/]www.internetmoney.bestbb.ru/viewtopic.php?id=31701/[/url] .
вывод из запоя в стационаре [url=www.vip.rolevaya.info/viewtopic.php?id=6915/]вывод из запоя в стационаре[/url] .
вывод из запоя в стационаре [url=https://vip.mybb.rocks/viewtopic.php?id=7691]вывод из запоя в стационаре[/url] .
капельница от похмелья цена [url=https://www.aktivnoe.forum24.ru/?1-7-0-00012847-000-0-0-1730814083]капельница от похмелья цена[/url] .
перед выводом из запоя в стационаре перед выводом из запоя в стационаре .