SSRS - The report definition has an invalid target namespace Error

This error normally occurred when you created a report on one SQL version and deploying/opening on another SQL version.

I've created my SSRS report on SQL 2016 and I'm trying to open it on SQL 2014 Report Builder. I'm getting below error.

The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' which cannot be upgraded.

The solution of this problem is, just open the report in XML format and changed the following lines.

<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition">

to 

<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">

 Just change 2016 version as shown above and remove below lines


Search "ReportParametersLayout" in the code and remove the whole block. This code is only for SQL 2016.

 <ReportParametersLayout>

    <GridLayoutDefinition>

      <NumberOfColumns>2</NumberOfColumns>

      <NumberOfRows>2</NumberOfRows>

      <CellDefinitions>

        <CellDefinition>

          <ColumnIndex>0</ColumnIndex>

          <RowIndex>0</RowIndex>

          <ParameterName>EndDate</ParameterName>

        </CellDefinition>

        <CellDefinition>

          <ColumnIndex>1</ColumnIndex>

          <RowIndex>0</RowIndex>

          <ParameterName>EntityId</ParameterName>

        </CellDefinition>

        <CellDefinition>

          <ColumnIndex>0</ColumnIndex>

          <RowIndex>1</RowIndex>

          <ParameterName>StartDate</ParameterName>

        </CellDefinition>

      </CellDefinitions>

    </GridLayoutDefinition>

  </ReportParametersLayout>

After then, you can see your report will open properly in SQL 2014

SSRS - Remove decimals If result is whole number

Remove decimals If value is whole number.

Example: If value is 28.00 then report should show 28 and If value is 28.25 then report should show 28.25

How we can achieve this.

Add expression in this report and add below lines.


=Replace(Round(Fields!HourPerWeek.Value,2),".00","")

or

=CDec(Replace(Fields!HoursPerWeek.Value,".00",""))