hi i created a dataset with name productinfo, and i used this expression as quary in this dataset; but becuase i used this quary in expression , i cant used this field in my tablix in layout ="SELECT competitor_annual_Plan.year , competitor_annual_Plan.parent_competitor_nameName , " & "sum(convert(float,competitor_product_info.quantitylastyear )) as total"& " FROM competitor_annual_Plan , "& "competitor_product_info "& "WHERE competitor_annual_Plan.competitor_annual_Plan_code = competitor_product_info.parent_competitor_annual_codeName "& " AND (competitor_annual_Plan.year = " & Parameters!year.Value & ") "& Iif(Parameters!product_family.Value="0",""," and competitor_product_info.MSC_product_family = " & Parameters!product_family.Value ) & " group by competitor_annual_Plan.year , competitor_annual_Plan.parent_competitor_nameName "

You will have to use CASE EXPRESSION within a SELECT... Why not using a stored procedure and there to play with parameter ? CREATE PROCEDURE sp1 @Productfamily INT AS SELECT competitor_annual_Plan.year , competitor_annual_Plan.parent_competitor_nameName , "sum(convert(float,competitor_product_info.quantitylastyear )) as total FROM competitor_annual_Plan JOIN competitor_product_info ONcompetitor_annual_Plan.competitor_annual_Plan_code = competitor_product_info.parent_competitor_annual_codeName WHERE (competitor_annual_Plan.year =@Productfamily)AND (MSC_competitor_product_info.MSC_product_family=@Productfamily OR @Productfamily IS NULL) Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/ Blog : MS SQL Development and Optimization Blog : Large scale of database and cleansing

i right click on dataset and this window appeare but i dont know how to add my fields in above dataset here

Hi Shabnam2012, First create a dataset using the query below: SELECT competitor_annual_Plan.year , competitor_annual_Plan.parent_competitor_nameName , sum(convert(float,competitor_product_info.quantitylastyear )) as total FROM competitor_annual_Plan, competitor_product_info WHERE competitor_annual_Plan.competitor_annual_Plan_code = competitor_product_info.parent_competitor_annual_codeName GROUP BY competitor_annual_Plan.year , competitor_annual_Plan.parent_competitor_nameName In this steps, just create a normal dataset. After the dataset has been created success, then change the dataset and use your expression as query in the dataset. Then you can use the field in your tablix in layout. For more information about dynamic dataset, please see: http://msdn.microsoft.com/en-us/library/aa237477(v=sql.80).aspx Please feel free to ask if you have anything unclear. Regards, Charlie Liao

Hi Shabnam2012, First create a dataset using the query below: SELECT competitor_annual_Plan.year , competitor_annual_Plan.parent_competitor_nameName , sum(convert(float,competitor_product_info.quantitylastyear )) as total FROM competitor_annual_Plan, competitor_product_info WHERE competitor_annual_Plan.competitor_annual_Plan_code = competitor_product_info.parent_competitor_annual_codeName GROUP BY competitor_annual_Plan.year , competitor_annual_Plan.parent_competitor_nameName In this steps, just create a normal dataset. After the dataset has been created success, then change the dataset and use your expression as query in the dataset. Then you can use the field in your tablix in layout. For more information about dynamic dataset, please see: http://msdn.microsoft.com/en-us/library/aa237477(v=sql.80).aspx Please feel free to ask if you have anything unclear. Regards, Charlie Liao