SSRS Filter in table

Question

My data source have multiple years. I want to show latest year of data in one of the table in SSRS report.

When I tried to filter the table with First(field!year.value,"DataSource")

I am getting error message "Aggregate functions cannot be used in dataset filters or data region filters"

Answer 1

Hi @s imam ，
Why not query the usage details of the data source in the database?
1） The way I can think of is to query the creation and modification of the data source through TSQL:

USE [ReportServer]  
GO  
  
SELECT CATALOG.NAME  
    ,CATALOG.[Path]  
    ,DataSource.NAME datasource  
    ,CATALOG.[Description]  
    ,Created.UserName AS CreatedByUser  
    ,CATALOG.[CreationDate]  
    ,Modified.UserName AS ModifiedByUser  
    ,CATALOG.[ModifiedDate]  
FROM [dbo].[Catalog]  
LEFT JOIN (  
    SELECT [UserID]  
        ,[UserName]  
    FROM [dbo].[Users]  
    ) AS Created ON CATALOG.CreatedByID = Created.UserID  
LEFT JOIN (  
    SELECT [UserID]  
        ,[UserName]  
    FROM [dbo].[Users]  
    ) AS Modified ON CATALOG.ModifiedByID = Modified.UserID  
JOIN DataSource ON CATALOG.ItemID = DataSource.ItemID  
JOIN CATALOG cat1 ON DataSource.Link = cat1.ItemID  
WHERE CATALOG.[Type] = 2   
ORDER BY [Path]  
    ,NAME  

2.）Select the data source within one year by setting parameters in SSRS:
I am not sure whether I can directly filter out the data sources within a year in SSMS, because I am not good at using TSQL.

Hope this helps. If I misunderstand what you mean,please feel free to correct me.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello,

If you are looking for filtering at tablix label only then you can put filter column grouping of your Year Column. Refer the below URL for Reference:

http://www.sqlcircuit.com/2013/09/ssrs-how-to-filter-report-data-at.html

Thanks,
VJ

****f the answer is helpful, please click "Accept Answer" and upvote it****