SQL Server 2012 SSRS Samples November 13, 2013
SQL Server 2012 SSRS report samples.
SQL Server 2012 SSRS drill through samples.
SSRS November 12, 2013
We can implement 2 forms of caching in Reporting Services: temporary cached reports and report snapshots.
A subscription definition consists of the following parts:
- A pointer to a report that can run unattended (that is, a report that uses stored credentials or no credentials).
- A delivery method (for example, e-mail) and settings for the mode of delivery (such as an e-mail address).
- A rendering extension to present the report in a specific format.
- Conditions for processing the subscription, which is expressed as an event.Usually, the conditions for running a report are time-based. For example, you may want to run a particular report every Tuesday at 3:00 P.M. UTC. However, if the report runs as a snapshot, you can specify that the subscription runs whenever the snapshot is refreshed.
- Parameters used when running the report.Parameters are optional and are specified only for reports that accept parameter values. Because a subscription is typically user-owned, the parameter values that are specified vary from subscription to subscription. For example, sales managers for different divisions will use parameters that return data for their division. All parameters must have a value explicitly defined, or have a valid default value.
The null delivery provider is a highly specialized delivery extension that is used to preload a cache with ready-to-view parameterized reports This method is not available to users in individual subscriptions. Null delivery is used by administrators in data-driven subscriptions to improve report server performance by preloading the cache.
Report Manager is used only for a report server that runs in native mode. It is not supported for a report server that you configure for SharePoint integrated mode.
The following features behave differently on a report server that is configured for SharePoint integrated mode when compared to a report server in native mode:
- URL addressing is different in SharePoint integrated mode. SharePoint URLs are used to reference reports, report models, shared data sources, and resources. The report server folder hierarchy is not used. If you have custom applications that rely on URL access as supported on a native mode report server, that functionality will no longer work when the report server is configured for SharePoint integration.
The following features are not available on a report server that runs in SharePoint integrated mode:
- Reporting Services custom security extensions cannot be deployed or used on the report server. The report server includes a special-purpose security extension that is used whenever you configure a report server to run in SharePoint integrated mode. This security extension is an internal component, and it is required for integrated operations.
- Report Manager cannot be used to manage a report server instance that is configured for SharePoint integration.
- Linked reports are not supported.
- My Reports is not supported.
- My Subscriptions
- Batching methods are not supported.
Reporting Services tutorials.
Excel 2013 tutorial for Power View. The Data Model is transparent in Excel, but you can view and modify it directly using the Power Pivot add-in. The Data Model is discussed in more detail later in this tutorial.
Various types of connection for data model for Power View.
SQL Server Data Tools (SSDT) is a Microsoft Visual Studio environment with enhancements that are specific to business intelligence solutions. SQL Server Data Tools (SSDT) is included with SQL Server 2012.
Use SQL Server Data Tools for creating and managing solutions and projects for Reporting Services reports and report-related items. SQL Server Data Tools provides the Report Designer authoring environment. In Report Designer, you can open, modify, preview, save, and deploy report definitions, shared data sources, shared datasets, and report parts.
Report Designer is available within SQL Server Data Tools (SSDT). The design surface in the tool includes tabbed windows, wizards, and menus used to access report authoring features. The report designer tool becomes available when you choose a Report Server Project or a Report Server Wizard template. To learn more, see Reporting Services in SQL Server Data Tools (SSRS).
SQL Server Data Tools (SSDT) transforms database development by introducing a ubiquitous, declarative model that spans all the phases of database development and maintenance/update inside Visual Studio. You can use SSDT Transact-SQL design capabilities to build, debug, maintain, and refactor databases. You can work with a database project, or directly with a connected database instance on or off-premise.
The following topics and sections discuss how SSDT can help you do database development. How To topics are included to help guide you through completing tasks for your database project. These tasks, written like a tutorial and completed in order, use Northwind Traders, a fictitious company that imports and exports specialty foods.
SSRS Cmdlet Jumpoff May 14, 2013
SharePoint 2013 and SQL Server 2012 PowerPivot April 13, 2013
Very good on architecture and trouble shooting of that architecture. All new now with Excel Services taking the main role. Use spPowerPivot.msi to install the correct data providers on SharePoint Servers.
And the following is very important for the security planning.
The data providers establish direct TCP/IP connections to the data models on the SSAS server. This direct approach helps to minimize latencies in the client/server communication, which ultimately provides best performance, but it also requires the security accounts of the client processes to have SSAS administrator permissions. Only SSAS administrators can load data models on an Analysis Services instance running in SharePoint mode.
And the following for architecture and troubleshooting. Notice that SSRS is installed on the SharePoint Application Server.
and then install and configure SharePoint 2013 and Reporting Services Power View on a separate application server. Verify that Excel Services can load data models and then try to create a Power View report on top of a workbook. If all goes as advertised, then you should be able to interact with the workbook in the browser, but Power View won’t be able to create a report. Now, don’t jump to conclusions. Leave the Reporting Services configuration alone for a moment. Let’s first double-check the dependencies for workbooks as a data source by running some more or less straightforward PowerShell scripts. Start SharePoint 2013 Management Shell with elevated permissions and then copy and paste the following lines:
Is at least one SSAS server registered in Excel Services?
Is the correct data provider version installed?
Can you connect to the SSAS server directly?
Can you connect to a workbook in the local farm?
Is the SPClient assembly installed on the SharePoint server?
And there you have it, SPClient is missing! Apparently, I forgot to install the data providers via spPowerPivot.msi on this computer. I installed Reporting Services using SQL Setup, but in SQL Server 2012 SP1 CTP3, the Reporting Services options do not install the SPClient assembly. This is fixed in later builds. For CTP3, downloading and installing spPowerPivot.msi on the SharePoint server solves this problem. Note that you don’t need to run the PowerPivot Configuration Tool. Simply install spPowerPivot.msi to fully deploy the data providers included in the installer package, as illustrated in the following screenshot.
PowerPivot for SharePoint 2013 is a Microsoft Windows Installer package (spPowerpivot.msi) that deploys Analysis Services client libraries and copies PowerPivot for SharePoint 2013 installation files to the computer. The installer does not deploy or configure PowerPivot features in SharePoint. The following components install by default:
- PowerPivot for SharePoint 2013. This component includes PowerShell scripts (.ps1 files), SharePoint solution packages (.wsp), and the PowerPivot for SharePoint 2013 configuration tool to deploy PowerPivot in a SharePoint 2013 farm.
- Microsoft OLE DB Provider for Analysis Services (MSOLAP).
- ADOMD.NET data provider.
- SQL Server 2012 Analysis Management Objects.
Configure PowerPivot and Deploy Solutions (SQL Server 2012 SP1)
This topics describes the deployment and configuration of middle-tier enhancements to the PowerPivot features in SharePoint 2013 including PowerPivot Gallery, Schedule data refresh, Management Dashboard, and data providers. Run PowerPivot for SharePoint 2013 Configuration tool to complete the following:
- Deploy SharePoint solution files.
- Create a PowerPivot service application.
- Configure an Excel Services Application to use an Analysis Services server in SharePoint mode. For information on backend services and installing a Analysis Services server in SharePoint mode, see Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).
Install or Uninstall the PowerPivot for SharePoint Add-in
A recommended best practice is to install spPowerPivot.msi on all servers in the SharePoint farm for configuration consistency, including Application servers and web-front end servers. The installer package includes the Analysis Services data providers as well as the PowerPivot for SharePoint 2013 configuration tool. When you install spPowerPivot.msi you can customize the installation by excluding individual components.
Data providers: Several SharePoint and SQL Server technologies use the Analysis Services data providers including Excel Services, PerformancePoint Services, and Power View. Installing spPowerPivot.msi on all SharePoint servers ensures the full set of Analysis Services data providers and PowerPivot connectivity is consistently available across the farm.
SharePoint 2013 and SQL Server 2012 SP1 Installation April 12, 2013
What’s New in SQL Server 2012 SP1 Installation for Business Intelligence
- PowerPivot for SharePoint: Analysis Services provides a backend service for Excel Services to load, query, and refresh PowerPivot data models so that users can interact with Excel workbooks that contain data models in the browser. Analysis Services in SharePoint mode is fully independent of and external to SharePoint, although the server running Analysis Services must be on the same network and Active Directory Domains Services (AD DS) forest as the SharePoint farm.You install and manage Analysis Services using SQL Server installation media and tools. Choose the setup option PowerPivot for SharePoint. After you install Analysis Services in SharePoint mode, the only additional configuration tasks are to grant the SharePoint services accounts server administrator permissions in Analysis Services and to configure Excel Services to point to the Analysis Services instance.For detailed steps on installing a single server, see. Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).
- spPowerpivot.msi: A Windows Installer package available with the SQL Server 2012 SP1 Feature Pack. The installer enhances the PowerPivot for SharePoint experience with more features such as PowerPivot Gallery and Schedule Data Refresh. The .msi deploys Analysis Services client libraries, the PowerPivot for SharePoint 2013 Configuration tool, and copies PowerPivot for SharePoint 2013 installation files to SharePoint servers.After you run the .msi, you need to run PowerPivot for SharePoint 2013 Configuration to deploy the solutions and configure the farm. For more information see:
- Reporting Services: The overall Reporting Services installation remains the same with SQL Server 2012 SP1 as it was with SQL Server 2012. There is an updated Reporting Services for SharePoint add-in that supports SharePoint 2013. For more information, see Supported Combinations of SharePoint and Reporting Services Components.
Overview of Installation with SharePoint 2013
- Install SharePoint Server 2013 and enable Excel Services.
- Install Analysis Services in SharePoint mode, and grant the SharePoint farm and services accounts server administrator rights in Analysis Services.For more information, see Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).
- Configure Excel Services in SharePoint Server 2013 to use the Analysis Services instance you installed in step 2.See the section “Configure Basic Analysis Services SharePoint Integration” in Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).
- Install Reporting Services in SharePoint mode and the Reporting Services add-in for SharePoint products.For more information, see Install Reporting Services SharePoint Mode for SharePoint 2013.
- Configure the Reporting Services SharePoint service and at least one Reporting Services service application.For more information, see the section “Create a Reporting Services Service Application” in Install Reporting Services SharePoint Mode for SharePoint 2013.
|If you want to use both Analysis Services and Reporting Services, run SQL Server Installation Wizard twice because the PowerPivot for SharePoint option does not offer choices on the Setup Role page.|
The SQL Server setup option PowerPivot for SharePoint has no dependencies on SharePoint. It does not use the SharePoint object model or interfaces to support integration. Therefore, Analysis Services can be installed on any computer running Windows Server 2008 R2 or later version. It can be but does not have to be an application server in a SharePoint farm. One of the configuration steps is to point Excel Services to the server running Analysis Services. For load balancing and fault tolerance, it is recommended to install and register multiple Analysis Services servers running in SharePoint mode.
PowerPivot for SharePoint 2013 and Reporting Services in SharePoint mode Single Server Deployment
A single server deployment is useful for testing purposes but it is not recommended for production deployments.
|(1)||Excel Service Application. The service application is created as part of the SharePoint installation.|
|(2)||PowerPivot Service Application. Default name is Default PowerPivot Service Application.|
|(3)||Reporting Services service application.|
|(4)||Install the reporting services add-in for SharePoint from either the SQL Server 2012 SP1 installation media or the SQL Server 2012 SP1 feature pack.|
|(5)||The SharePoint content, configuration, and service application databases.|
|(6)||Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.|
Follow the remaining prompts to complete the installation. After the installation completed, resist the urge to run either of the PowerPivot configuration tools. These are used if we actually ARE using full PowerPivot (see below), and that will require a SharePoint server. If you run it on a non SP farm machine, things can get very messed up.
Once that is set up, you need to tell Excel services to use it. Navigate to your Excel Services service application in Central Administration, and select “Data Model Settings”.
Now that the engine is baked into Excel, is there any need for PowerPivot any longer? As we’ve seen, for simple analysis, no. However, if we want to do anything advanced, like filtering import data, modifying the model, creating complex relationships, or using DAX (the tabular answer to MDX), the answer is emphatically yes.
On the client side, PowerPivot is actually included with Excel – no download required anymore. It is disabled by default, and is enabled through the Excel Com Add in interface. You can view this as your individual or team model designer. With it you can create complex models and then share them out via SharePoint. Optionally these very same models can be imported into Analysis Services projects if/when they become mission critical, or too large. Another nice thing is that models created with PowerPivot can be shared on a SharePoint farm that is not using the full PowerPivot for SharePoint (but does need “Analysis Services in SharePoint mode – confused yet?).
On the Server side, we can install PowerPivot for SharePoint, just as we did with SharePoint 2010. The reason that we would want to do this is to gain access to PP for SP features like the Pivot Gallery, but primarily to access refresh capabilities. In the scenarios that I’ve described above, using Excel Services, the data that we’re using is relatively static. Data is imported when creating the model, and is used for analysis, but there is no mechanism to refresh the data in the model in the way that Analysis Services can. PowerPivot for SharePoint offers this capability in SharePoint 2010, and this carries forward with SharePoint 2013.
Another thing that you should note is that the order of operations is important here. If you install Reporting Services – SharePoint prior to installing SharePoint on the farm, the option to create a Reporting Services application will not appear. That’s because it won’t be registered with the farm as a service application. If this happens, you can run the following PowerShell to register the Service Application
The reason that I have installed both the 2008 R2 versions and the 2012 versions is that some components in SharePoint 2013 seems to need the 2008 R2 versions while others use the 2012 versions.
The reason for this is that I now want to run a installation of SQL a second time this time I choose to add features to a existing instance
This time I add Reporting Services in SharePoint integrated mode and the Reporting Services Add-in for SharePoint Products. The reason that I am doing this in a separate installation is that I had some issues with SSRS not showing up in Central Admin when I added this in the first installation. This could probably have been solved in some other way but I wanted to ensure that the install went through without any glitches.
After the installation has finished it is time to install PowerPivot for SharePoint. So I start the installation of SQL Server 2012 SP 1 Preview a third time. This time I choose to add a new instance
After the installation has finished it is time to run the configuration for PowerPivot for SharePoint. You can see that there are two different configuration tools one for SharePoint 2010 and one for SharePoint 2013. Since I am using 2013 I start that tool.
Now it’s time to install SQL Server 2012. Note that Service Pack 1 for SQL Server 2012 is a requirement for PowerPivot for SharePoint. If you do not install it you will get an error on the “SharePoint installation requirement for PowerPivot for SharePoint” rule when you try to install PowerPivot for SharePoint.
My previous blog post, “Verifying the Excel Services Configuration for PowerPivot in SharePoint 2013,” discussed steps to verify that Excel Services can load workbook data models on an out-of-farm PowerPivot server running SQL Server 2012 SP1 CTP3 Analysis Services (SSAS) in SharePoint deployment mode so that users can enjoy full workbook interactivity in the browser. But data exploration in the browser is only the first step. The next logical step is to consume the data model as a data source in PerformancePoint Services, Power View, or in a custom solution running inside the farm. Let’s take a look at an architecture diagram to understand how this scenario works in SharePoint 2013.
And there you have it, SPClient is missing! Apparently, I forgot to install the data providers via spPowerPivot.msi on this computer. I installed Reporting Services using SQL Setup, but in SQL Server 2012 SP1 CTP3, the Reporting Services options do not install the SPClient assembly. This is fixed in later builds. For CTP3, downloading and installing spPowerPivot.msi on the SharePoint server solves this problem. Note that you don’t need to run the PowerPivot Configuration Tool. Simply install spPowerPivot.msi to fully deploy the data providers included in the installer package, as illustrated in the following screenshot. Rerunning the PowerShell script now shows that the SPClient assembly is present. Of course, you can also check Programs and Features in Control Panel to see if the spPowerPivot.msi package is installed.
SharePoint BI Setup September 9, 2012
Links to setup Performance Point.
SQL Server 2012 SSRS SharePoint integrated mode.
Reporting Services SQL Server 2012 jump off.
SQL Server 2012 and SSRS June 14, 2012
SQL Server 2012 Reporting Services Tips, Tricks, and Troubleshooting
Went to test the Data Source link and got the error Cannot convert claims identity to windows token. Started digging around on the web and found numerous articles and blog posts about making sure the Claims to Windows Token Service (C2WTS) is set up and running in SharePoint. We had set this up for performance point in our production environment but not in our test environment. Got out the notes and set up a new service account in test to run the service. Made sure it was given the correct rights on the SSRS server of act like a part of the OS and able to impersonate another identity. Set up the delegation to the SQL server we were trying to hit and started the service. Still got the error Cannot convert claims identity to windows token. Went through this video and tried to verify we had everything set up correctly. Got Microsoft Support on the case and they started looking at the issues as well. I finally found this blog and started focusing on the bz71 error which I found. Tying the bz71 error to the Process ID I noticed that the User Account running the process was the SSRS Service Account. I thought the process of converting a claims identity to a windows token would be handled by the user running the C2WTS and the account associated with that service. It appeared that the process doing that conversion or at least the process throwing the error is actually the service account running the SSRS Service Application. I then set the permissions for the SSRS Service account in the local policy on the SSRS Server to be the same as the C2WTS Account. I restarted the Service Applications via central admin and things started working.
SSRS Sharepoint List Reporting March 23, 2011
Native List Reporting
SQL Server 2008 R2 Reporting Services now supports SharePoint lists as a data source. This support allows you to retrieve list data from SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007. The ability to access list data is not reliant on the add-in or running Report Server in Native or SharePoint Integrated mode. The functionality is built into Report Server. What changes in the different configurations is the method of access.
There are two methods by which SharePoint list data is accessed. One is via the lists.asmx web service and the other is via the SharePoint object model APIs. On any SharePoint installation, if you enter the URL http://<sharepoint_server_name>\lists.asmx, you’ll get an XML list of all the lists on the SharePoint site that you’re able to access. By using this method, Report Builder 3.0 is able to retrieve the lists. A Report Server configured in Native mode also uses this method.
The SharePoint object model API method can be used in two scenarios. One is where a Report Server is configured in SharePoint Integration mode and the list exists in the same SharePoint farm Reporting Services is integrated with, and this is all on the same machine; remember that in this scenario there is a copy of SharePoint running on the Report Server that gives it access to the API set. The other scenario is where you have SharePoint 2010 installed along with the add-in, but you have no Report Server. This is called local mode and is covered later in the section “Reporting Without Reporting Services.”
To use data obtained from a SharePoint list within a report first requires you create a data source, then a dataset that uses that data source. In Report Builder 3.0, there is a new connection type on the Data Source properties page called Microsoft SharePoint List, as shown in Figure 3. Along with this option, you enter the URL of your SharePoint site—no need to add lists.asmx to the URL. The data source can also be configured with different credentials to use when accessing the SharePoint server.
SSRS Host-Header And Kerberos April 21, 2010
HTTP is the service class. The Report Server Web service runs in HTTP.SYS. A by-product of creating an SPN for HTTP is that all Web applications on the same computer that run in HTTP.SYS (including applications hosted in IIS) will be granted tickets based on the domain user account. If those services run under a different account, the authentication requests will fail. To avoid this problem, be sure to configure all HTTP applications to run under the same account, or consider creating host headers for each application and then creating separate SPNs for each host header. When you configure host headers, DNS changes are required regardless of the Reporting Services configuration.
The values that you specify for <computername>, <domainname>, and <port> identify the unique network address of the computer that hosts the report server. This can be a local host name or a fully qualified domain name (FQDN). If you only have one domain and are using port 80, you can omit <domainname> and <port> from your command line. <domain-user-account> is the user account under which the Report Server service runs and for which the SPN must be registered.
How to: Configure Reporting Services to Use a Non-Default Web Site (Reporting Services Configuration)