book

Microsoft SQL Server 2012 Reporting Services

by Stacia Misner

April 2013

Beginner to intermediate

1030 pages

30h 21m

English

Microsoft Press

Read now

Unlock full access

Who should read this bookAssumptionsOrganization of this bookConventions and features in this bookSystem requirementsCode samplesInstalling the code samplesAcknowledgmentsErrata & book supportWe want to hear from youStay in touch
Introducing new featuresPower ViewData sourcesData visualizationInteractive scatter chartMultiplesHighlighted valuesSlicerTilesView filterVisualization filterReport sharingSharePoint integrationData alertsData Alert DesignerData Alert ManagerReviewing other changesSQL Server Data ToolsNew rendering extensionsExcel 2010 rendererWord 2010 rendererParting with Report Builder 1.0Upgrading from prior versionsPreparationIn-place upgradeNative-mode report server upgradeSharePoint integrated-mode report server upgradeReports upgradeMigrationNative-mode report server migrationSharePoint integrated-mode report server migration
A reporting platformReporting life cycleReport developmentReport administrationReport accessReporting Services architectureNative modeData tierApplication tierServer tierService architectureSharePoint integrated modeData tierApplication tierServer tier
Single-server deploymentMemoryDisk spaceDisk activityDistributed deploymentNative-mode distributionSharePoint integrated-mode distributionOther componentsScale-out deploymentLoad balancingFailover clusterDistributed workloadInternet deployment
Reviewing hardware and software requirementsSelecting an operating systemChoosing a Database Engine editionReviewing SharePoint integrated-mode requirementsPlanning accounts for reporting servicesPerforming the installationInstalling prerequisitesInstalling SQL Server 2012Product keyLicense termsSetup roleFeature selectionInstallation rulesInstance configurationDisk space requirementsServer configurationDatabase Engine configurationAnalysis Services configurationReporting Services configurationInstallation configuration rulesConfiguring native-mode Reporting ServicesService accountWeb service URLDatabaseReport Manager URLEmail settingsExecution accountEncryption keysScale-out deploymentConfiguring partial integration with SharePointConfiguring SharePoint for local mode reportingConfiguring SharePoint integrated modeInstalling the Reporting Services Add-in for SharePointCreating a SharePoint web applicationCreating a business intelligence center siteConfiguring SharePoint for Reporting Services integrationConfiguring the service applicationProvisioning subscriptions and alertsConfiguring a SharePoint siteAdding another report serverAdding another Reporting Services web front-endInstalling Report BuilderInstalling sample databases
Understanding design environmentsComparing design environmentsChoosing a report designerUsing Report DesignerUsing Report BuilderUsing Power ViewDeveloping a report with Report BuilderLaunching Report BuilderNative modeSharePoint integrated-modeStand-alone applicationGetting Started WizardUsing the dataset designerGetting familiar with the Report Builder interfaceAdding a data sourceAdding a datasetAdding a report titleUsing data region wizardsTable Or Matrix WizardChart WizardMap WizardAdding a data region manuallyAdding fields to a tableDragging fields from the Report Data paneUsing the Field List iconAdding a totalGrouping dataInserting page breaksFormatting a reportPreviewing a reportSaving a reportManaging a reportPublishing a reportPublishing to a native-mode report serverPublishing to a SharePoint integrated-mode report serverAdding a descriptionReviewing processing optionsReviewing processing options in Report ManagerReviewing processing options in a SharePoint document libraryViewing a reportOpening a reportOpening a report in Report ManagerOpening a report in a SharePoint document libraryUsing the HTML Viewer

Knowing your audienceReviewing report optionsLayout decisionsData considerationsParametersMobile devicesPrinted reportsDeveloping standards
Getting started with report server projectsCreating a Reporting Services projectCreating a new project and solutionAdding a new project to an existing solutionAdding an existing project to an existing solutionSelecting a project typeReport Server Project WizardReport Server projectGetting familiar with the Visual Studio interfaceSolution ExplorerProperties windowOutput windowError List windowToolbox windowReport Data windowRemoving a project from a solutionWorking with report project filesReviewing report project file typesUsing the Report WizardInitial stepsTabular reportMatrix reportFinal stepsAdding a new item to a projectAdding an existing item to a projectRemoving an item from a projectUsing report project commandsProject menuBuild menuConfiguring report project propertiesWorking with project configurationsSetting project properties
Understanding data source typesRelational data sourcesSQL ServerSQL AzureSQL Server Parallel Data WarehouseOracleTeradataOLE DBODBCMultidimensional data sourcesSQL Server Analysis ServicesSAP NetWeaver BIHyperion EssbaseMiscellaneous data sourcesXMLSharePoint listReport server modelConnecting to data with data sourcesComparing embedded and shared data sourcesWorking with an embedded data sourceCreating an embedded data sourceProcessing queries as a single transactionEditing an embedded data sourceConverting an embedded data source to a shared data sourceDeleting an embedded data sourceWorking with a shared data sourceCreating a shared data sourceReferencing a shared data source in a reportEditing a shared data sourceDeleting a shared data sourceSetting data source credentialsIntegrated securityUser name and passwordPrompt for credentialsNo credentialsRetrieving data with datasetsComparing embedded and shared datasetsWorking with an embedded datasetCreating an embedded datasetEditing an embedded datasetConverting an embedded dataset to a shared datasetDeleting an embedded datasetWorking with a shared datasetCreating a shared datasetReferencing a shared dataset in a reportEditing a shared datasetDeleting a shared datasetExploring query designersGraphical query designerReport model query designerSharePoint list query designerMDX query designerText-based query designerUnderstanding dataset propertiesFieldsOptionsFiltersParameters
Reviewing report itemsGeneral report itemsText boxesLines and rectanglesImagesSubreportsTablix data regionsTableMatrixListGraphical data regionsChartGaugeMapData barSparklineIndicatorAdding general report items to a reportAdding a text boxAdding a lineAdding a rectangleAdding an imageEmbedded imageExternal imageDatabase imagesAdding a subreportWorking with data regionsAdding a tableBinding data to a data regionGrouping dataAdding a total to a tableAdding a matrixSorting groupsAdding a listUsing properties to control appearance and behaviorConfiguring the placement and size of a report itemSetting the Location propertySetting the Size propertyControlling text box growthChanging the appearance of a report itemSetting font propertiesSetting selected text propertiesSetting alignment propertiesSetting the Format propertySetting locale propertiesSetting border propertiesSetting fill propertiesSetting list propertiesSetting line properties
Exploring the tablixWhat is a tablix?Tablix data regionsTableMatrixListTablix cellsCell contentCell scopeCell merge and splitTransforming a tablixTable to matrixMatrix to tableDetail list to grouped listConfiguring tablix propertiesNo data rowsGroups before row headerSortWorking with tablix groupsRow groupsSeparate columns for each groupSingle column for each groupStatic columnsRepeating static columnsAdjacent columnsAdjacent groupsGroup propertiesMissing group values in nested data regionsRecursive hierarchy groups
Introducing expressionsSimple expressionsDataset field placeholderAggregate function placeholderBuilt-in field placeholderParameter value placeholderComplex expressionsUsing the Expression dialog boxOpening the Expression dialog boxExploring the Category treeUsing IntelliSenseMember or property selectionFunction tooltipSyntax errorUsing built-in collectionsDataSets collectionDataSources collectionFields collectionGlobals collectionParameters collectionReportItems collectionUser collectionVariables collectionWorking with expressionsAdding calculations to a reportCalculated fieldsText box expressionsReport item referencesBuilt-in fieldsApplying conditional formattingWorking with variablesGroup variablesReport variablesDeferVariableEvaluation propertyUsing expressions for dynamic connections and datasetsDynamic connection stringsDynamic query strings
Getting help for functionsFunction description and syntaxFunction tooltipUsing text functionsThe character functionsThe Asc and AscW functionsThe Chr and ChrW functionsThe Space functionThe search functionsThe Filter functionThe GetChar functionThe InStr and InStrRev functionsThe formatting functionsThe Format functionThe FormatCurrency, FormatNumber, and FormatPercent functionsThe FormatDateTime functionThe array functionsThe Join functionThe Split functionThe conversion functionsThe LCase and UCase functionsThe StrConv functionThe string manipulation functionsThe Left, LSet, Right, and RSet functionsThe Mid functionThe StrDup functionThe StrReverse functionThe string evaluation functionsThe Len functionThe StrComp functionThe cleanup functionsThe Replace functionThe Trim, LTrim and RTrim functionsUsing date and time functionsThe date calculation functionsThe DateAdd functionThe DateDiff functionThe date manipulation functionsThe DatePart functionThe interval functionsThe date conversion functionsThe DateSerial and TimeSerial functionsThe DateValue and TimeValue functionsThe MonthName and WeekdayName functionsThe current date and time functionsUsing math functionsThe trigonometry functionsThe sign functionsThe exponent functionsThe rounding functionsOther math functionsUsing inspection functionsUsing program flow functionsThe Choose functionThe Iif functionThe Switch functionUsing aggregate functionsThe aggregate functionsThe Scope argumentThe RunningValue functionUsing financial functionsUsing conversion functionsUsing miscellaneous functionsThe InScope functionThe lookup functionsThe Lookup functionThe MultiLookup functionThe LookupSet functionThe row functionsThe Previous functionThe RowNumber functionWorking with hierarchical dataThe Level functionThe Recursive keyword
Interacting with the report layoutInteractive sortingSort detail rowsSort groupsSort by expressionApply sort to other groups and data regionsUsing fixed headersConfiguring visibilityShow or hide a rowDrilldownAdding tooltipsAdding navigation featuresDefining a document mapCreating a document mapCustomizing a document mapShowing and hiding a document mapRendering a document mapUsing embedded HTML tagsWorking with report actionsGo to reportGo to bookmarkGo to URL
Working with report parametersCreating a report parameterSetting report parameter propertiesGeneral propertiesAvailable valuesDefault valuesAdvanced parameter optionsDeleting a report parameterDisplaying parameter selections in a reportUsing filtersCreating a filterFiltering a datasetFiltering a data regionFiltering a groupUsing a report parameter as a filterUnderstanding filter operatorsEquality and inequalityLikeTop and bottomBetweenInNot inApplying OR logic to multiple filtersUsing parameters with subreports and drillthroughPassing a parameter to a subreportPassing a parameter to a drillthrough reportConfiguring the drillthrough report parameter valueOmitting a parameterWorking with query parametersCreating a query parameterLinking report and query parametersDeleting a query parameterCascading parametersCreating a child report parameter datasetLinking a parent report parameter to a query parameterChanging parameter order
Understanding renderingReviewing rendering formats by pagination typeUsing the RenderFormat global variableAdjusting the report sizeUnderstanding the page structure propertiesReport page structurePage size propertiesColumn propertiesWhite spaceUsing device information to change page propertiesWorking with the page header and footerAdding a page header or footerConfiguring page header or footer propertiesUsing expressions in a page header or footerPage headers and footers in WordPage headers and footers in ExcelRemoving a page header or footerConfiguring page breaksCreating page breaksUsing the KeepTogether propertyAdding page breaks by groupRepeating headersNumbering pagesGlobal variablesResetPageNumber propertyNaming pages
Creating an Analysis Services datasetAnalysis Services data sourceMDX graphical query designerMetadata paneData paneFilter paneToolbarQuery modeCalculated Member BuilderBuilding a reportAnalysis Services dataset fieldsExtended field propertiesAggregate functionAggregate values in detail rowsShow Aggregations buttonDesigning parametersAuto-generated query and report parametersFilter paneReport parameterAvailable values datasetCalculated fieldParameterized datasetCustom query parameterQuery parameterMDX query parameterizationQuery parameter valueReport parameterCalendar control
Adding a chart to a reportSelecting a chart typeUnderstanding chart typesLinear dataRatio dataMultivalue dataUsing the chart data paneValuesField selectionValue aggregationFieldsSeries propertiesCategory groupsField selectionCategory group propertiesSeries groupsField selectionSeries group propertiesFormatting chart elementsChartChart titleAxis titleAxesSeries labelsLegendWorking with data barsAdding a data barConfiguring data bar propertiesData bar lengthData bar appearanceWorking with sparklinesAdding a sparklineConfiguring sparkline propertiesSparkline appearanceSparkline axes alignment
Changing color palettesBuilt-in color palettesCustom color paletteWorking with vertical axis propertiesScale breakLogarithmic scaleWorking with horizontal axis propertiesAxis typeIntervalsInterval propertiesInterlacingWorking with series propertiesEmpty pointsMarkerEmpty point colorZero valueBubble chartsCalculated seriesSecondary axisMultiple chart typesMultiple chart areasWorking with pie chartsSlice consolidationSecondary pie chart
Adding a gauge to a reportUnderstanding gauge typesCreating a gaugeRadial gaugesLinear gaugesWorking with gauge elementsUnderstanding gauge elementsPointerValuePointer propertiesCustom pointerSnapping intervalScaleScale propertiesMultiple scalesRangeRange propertiesMultiple rangesGaugeRadial gauge propertiesLinear gauge propertiesCommon gauge propertiesGauge labelGauge panelGrouping gaugesMultiple gauges in a gauge panelAdjacent gaugesChild gaugesRepeating gauges
Adding an indicator to a reportUnderstanding indicator typesCreating an indicatorConfiguring an indicatorAssigning an indicator valueDefining indicator propertiesGeneral propertiesValue and statesSynchronization scopeGrouping indicatorsAdjacent indicatorsGauge and indicatorGauge with child indicatorCustomizing indicatorsUsing customized imagesAdding labelsAdjusting location and sizeDynamic sizing
Understanding spatial dataTypes of spatial dataPointsLinesPolygonsSQL Server spatial data typesGeometryGeographyUsing the Map WizardSelecting a spatial data sourceMap GalleryESRI shapefileSpatial queryChoosing spatial data and map view optionsSpatial field and layer typeCenter pointZoom factorMap resolutionEmbedded map dataBing Maps layerChoosing map visualizationPoint data mapsLine data mapsPolygon data mapsAdding an analytical data setSetting theme and data visualization optionsBasic marker mapBubble marker mapAnalytical marker mapLine mapLine analytical mapBasic polygon mapColor analytical mapBubble map for polygonsFinalizing the mapMap previewSimple adjustmentsMap titleLegendScales
Configuring map elementsMapViewportCenter and zoomParallels and meridiansViewport coordinate system, projection, and boundariesOptimizationMap titleLegendScalesColor scaleDistance scaleWorking with map layersMap Layers paneMap layer propertiesCommon spatial element propertiesColor rulesColorsDistributionLegend optionsPoint mapsPoint propertiesPoint color rulePoint size ruleMarker type ruleLine mapsLine propertiesLine color ruleLine width rulePolygon mapsPolygon propertiesPolygon color ruleCenter pointsTile layer mapsInserting custom pointsUnderstanding order of precedence
Deploying contentDeploying a report projectDeployment from SSDTDeployment verificationSaving a report from Report BuilderUploading a reportUsing the rs utilityDeploying a report modelManaging contentUsing folders to organize contentMoving contentCreating a linked reportCopying a reportConfiguring report parametersConfiguring data source propertiesSelecting a data source for a reportSending credentials to a data sourceWindows integrated securityPrompted credentialsNo credentialsStored credentialsConfiguring report processing optionsExecuting a report on demandCaching reportsConfiguring report time-outRefreshing the cacheWorking with snapshotsCreating report snapshotsSaving report snapshots in report history
Configuring report server security policiesUnderstanding authentication typesReviewing default authorization policiesNative-mode rolesSharePoint integrated-mode groups and permission levelsAssigning user permissionsNative-mode role assignmentsSharePoint permission-level assignmentsConfiguring item-level securitySecuring folders and document librariesNative-mode folder securitySharePoint integrated-mode document library securitySharePoint integrated-mode folder securityApproving data sources in SharePoint integrated modeSecuring an itemAssigning a system roleSecuring a report modelImplementing data securityUsing a query parameter and a role to secure dataUsing a permissions table to secure dataUsing a dataset filter to secure data
Configuring the report serverUpdating report server propertiesGeneral propertiesExecution propertiesHistory propertiesLogging propertiesSecurity propertiesAdvanced propertiesManaging encrypted informationConfiguring authenticationManaging memoryDisabling report server featuresDisabling an extensionManaging the report serverCanceling jobsSuspending report executionPerforming backup and recoveryNative-Mode report server databaseSharePoint integrated-mode report server databaseEncryption keysConfiguration filesMonitoring the report serverChecking the application database sizeMonitoring the Windows application event logUsing the trace logsAdding the HTTP logSharePoint diagnostic loggingExecution loggingUsing the ExecutionLog3 viewUsing performance counters
Using Report ManagerSearching for a reportUsing the HTML ViewerUsing the document mapPrinting a reportUsing the My Reports folderAccessing reports in SharePointSearching for a reportUsing the SharePoint Report ViewerCreating a dashboardCreate a Web Part pageAdd a SQL Server Reporting Services Report Viewer Web PartConfigure the Web PartExporting reportsUsing soft page-break renderersExcel formatWord formatMHTML formatUsing hard page-break renderersPDF formatTIFF formatExporting a report for data exchangeCSV formatXML formatATOM format
Creating a shared scheduleSchedules listNew scheduleCreating standard subscriptionsNew subscriptionDelivery providersEmail deliveryWindows file share deliveryNull Delivery ProviderSharePoint Document LibraryReport parameter valuesSubscription processing optionsCreating data-driven subscriptionsCreating a subscription delivery tableConfiguring data-driven subscription settingsNative modeSharePoint integrated modeManaging subscriptionsUsing the My Subscriptions pageDeleting a subscriptionNative modeSharePoint integrated mode
Understanding the data alerting architectureData alert workflowAlerting serviceCreating data alertsSharePoint Report ViewerData feedsData alert rulesRule scopeRule conditionMultiple rulesData alert condition removalSchedule settingsEmail settingsReceiving data alertsSuccessful alertAlert failureManaging alertsAlert statusEdit commandDelete commandRun commandConfiguring data alertingRsReportServer.Config file settingsIsAlertingService flagAlerting interval defaultsSharePoint configuration database settingsMonitoring the alerting processExecution logAlerting stored proceduresPerformance counters
Introducing report partsReport partReport part benefitsDeploying report partsDeployment from SSDTDeployment from Report BuilderRedeployment of a report partReport part management on the report serverUsing report partsReport Part GalleryUpdate notificationChoosing report parts vs. subreports
Using tabular models with Power ViewIntroducing tabular modelsCreating a BI Semantic Model connectionEnhancing tabular models for Power ViewSummarizing valuesFormatting valuesSetting the default sortMarking the date tableManaging grouping behaviorAdding imagesUsing binary dataUsing external imagesImproving the appearance of identifying informationSimplifying the creation of a new table
Getting started with Power ViewVerifying browser requirementsConnecting to a data sourcePowerPivot GalleryReporting Services shared data sourceBI Semantic Model connection fileGetting familiar with the design environmentView workspaceViews paneRibbonField listLayout sectionVisualizing dataTableTable columnsTable layout sectionTable sort orderMatrixMatrix row and column groupsMatrix layout sectionChartsColumn, bar, and line chartsColumn, bar, and line chart layout sectionColumn and bar chart sort orderMultiplesScatter chart layout sectionPlay axisCardsFiltering dataHighlighted valuesSlicerTilesTile By areaTile visualization modeMultiple visualizations in a tiles containerSynchronizationView filterBasic filter modeAdvanced filter modeSelecting a display modeSaving a Power View reportFile save optionsPowerPoint export
Introducing Report Definition LanguageRDL schema definitionRDL usageExploring key elementsReport elementDataSources and DataSource elementsDataSets and DataSet elementsTablix elementWorking with RDLManual RDL editsProgrammatic RDL editsRDL generationSolution overviewRDLGenerator classSolution deployment
Embedding code in a reportCode propertyCustom function callUsing assemblies to share custom codeClass libraryClass definitionCustom assembly digital signatureAssembly deploymentCustom assembly callCreating a custom data processing extensionData processing extension overviewData processing extension developmentAssembly propertiesConnection classCommand classDataReader classParameter classParameterCollection classTransaction classData processing extension deploymentCustom extension digital signatureFile locationsConfiguration changesImplementation
Scripting administrative tasksDeploying reportsDeployReports.rss script walkthroughDeployReports.rss script executionCreating a linked reportCreateLinkedReport.rss script walkthroughCreateLinkedReport.rss script executionWorking with configuration settingsUsing the WMI providerWMI provider application walkthroughWMI provider application executionUsing Windows PowerShell cmdletsUsing the web serviceWeb.configReferencesInitial variable declarationsPage loadRecipient listSchedule listReport listSubscription creationWeb application execution
Linking to reports with URL accessViewing a reportURL access on a native-mode report serverURL access on a SharePoint integrated-mode report serverUsing URL access parametersReport parametersHTML Viewer commandsReport Viewer commandsReport Server commandsUsing the ReportViewer controlReports Application projectReport WizardReportViewer controlReportViewer propertiesReportViewer applicationAccessing a report with the web serviceReferencesLoad methodRender methodSolution deployment

Content preview from Microsoft SQL Server 2012 Reporting Services

Chapter 8. Retrieving data for a report

Reports are powerful tools that help you visualize and share data in various ways. Therefore, the first step in creating a report is getting data! Reporting Services supports a number of different data sources you can use to easily create reports on your data, no matter how and where it’s stored. After you identify where the data is stored, you provide Reporting Services with the query to use to retrieve data from each source.

Understanding data source types

The first step to accessing your data for a report is to create a data source. A data source is fundamentally a connection string. The content of this connection string depends on the data provider that you use. For example, a connection string for a SQL ...