Forum Discussion
No data Available for Selected Parameters In Paginated Reports
I have a Scenario on Paginated Report (SSRS Reports), I Have developed a Report where i have Product Name Parameter. This Parameter is Text Parameter, Where I can type in the values for Product Name parameter and Submit the report for result. Result would be rendered like One page(or) Tab for each Product (Ex: If I type in 3 Product names in parameter 3 pages or tabs will be created in excel, one for each product). Now the scenario is if I Type in 3 Product name and i have data for only two products and 3rd product has no data, in this case I have requirement to show new tab for 3rd product saying No data Available for selected Parameter and need to show the Product Name as well.
any help would be much appreciated
1 Reply
- For this, it is all depends on how you bringing back your data in the resultset/dataset. I'm assuming, when people enter 3 products in the parameter, you must somehow knows how to separate them, probably using comma separated, or semi-colon, or whatever, that way you know how to parse that and know it is 3 products that were entered and not just one. Normally, if the product list is not that many, you may want to show the list of products available and make that parameter as multi-value/multi-select parameter.
Anyhow, in the main query, after you already parse that 3 products into its own column and row (store it in temp table or something). Then, you want to start your query with something like this:
select t1.product_name, t2.*
from #product_list t1
left join (select .... do you subquery here) t2 on t2.product_name = t1.product_name
This way, your query will always bring back 3 rows (again, idk what or like how many rows will your subquery bring back). If there is no product from your sub query, it will still have that missing product in your resultset with null values on the t2 subquery. Then, when you do paginate and group it by product, that missing product will still create a new tab.
Good luck
