Environment: SQL Server 2012 reporting services 2012 w/ SharePoint 2010 Enterprise.
For SSRS reports hosted on SharePoint, the Data Alerts & Subscribe are grayed out or disabled.
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
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.
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:
Image Credit & For step by step tutorial, please refer to: Integrate Power View with SharePoint using web parts
Environment: SQL Server 2012 in SharePoint Integrated mode w/ Power View Activated for the site. Also, SharePoint Enterprise 2010.
Topic: Integrating SSRS with SharePoint
Speaker: Kevin Goff
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
Power View reports that are hosted on SharePoint has “Edit report” which you cannot hide:
We 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:
Official resource: http://office.microsoft.com/en-us/excel-help/power-view-in-sharepoint-server-create-save-and-print-reports-HA102834736.aspx
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
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:
can you guess – which Team took action items from the meeting? :)
I found a great sample Dashboard on Social Media Analytics in Excel that is powered by PowerPivot. Here’s the screenshot of the Dashboard.
Here are the steps if you want to download and play with the Dashboard:
- Install Power Pivot add-in
- Download the “Analytics for Twitter” excel sample (powered by PowerPivot). Link: http://www.microsoft.com/en-us/download/details.aspx?id=26213
- It creates an “Analytics for Twitter” excel file on Desktop > Open it.
- The dashboard is powered by data it pulls in the Power Pivot:
- You can change the search queries:
a. Edit the default search terms:
b. Refresh Data:
c. Updated Dashboard!
That’s about it. And here’s a Youtube Video showing some features in this sample:
In this blog-post, I shared a great sample dashboard built on top of PowerPivot model.
One of the common task while creating the Performance Point services Dashboard is to customize the Title and Logo of the Dashboard. In this blog post, we would see how you can do that in Performance Point 2010
2. Click on Site Actions > Site settings > Look and Feel > Title, Description and icon
3. You’ll should see
4) Now if you do not have the URL of the Image. Here’s what you could do:
Go to Libraries > Images.
5) Now, upload an Image and note down the URL. And Paste the URL in Step 3. If you are using a theme then you can change the logo image from their too. For this blog post, I thought I would keep it simple.
After you have entered the Title and Logo URL, you should see:
In this blog post, we saw how to change the Logo and Title of the Performance Point Services Dashboard.
I 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
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
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.
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
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.
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:
That’s about it!