Show SSRS report data of a grouped week or month based on the parameter

Question

I am trying to create an appropriate group that will be displayed in the SSRS report, which is controlled by the "weekly" or "monthly" parameter defined in SSRS (not an argument for sproc). For this, I use the following expression of the category "Category" for the field "Date" (format "2014-03-01" as an example):

 =IIF( Parameters!date_range.value="Weekly", DATEPART("week", Fields!Date.Value), DATEPART("month", Fields!Date.Value) ) 

This results in the following exception:

The expression "Value" for the Date field contains an error: The argument 'DateValue' cannot be converted to the type 'Date'. (RsRuntimeErrorInExpression). An error occurred while processing the report. (RsProcessingAborted)

Why?

Answer 1

The easiest way to achieve this is, first of all, to write your query, which pushes the results in this way.

SQL Server Query

 SELECT DATEPART(MONTH, Date_Column) AS [Monthly] ,DATEPART(WEEK, Date_Column) AS [Weekly] ,SUM(Some_Column) AS Total FROM Table_Name GROUP BY DATEPART(MONTH, Date_Column) ,DATEPART(WEEK, Date_Column) 

SSRS Report

Add a matrix data area. Drag the Total column to DATA .

Create a say GROUP ON of Text parameter and enter the values

 1) Weekly 2) Monthly 

Now below in the ROW GROUPS right-click only the visible row group and go to GROUP PROPERTIES In the GROUP ON section, enter the following expression.

 =IIF(Parameters!Groupby.Value = "Monthly", Fields!Monthly.Value, Fields!Weekly.Value) 

Use the same expression in the ROWS data ROWS .

For the column name, you can use the following expression ...

 =IIF(Parameters!Groupby.Value = "Monthly", "Monthly", "Weekly") 

and you are good to go.

Important Note

SSRS is a cool tool for presenting data, which is not so cool when it comes to data manipulation, to improve performance, all types of data manipulation are closer to the source (database, SQL Server).

All presentation materials must be processed in SSRS.

Answer 2

Try something like:

 Format(Fields!date.Value,"yyyy-MM-dd") 

Is this SQL Server 2014?

Reference In SQL Server 2014, DATEPART implicitly outputs string literals as a datetime2 type. This means that DATEPART does not support the YDM format when the date is transmitted as a string. You must explicitly pass the string to datetime or smalldatetime to use the YDM format.

Answer 3

So, following from @nshah's suggestion, convert the expression to this:

 =IIF( Parameters!date_range.value="Weekly", DATEPART("week", format(Fields!Date.Value,"yyyy-MM-dd")), DATEPART("month", format(Fields!Date.Value,"yyyy-MM-dd")) ) 

Answer 4

I recommend using DATEPART, but I found that DATEPART in SSRS does not always work as described in DATEPART (SSIS expression) . Here are a few examples with the specific syntax that worked for me:

• = DATEPART ("yyyy", Fields! Date.Value) for the year.
• = DATEPART ("m", Fields! Date.Value) for a month.
• = DATEPART ("ww", Fields! Request_Date.Value) for a week.