Sales Bookings vs Quota Dashboard for a B2B company:

Sales Team bookings vs quota dashboard
Standard

Business Goal:

Need a daily report delivered in sales team’s inbox that shows Sales Team’s Bookings vs Quota for current & next month.

Brief Description:

Ability to see Bookings vs Quota in near real-time is a key to effectively manage performance for any sales team. Before the project, analyst(s) would have to manually put together this report and since the report took more than a day to put together they couldn’t afford to run it daily and so they delivered this report bi-weekly/monthly basis to the sales team. After the project, the process was automated and the sales team received an email with a report on a daily basis and this helped them see Bookings vs Quota in near real-time. As a famous saying goes “if you can’t measure it, you can’t improve it” (by Peter Drucker) – in this case, the report helped them measure their actual numbers against their goals and helping them improve their sales numbers which directly hits their top-line!

Tools used: SharePoint report subscription, SQL server analysis services, SQL Server Integration services, SQL server reporting services & Excel.

Mockup:

Note: Drill down reports are not shown and the numbers are made up.

Sales Team bookings vs quota dashboard

SSRS in SharePoint mode: Data alert & Subscribe are grayed out or disabled.

Standard

Environment: SQL Server 2012 reporting services 2012 w/ SharePoint 2010 Enterprise.

Problem:

For SSRS reports hosted on SharePoint, the Data Alerts & Subscribe are grayed out or disabled.

data alert subscribe ssrs sharepoint 2012

What do you have to do?

You need to upload a SSRS and for the data sources, you’ll have to store the credentials or no credentials.

It’s not ideal for user-level security (e.g. via Windows Authentication) setup on the data source side but the requirement of data alerts and email subscription dictate that you need to stored the credentials or not have credential requirements.

If the New Data Alert option is grayed, the report data source is configured to use integrated security credentials or prompt for credentials. To make the New Data Alert option available, you must update the data source to use stored credentials or no credentials. – http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx

Real-world story:
what we ended up doing at a client’s was to create a SharePoint library for “report subscriptions” which is hidden from end-users. We added a service account to the data source & we store the credentials of the service in the report used for report subscription. And IT “controls” who receives the email. So after a user submits a request to get emails, IT goes in the security database & see’s if a user is fit to receive the email or not. So not all users may get approval to receive the email. That was a solution that we had to take to stay compliant.

How to embed or integrate Power View reports into SharePoint pages?

Standard

Why do you want to do that?

One of the common tactic that you can consider to drive adoption of a Business Intelligence system is to integrate/embed the BI reports to the APPS/SITE that the users are already using. Don’t make your users come to you, go to them! As a part of that, I figured out a way to integrate/embed Power View in a Site that was used by existing user base.

You can integrate/embed Power View reports in SharePoint web Parts. Here’s How:

Power View sharepoint integrate embedImage Credit & For step by step tutorial, please refer to: Integrate Power View with SharePoint using web parts

Note:

Environment: SQL Server 2012 in SharePoint Integrated mode w/ Power View Activated for the site. Also, SharePoint Enterprise 2010.

 

PASS BA VC EVENT: Integrating SSRS with SharePoint on Nov 25th, Monday:

Standard

Topic: Integrating SSRS with SharePoint

Register TODAY! URL: bit.ly/PASSBAVC

Speaker: Kevin Goff

Topic Abstract:

Have you ever seen a SharePoint site that integrates reports from SSRS, and wonder how all the pieces fit? If so, this session is for you. I’ll cover the necessary integration/configuration steps for integrating SSRS 2008R2/SSRS 2012 with SharePoint 2010/2013, as well as deploying reports to a SharePoint location. Because different versions of SSRS integrate differently with the available versions of SharePoint, I’ll provide a feature matrix for specific version integrations. I’ll also show how to integrate SSRS reports into SharePoint/PerformancePoint Services dashboard pages, as well as how to schedule reports for delivery to SharePoint document libraries. You’ll also see the improvements from Microsoft that make reporting against SharePoint lists much easier than before. At the end, I’ll show 2 new features in SSRS: Data Alerts in SharePoint, along with the new SSRS data visualization tool, Power View

I hope to see you there!
Paras | VP of Marketing | PASS BA VC

 

A Note about “Edit Report” button seen on Power View reports in SharePoint:

Standard

Power View reports that are hosted on SharePoint has “Edit report” which you cannot hide:

Power View Edit report buttonWe can’t hide this button but how do you give someone “read-only” access to Power View?

To do so, you’ll have to host the Power View report in a SharePoint library where a user has read-only access. When a user opens the report, he/she could click on edit report & make changes to Power View report but they won’t be able to save the report instead they’ll see an error:

Power View Access Denied

Official resource: http://office.microsoft.com/en-us/excel-help/power-view-in-sharepoint-server-create-save-and-print-reports-HA102834736.aspx

[update]:

Also, check out this MSDN forum thread, it describes a solution to hide the “toolbar”: http://social.technet.microsoft.com/Forums/sharepoint/en-US/cec0feff-1055-4b53-a5ae-00373abdabdd/how-to-disable-edit-report-option-in-powerview-report-in-sharepoint-2013?forum=sharepointdevelopment

Gallery: Visualizing report execution logs data to understand what’s up with slow running reports.

Standard

At my current project, We faced an issue of slow running reports. SSRS was on SharePoint integrated mode. So before a meeting was scheduled between SharePoint Team & Report Development Team, I created a Data Visualization using SSRS Report Execution Logs like the one shown below so that we all had a starting point to discuss possible troubleshooting tasks for our team:

Here’s the data visualization:

Report Execution Logs SSRS data

can you guess – which Team took action items from the meeting? 🙂

Microsoft Business Intelligence: Power View can be Exported to PowerPoint (PPT)!

Standard

A short post to point out that Power View reports can be exported to PowerPoint (PPT) – and PPT slides would also have a “click to interact” button and if the security plus network access is configured correctly then the Interactive data exploration without leaving the Slides! very cool. I had pointed this fact out earlier here. And the official resource can be found here: Export a Power View Report to PowerPoint

I am referring to SQL Server 2012 BI and SharePoint 2010 here. And here is a  step by step guide:

1) Create report > Save it > And then Go to File > Export to Power Point

Export Power View reports to PowerPoint 2010 SQL Server 2012

A note about security: “Export to PowerPoint” requires windows authentication method.

2) Select the location of the PPT file that will have the “Power View” reports.

Export to PowerPoint requires windows authentication method File Location

Now you can store the PowerPoint file at any place but it would be important to consider whether the machine from which it would be accessed has the network access to the SharePoint Power View reports. If not, the Power View reports would just show up as “static images” and the click to interact would not work.  Important security point to consider for your scenario.

3) Open the File > start slideshow > Navigate to the slide (if applicable) > can you see the “click to Interact” button? Yes? Great! you can do interactive data exploration from the PowerPoint environment itself! I find this very Impressive because Power View is meant for Business Users to do Data Exploration and create rich visualizations and once they are done with it – they can export it to PowerPoint and show their Power View chops to their boss and peers and more importantly, make better business decisions. Any-how, here’s the image:

CLICK to Interact button Export to PowerPoint Powe View SQL Server 2012 SharePoint 2010

That’s about it!

How I solved sharepoint’s service unavailable error, HTTP 503?

Standard

I have Sharepoint installed on one of my VM and I needed to open Sharepoint central administration for some reason. BTW, if you are into Sharepoint BI – there’s a little chance that you may run into this error and the error says “Service unavailable. HTTP error 503”. And after quick searches on Internet, figured out how to solve the problem. It turns out that I had changed the password of the Identity. It was my demo machine and I was running sharepoint using the domain’s administrative account. Don’t hit me! I am learning..Kidding. Any-who, Here are the steps:

Go to IIS Manager > Application pools > Locate sharepoint central admin that stopped > right click > Advanced settings > reset password.

Here’s a quick video. I am sorry for poor audio, Next time I’ll use a better tool: