Transcript SQL-Saturday 2015 – But I want it in Excel

But I Want it In Excel
_____________________________________________________
Perry T Patterson
June 2015
_____________________________________________________
954.729.8455
www.3Deers.com
[email protected]
SQL Saturday
Perry T Patterson – Introduction
Principal Consultant
3Deers.com, LLC
SQL Dashboards
BI Reporting with Excel
Masters in MIS, eCommerce
Nova Southeastern University
Background
Companies spend piles
of money on a SQL
Database
infrastructure…
… yet find they still can’t
get the users to look at
reports.
Background
Let’s see… faster than a speeding bullet… strength of a
thousand men… can leap tall buildings in a single bound…
very impressive resume.
So… how are you with Excel?
Do your CFO’s,
accountants,
managers and other
end users
REQUIRE
their reports in
excel?
Background
Do your users have
different results
than your IT
Department?
What Do You Do?
In the past…
… you became an IMPORTER
/ EXPORTER!
I’m an Architect
What Do You Do?
You don't need
Vandelay Industries!
Get out of the business
of Importing and
Exporting of Data.
Simple solution built into Excel!
Overlooked feature of Microsoft Excel
allows users to connect directly to ...
MS Query
Visual data creation …
Standard Excel Table
… Straight into Excel
No Cut / Paste!
No EXPORT / IMPORT!
Powerful Pivot Tables
Demonstration – Advanced
NorthWind Sales Report
https://dl.dropboxusercontent.com/u/39931545/A
W_Sales.xlsm
Demonstration – Advanced





Macros – Refresh on button or field change
Pivot Table Filter
Table Formatting / Data
Turn off Background refresh!
IT Developed “Views” in SQL
◦ Easier Data for Most Users
◦ Controlled Calculations / Information
Demonstration – Polishing a Report







Headings and Tab Names
Name your tables
Freeze Views
Multiple Tabs / Views with same data
Refresh Macro
Date Grouping
Prompting for Parameters
Summary
MS Excel For Business Intelligence
Most likely already owned
 Easy Query for non-technical users
 Powerful Options
 Familiar Tool…
therefore USED!
