Before starting I want to wish you all a happy labor day :)
Overview
Building a report on SQL Server Reporting Services (SSRS) is very easy and fast, and it has the most important features to create a very professional business reports that covers the needs. Also as we all know the most of the business requests require a lot of calculations, logic, and sometimes it needs to integrate or import from an external sources such as; XML files generated by a system or a very old system, etc…
Expressions
Its the ability to write a VB.NET code in the report body to add your logic to the report. The extensibility provided by the expressions is required for almost every single report, but using them are tricky.
Applying an expression is very easy, just click on the fx button beside the properties for any report object, such as textbox.
Expression button beside the value property for the textbox object
For more info about expression check this MSDN Post
So Whats This Post About?
Using the expressions directly in the report body is a solution for logic issues but in the other hand, it has a lot of issues;
- Its in VB.NET, I know its not a big issues but C# ROCKS!.
- Lose the ability to debug code, and that is a huge pain if you are writing a complex logic functions.
- Redundant code, because you cannot reuse the code written, you can define the function on the scope of the report, but this does not solve the redundancy between reports.
- Limits in implementing the OOP concepts, such as abstraction, encapsulation, inheritance and polymorphism.
- Ability to use static classes.
- Tracking the changes of code on a source control, because the changes on the expressions are saved in the body of the report which is XML based.
And that list can be longer.
The Solution
The solution is very simple. Its by creating a DLL that holds the required functions for the reporting services and use it as a reference in the report. with this we can solve all the issues in the above list.
Class Library Structure
I would recommend that each reporting project, should has in the opposite a Class Library project. and each report is represented as a class, and for the common functionality such as the helper classes for date, formatting, etc… can be in a folder or in a separate Class Library that can be used among the report projects.
A screenshot from a solution exp., for the Report Project and the Class Library Project.
Notice that the Products Managed Report, has a class in the opposite which handles all the local functions, and a there is a common static class which is the format helper.
Writing the Expressions
A quick example, is in the Employees table the salary of the employee is saved in hour basis, but in the report it needs to be an annual salary.
Not Managed Report Expression
Report Body Code Snippet
- =Fields!BaseRate.Value * 8 * 5 * 4 * 12
Managed Report Extension
Report Body Code Snippet
- =objProduct.CalculateAnuualSalary(Fields!BaseRate.Value)
Class Library Code Snippet
- /// <summary>
- /// Calculates the annual salary of an employee for a year
- /// </summary>
- /// <param name=”baseRate”>Employee base rate</param>
- /// <returns>Annual salary</returns>
- public decimal CalculateAnuualSalary(decimal baseRate)
- {
- return baseRate * 8 * 5 * 4 * 12;
- }
Expression In Report Body Using Static Function Code Snippet
- =GbSamples.ManagedRdl.Managers.Helpers.FormatHelper.FormatFooter(Globals!PageNumber, Globals!TotalPages)
Reference The Class Library
Reference the library by click on the report body -> Properties -> References. And browse the DLL, and create an instances from classes (Which needs).
Report properties, the reference and creating the instances.
<a href="https://github.com/ghalebbadran/ManagedRdls" target="_blank"> The code sample for this article is on Github.</a>
The post Design and Build an Organized SSRS Project appeared first on Ghaleb's Blog.
