question

simam-9471 avatar image
0 Votes"
simam-9471 asked VJ-5333 edited

SSRS Filter in table

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"

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi @simam-9471
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VJ-5333 avatar image
0 Votes"
VJ-5333 answered VJ-5333 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.