I am using SQL Server 2008 R2 Reporting Service. Now thing is that in my Query there is main category and sub category. They are not id base they are text type and all are in one table. Same sub category can be in more than one main category. Now when generating report user wants to select subcategory that they want to show in report. So like he wants "xxx" sub category in "yyy" main category but same sub category is in "aaa" main category but he don't want to show it in "aaa" main category. But I am thinking is that this can become tedious when generating report because, let say we have 10 Main category and each main category have 6 subcategory so end user have to loop 60 times in the subcategory category. So I am confused now how can i go ahead. Kindly can somebody give me tips on how can make GUI and query both. I am using reporting viewer control with my customize asp.net screen.

Hi Milind, It looks like you have same subCategories across diff categories. So, Can use also select the category first and then as a cascading parameter you can add SubCategory paramater. This will be the ideal way to return one row.. Now, if you do not want user to select category first, the only choice you have to get all the data as per SubCategory. Also, there is no need to loop through the data, you can select only that data from table based on parameters.. From your example, what is a basis for disaplying 'XXX' subcategory from 'YYY'and not from 'AAA'. - Chintak (My Blog)

Then what about other categories !! in main category

make 2 parameters param1 : para_main_category Type : String param2 : para_sub_categories Type : String Multivalued param For the available values in your para_main_category, make a dataset with a query that gets all your main categories. Like Select distinct ColumnThatContainsMainCategory from YourTable Then, for para_sub_categories, make a dataset with the following query : select distinct ColumnThatContainsSubCategory from YourTable where ColumnThatContainsMainCategory = @para_main_category Then, in your main query, select WhatevercolumnYouWantToSelect from YourTable Where ColumnThatContainsMainCategory = @para_main_category and ColumnThatContainSubCategory in (@para_sub_categories) So you will be able to query 1 main category at a time, with multiple sub categories.

make 2 parameters param1 : para_main_category Type : String param2 : para_sub_categories Type : String Multivalued param For the available values in your para_main_category, make a dataset with a query that gets all your main categories. Like Select distinct ColumnThatContainsMainCategory from YourTable Then, for para_sub_categories, make a dataset with the following query : select distinct ColumnThatContainsSubCategory from YourTable where ColumnThatContainsMainCategory = @para_main_category Then, in your main query, select WhatevercolumnYouWantToSelect from YourTable Where ColumnThatContainsMainCategory = @para_main_category and ColumnThatContainSubCategory in (@para_sub_categories) So you will be able to query 1 main category at a time, with multiple sub categories.