Building Reports in SSRS, the Optimus Way

SSRS Reports are well integrated with the Microsoft stack.

You may have noticed our blog is all about building reports in SSRS these days. That’s because we’ve seen a big trend in the BI market towards the Microsoft stack. We have two major types of projects right now: creating new reports in SSRS and migrating reports from other systems into SSRS.

Below is our 6-step approach to building reports in SSRS from a project management perspective. We use a standard issue tracking system to monitor a report from start to finish. This enables us to track common issues and see exactly how long it took to create each report. After creating a few reports for a new system, we can then make fairly accurate estimates at the amount of time required per report.

Step 1: Define Project Scope

The first step in a reporting project is to define the scope. To do this, we work with our clients to identify which systems will need reports developed and then place the reports in priority sequence. This helps to plan timelines. For each report we will establish a report owner from the client’s team. They will be responsible for the final sign-off of the report. In addition, they are extremely helpful when designing and testing the report. By working closely with the report owner, we are able to seek out creative ways to make the report more efficient, user friendly, and functional.

Pro tip: Group the reports by modules so that business analysts and developers can learn new areas of the software one at a time. For example, learn the AP module and develop all the AP reports together. It will greatly increase efficiency.

Step 2: Requirements gathering

After the scope has been defined, business analysts can begin the requirements gathering phase. We will typically gather the requirements for one report and push it into development while we gather requirements for the next report. This way we can keep the whole team busy at all times instead of doing each phase sequentially.

During requirements gathering, we create documentation that accompanies each report. This documentation contains everything the report owner, developer, and future support staff need to know about the report. Details such as purpose, design, data mapping (where each field is located on the front end and where it is in the database), sorting, grouping, security, performance and equally important, where the actual files will be located. In complicated architectures, it’s important to note where the report will be accessed and where the requirements document will be stored.

Six months after delivering the report, a change request may come in. If the business analyst and developer can open up the old requirements document and readily access all required information, it will make the changes much easier.

Once the requirement document is complete, it’s important to review the document with the report owner (and even a developer) to make sure the document is complete and assumptions are correct. The more mistakes or possible improvements you catch early on, the better.

Pro tip: After gathering the requirements for the first report. Send it to the developers. We like to take the first report end-to-end to make sure all our assumptions are correct. For example, on the one hand, deploying a report to SharePoint is relatively straight forward but you still want to test it out to make sure users have proper access. On the other hand, deploying a report to be integrated inside of Microsoft Dynamics AX is more complicated, so taking the first report end-to-end will give you confidence your deployment method will work and ensure you have time to correct it if it doesn’t.

Step 3: Report Development

After the requirement document has been reviewed, it’s time to pass it over to development (and testing!). The trick here is that the requirements document should be so well made, that the developer can create the entire report without asking any questions. If common questions are coming back from development, that indicates the requirements document is not clear. This process is much better than having a meeting with the developer to start each report because the document must contain all information about the report. If some small detail is explained in person and left out of the document, future developers will be lost.

For development, we always create a standard template to build reports off. Prior to starting the first report, we build 2-3 templates and work with the client to choose appropriately. Once a template has been chosen, that becomes the starting point for each report. The template will include details such as color schemes, naming conventions, report titles, source of data, date printed, created by which user, etc. Creating a standard template before the first report is important because going back to change all the reports is a big waste of time. We work closely with clients to ensure the templates are top notch.

During development there are a few major tasks going on: writing the query, designing the report file, and clarifying details. Ideally, all three of these tasks can be completed without too much back and forth with the business analyst.

Pro tip: Be sure to use shared data sets. By pulling the data sets out of the report, you can access them in other reports. This is extremely useful if multiple reports need the same data. For example, if many of the reports require a list of vendors as a parameter, create a shared data set that gathers that list. Then, if you ever need to change that query, you can change it once and all reports that use it will be updated accordingly. This will also enable you to pull data from multiple sources into one report.

Step 4: Testing

We offer software testing services in Vancouver so we take testing very seriously. Report testing is crucial. We’ve learned some tough lessons about thorough report testing throughout our projects. Just like software development, catching mistakes early on (and testing assumptions!) is crucial to staying on budget. When we pass our requirements document to development, we pass it to our testing team at the same time. Both teams independently review the requirements document. Development starts creating the report while testing starts creating the test cases. Any confusion is taken up with the business analyst and brought back to the client’s report owner.

We have found that everyone involved in the report development process plays an important role in catching errors. The report owners will make a lot of assumptions based on their industry experience. The business analyst will take an unbiased look at the application and database to validate those assumptions. Then the developers and testers review and validate once again. At each level, assumptions need validation. This can be particularly challenging when it’s a new system and even the client isn’t 100% on how rules will be applied.

When testing, it’s important to test formatting, printing, export options, parameters, and most importantly, data validation. Validating the data can get very complicated, especially for complex accounting reports. It is best to design repeatable test cases and run through them carefully. The report owners can be very helpful during this phase, especially if they’re already intimately familiar with the system. Also, be sure to test the report in the actual system it will be deployed to. If the report will be deployed to SharePoint, test the report in SharePoint. That way any quirks can be identified early.

Pro tip: Don’t only validate the data that is there, look for the data that is missing. Join conditions and inconsistencies in the database can lead to missing data. It’s important that the test cases cover as many rare situations as possible.

Step 5: Sign-off and Moving to Production

As I mentioned above, it’s best if you take the first few reports and test your deployment plan. Even if it’s before go-live, we like to move reports into Production so that we can make sure the migration/deployment is smooth. Doing so enables us to note any steps in the process.

Pro tip: Don’t think your done at this point! Change requests will come in as soon as users start regularly using the reports.

Step 6: Managing Change Requests

Now that the reports are in production and users are running them regularly, change requests are inevitable. Change requests are either due to bug, change in requirements, or potential improvements. They should be embraced, but managed carefully. A change request will kick the whole report development cycle back up: requirements gathering, development, testing, deployment. So it’s important to consolidate requests as best you can. If a few low priority requests come in, wait a few days/weeks until you’ve gathered enough feedback. Then before moving to development, be sure to review the changes with the report owners and users and ask if there is anything else that should be changed before moving to development. This is the best time to make changes. And press the users because often they will say everything is ok, but then provide feedback after you’ve already started testing the new version.

However, when bugs or high priority change requests come in, we handle them right away. We always plan our resources to be prepared after a batch of reports have gone into production so that we can handle important change requests quickly. Business rely on their reports, so they better be accurate and available.

Pro tip: Make a mock-up of the change request and show it to users. Once they have the report in front of them, it is easier to provide feedback and insight into other improvements. It also works to ensure that the request was properly interpreted.

Conclusion

As evidenced by the above, we are passionate about SSRS report development. If you’d like to learn more, check out our “Thoughts from a Business Analytics Vendor in Vancouver” or contact us to setup a brief introductory meeting.