SSRS Report Date Parameter Error to Query SSAS Tabular Model - The syntax for '5' is incorrect

Question

The actual text of the error as visible in Visual Studio (2015 enterprise) is this.

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset 'ProductivityDS'.
The syntax for '5' is incorrect. (3/2/2017 5:00:00 AM)

It is querying a simple tabular model that I created and deployed. The model has four tables. An employee table, a job card table (contains job number, hours, etc.), a date dimension table, and a payroll time clock table. The goal is to report on hours that are billable against orders vs actual time in attendance for "direct" departments.

Tables are [Employee], [Labor Date] (date dim), [ADI Time Detail] (payroll), and [Route Trans] (job cards). Measures are [ADI Time Detail].[Direct Hours] and [Route Trans].[Sum of Earned Hours].

I don't usually use the query designer since I'm pretty good with SQL, but this is my first outing with SSAS so I used it here.

I brought in [Labor Date].[Date], [Employee].[Employee ID], [Employee].[Employee Name], [Measures].[Sum of Earned Hours], and [Measures].[Direct Hours]. I also added Dimension in the top. It was {"Dimension":"Labor Date", "Hierarchy":"Date", "Operator":"Equal", "Filter Expression":"{ 6/26/2013 }", "Parameters":"Checked"}.

Executing the query in the Query Designer it produces the dataset as expected.

Toggling over to Design mode the query text is this

SELECT NON EMPTY { [Measures].[SUM OF Earned Hours], 
       [Measures].[Direct Hours] } 
ON COLUMNS, 
       NON EMPTY { ([Labor DATE].[DATE].[DATE].allmembers * [Employee].[Employee ID].[Employee ID].allmembers * [Employee].[Employee Name].[Employee Name].allmembers ) } DIMENSION PROPERTIES member_caption, 
       member_unique_name 
ON ROWS 
FROM   ( 
              SELECT ( STRTOSET(@labordatedate, constrained) ) 
              ON COLUMNS 
              FROM   [Model]) CELL PROPERTIES VALUE, 
       back_color, 
       fore_color, 
       formatted_value, 
       format_string, 
       font_name, 
       font_size, 
       font_flags

Clicking OK to accept the query and then back in the Dataset properties I click Parameters to tie the Parameter named LaborDateDate (inside query) to [@PerDate] (report parameter).

The report parameter [@PerDate] is using this expression for its default value:

=DateAdd(DateInterval.Day, -1, Today.Date)

The query works in the query builder, but it fails in report execution. The dates in the date dimension and the parameter are both zero hour datetimes so I'm confused where the 5 AM is coming from.

What am I doing wrong here?

Solution After Answer Below

I left the [@PerDate] as-is. I created a second parameter, marked hidden, named [@PerDateStr] with a datatype of Text. I set the available and default expressions in it to ...

="[Labor Date].[Date].&[" & Format(Parameters!PerDate.Value, "yyyy-MM-dd") & "T00:00:00]"

Then in the dataset I mapped the internal query parameter LaborDateDate to the report parameter [@PerDateStr]. Success!

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-03-03T14:30:19

Use a valid member in your Labor Date dimension instead:

="[Labor Date].[Date].&[" & DateAdd(DateInterval.Day, -1, Today.Date) & "]"

Be sure you are formatting the date value according to your dimension values.

SSRS Report Date Parameter Error to Query SSAS Tabular Model - The syntax for '5' is incorrect

1 Answers