SSRS Default parameter based on the data with limitations

Question

Hai, here in ssrs report, i have a cascading parameter, which is working fine.

Now I am in need to bring the particular items to default parameters.

For Instances,
I have a parameter named Category, where values are 'Vegetables and fruits'. I selected fruits.
a parameter named Subcategory is there, and the available values are 'Apple, Orange, Grapes, kiwi'.
what exactly I need is, After selecting category as Fruits, in subcategory is 'Apple, Orange' is available in the dataset, that 'Apple, Orange'alone should be shown in default values, If Orange alone is there, default is Orange, If apple alone is there, default is apple.

if neither Apple nor Orange is there, then default should be Grapes, kiwi

SQL for subcategory: SELECT DISTINCT FRUITS FROM TBL.FRUITS WHERE CATEGORY in (@CATEGORY)

Answer 1

Hi @Navinkumar Kanagaraj ,
I assume the dataset query for parameter Subcategory is from database table [TableC]

CREATE TABLE TABLEC  
(CATEGORY VARCHAR(20),  
NAME VARCHAR(20)) DELETE FROM TABLEC  
INSERT INTO TABLEC  
SELECT 'FRUIT','GRAPE'  
UNION ALL  
SELECT 'FRUIT','XXX'  

SELECT * FROM TABLEC  

The query used for the dataset is as follows:

declare @hasappleororange int  
  
set @hasappleororange =(select count(*)from TABLEC where NAME in('APPLE','ORANGE'))  
if (@hasappleororange>=1)  
SELECT distinct NAME from TABLEC where NAME in('APPLE','ORANGE')  
else  
SELECT * FROM (VALUES('GRAPE'),('KIWI')) AS p(NAME)  

Preview：

And always keep refreshing in the Parameter Properties:

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.