I wish to show a table with empty value if there is no record found however i don't know how to do it..Hope can get some guidance. I got a table A Table A Date Item Stock Quantity 19/7/2011 Hotdog 5 19/7/2011 Nugget 6 When user enter parameter ' 19/7/2011' it will show table below on the SSR:- (able to retrieve the data ) Item Quantity Hotdog 5 Nugget 6 Now i need to do something like this when the user enter parameter '12/12/2012' it will show table like this:- Item Quantity Hotdog 0 Nugget 0 Currently my ssrs show nothing, since there is no value...is it something need to be done on my stored procedure or on the ssrs expression ? any helps would greatly appreciated. Thank you.

Have a calendar table which contains all dates and issue LEFT JOIN with your table SELECT <> FROM Calendar LEFT JOIN tbl ON.... AND date=@parBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

You are right to suppose that if your query is returning no data, then the table will display nothing. I can thing of two way's around this; first is to put a UNION in your query to add some dummy data (i.e. the headings but 0's for the data), the second method is to fiddle around with the expression used to display the data so that it returns 0 if the date is in the future (i.e. using Iif( date > currentdate, 0, expression) ).I work to UK time. If this post, or another, answers your question, please click the 'Mark as answer' button so others with the same problem can find a solution more easily.

May be you as can try the below query.. select Item, [stock quantity] as Quantity from [table A] where date = @DateParam IF @@rowcount =0 Select distinct Item, 0 as Quantity from [table A] Pls mark as answer, if this helps- Kerobin

still can't...i will keep trying

Try the below code SELECT it.Item,ISNULL(T.[stock quantity],0) FROM (SELECT DISTINCT Item AS Quantity FROM [table A]) it LEFT JOIN [table A] T ON it.Item = T.Item AND T.Date = @DateParam

Hi KazukiChiyan, Based on your requirement, please take the following steps of my example as a reference: 1. In your table, add the following expression to the data region: =IIF(IsNothing(Fields!StockQuantity.Value),0,Fields!StockQuantity.Value) 2. Right-click on the detail row of the table and select “Row Visibility”. Then, select “Show or hide based on an expression” option and set the expression like this: =IIF(Fields!Date.Value=Parameters!Date.Value,FALSE,TRUE) Note: This method is unsuitable if we add a filter which is based on the date parameter to filter data For more information about Expression Examples (Report Builder 3.0 and SSRS), please refer to the article below: http://msdn.microsoft.com/en-us/library/ms157328.aspx If you have any more questions, please feel free to ask. Thanks, Bin LongPlease remember to mark the replies as answers if they help and unmark them if they provide no help.