SSRS: Reporting Business Requirement Template

There comes a time in every developers career where you will receive change request after change request on what in the beginning seems like an “easy” report.  This can increase development time and create unnecessary tension and frustration between the end business user and the developer.  How about those times where you encounter scope creep?  Annoying right?

I have been put into a position to create business requirement documentation or a BRD for the reporting groups to help streamline the issues that we were facing in the above paragraph.

In general, I think the most important features are:

1. Report mock-up
This has been a LIFE SAVER!  Seriously….This should be the easiest piece for the business user slapping together the document.  Go into excel, create what you want the report to look like colors, layout, etc.  Paste it into the requirements document.  It alleviates any confusion for the developer on something as simple as the column headers or color schema.

2. Calculations
Believe it or not this has been a tough one to get the business user to understand.  Especially where I am now, they use different calculations for the same ‘meaning’ depending on the client.  This can become confusing and flat out make a data warehouse impossible to obtain…But on the flip side, having these all up front can help the developer determine if the SQL engine will be best utilized in the dataset or within the RDL itself.

3. Roles – Developers, Testing, Approvals
Seems like a no brainer, but you would not believe how many times I have had to go back to the requestor and ask who will be validating and testing their material.

I highly encourage you to take a look at the document I have attached.  It is the form I created and fine tune it to meet your organizations needs.

ss1 ss2 ss3 ss4
For a Requirements Document Template for an ETL Project

After enough trial and error from the best and worst clients, business analysts, executive sponsors, and my own shining and less-than-shining moments I have seen many developers confronted with poor requirements turn into … the DEV Nazi!!

The DEV Nazi

So to make sure that doesn’t happen to you, here’s a template for your reporting projects.

In scope for this article

  • A requirements doc template designed for business analysts to cover most reporting projects.
  • Witty advice

Out of scope for this article
(But maybe if you vote Yes and comment nicely at the bottom of this article I’ll write another article on it.)

  • Requirements Elicitation
  • Requirements documents specific to other types of projects, such as ETL and Data Warehousing
  • Report Design, especially the new minimalist style that’s gaining popularity

So here we go..

Version History
A ‘who changed what when’ chronology of all changes, either using Word change tracking or lines like ‘Bob’s changes in yellow highlight’.  Keeps everyone honest when there are lots of changes.

Purpose
Also known as project objective, business goals, business problem statement, and various other terms.
A simple ‘Here’s why we’re doing this’ paragraph.  The target audience being those that are likely to only read this paragraph, but this also gives the developer some design decision guidance.

In Scope / Out of Scope
Everybody LOVES this section!  Okay, developers LOVE this section.  In Scope is a summary of what’s in the requirements.  Out of Scope is usually a Top 10 list of things that are close but not in, and answers the often asked question ‘Are we also getting this too?’  This is a developer’s best line of defenseagainst scope creep by false or unstated expectations.

And yes, just because person x told person y a month ago that it’s in requirements, or this email two months ago said it’s in, or was mentioned on the golf course last year during preliminary negotiations means that it’s in.   I’ve also known more than a couple of clients that will negotiate effort, cost, and time, and then scope creep the hell out of a project in order to make themselves look better.  Been there, dealt with that.

grumpy-cat-out-of-scope.jpg
Schedule
A statement on when this can be completed, such as ‘When division X is in the data warehouse’, ‘When project ABC is completed’, ‘Beginning in FY 2013’, ‘Yesterday’, etc.  Helps project managers plan this project within a project management system that deals with all projects, all resources, and available time.

Report properties

  • Name – The name people will call it.
  • Description – 25 words or less.
  • Population – “All x’s by y for time period z-1 to z-2”.  Also note if report values are supposed to match any other report.
  • Business Owner(s) – The person that will approve the creation of the report, and likely any future changes.
  • Technical Owner(s) – The person that will build the report, and likely any future changes.
  • Is there a Service Level Agreement (SLA)? – This will dictate how much effort will be done to log report success/failure, does this warrant somebody getting a phone call at two in the morning to fix it, and how many resources should be thrown to break/fix it.
  • Style sheet / cosmetics to use – Usually in the form of ‘make this look like..’
  • Exact source of data if known, and if it matters.
  • Page count – Does it absolutely, positively have to fit on one page?
  • Does this report need to be historically reproducable? – If your data is frequently updated or deleted, re-running a report for a prior period may not result in the same exact report as when it was originally ran.
  • Availability – Once a day at 8am, 24/7, etc.
  • Accessibility – How will it be accessed?  Internally, externally, on a boat, with a goat…
  • User Security – Who can view the report.
  • Data Security – Does it contain Personal Health Information (PHI) or Personal Identity Information (PII) that would require protection?
  • Does anything get turned off once this report is placed in production?

Parameters

  • User supplied values that will change the population.  For each parameter
  • Name as it would appear on the screen
  • Format – Number, date, text, etc.  Also is it free-form text, or populated from a range of values?
  • Cascading – Are parameter 2 values dependent on what is selected in parameter 1?
  • Should an ‘All’ option be added?
  • Can users select multiple values?
  • User security – Does any of the above require users to have access to only a specific set of parameters, such as the Seattle boss can only run it for Seattle?
  • Time period – Will this report ever need to be run for a specific date or range of dates?

Header

  • Will usually be dictated by style.  Frequent header items are the company logo, formal name, report number if it exists, and any parameters that were used to generate the report.

Detail
For each section

  • How is it populated – Especially if sections are populated differently.
  • Drill down – Click or mouse over something and details either appear below it
  • Grouping, including header and footer info.
  • Sorting – Default sort order, which columns are sortable, and any special sort order other than 0-9 or A-Z.
  • Links to other reports – If there are multiple reports and links between them, a map is an excellent idea. Each ‘other report’ would require its own requirements doc.
  • Alternating background color – Makes the report easier to read.

For each column

  • Name
  • How populated – Single value, a defined calculation such as ‘Beginning inventory for the month + Purchases – Sales’

Fun fact:  Providing a PowerPoint presentation from some VP and saying ‘these numbers are just to illustrate the concept’ is not a good requirement for a calculated column. Trust me on this one.

  • Data Type – Numeric, Currency, Date, Text.  Also other formatting info such as decimal places, dollar sign, thousands separators, and negative number handling.
  • Borders – Around all cells, none, heavy borders on certain sections.
  • Conditional Formatting – Make the color of bad stuff red, good stuff green, etc.

Does any part of the report have to match other parts of the same report?  (i.e. Sum of Dollar Amount matches ‘Grand Total’ text box)

Footer

  • Will usually be dictated by style.  Frequent footer items include page number, date/time of generation, and a legal notice.

Execution
Automated Execution

  • Time period – Mmonthly, daily, immediately after another process completes
  • Delivery method – Email, Excel, etc.
  • Distribution List – Who gets the report?
  • Data Security – Does the report need to be encrypted/decrypted?

Manual Execution

  • Where does the user go to execute it?

Archival

  • Are reports archived – Where, and for how long…
  • If yes, is there a reason why – SOX, ISO 9000, CIA, Company Policy, etc.

Are requirements easily understood?
Any confusion in requirements is going to be defined differenty by different people, resulting in time and effort, and goodwill if that confusion is between you and the client.

unclear-business-requirements-or-scope-c
A big honkin’ list of other quantifiable things you may need to directly address
Availability, Capacity, Data Currency, Data Retention, Degradation, Deployability, Exception handling, Extensibility (flexibility), Internationalization, Interoperability, Audit logs, Maintainability, Portability, Privacy, Recoverability, Reliability, Response time, Scalability, Security, Upgradeability, Usability.

NOT to be confused with these MBA Buzzword Bingo words, which may sound real impressive but have no quantifiable characteristics whatsoever:

World-class, best of class, best of breed, industry leading, empowerment, collaboration, repurpose, frictionless, client-focused, ecosystem, excellence, synergize, geo-targeted, diverse, environment, core competency…

dilbert-buzzword-bingo.jpg

And finally :: drum roll ::  Top 10 tips for writing requirements docs…

10   If requirements documents were easy, they’d offshore both the requirements and development for a third of what you cost, so don’t get bent out of shape when things are complicated.  It’s job security.
9      Never underestimate the awesome power of a :: blank stare :: to get people to provide you with better requirements
8      Sometimes ‘draw me a sketch’ is an excellent place to start requirements elicitation.
7      Never assume customers know exactly what they want.  Sometimes you have to guide them to the answer.
“If I had asked people what they wanted, they would have said faster horses.” – Henry Ford
6     Scope Creep – Changes in requirements after the initial ones are approved, but there cannot be an expectation that they will be immediately accepted.  They must be managed with budget, schedule, and resources.
5      Make sure the right stakeholders are defined, included in requirements elicitation, and accountable.
4      You are not an order taker!  (see ‘Faster Horses’ above).  This means you have to own your expertise and discover your customers needs together
3      Being a Business Analyst is its own career track, with its own training and certification.  The IIBA has plenty of resources.
2      Requirements documents should be signed by all customers.  In blood would be preferable, but in modern times ink is an acceptable alternative. … and the number one tip for writing requirements docs…
1      Any requirement that is missed, and caught in later stages of development, will cost WAY more money to fix, so make sure you get it all!!

Thank you for reading my article, please leave valuable feedback. If you liked this article and would like to see more, please click the Good Article button near the bottom of this article.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s