Monday, November 25, 2013

Tips for working with Report Server (SSRS)

Below are some tips for working with SQL Server Reporting Services. The tips are absolutely necessary as almost every step, in case the report has any complexity, is slow and painful, and often unintuitive.
  • reporting services - How to get rid of blank pages in PDF exported from SSRS - Stack Overflow -set ConsumeContainerWhitespace to True
  • How do I best display CheckBoxes in SQL Server Reporting Services? (link) - use Webdings 2 font; or HTML source
  • Working with Dates stored as UTC - convert using SQL CLR UDF (link); access timezoneinfo from SQL (link);
  • Related links: TZI in SQL CLR (link), Issue with NodaTime in CLR (link), second (link), Enabling CLR in SQL Server (link), Create CLR Functions (link), Create and run CLR function (link); download SSDT (link); set database Trustworthy (link)
  • Another way of converting Dates is to convert them at the Report level. This leads to a different set of problems, like inability to use deployment from Visual Studio. Add code to a report (link), deploying assemblies (link) and one more (link), Using System.Core: Resolve issues with System.Core version (link). After adding a reference to System.Core the problems arise. Version 4 can not be used, hence a reference must be changed to This breaks Deployment from Visual Studio. Manual upload will work and the report will run.
  • Setting visibility of a textbox
=IIF(First(Fields!Street2.Value, "AddressDataSet") is nothing or
First(Fields!Street2.Value, "AddressDataSet") = "", true, false)
  • Format date/time (link). Use =Format(value, "dd/MM/yyyy HH:mm").
  • Pass a parameter in URL (link). Simply add another key/value pair, such as "&key=value".
  • Rendering HTML (link
  • Set OverwriteDataSources property for objects at the Project level.

No comments: