Filter Report Builder to Max Date

Question

I have the data below

create table #Sales (ID INT,Amount Money, Dates Date, Product varchar(50))
insert into #Sales values
(1,12,'2023-01-03','M & S Trouser'),(2,22,'2023-01-01','Spoke'),(3,13,'2023-01-01','Peddal'),
(4,30,'2023-01-05','Mango'),(5,30,'2023-01-03','Milk'),(6,11,'2023-01-03','Wheel'),
(7,20,'2023-01-05','Tyres'),(8,24,'2023-01-03','Samsung TV'),(9,29,'2023-01-01','Clark'),
(10,13,'2023-01-03','Sardine'),(11,22,'2023-01-03','TM Lewis'),(12,27,'2023-01-03','Sonny Sound System'),
(13,14,'2023-01-01','Bulbs'),(14,11,'2023-01-05','Star Beer'),(15,30,'2023-01-03','Apple Tab'),
(16,15,'2023-01-03','Clutch'),(17,14,'2023-01-02','Asda shoes'),(18,27,'2023-01-05','Overtin')
select * from #Sales
drop table #Sales

In report builder i filtered the matrix with the properties.

when i run it i got erroe message

My expected Output looks like this

Any idea how i can resolve the issue? Thanks

Answer 1

what you can do is create 2 datasets

The first one is :

create table #Sales (ID INT,Amount Money, Dates Date, Product varchar(50))
insert into #Sales values
(1,12,'2023-01-03','M & S Trouser'),(2,22,'2023-01-01','Spoke'),(3,13,'2023-01-01','Peddal'),
(4,30,'2023-01-05','Mango'),(5,30,'2023-01-03','Milk'),(6,11,'2023-01-03','Wheel'),
(7,20,'2023-01-05','Tyres'),(8,24,'2023-01-03','Samsung TV'),(9,29,'2023-01-01','Clark'),
(10,13,'2023-01-03','Sardine'),(11,22,'2023-01-03','TM Lewis'),(12,27,'2023-01-03','Sonny Sound System'),
(13,14,'2023-01-01','Bulbs'),(14,11,'2023-01-05','Star Beer'),(15,30,'2023-01-03','Apple Tab'),
(16,15,'2023-01-03','Clutch'),(17,14,'2023-01-02','Asda shoes'),(18,27,'2023-01-05','Overtin')
select * from #Sales where Dates = @date
drop table #Sales

The second one can be the same as above, but instead of select * from #Sales, you go select max(Dates) as default_date from #Sales ( and no where clause)

Now your report has a parameter of Dates. Set the default parameter for this to be default_date from your second dataset. You can hide the parameter if you do not want the user to be able to select other dates.

Answer 2

Harry's solution is fine and gives you more flexibility by using parameters, but if that's not required then it would be more efficient to do this in the dataset query, it will reduce the data returned to the report to only the records you need rather than all records which are then filtered by the report itself.

So just change the SELECT statement to this

SELECT * FROM #Sales WHERE [dates] = (SELECT MAX([Dates]) FROM #Sales)