ad hoc reporting: microsoft report builder jim drever, joe nevshemal
TRANSCRIPT
Ad Hoc Reporting: Microsoft Report Builder
Jim Drever, Joe Nevshemal
Agenda
• Goal of this session• What is Report Builder?• How does it work?• Where do I find it?• How do I use it?• Then what?
Today’s Goal
• To learn how to launch (get to) Report Builder• Understand how it works with the RiteTrack
database• See a sample report or two to help get you
started
Report Builder• Ad Hoc reporting tool– ( A better Report Genie, although we aren’t
dropping it)
• Microsoft includes Report Builder with every copy of SQL Server 2005 Reporting Services
• Allows users to:– Build Reports (table, matrix, chart)– Explore (query) data– Use Reporting Services features (subscribe)
How Report Builder Works
• Quick look at databases… really quick, I promise
• Your RiteTrack database is a relational database that sits on your SQL Server or ours if we host your data.
• It consists of over 300 tables storing data, configuration, and functionality.
• Let’s look at the difference between relational and flat databases.
Flat Data (Think: Excel)PersonID Name Telephone Number
1 Jim (307) 742-0030
2 Even (307) 742-7272
3 Casey (307) 742-8293
What happens if we want to add our work numbers? •Do we add another column?•Do we add another row for each of us?
For the purpose of taking up less hard drive space AND speeding up the process of retrieving data, we make more tables.
Relational DataID Name
1 Jim
2 Even
3 Casey
PersonID NumberID
1 1
2 2
3 3
1 4
2 4
3 4
ID Number
1 (307) 742-0030
2 (307) 742-7272
3 (307) 742-8293
4 (307) 742-5555
Primary Key
Primary Key
Foreign Keys
How Does Report Builder Use This?
• Report Builder analyzes the primary/foreign key relationships throughout the database.
• It “follows” these relationships/paths (as we’ll see shortly).
• Most standard RiteTrack tables have these paths; some custom tables do too.
Your RiteTrack Database
• Two fundamental tables– dtPerson– dtEvents
• There are numerous “sub-tables” that join these two tables to hundreds of others.– For example: dtEventInvolved Parties,
dtPersonNumbers, dtCases and more• “Lookup” tables hold static data: race, number
types, gender, case types and more.
Next…
• We have seen the rough logic as to how Report Builder works.
• Let’s go look at Report Builder!!!
How to Launch Report Builder
• Report Builder is a Microsoft product, so it ONLY works with Internet Explorer (although you can access Reporting Service with Firefox, etc.)
• Two ways to get there– One: You have your project manager put a one-
click button in RiteTrack for you.– Two: Navigate using Internet Explorer.
Option Two - Navigate
• In your web browser put in the name of your database and reporting services.– http://<servername>/ReportServer/
Reportbuilder/reportbuilder.applications– http://<Servername>/Reports • (if you are on the server itself, http://localhost/Reports)
• Click on Report Builder at the top.
What You Will Need
• Name of your SQL Server • Permissions, as denoted in your network security,
(non verbal) to access the SQL server• Existing data model– Must have this for existing reports.– Online tutorials can show you how.– Handel can build it for you (15 min. or so).– To really use event data, you need to know your
configuration or have your Handel PM build you an easy-to-use custom view (several hours perhaps).
Let’s Take a Look• Using Handel’s demo database and server name,
let’s go to the report server. – Notice how our IT changed it from reports to
reports$SRS.– http://hit08/Reports$SRS
• Once there, we’ll look around and click Report Builder to launch Report Builder.• NOTE: If you have ANY other databases, you can
query them too. Access is read only.
Great Resource
• http://msdn.microsoft.com/en-us/library/aa337398.aspx
• Google Report Builder• Books
Finally
• Free to go via website.• Free basic set-up; it is already part of the
database.• This is a Microsoft product; we aim to help
and support, but we are limited in how much we can do.
• Integrate with Microsoft.• You won’t hurt anything.
Thanks!!!
See you next time.