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, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, 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 (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. 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