With Vision 6 we can finally use Microsoft SQL Reporting Services to create our own custom reports or even change existing ones. But modifying existing standard reports is not that easy as it seems in the first place.
The main reason for that is the following: I guess to provide the same functionality they had in version 5 where you can define groupings and columns and their widths they had to resort to some wicked stuff in the code. I had to find this out the hard way when I tried to modify an existing standard report. When I looked closer into the select statement in the report I saw that this was a dummy statement that is nothing but a placeholder.
I assume, Deltek does the following: each standard report is bound to a specific options dialog in the custom properties of the report. This options dialog then creates a SQL statement on the fly, based on the report options you configure in VISION. It then replaces the statement in the report on runtime. Based on the named tables and columns then the dialog code goes through the report and applies any additional formatting just before the report is processed.
This has some major implications when you want to modify a report. I ran into the following issues:
- You are not able to add additional columns to the select statement (except the invoice report) because the select statement is created on-the-fly.
- You get a runtime error every time you add a custom column to a table that VISION tries to modify at runtime based on the formatting options.
Problem 1 is a tougher one. You actually have to create custom code in the report that connects back to the server and looks up additional data for each record of your report. I will have some details on that on my next blog entry
Problem 2 is easier to solve if the data is already supplied by the standard report and you just want to make the data visible. The trick here: you cannot modify the original table because you would run into that runtime error. So you do the following:
- Copy the original table and rename it
- Hide the original table (don’t delete it because VISION will still try to modify it)
- rename the copy and add or remove any column
What this does is the following: it connects to the same dataset as the original table so you can access the on-the-fly data select that VISION sent in. But because it will only try to alter the layout of the original table you don’t run into any errors. Biggest draw-back: any formatting you specify in the options dialog will have no effect to your table copy so you will have to do that manually and hard-coded.
After you did your modifications, you can upload the report either as custom report or simply replace the standard report. The beauty of uploading it as a custom report: you still reference the original options dialog. So when you start the report you get the same options dialog as the original – just with some different behaviour.