question

NimishaVernekar-9719 avatar image
0 Votes"
NimishaVernekar-9719 asked josefdef edited

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-servicesmsc-operations-manager
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.

josefdef avatar image
0 Votes"
josefdef answered josefdef edited

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

198590-image.png
January to October

198627-image.png


Steps:
My test table is like this:
198635-image.png

Create parameters: pastyearstartmonth AND pastyearendmonth

198681-image.png
198691-image.png
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:

198665-image.png

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

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 @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

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