Applying filters on certain column in the tablix

Question

question

NimishaVernekar-9719 asked May 3, '22 IsabellaZhangMSFT-7746 edited May 4, '22

Applying filters on certain column in the tablix

I have a sales table that has columns : sales for present year, sales for past year, total sales of past year
Rows of the tablix have some groupings according to the type of product.

I have used FromDate and ToDate as the parameters to add filters on the tablix data which will be displayed. For example, I want data from January 2021 to Augut 2021, so I can enter these values (202101 to 202108) and the report will be filtered accordingly. Till here, everything works fine.

My requirement is that third column "Total sales of past year" should always display values from January to December of a particular year, which means the whole year. In our example, the column should show data for the whole year 2020. How can I make this happen? I know I need to use expression for this but I am not sure which function will help me.

sql-server-reporting-services msc-operations-manager

5 |1600 characters needed characters left characters exceeded

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

Answer 1 · 2022-05-04T02:16:27.16Z

IsabellaZhangMSFT-7746 answered May 4, '22 IsabellaZhangMSFT-7746 edited May 4, '22

Hi @NimishaVernekar-9719
I did a local test，you can take a reference,my approach is to filter out the data not in the fit month

Test：January to December

January to October

Steps:
My test table is like this:

Create parameters: pastyearstartmonth AND pastyearendmonth

Dateset :

 ;with cte as(select *,yearpart= DATEPART(yy,years),monthpart = DATEPART(mm,years) from tableproduct)
 SELECT * FROM cte WHERE yearpart = (SELECT currentyear = datepart(YY,GETDATE())) AND monthpart BETWEEN (@pastyearstartmonth) AND  (@pastyearendmonth)
 UNION ALL
 SELECT * FROM cte WHERE yearpart = (SELECT currentyear = datepart(YY,GETDATE())-1) AND monthpart BETWEEN (@pastyearstartmonth) AND  (@pastyearendmonth)
 UNION ALL
 SELECT * FROM cte WHERE yearpart = (SELECT currentyear = datepart(YY,GETDATE())-2)

Report is like this:

Hope this will help you.

Best Regards,
Isabella

image.png (6.5 KiB)

image.png (7.4 KiB)

image.png (20.6 KiB)

image.png (13.6 KiB)

image.png (12.5 KiB)

image.png (3.5 KiB)

5 |1600 characters needed characters left characters exceeded

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

Answer 2 · 2022-05-04T01:25:33.947Z

Joyzhao-MSFT answered May 4, '22 Joyzhao-MSFT edited May 4, '22

Hi @NimishaVernekar-9719 ,
I think you need to take into account here how to always show sales for a specific year in the third column after selecting the parameter. You should let us know what fields are in your report and some test data with the same structure as the original table. If possible, Please also attach screenshots of your design report and your final desired effect (via excel or other forms).

The conditional functions commonly used in SSRS are IIF, Switch and Choose etc. See more: Decision Functions.
SSRS IIF, Switch and Choose Functions for Dynamic and Appealing Reports.
Best Regards,
Joy

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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

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

question