Search This Blog

Tuesday, October 20, 2015

Too Big To Be Visible - SQL Server vs. Oracle data in SSRS reporting

[2015-Oct-20]  A sourcing system failed to return correct results (returned value was a very high figure with double digit number after "E"). An existent ETL process was capable to save this value into an Oracle staging database, however previously working SSRS report showed me a blank column with no data instead.

I knew where the problem was, so I decided to explore this SSRS limitation in a attempt of showing very big numbers.


First, I took a sample query from the AdventureWorksDW database and added an extra row  to query a very big number that SQL Server can handle:






Then I "migrated" this data set into Oracle environment with a small adjustment to support very big numbers that Oracle can handle too:





and then I created a SSRS report with both SQL Server and Oracle data sets. What happened next was a surprise for me; the Oracle "Fairy Land" value didn't show up in my report:





So after playing with allowed data limits for my Oracle data set, I came to a range that SSRS actually could show in the report: it only allowed me to show Oracle data values when a big number was within (-7E-28 .. +7E+28), I may be wrong in my assumptions, but somehow Oracle client was not returning data out of this range for my SSRS report:





and only after changing my Oracle "Fairy Land" data, SSRS finally showed me expected results. So this was my finding, that SQL Server data will be shown as it is in a SSRS report, however Oracle data connection has some limitations.




Happy data adventures!