A consistently asked question when using Code On Time (COT) is how to develop full fidelity reports. The folks over at COT have enabled customers to integrate Microsoft .RDLC files (Report Definition Language - Client) with COT controllers. The idea is that you can use the Visual Studio Report Designer (VSRD) to design an RDLC report that is based on one of the COT Data Access Objects (DAO) produced in your COT project when you generate it. You can then hook this report into the associated COT controller which is also using the same DAO to produce fully formatted reports as PDF, MS Word, or Excel format(s). On paper, this looks like a pretty straightforward process UNTIL you actually try to design a report using the VSRD. This tool has a reputation for being cumbersome/onerous/unwieldy to use - and rightly so!
As a result, one is faced with the frustrating task of muddling their way through using this tool or they must find a substitute reporting tool which also produces RDLC files. While there are third-party reporting components available which allow you to work with RDL/RDLC files (both types use the same XML schema), my guess is that you'd rather not run out and buy another product after having just made an investment in COT. And if that is the case, then you're in luck...
Luckily, I am a Microsoft Access junkie from back in the day. I know my way around the MS Access Report Designer pretty well and the experience between using it versus using the VSRD is like night and day! So with that in mind and the stage set, let me walk you through my sanity-saving, no-cost (if you already have MS Access and SQL Server) alternative to writing COT-compatible reports:
- Use the same exact SQL that the related COT controller XML file uses and create a SQL Server view out of it;
- Use MS Access to develop the report (based on the view created in the previous step) due to its superior report designer;
- Create a SQL Server Business Intelligence Development Studio project and import the Access report by right-clicking on the Reports folder created in the project solution and selecting the Access DB you created the report in (this will save a new copy of the report as a .RDL file);
- Use File Explorer to find the .RDL file created - copy it to the COT website and place it in the Reports folder, changing the extension from .RDL to .RDLC;
- Open the report in the VSRD and tweak any design elements needed. Also, you must pick the appropriate data source (generally the first SELECT object associated with the controller you are working with) from the listing of data objects shown in the project;
- Change the name of the dataset to exactly match the name of the related controller;
- Add a "FilterDetails" parameter to the report and then add the related field to the header of the report so that users can tell if any filters have been applied to the data shown;
- Hook the report into the controller file XML by adding a new "action" to the "actionGroup" with the headerText = "Report"*. For instance: <action id="a5" commandName="ReportAsPdf" commandArgument="AssetsReport.rdlc" headerText="Formatted Assets Report" description="View Formatted PDF Assets Report." />
- Test in a browser and make any changes needed to formatting, etc. within the VS IDE.
While there is, admittedly, some trickiness involved with this solution I think you'll come to realize that this methodology is an order of magnitude easier than just trying to use the VSRD by itself! :^)
Please feel free to contact me if you need any help implementing your COT reports - I'd be happy to help.
* If you decide that you want to use the COT GUI versus editing the raw XML, YMMV on Step 8...