CODEWITHSUNDEEP
sqlsql-serverreporting-services
user2896521
user2896521

Reputation: 295

Show data on SSRS report grouped week or month based on parameter

I'm trying to achieve the appropriate grouping to be shown on SSRS report which is driven by the 'weekly' or 'monthly' parameter defined in SSRS (not a argument for sproc). For that I'm using following in the Category Groups expression for the field called "Date" (format is '2014-03-01' as example):

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

This results in the following exception:

The Value expression for the field ‘Date’ contains an error: Argument 'DateValue' cannot be converted to type 'Date'. (rsRuntimeErrorInExpression). An error has occurred during report processing. (rsProcessingAborted)

Why?

Upvotes: 7

Views: 30149

Answers (4)

user219454
user219454

Reputation: 81

I recommend using DATEPART, but I have discovered myself that DATEPART in SSRS does not always work as described in DATEPART (SSIS Expression). Here are some examples with specific syntax that worked for me:

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

Upvotes: 0

nshah
nshah

Reputation: 340

Try something like:

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

Is this SQL Server 2014?

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

Upvotes: 0

M.Ali
M.Ali

Reputation: 69554

The easiest way to achieve this is to first of all write your query which pulls forward the results like this.

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 region. Drag and drop Total column to DATA.

Create a Parameter say GROUP ON of Text type, and provide values 

1) Weekly
2) Monthly

Now below in ROW GROUPS pane, right click the only visible Row Group and goto GROUP PROPERTIES In GROUP ON section put following expression. 

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

Use the exactly same Expression on Data region ROWS section.

For 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 data presentation, not so cool when it comes to Data manipulation, to get better performance do all sorts of Data Manipulation closer to source (database, SQL Server).

All of the presentation stuff should be handled on SSRS.

Upvotes: 3

whytheq
whytheq

Reputation: 35587

So following from @nshah 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"))
)

Upvotes: 0

Related Questions