In this solution for professional services, we focused mainly on solving the most important business need, which was tracking the resource utilization. Other areas that we would need to add in future iterations would probably include tracking customer satisfaction, which is one of the most important metrics because it is a great predictor of likely future revenue. The business operations side of professional services also needs information on cost factors such as travel and expenses to be able to manage the business successfully. These measures would all prove useful for analytical purposes, too (for example, tracking trends and breaking down costs), so an Analysis Services cube is another likely addition to the solution.
Adding Code to Reports Using .NET
In our solution, we managed to implement all the required calculations using a combination of SQL and Reporting Services expressions. Some reports will need more than that; for example, your reports may require complex mathematical or financial calculations. You can achieve this in Reporting Services by adding a reference to a .NET assembly containing your code, and then call this assembly from report expressions. Also, if you need to reuse the same code fragment a few times in a report, you can add custom VB.NET functions to the report that can be called from expressions. See "Using Custom Assemblies with Reports" in Books Online for more information.
Using Report Builder for End-User Reporting
Many of the users of the information in the data warehouse will be "power users" who would benefit from the capability to create their own queries and reports. Report Builder is a simple, Microsoft Office-like report design tool that allows users to connect to a user-friendly view of the data called a report model. You can design a report model in BI Development Studio using the Report Model project type, using either the relational data warehouse or an Analysis Services database as the source.
One possible approach for the professional services solution is to create an Analysis Services database that could be accessed by the user's client tool of choice, and then create a report model from this Analysis Services database for those users who want to use Report Builder to create custom reports. You can easily generate a report model by creating a new reporting folder using Report Manager or SQL Server Management Studio, adding a data source that points to the Analysis Services cube, and then clicking the Generate Model button or context menu item on the data source. This creates a new report model in that reporting folder, and users can immediately launch Report Builder using the button on the Report Manager toolbar.
Supporting Multiple Languages in a Report
One area that will be important in many solutions is localization, or providing the same report in multiple languages. This area involves a few complexities, such as whether just report captions need to be language specific, or data such as product names also needs to be localized. One simple solution to this problem is just to rewrite the report for different languages and provide the correct reports to different audiences. This may not work well where you have a large number of reports that often need to be modified, because you will be maintaining more than one copy.
When a user views a report, the browser passes the user's preferred language to the server; this can be accessed in a report expression using the User!Language syntax. You can localize data and captions by using this language as a parameter to either SQL queries or a .NET assembly referenced in the report. Another powerful technique often used to localize .NET applications is using resource files. Because Reporting Services will correctly set the language of the thread used to process a report, you can build .NET assemblies that use the ResourceManager class to return the appropriate string for a specified key. See the MSDN documentation on localizing applications for more information.