reporting-servicespowerbireportbuilderpower-bi-report-serverpowerbi-paginated-reports

Filter Report Builder to Max Date

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.

enter image description here

when i run it i got erroe message

enter image description here

My expected Output looks like this

enter image description here

Any idea how i can resolve the issue? Thanks

Solution

  • 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.