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

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?

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:

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:

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.

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? :)

A Social Media Analytics Sample Dashboard in Excel Powered by PowerPivot.

I found a great sample Dashboard on Social Media Analytics in Excel that is powered by PowerPivot. Here’s the screenshot of the Dashboard.

excel powerpivot twitter social media analytics dashboard 1

Here are the steps if you want to download and play with the Dashboard:

  1. Install Power Pivot add-in
  2. Download the “Analytics for Twitter” excel sample (powered by PowerPivot). Link: http://www.microsoft.com/en-us/download/details.aspx?id=26213
  3. It creates an “Analytics for Twitter” excel file on Desktop > Open it.
  4. The dashboard is powered by data it pulls in the Power Pivot:excel powerpivot twitter social media analytics dashboard
  5. You can change the search queries:
    a. Edit the default search terms:excel powerpivot twitter social media analytics dashboard
    b. Refresh Data:excel powerpivot refresh data
    c. Updated Dashboard!excel powerpivot twitter social media analytics dashboard

That’s about it. And here’s a Youtube Video showing some features in this sample:

Conclusion:
In this blog-post, I shared a great sample dashboard built on top of PowerPivot model.

 

PowerPivot: Quick Tip regarding Copy-Pasting Tables in PowerPivot 2010

Power Pivot Paras Doshi Microsoft Business Intelligence Excel 2010I was working on couple of PowerPivot models recently and both models needed a Date Table. So here’s what I did:

1. Imported the filtered version of the Date Table (DateStream) from Azure Data Market. Added Calculated Columns to the table. Let me call this Table1

2. I copied entire Table1 and pasted it to an another PowerPivot model – let’s call that Table2

 

After this:

And I went to happily create data models! Later, I while testing the model (that had Table2), I realized that it had lost the “formulas” – in other words when I copy-pasted from Table1 to Table2 in PowerPivot – it did not copy-paste the formulas but it just pasted the values.

So, 1. Copy Pasting a Table in a PowerPivot does not copy-paste’s the Formula’s in the columns.

And also it does not keep the “Data connection”. In this case, Table1 was connected to Azure Data source and I could “refresh” data if I chose to. But Table2 was “Static” – In other words, It lost the data connection with the data sources. So I could not “refresh/update” data from the data source if need be.

S0, 2. If you copy Paste a Table in a PowerPivot, the newly copied table does not keep the “Data connection” to data source.

 

I have also posted this here: http://beyondrelational.com/modules/1/justlearned/tips/17976/power-pivot-copy-paste-a-table-in-a-powerpivot-does-not-copy-pastes-the-formulas.aspx

“An unexpected system error occured…” – While trying to establish a Data Connection in Performance Point 2010 Dashboard Designer

So I got an error while trying to set up a Data Connection via Performance Point 2010 Dashboard Designer: “An unexpected system error has occurred . additional details have been logged for your administrator” – and so I did quick searches and read this & this & this & this & this and with the help of the links I solved the error. Now, there are lots of moving parts but for machine I was able to solve the error by:

1. SharePoint 2010 Central Administration

2. Manage Services on Server

3. Stopping “PerformancePoint Service”

4. And then Starting it Again.

5. If you are still facing issues then consider re-booting the server. In my case, it was a demo machine and so I quickly rebooted it.

Simple? Yeah but I spent 30 odd mins trying to figure out how to solve this error and so I thought I would document this.

 

Works? Yes? Great! No? Try:

-> Configure secure service store and unattended service account.

-> you can check out the links that I added earlier.

 

Before I got the error – I had

1. Successfully Added “PerformancePoint Service Application” via “Manage Service Applications”

2. I had made sure that the PerformancePoint Service and the Secure Store Service were Started.

3. Created a Business Intelligence Center Site and checked the Site Collection Features were properly configured.

But I got an error while trying to create a Data Connection. And I solved it! And Now can spend time creating few reports! That’s about it for this post.

performance point 2010 sharepoint business intelligence microsoft

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

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!