First and last record data turnout different when SQL runs in SSRS report

Question

Post reply

First and last record data turnout different when SQL runs in SSRS report

cshquah

SSC Veteran

Points: 221

June 14, 2017 at 4:30 am

#337561

Hi all,

I wrote a SQL like below to capture the first and last card printedcode of a list in a certain batch, base on the sorting of part of the card’s chipserial number. All data generated by the query in the SQLdeveloper tool arecorrect, but when place into the SSRS report the data first and last card printedcode turns out different.

Example SQL query:

SELECT Production_DETAIL.CHIPSN, SUBSTR(Production_DETAIL.CHIPSN,5,8) AS PartialCHIPSN, Production_DETAIL.CardCodePrint, Prodution.BatchNumber AS Batch_Number, Product_NAME, FIRST_VALUE (Production_DETAIL.CardCodePrint) OVER (PARTITION BY Prodution.BatchNumber ORDER BY SUBSTR(Production_DETAIL.CHIPSN,5,8)) AS FIRST_CardCodePrint, LAST_VALUE (Production_DETAIL.CardCodePrint) OVER (PARTITION BY Prodution.BatchNumber ORDER BY SUBSTR(Production_DETAIL.CHIPSN,5,8) RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS LAST_CardCodePrint, FROM Prodution, Production_DETAIL WHERE (Prodution.ID=Production_DETAIL.ID(+)) AND Prodution.Location_NAME = :LocationName AND Prodution.Product_NAME in (:ProductName) AND(Prodution.BatchNumber BETWEEN :BatchNumber1 AND :BatchNumber2)

The command DENSE_RANKFIRST and DENSE_RANK LAST alsocan be use in place of FIRST_VALUEand LAST_VALUE. e.i.

MIN(Production_DETAIL.CardCodePrint) KEEP (DENSE_RANK FIRST ORDER BY SUBSTR(Production_DETAIL.CHIPSN,5,8) ASC ) OVER (PARTITION BY Prodution.BatchNumber ) AS FIRST_CardCodePrint, MAX(Production_DETAIL.CardCodePrint) KEEP (DENSE_RANK LAST ORDER BY SUBSTR(Production_DETAIL.CHIPSN,5,8) ASC ) OVER (PARTITION BY Prodution.BatchNumber ) AS LAST_CardCodePrint,

For the SSRS report side, below is the table Idesigned…

Row Group: Parent Group

Title: XXXXX XXXXX XXXXXX

First Card print Code : [FIRST_CardCodePrint]

Last Card print Code : [LAST_CardCodePrint]

Batch Name : [Batch_Number]

Row Group: Group Details

[CHIPSN]

[CardCodePrint]

Parent Group properties:

1. General -> group on: Batch_Number
2. Sorting -> sort by Batch_Number; then by PartialCHIPSN

Detail Group properties:

1. Sorting -> sort by Batch_Number; then by PartialCHIPSN

Results, the First and last card print code in the tableheaders section (Parent Group) do not match what is listed

Title: XXXXX XXXXX XXXXXX

First Card print Code : ABS0010

Last Card print Code : ABC0059

Batch Name : BATHC001

567810000001

ABS0025

477810000002

ABC0030

…

605810001000

ABS0012

Title: XXXXX XXXXX XXXXXX

First Card print Code : AAS0111

Last Card print Code : AAA0073

Batch Name : BATHC002

967810002501

AAA0027

687810002511

ABS0065

…

725810009200

ABB0102

My understanding is that SQL query processing data isindependent of SSRS report format (only base on parameters value).

I don’t understand why the SRSS report results the incorrectFirst and Last sorted / listed data being obtain?

Albert

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

How did you determine it's SSRS and not an issue with the provider/extension? You wouldn't want to compare with SQL Developer as it goes through a different data access process.
When you select the Dataset, query and then run the statement what are the results?
And did you try using ANSI joins instead of old style joins to see if that makes a difference?

Sue

cshquah SSC Veteran Points: 221 More actions · Answer 2

Hi Sue,
Its an SSRS area matter as I had testing it out even with a simple table of listing out all the values in a report, the report still give the wrong First & Last values.
I never thought "different data access process" has impact on the listed data. An SQL query is a command that is to be process by the database server, so I'm not sure how SSRS side has extra / add-on or overriding points to the SQL query in the database server. Enlighten me if you can?
Albert

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Albert

There are several different settings that can very depending on which user runs the query, or what provider you use to connect to the database and hence what's in the connection string. One example is default language, which can affect the value of DATEFIRST and the way dates are interpreted. ANSI NULLs is another example of a setting that can produce different results, and there's ANSI all sorts of other things as well.

John