Monday, February 19, 2018

SSRS 2017 Tips: Solving Default Font Issue in SSDT 15.5 Preview

Impetus 

The impetus for writing this article came from a recent question which was asked in one of the forums.
This was regarding the creation of SSRS report using VS 2017 shell based SQLServer Data Tools. There was a post which indicated that preview tab of the SSDT 2017 causes some tablix data to disappear.
This article discusses on the root cause behind the issue and a workaround you shall use to avoid the issue.

Illustration

Consider the below sample report for this example which shows some addresses


Now lets try using this data inside a report.

The source query is below

select SUBSTRING(LTRIM(RTRIM(DestAddr4)),1,2) as Post,DestAddr1,DestAddr4,TransportTelNo from dbo.SvrDestinations
WHERE DestAddr4 is not null
and LEN(LTRIM(RTRIM(DestAddr4))) > 2
ORDER BY DestAddr4


Using this in a table will look like this


Now go to the preview tab to check the report preview and we can see this


Analysis


As you see from the screenshot, the data is missing in all the textboxes except the last one. This had us bewildered for a while and we started the analyzing the behavior to understand the root cause for this issue.
We tried exporting this to Excel and CSV and the screenshots are as below



The two screenshots showed that data was in fact available inside the tables which made it clear the issue was with the HTML viewer in the VS shell ( SSDT 15.5 Version)
Our next attempt was to use a matrix container instead of table container and see if the issue persists,
As per this, created a matrix container and added the same columns inside and the screenshot is as below


The original table and matrix is shown above as in the designer. The main table's visibility option is set to hide and matrix is set to visible.  The behavior would be as below


As you see, we were able to display the data properly inside matrix. One thing to note here is that all columns are included inside grouping columns in the matrix with no values in detail columns (you can see the mandatory detail column blank in the last screenshot. Its column visibility would be set to hide for it to not generate the blank column)
From the above trials, the major things to note are


  • The data is not visible while displayed inside a table
  • The data is visible inside a matrix only when its inside a group.
Taking these things to consideration the next step was to check the report code behind
As you're probably aware, the code behind can be viewed by right clicking the report file (rdl) from solution and choosing view code. The code behind would be in XML format.
The code for the report looks like this



Fortunately I had another instance of SSRS in my machine which was of version 2012. 
I compared code behind of a similar report to the above and found that in the current version we have an additional section on top which includes the font family information (highlighted in blue above).


I changed the font to Arial which was what my previous version of SSRS was using inside and tried running the report.
The report now looked like this

So as it turns out, the issue was with the default font family which was used inside SSDT.

Conclusion

As shown by the illustration above, SSDT 15.5 has an issue with preview mode not displaying full data correctly for tables and in matrix detailed rows. 
The issue seems to be caused due to the default font (Segoe UI) being applied by the HTML preview screen. Changing the font seems to fix the issue. So whenever you face similar issue, try tweaking the font by opening the code behind to ensure its not a font based issue before you look for the other causes.
Hopefully this article could save you couple of hours of effort in fixing the issue caused due to font setting in the future.

References

The sample RDL along with table creation script can be downloaded from the below link