you have no items in your basket

Tel: UK 0845 757 3888 | Ireland: 01 284 6796

Creating business diagrams from within SQL Server without using Visio services or third party tools

Picture of Mark Fitzgerald

Mark Fitzgerald

Mark ‘Fitz’ is the principle technologist for business intelligence, covering SQL, PerformancePoint and ProClarity. Mark is a twenty-year veteran of the IT industry, with experience that ranges from mainframes, help desk and MIS systems. Mark has experience developing business applications in a range of products which enhance and distribute accurate, timely information within organisations. Mark has been with QA since 2000, and in 2003 & 2006 he won QA’s Trainer of the Year Award. Mark’s delegates have commented that he should get out more as his enthusiasm for the subject sometimes spills into breaks, lunch time and early evening if not hit with something hard (like a hammer).

Posted by Mark Fitzgerald
on 01 November 2012

Many businesses need to be able to produce business-oriented diagrams using SQL Server data. This can pose a problem for the SQL Server user, many of whom rely on Visio services or third party tools to be able to produce the reports which the business demands.

However, it is possible to use spatial maps within the reporting services element of Microsoft SQL Server 2008 R2, to create diagrams for use within business reports - whether this is from data stored in SQL Server as geometry/geography data types, embedded within the control itself (US only) or by using an ESRI shapefile. The diagrams below illustrate the level of reporting capability possible using this technique. All of the diagrams below are calculated from a standard parent and child relational source.

Below is a list of types of diagrams typically requested by the business:

Hierarchies : a simple parent and child hierarchy, allowing for multiple root members, colouring of the boxes (to show seniority or performance), naming of the boxes and providing tooltips
Multiple proportional pie charts : changing the size of the charts for successive periods to show growth or shrinkage, maintaining the essence of the proportions for each segment
Nightingale Rose: similar in design to a pie chart combined with a stacked bar. The segments around the chart are evenly spaced, but the radius of the segment changes and are proportional to the values shown
Extending maps with Sparklines: taking a geometric map (such as that above, based upon UK Ordnance Survey data using eastings and northings) as a base, sparklines charts can be added with links to the geometric shapes.
Networks: a simple diagram showing objects (tasks, stages, people) and the flows between them. This could be a workflow, a rail network or sites to specific network links
Gantt charts: although not a replacement for project management software and professional diagrams, a Gantt can be produced using geometry shapes

Equirectangular _cut

Making these available using SSRS will allow clients to visualise the data better and give the developers additional options for display. It is not likely to replace the common chart types available within the product, but with a little thought and effort most diagrams are possible.

All of the diagrams below are possible using standard TSQL objects (user defined table data types, user defined functions and stored procedures). No CLRs are used in creating the diagrams and each performs adequately.

Hierarchy : hierarchical view of items dependent upon parent and child arrangement - organisation chart, hierarchical KPI, viewing a decision tree

Output - KPI Map

Multiple proportional pies : growth of sales over time with the proportion of each sector

Multiple Pies

Nightingale Rose : changing sizes and proportions over time

Geometric map with Sparkline pies included : proportion of sales by category split regionally

Geometry Sparklines

Gantt Chart : tasks to the performed with dates

Gantt

Network : tasks and dependencies between them

Network

If you'd like to know more, you can download my white paper on the subject here >>

top of page

Useful Information

Portals

About Us

QA - training courses across the UK and Ireland from the leading provider of PRINCE2 and Microsoft training

QA provides PRINCE2 training, project management training, Microsoft training, Cisco training plus an extensive range of 1500 other courses covering all areas of IT, project management, service management, business applications and professional development. We provide PRINCE2 training and all courses in locations including London, Bristol, Reading, Milton Keynes, Birmingham, Leeds, Manchester, Newcastle, Glasgow, Edinburgh, Ireland plus online eLearning and virtual learning.

PRINCE2®, ITIL®, M_o_R®, MoP®, P3O®, MSP® and MoV® are registered trade marks of the Cabinet Office. The Swirl logo™ is a trade mark of the Cabinet Office. The PRINCE2®, ITIL®, M_o_R®, MoP®, P3O®, MSP® and MoV® Accredited Training Organization logos are trade marks of the Cabinet Office. PMI®, PMP®. CAPM® and PMBOK®guide are registered certification marks and trademarks of the Project Management Institute Inc. Green Project Management®, is a registered trademark of GPM Global. BPUG® is a Registered Trade Mark and Registered Community Trade Mark of Best Practice User Group Ltd. IIBA®, the IIBA® logo, BABOK® and Business Analysis Body of Knowledge® are registered trademarks owned by International Institute of Business Analysis. QA is an endorsed education provider of IIBA. IIBA® is a registered trademark owned by International Institute of Business Analysis. The Open Group Certification logo is a trademark and the word TOGAF® is a registered trademark of The Open Group in the United States and other countries. QA Limited is an Accredited TOGAF® 9 Training Course and complies with the accreditation requirements for The Open Group TOGAF® Certification for People program. Cisco, the Cisco logo, and Cisco Systems are trademarks or registered trademarks of Cisco Systems, Inc. and/or its affiliates in the United States and certain other countries. ILM is a trademark of the City and Guilds of London Institute and is used under licence. Microsoft is a registered trademark of Microsoft Corporation in the United States and/or other countries. APMG Swirl Agile Project Management Device is a Trade Mark of APM Group Ltd. Salesforce is a trademark of salesforce.com, inc.

All third party trademark rights acknowledged

QA reserves the right to improve the specification and format of its courses for the benefit of its customers without notice to the customer.