Professional Microsoft SQL Server 2012 Reporting Services

916 Pages·2012·47.341 MB·English

by  Paul Turley, Robert M. Bruckner, Thiago Silva, Ken Withee, Grant Paisley

www.it-ebooks.info
www.it-ebooks.info
ffffiirrss..iinndddd iivv 55//1100//22001122 33::3399::2299 PPMM
PROFESSIONAL
MICROSOFT® SQL SERVER® 2012 REPORTING
SERVICES
FOREWORD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxv
INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii
(cid:2) PART I GETTING STARTED
CHAPTER 1 Introducing Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
CHAPTER 2 Reporting Services Installation
and Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
CHAPTER 3 Confi guring SharePoint Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69
(cid:2) PART II REPORT DESIGN
CHAPTER 4 Basic Report Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95
CHAPTER 5 Report Layout and Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
CHAPTER 6 Designing Data Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
CHAPTER 7 Advanced Report Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
CHAPTER 8 Chart Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .229
(cid:2) PART III BUSINESS INTELLIGENCE REPORTING
CHAPTER 9 BI Semantic Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
CHAPTER 10 Reporting with Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .263
CHAPTER 11 OLAP Reporting Advanced Techniques . . . . . . . . . . . . . . . . . . . . . . . . .295
(cid:2) PART IV ENABLING USER REPORTING
CHAPTER 12 Tabular Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .349
CHAPTER 13 Visual Analytics with Power View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
CHAPTER 14 Report Builder Solution Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .445
www.it-ebooks.info
ffffiirrss..iinndddd ii 55//1100//22001122 33::3399::2288 PPMM
(cid:2) PART V SOLUTION PATTERNS
CHAPTER 15 Managing Report Projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .463
CHAPTER 16 Report Solutions, Patterns, and Recipes . . . . . . . . . . . . . . . . . . . . . . . . .483
(cid:2) PART VI ADMINISTERING REPORTING SERVICES
CHAPTER 17 Content Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .525
CHAPTER 18 Integrating Reports with SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . .559
CHAPTER 19 Native Mode Server Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581
(cid:2) PART VII REPORTING SERVICES CUSTOM PROGRAMMING
CHAPTER 20 Integrating Reports into Custom Applications . . . . . . . . . . . . . . . . . . . . 619
CHAPTER 21 Using Embedded and Referenced Code . . . . . . . . . . . . . . . . . . . . . . . . . 681
CHAPTER 22 Extending Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .697
(cid:2) PART VIII APPENDIXES
APPENDIX A T-SQL Commands, Clauses, and Predicates . . . . . . . . . . . . . . . . . . . . . .758
APPENDIX B T-SQL System Variables and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 779
APPENDIX C MDX Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .803
INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 829
www.it-ebooks.info
ffffiirrss..iinndddd iiii 55//1100//22001122 33::3399::2299 PPMM
PROFESSIONAL
Microsoft® SQL Server® 2012
Reporting Services
www.it-ebooks.info
ffffiirrss..iinndddd iiiiii 55//1100//22001122 33::3399::2299 PPMM
www.it-ebooks.info
ffffiirrss..iinndddd iivv 55//1100//22001122 33::3399::2299 PPMM
PROFESSIONAL
Microsoft® SQL Server® 2012
Reporting Services
Paul Turley,
Robert M. Bruckner,
Thiago Silva,
Ken Withee, and Grant Paisley
www.it-ebooks.info
ffffiirrss..iinndddd vv 55//1100//22001122 33::3399::2299 PPMM
Professional Microsoft® SQL Server® 2012 Reporting Services
Published by
John Wiley & Sons, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2012 by John Wiley & Sons, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-1-118-10111-7
ISBN: 978-1-118-22379-6 (ebk)
ISBN: 978-1-118-23713-7 (ebk)
ISBN: 978-1-118-26210-8 (ebk)
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,
electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108
of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization
through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers,
MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the
Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax (201)
748-6008, or online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with
respect to the accuracy or completeness of the contents of this work and specifi cally disclaim all warranties, including
without limitation warranties of fi tness for a particular purpose. No warranty may be created or extended by sales or
promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work
is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional
services. If professional assistance is required, the services of a competent professional person should be sought. Neither
the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is
referred to in this work as a citation and/or a potential source of further information does not mean that the author or the
publisher endorses the information the organization or Web site may provide or recommendations it may make. Further,
readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this
work was written and when it is read.
For general information on our other products and services please contact our Customer Care Department within the
United States at [phone], outside the United States at [phone] or fax [phone].
Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with
standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to
media such as a CD or DVD that is not included in the version you purchased, you may download this material at
http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.
Library of Congress Control Number: 2012933621
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress
are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affi liates, in the United States and other
countries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of
Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not
associated with any product or vendor mentioned in this book.
www.it-ebooks.info
ffffiirrss..iinndddd vvii 55//1100//22001122 33::3399::3322 PPMM
This book is dedicated to my wonderful wife, Sherri,
for her love and endless support. To my dad, Mark,
and to the most incredible young people on the planet;
Krista, Sara, Rachael, and Josh.
— Paul Turley
Dedicated to my parents.
— Robert M. Bruckner
I dedicate this book to my beautiful wife, Michelle,
who still loves me and encourages me, after all
these years; to my children, Gabriella, Joshua, and
Olivia, who brighten my life with their smiles and
unconditional hugs; and to my mother Lucia who
keeps believing that I am a rockstar.
— Thiago Silva
I dedicate this book to my wife and best friend,
Rosemarie Withee.
— Ken Withee
I dedicate this book to my wife Sue, who still loves
me after all these years; to my teenage kids, Megan,
Lisa, and Zoe, who have turned out even better than I
could have hoped; and to mum and dad who gave me
the opportunity and encouragement to always do and
learn new things.
— Grant Paisley
www.it-ebooks.info
ffffiirrss..iinndddd vviiii 55//1100//22001122 33::3399::3322 PPMM
www.it-ebooks.info
ffffiirrss..iinndddd vviiiiii 55//1100//22001122 33::3399::3322 PPMM