Solved: SSRS report: filter on month/year?

Create Account

Microsoft SQL Server

Questions

Followers

Top Experts

janhoedt

SSRS report: filter on month/year?

Hi,

I'd like to filter my existing SSRS report on creation date. Found the article below, but I am a bit lost on howto implement.
An example of my datetime format is 03/06/2020 10:17:46

Tried to change the query with this
select Cast('9/10/2013 8:19:29' as datetime) DateCreated
UNION
select Cast('10/10/2014 8:19:29' as datetime) DateCreated
UNION
select Cast('11/10/2015 8:19:29' as datetime) DateCreated
where Year(DateCreated)=@Year and Month(DateCreated)=@Month

Then I got the error:
Must declare the scalar variable "@Year".
----------------------------
Query execution failed for dataset 'MyDataSet'. (rsErrorExecutingCommand)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)

---source which I used
https://social.msdn.microsoft.com/Forums/en-US/afdaa9d9-e5fd-4a15-94e2-3a87b332c9b5/how-to-filter-the-ssrs-report-by-year-and-month?forum=sqlreportingservices

select Cast('9/10/2013 8:19:29 AM' as datetime) LogTime
UNION
select Cast('10/10/2014 8:19:29 AM' as datetime) LogTime
UNION
select Cast('11/10/2015 8:19:29 AM' as datetime) LogTime
where Year(LogTime)=@Year and Month(LogTime)=@Month

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

MacNuttin🇺🇸

U need:

Declare @Year as datetime

Declare @Month as datetime

Also:

To make life easier ask DBA if there is a calendar table and then join to that

janhoedt

ASKER

Thanks. Where do I decare this datetime? In the sql query of the dataset?
Tried that at then end but that gives an error.

MacNuttin🇺🇸

It has to be first line in the query. Just think: it has to be declared before you use it right.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

janhoedt

ASKER

Thanks.
This is my query. I get 'Invalid column name 'DateCreated'

Declare @Year as datetime
Declare @Month as datetime
SELECT
...
,Mytable.DateCreated
FROM
MyTable
select Cast('9/10/2013 8:19:29' as datetime) DateCreated
UNION
select Cast('10/10/2014 8:19:29' as datetime) DateCreated
UNION
select Cast('11/10/2015 8:19:29' as datetime) DateCreated
where Year(DateCreated)=@Year and Month(DateCreated)=@Month

MacNuttin🇺🇸

You can't use DateCreated as an alias and a column name both drop datecreated from this subquery:
SELECT
...
,Mytable.DateCreated
FROM
MyTable

and show me the rest of this query zo I can troubleshoot it

janhoedt

ASKER

Thanks, but I tried it a different way now, as described https://social.msdn.microsoft.com/Forums/en-US/afdaa9d9-e5fd-4a15-94e2-3a87b332c9b5/how-to-filter-the-ssrs-report-by-year-and-month?forum=sqlreportingservices

Got a step closer but now have a data type issue:
The processing of FilterExpression for the dataset ‘Mytable’ cannot be performed. Cannot compare data of types System.Int32 and System.Object[]. Please check the data type returned by the FilterExpression. (rsComparisonTypeError)
----------------------------

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

janhoedt

ASKER

Figured it out, was a typo.
One more thing, I have a KPI on top of the report which shows total values

KPI1= CountRows("tablix01")
KPI2 = CountRows("tablix02")
KPI3 = CountRows("tablix03")

It shows the correct values but creates an extra row each time(!) Please advise on that.

ASKER CERTIFIED SOLUTION

MacNuttin🇺🇸

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

janhoedt

ASKER

I had to use tablix instead of table to fix the duplicate rows.

Microsoft SQL Server

Questions

Followers

Top Experts

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.