SSRS report getting different results when run from different machines.

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything

SSRS report getting different results when run from different machines.

I have developed a report in SSRS which queries a teradata database.

 

The report has different values for SOME fields when I run it from the Visual Studio 2017 development environment using preview and after I import it to our production SSRS website.  I have used report builder - query builder to determine that the results of the terdata query are different in the two cases.

 

The query produces summarized values grouped by time period and member type.  Each record is one member type for a usually 30 day period.  The dollar amounts and counts which come from SUM() calculations are the same in bot cases.  Two values which come from Count(Distinct <string>) in the same summarized records are different.  The strings are made up of multiple fields and concatenated together (member number - date [27 chars]) in one case to get days shopped and (member number - date - store number - hour[34 chars]) in the other case to get number of trips.

 

On the local machine (VS 2017) running the query in teradata SQL assistant gets the same (I believe the correct) results as the SSRS development environment.

 

Since the connect strings are the same, on both machines, I am at a loss as to what to investigate next.  The cross vendor nature of Microsoft SSRS to Teradata database makes me hesitate to contact either of the vendors.

 

Has any one had a similar problem, or have an idea what is happening?


Accepted Solutions
Teradata Employee

Re: SSRS report getting different results when run from different machines.

Sounds like the Production connection is using a different transaction mode (likely ANSI mode) than Development or SQLA (likely Teradata mode).

 

For example, if the concatenation includes literals (field delimiters?), literals are CASESPECIFIC by default in ANSI mode but NOT CASESPECIFIC in Teradata mode (and if any "term" in a string expression is CASESPECIFIC then the result is also).

 

The default format for converting DATE (in particular) to VARCHAR may also differ.

 

You can also write SQL that will function the same in both modes, by being explicit, for example:

  • CAST(expr-or-literal AS NOT CASESPECIFIC) or just expr-or-literal (NOT CS)
  • Explicitly doing any necessary type conversions, such as converting dates to VARCHAR using CAST with a specific FORMAT, TO_CHAR, etc..
1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: SSRS report getting different results when run from different machines.

Sounds like the Production connection is using a different transaction mode (likely ANSI mode) than Development or SQLA (likely Teradata mode).

 

For example, if the concatenation includes literals (field delimiters?), literals are CASESPECIFIC by default in ANSI mode but NOT CASESPECIFIC in Teradata mode (and if any "term" in a string expression is CASESPECIFIC then the result is also).

 

The default format for converting DATE (in particular) to VARCHAR may also differ.

 

You can also write SQL that will function the same in both modes, by being explicit, for example:

  • CAST(expr-or-literal AS NOT CASESPECIFIC) or just expr-or-literal (NOT CS)
  • Explicitly doing any necessary type conversions, such as converting dates to VARCHAR using CAST with a specific FORMAT, TO_CHAR, etc..

Re: SSRS report getting different results when run from different machines.

Thank you!!!

Explicity casting the date within the Count(Distinct resolved the issue.