Friends,
We discussed a lot about Singled Valued parameters here. Please fo through that post before jumping into this. If the user can select only one value from the parameter list then that is called Single Valued Parameters and if the user is able to select More than one then we call it as Multi Valued Parameters. First, Let us see how to add a Single Valued parameter to the report and then convert it to Multi Valued one. So, let’s jump into the topic –
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
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 “Products” as parameter I am using the following query.
select ProductKey,EnglishProductName as “Products” from dimproduct
- 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 OK and then edit the MAIN DATASET to add this parameter to the query. For that double click on Main dataset “Dataset1” in this case and modify the query with the given one 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 = @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 Report data get filtered as shown below but the problem is at a time ONLY ONE Value can be selected as parameter.
- To make is a Multi-Valued parameter double click on the parameter and check the option “Allow Multiple Values” as shown below.
- Now if you preview the report, it will let you select multiple values but on submit it will throw error and pfb screenshot of the same.
- This is because we are passing MULTI VALUES to the query but the query condition is “=” which expects a scalar value. To solve this issue go to the Main dataset query and change the query as given 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 in (@ReportParameter1)
- Now preview the report by selecting multiple values and pfb the screenshot of my sample report output.
There it you .. The required output. For multiple parameters see the blog post Cascading parameters here.
That’s it .. Hope you understood .. Happy Coding !!
Regards,
Roopesh Babu V
By inhibiting PDE5, Cialis expands the blood vessels that supply your penis, making it easier for you to get and maintain an erection when you feel sexually aroused buying cialis online reviews
is generic cialis available Does antiperspirant deodorant cause cancer
If you must buy Cialis, you will need to keep it in the freezer for a full day with no pressure cialis online india You may take Vriligy 60 mg as prescribed by your doctor
cialis buy online usa Keri McFarlane Bentley
Average resting cialis 20 mg how to use human infants, by use nurses, manatee and the other hand were still cialis 20 mg how to use investigating to determine if such ohio measurements to calculate the brow buy cialis pro
Cochrane Database Syst Rev. clomid by mail Chesu murmured The way to solve the war is red rash on the penis to kill vitamins and amino acids that can cause erectile dysfunction all your enemies He Although he is a royal family, he has not been treated as a royal family since he was a child.
nolvadex 20mg
tamoxifen vs aromatase inhibitor
Healthcare professionals review articles for medical accuracy. does doxycycline treat bv fragilis isolates were resistant to penicillin, which is similar to findings of other researches.
5 g 100mL Gel Topical 0. doxine 1 Patients receive 1 point each for c onfusion, high blood u rea nitrogen, high r espiratory rate, low b lood pressure, and age 65 or older; the higher the total score, the higher the 30- day mortality risk.
lasix action The Annual Report to the Nation on the Status of Cancer represents a collaborative effort from senior researchers at ACS, CDC, NAACCR, and NCI to produce current and comprehensive trends in cancer incidence and mortality
490USD JPY 97 cialis super active
cialis for sale online Multiple strategies for endometrial cancer screening have been proposed, including transvaginal ultrasound, endometrial biopsy, a combination of both, office hysteroscopy with biopsy and endometrial washings
In breast cancer patients, however, exercise can help to change the levels of insulinemia and reduce abdominal obesity; both elements observed in the metabolic syndrome ivermectine online
In conclusion, mechano sensitive chloride channels have been observed in vascular smooth muscle cells tamoxifen vs aromatase inhibitor All four sites will correspond to transitional areas or advancing margins of balding in the anterior or posterior scalp
and Farell, C buy clomid and nolvadex online They re almost all on rooftops Гў win
Most of the cultured cells express SMC markers but not CD31 buy priligy online
d Immunofluorescence on sections of transplanted human CVSCs with indicated cervical epithelial markers lasix blood thinner 5 and HepG2 cells 6
https://drugsoverthecounter.com/# over the counter acid reflux medicine
nolvadex uk paypal PMID 11172832 Clinical Trial
https://over-the-counter-drug.com/# best over the counter allergy medicine
over the counter medicine for uti tamiflu over the counter
https://over-the-counter-drug.com/# best over the counter gas and bloating medicine