Share via

datepart parameter is not accepting multiple parameters in ssrs

Newtojob 66 Reputation points
2022-03-11T03:07:53.757+00:00

Hi,

i have a ssrs report which accepts month, year from user which are varchar values and i convert them into date in sql query and i use DATEFROMPARTS function to get the date formate.

it is filtering the report if only one values is passed but fails if multiple values selected for month or year.

sql used to convert is:

declare

@uyearmon date
select @yearmon =datefromparts(@uyear,@umon,1)

select * from table
where datecolumn in (@uyearmon)

please let me know if any additional info required.

Thank you

SQL Server Reporting Services
SQL Server Reporting Services

A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.


4 answers

Sort by: Most helpful
  1. Newtojob 66 Reputation points
    2022-03-14T15:16:26.687+00:00

    we are using sql server 2016 but DB compatibility is 120 .
    STRING_SPLIT is available for the versions above 130 compatibility levels.

    Thank you

    Was this answer helpful?

    0 comments No comments

  2. Olaf Helper 47,621 Reputation points
    2022-03-14T10:14:13.5+00:00

    Which SQL Server version are you using? Since 2016 you can use the STRING_SPLIT function to convert the CSV to a table result:
    https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

    Was this answer helpful?

    0 comments No comments

  3. Newtojob 66 Reputation points
    2022-03-11T12:54:25.613+00:00

    Thank you OlafHelper-2800 ,

    These are the values selected by user in the report parameters, can you please provide details how to handle this.

    Was this answer helpful?

    0 comments No comments

  4. Olaf Helper 47,621 Reputation points
    2022-03-11T08:42:03.247+00:00

    fails if multiple values selected for month or year.

    Multiple parameter values are passed as comma separated list (CSV) to the SQL query; your query can't handle CSV, only one scalar value and so it fails.

    Was this answer helpful?

    0 comments No comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.