four phases of report authoring targeted for executives and upper management by: ben aminnia...

Four Phases of Report AuthoringFour Phases of Report Authoring

Targeted for Executives and Upper ManagementTargeted for Executives and Upper Management

By: By: Ben AminniaBen Aminnia

President, L.A. SQL Server Professionals Group President, L.A. SQL Server Professionals Group www.sql.lawww.sql.la Database Architect, Pointer Corporation www.pointercorp.com Database Architect, Pointer Corporation www.pointercorp.com

22

ObjectivesObjectives

There are four phases in report authoring;There are four phases in report authoring; Most textbooks only cover 1.5 of those phase;Most textbooks only cover 1.5 of those phase; We’ll go over all four phase;We’ll go over all four phase; With emphasis on the missing 2.5;With emphasis on the missing 2.5;

33

AgendaAgenda

Phase 1: Phase 1: The UsersThe UsersPhase 2: Phase 2: The DataThe DataPhase 3:Phase 3: The ReportThe ReportPhase 4:Phase 4: The Delivery and AdministrationThe Delivery and AdministrationQuestions and AnswersQuestions and Answers

44

What’s Covered in the What’s Covered in the TextbooksTextbooks

Phase 1: The Users No 00 %

Phase 2: The Data Not Enough 20 %

Phase 3: The Report OK 80 %

Phase 4: The Delivery and Administration So-So 50 %

Overall % Presented for all 4 phases 150 %

Maximum % for all 4 phases 400 %

55

A Typical ScenarioA Typical Scenario

A client calls me or sends me an email …A client calls me or sends me an email …Ben, I need a report;Ben, I need a report;How much is it going to cost?How much is it going to cost?When is it going to be ready?When is it going to be ready?Now, you be “Ben” and I’ll be “the client” …Now, you be “Ben” and I’ll be “the client” …How would you answer these questions?How would you answer these questions?

What questions will you ask me before What questions will you ask me before giving me your cost and time estimates?giving me your cost and time estimates?

Or maybe you have an estimate, even Or maybe you have an estimate, even without asking any questions?!without asking any questions?!

66

Why These Four Phases?Why These Four Phases?

You build the most sophisticated report;You build the most sophisticated report;Then the manager says: “That’s nice but …”Then the manager says: “That’s nice but …”

Why is that % value over there lower Why is that % value over there lower than this % value?than this % value?

It should be higher!It should be higher!

77

Phase 1: Phase 1: Understanding the Understanding the UsersUsers

Reading their mindReading their mindUnderstanding their psychologyUnderstanding their psychologyDo they know what they want?Do they know what they want?Do they have time to tell you what they want?Do they have time to tell you what they want?Is it in writing or just in their head?Is it in writing or just in their head?Is there a sample?Is there a sample?Is it similar to another existing report?Is it similar to another existing report?

88


Maybe they can designate someone else (like a Maybe they can designate someone else (like a manager or director who reports to them) to be manager or director who reports to them) to be your contact and who will have time to sit your contact and who will have time to sit down with you and tell you exactly what the down with you and tell you exactly what the boss wants?boss wants?

What problem are they trying to solve?What problem are they trying to solve?WHYWHY do they need this report? do they need this report?Is it for their personal use to manage their own Is it for their personal use to manage their own

business better or is it mandated by some business better or is it mandated by some other authority – inside or outside the other authority – inside or outside the organization – or even a government agency?organization – or even a government agency?

99


Did they request the report themselves or did one Did they request the report themselves or did one of their staff tell you what they want?of their staff tell you what they want?

Do they even look at the existing report which you Do they even look at the existing report which you are supposed to improve?are supposed to improve?

How sophisticated are they?How sophisticated are they?If they asked for a If they asked for a weeklyweekly report … report …Do they understand the difference between Do they understand the difference between “Last “Last

week”week” and and “Last 7 days”“Last 7 days”??Which one do they want?Which one do they want?Can you show them a prototype / mock report Can you show them a prototype / mock report

before spending hours / days on development and before spending hours / days on development and formatting?formatting?

1010

Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources

Some fundamental questions which are Some fundamental questions which are virtually missing from every reporting textbook virtually missing from every reporting textbook ……

Where is the data?Where is the data?How accessible is it?How accessible is it?Is it ready for reporting?Is it ready for reporting?Why is it that textbooks do not cover these Why is it that textbooks do not cover these

topics?topics?Is it because they don’t have the answers?Is it because they don’t have the answers?Or they don’t understand the concept of an Or they don’t understand the concept of an

end-to-end textbookend-to-end textbook??

1111


In the view of reporting textbooks …In the view of reporting textbooks …The Data Source is there and ready to be The Data Source is there and ready to be

reported on;reported on;You just need to know the connection string / You just need to know the connection string /

id / password / etc.id / password / etc.Not so easy in the real world!Not so easy in the real world!What if the data source is unreachable?What if the data source is unreachable?What if the data owner doesn’t give you What if the data owner doesn’t give you

access?access?What if some required data elements aren’t What if some required data elements aren’t

being tracked yet? being tracked yet? (e.g. CreateDate vs. LastUpdate)(e.g. CreateDate vs. LastUpdate)

1212


And even more interesting …And even more interesting …Not only they may want one date instead of Not only they may want one date instead of

another another (e.g. CreateDate vs. LastUpdate)(e.g. CreateDate vs. LastUpdate)

They may also say: “Why is that other previous They may also say: “Why is that other previous report using report using LastUpdatedLastUpdated? That should have ? That should have used used CreateDateCreateDate as well.” as well.”

1313


Some examples from my real-life experience in Some examples from my real-life experience in the past couple of years …the past couple of years …

From a user email elaborating on the report From a user email elaborating on the report requirements: “requirements: “The weekly report needs to The weekly report needs to represent the status of the cases as they were represent the status of the cases as they were on those days even if we pull it a week, a on those days even if we pull it a week, a month etc... later.month etc... later.””

1414


From another user email elaborating on the From another user email elaborating on the report requirements: “report requirements: “The report should show The report should show only my staff’s weekly activities; not John’s only my staff’s weekly activities; not John’s staff.staff.””

My 1My 1stst thought: thought: WHERE DeptID=“X”WHERE DeptID=“X”Not so fast!Not so fast!This was a business divorce case!This was a business divorce case!We had to split the database and put it on two We had to split the database and put it on two

separate servers.separate servers.

1515


Another example …Another example …We need five SSRS reports and here’s the We need five SSRS reports and here’s the

layout and screenshot of the application where layout and screenshot of the application where the data is entered.the data is entered.

Where’s the data connection information?Where’s the data connection information?Talk to John; Here’s his email and phone #;Talk to John; Here’s his email and phone #;But that’s a different company!But that’s a different company!I ended up building an elaborate SSIS package I ended up building an elaborate SSIS package

to pull the data from the other company’s to pull the data from the other company’s server, before I could start working on the new server, before I could start working on the new SSRS reports.SSRS reports.

1616


Another example …Another example …We need some new reports.We need some new reports.Where’s the data connection information?Where’s the data connection information?Talk to Bob; Here’s his email and phone #;Talk to Bob; Here’s his email and phone #;Bob: What’s the connection information to this Bob: What’s the connection information to this

data source?data source?We will not give it to you; Only our staff are We will not give it to you; Only our staff are

allowed to extract this data!allowed to extract this data!

1717


The The IdealIdeal vs. the vs. the Real / Practical Real / Practical Data SourceData Source

In an Ideal Data Source …In an Ideal Data Source …All Calculations / Summarizations / All Calculations / Summarizations /

Aggregations are already done;Aggregations are already done;Data has been stored in Analysis Services Data has been stored in Analysis Services

(SSAS) where applicable;(SSAS) where applicable;Shared Data Sources have been defined within Shared Data Sources have been defined within

Reporting Services (SSRS) before report Reporting Services (SSRS) before report authoring starts;authoring starts;

1818


The The IdealIdeal vs. the vs. the Real / Practical Real / Practical Data SourceData Source

In the Real World (or at least in most cases) …In the Real World (or at least in most cases) …Calculations / Summarizations / Aggregations Calculations / Summarizations / Aggregations

are missing;are missing;Data may not even be accessible to the Data may not even be accessible to the

reporting world and you may need to run an reporting world and you may need to run an SSIS package to import it periodically;SSIS package to import it periodically;

1919


You have to choose between two You have to choose between two methodologies in order to bridge the gap:methodologies in order to bridge the gap:

1.1.A simple data source (e.g. one or more joined A simple data source (e.g. one or more joined tables) followed by a complex report definition;tables) followed by a complex report definition;

2.2.A complex data source (e.g. one or more A complex data source (e.g. one or more stored procedures with temporary tables, stored procedures with temporary tables, groupings, aggregations, etc.) followed by a groupings, aggregations, etc.) followed by a simple report definition;simple report definition;

My choice is the 2My choice is the 2ndnd approach as much as approach as much as possible: possible: GO SP!GO SP!

2020

Phase 3: Phase 3: Composing the ReportComposing the Report

That’s where the textbooks are most helpful.That’s where the textbooks are most helpful.Categories / Templates (lists, tables, 1-to-Categories / Templates (lists, tables, 1-to-

many, cross-tab, tablix, etc.)many, cross-tab, tablix, etc.)Formatting; Fonts; Page Breaks; Formatting; Fonts; Page Breaks;

Headers/Footers; Groupings; etc.Headers/Footers; Groupings; etc.Wizards and other tools to get you started Wizards and other tools to get you started

quicklyquickly33rdrd Party vendors Party vendors

2121

Phase 4: Phase 4: Delivery and Delivery and AdministrationAdministration

How are the users going to get the report?How are the users going to get the report?Are they accessing the Report Server directly?Are they accessing the Report Server directly?Do they have a SharePoint site where their Do they have a SharePoint site where their

reports are deployed?reports are deployed?Is there an existing application where this new Is there an existing application where this new

report will just become another entry under its report will just become another entry under its “Reports” menu?“Reports” menu?

2222

Phase 4: Phase 4: Delivery and Delivery and AdministrationAdministration

Are we building a new application to host these Are we building a new application to host these reports?reports?

Specifying Connections and Credentials for the Specifying Connections and Credentials for the ReportViewer Web Server Control ReportViewer Web Server Control http://msdn.microsoft.com/en-us/library/aa983458(v=VS.90).aspxhttp://msdn.microsoft.com/en-us/library/aa983458(v=VS.90).aspx

Do they want the report emailed to them Do they want the report emailed to them periodically?periodically?

SchedulingSchedulingDeploymentDeployment

PushPush: From Report Builder => Save As …: From Report Builder => Save As … PullPull: From Report Server => Upload File: From Report Server => Upload File

2323

In Summary: In Summary: Here’s what we Here’s what we learned todaylearned today

Understanding the users and their requirements

Two Weeks 80 Hours

Developing Data Sources (Stored Procedures)

Two Days 16 Hours

Developing Report Definitions (RDLs), Deploying, and Scheduling

Half a Day 4 Hours

TOTAL Two and a Half Weeks

100 Hours

2424

Questions and AnswersQuestions and Answers

2525

Contact InformationContact Information

• Emails:Emails: [email protected] [email protected] [email protected] [email protected]

• Websites:Websites: www.sql.lawww.sql.la www.pointercorp.com www.pointercorp.com www.vipletters.com www.vipletters.com

2626

Thank You!Thank You!

four phases of report authoring targeted for executives and upper management by: ben aminnia...

Documents

existing report

data sourcesin

data sourcesand

weekly report

report authoringmost

data owner doesnt

prototype mock report

required data elements