Hello, I created a parameter with available values from a dataset. By default i want it to display the maximum value. and then the user can select the dates if he does not like the default value. how to i do this. because when i tried to set the paramter default value to max(dataset!dt.value) it says expression canot be used. how to do this. SHould i create a dataset with maximum value and then assign it to this one. Please help thanks

You got it. Create another data set that returns one row (the max). Set your default value equal to that one.

Hmm here then there is no way i could use the same dataset.

Hi There You can do it with one dataset. Please bring the maximum value of your dataset with in the same dataset and attached your default value with the maximum value of the dataset I am putting screenshots foyr your help. I hope this will help Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. SELECT [ColumnName] ,[ColumnID] ,maxColumn FROM [dbo].[GroupColumnname] inner join (select max([ColumnID]) as maxColumn FROM [dbo].[GroupColumnname] ) maximum on Columnid=[ColumnID]

You got it. Create another data set that returns one row (the max). Set your default value equal to that one.

I would caution that the above could significantly degrade performance as the query that is aggregating the max value would run once for each row in the data set. breaking it out into its own dataset ensures that it only runs one time.

I would caution that the above could significantly degrade performance as the query that is aggregating the max value would run once for each row in the data set. breaking it out into its own dataset ensures that it only runs one time. Hi BI Baracus I partially agree with you, this is just a work around if you do not want to use another dataset. To be honest it also depends how many values you have in parameter for your query performance to be adversely affected Many thanks Syed

Hi Madhavi, As Syed posted above, you achieve your goal by creating another dataset and retrieving the maximum value of a specified field from the new dataset. From your description, it seems that the parameter provides a list of date. However, the date type value cannot be used in the Max function. If the parameter has numeric values, you can create the dataset using a query like the one below: SELECT MAX(SalesAmount) AS MAX_SalesAmount FROM (SELECT st.SalesTerritoryGroup, SUM(s.SalesAmount) AS SalesAmount FROM FactInternetSales AS s INNER JOIN DimSalesTerritory AS st ON s.SalesTerritoryKey = st.SalesTerritoryKey GROUP BY st.SalesTerritoryGroup) AS MAX This query only returns the maximum value in the SalesAmount column: If you have any questions, please feel free to ask. Regards, Mike Yin

Hi Madhavi, As Syed posted above, you achieve your goal by creating another dataset and retrieving the maximum value of a specified field from the new dataset. From your description, it seems that the parameter provides a list of date. However, the date type value cannot be used in the Max function. If the parameter has numeric values, you can create the dataset using a query like the one below: SELECT MAX(SalesAmount) AS MAX_SalesAmount FROM (SELECT st.SalesTerritoryGroup, SUM(s.SalesAmount) AS SalesAmount FROM FactInternetSales AS s INNER JOIN DimSalesTerritory AS st ON s.SalesTerritoryKey = st.SalesTerritoryKey GROUP BY st.SalesTerritoryGroup) AS MAX This query only returns the maximum value in the SalesAmount column: If you have any questions, please feel free to ask. Regards, Mike Yin

Hi Mike Just to clerify are you saying taht we can not use the max function inside SSRS ? or on Dataset Please update as far as I understand you can not use any aggregate function inside default value for any data type inside SSRS am I right ? but in query you can as shown below Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. SELECT [OrderId] ,[ClientId] ,[OrderDate] ,[OrderStatus] ,maximum.maxdate FROM [dbo].[Orders] inner join (select max([OrderDate]) as maxdate FROM [dbo].[Orders] ) maximum on [OrderDate]=[OrderDate] GO

Hi Mike Just to clerify are you saying taht we can not use the max function inside SSRS ? or on Dataset Please update as far as I understand you can not use any aggregate function inside default value for any data type inside SSRS am I right ? but in query you can as shown below Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. SELECT [OrderId] ,[ClientId] ,[OrderDate] ,[OrderStatus] ,maximum.maxdate FROM [dbo].[Orders] inner join (select max([OrderDate]) as maxdate FROM [dbo].[Orders] ) maximum on [OrderDate]=[OrderDate] GO

Hello Syed, I had this idea, but just created a dataset again. just for me to keep track and simple thats it. Thank you all for the responses.

Hi Syed, I agree with you that aggregate function cannot be contained in the expressions when specifying the parameter values. I though it was invalid to use date/datetime type values in Max function, but it is proved to be valid according to your screenshot and my later test. In addition, the query I posted above can also return the maximum value of a field. Thank for your posting. Your eager to help others and your technical skills are highly appreciated. Hi Madhavi, I am glad to hear that you have resolved the issue. Thanks for asking a question in the SQL Server Reporting Services forum. Regards, Mike Yin

Yes you can, just sort (ORDER) your dataset so that the max value is the first row returned, then use =First(Fields!dt.Value, "DataSet1") as your default parameter value