Self Service BI in SharePoint 2010: Part 4 Reporting Services

This is part 4 of the 4 part blog series based on session by Peter Myers in TechEd 2011 . You can find the slides here. I have taken the key learning from the presentation for my future reference. For Part 1 or Part 2 or Part 3 go here or here or here

Reporting Services

SQL Reporting Services can be integrated with SharePoint 2010 with Web Parts. Report Builder 3.0 is a rich client authoring tool that can be installed right from SharePoint. Reports can be saved in SharePoint library and the management and delivery functions of Reporting Services is available within the SharePoint interface. The advantages of using SharePoint deployment in Reporting Services is that it enhances team collaboration and versioning of reports.

Report Builder 3.0 Interface:

Insert a Map:

Integrated with Bing Map:

Use existing queries to pull the data in for the report.

You can create relationships between spatial and analytic data: In the below example map STATENAME to State:

Note the mapping:

Apply Data Visualization: Lower the sales, lighter the color; Greater the sales, darker the color.

Overlay the state code if needed:

Create Tooltips using the expression builder so that when the user hovers over the state, the tooltip will display the state and the number:

When you run the report, note the Time Filter you got by just checking the parameter checkbox:

Result:

Note the tool tip:

Save the Report to the Report Library:

Back in the SharePoint, the report is visible:

This report could also be added to the Presentation Point Service Dashboard and integrated with KPIs.

Self Service BI in SharePoint 2010: Part 3 Visio Services

This is part 3 of the 4 part blog series based on session by Peter Myers in TechEd 2011 . You can find the slides here. I have taken the key learning from the presentation for my future reference. For Part 1 or Part 2 go here or here

Visio Services

With SharePoint 2010 Visio Services, you can now render Visio diagrams within a Browser. Visio diagrams can be based on data sources and can be made interactive. Data Sources can be SQL Server, SharePoint List Data, Excel Services and OLE DB/ODBC sources. Use the Visio Web Parts to embed diagrams in SharePoint.

Note the Visio file with showing Servers of Contoso:

The visio diagram will get its data from a SharePoint List:

Visio Shapes can have data assigned to them:

Data tab contains the Link Data to Shape:

Select Microsoft SharePoint Foundation List:

Choose the list:

Data is imported into the diagram:

Data can be automatically linked to Shapes:

Use the dialog to map the data column to the shape field:

Next bring the diagram to life with the help of Data Graphics which is a collection of items:

Create a New Graphic Item to Display the Server:

Now Select the Shapes where you want to apply the new graphic item. Note that the header picks up the data from the SharePoint List we had imported:

Create a new graphic item for the status of the servers: online or offline

All the servers show green because the status is green:

Publish to SharePoint:

Choose Web Drawing:

Upload the Visio diagram:

Visio services open the document in the browser:

If you change the status of the server, visio services picks it up:

Server is offline:

Self Service BI in SharePoint 2010: Part 2: Performance Point Services

This is part 2 of the 3 part blog series based on session by Peter Myers in TechEd 2011 . You can find the slides here. I have taken the key learning from the presentation for my future reference. For Part 1, go here

Performance Point Services (PPS)
Performance Point Services (PPS) is a performance management application where you define rich KPI and Score Card definitions to support monitoring. It includes comprehensive report types to support analysis and delivers interactive browser based dashboard all within the SharePoint. The picture below shows the key elements of PPS and how they are related.

Start by going to the Performance Point Content Library in SharePoint:

PPS comes with a Dashboard Designer tool:

See Data Connections:

Create new Scorecard:

Choose a template:

You are presented with a Wizard to create a KPI. You can import the KPIs from an excel workbook which is part of the Excel Reports Library in SharePoint.

Scorecard is created:

A KPI consists of 3 main things: Value, Goal & Status, Trend.

The Value of the KPI is got by using the formula: KPIValue(“Sales Performance”).
The threshold for the goals can be set as follows:

Likewise trends can be defined as below.

Once you save the dashboard, the KPI and the Scorecard will be visible in SharePoint

Back in the PPS Designer, note that the dimensions of the cube is visible which can be added to the Scorecard:

The Scorecard is updated with the new dimension

Analytic capabilities of PPS is handled by Reports:

Choose the report template:

Create the Report:

Next define a filter:

Choose the type of filter:

Select the Members for the Filter:

Select the dimension:

You can choose to display the filter as a List, Tree or Multi-Select and save the filter:

You can create an Excel Services Report:

Enter the Report Settings:

Note the Year Parameter that was defined on the Fiscal Date Hierarchy will show:

Now it is time to bring all of the above in a Dashboard; so start by creating a new Dashboard:

Drag and Drop the filter, Scorecard and Report on the dashboard. Filters can pass values to Scorecards and Reports and Scorecards can pass values to Reports. Reports can only consume from either scorecards or filters.

Drag and Drop the filter on the Scorecard and the report to make a connection between the items of the dashboard:

Connect the Scorecard to the Report by pass the Sales Person Name (just drag the Member Unique Name onto the Report to make the connection between items of the dashboard):

Finally Deploy to SharePoint:

Dashboard View in the SharePoint:

Note Selection of North America from the hierarchy in the Scorecard changes the Salespeople Report:

Note the ability to drill across dimensions:

You can choose other measures available in the cube:

See details behind aggregated numbers:

Note the details ability to export to Excel:

Create graphs and analyze the data in decomposition tree:

Note the decomposition tree. The Decomposition tree is rendered through Silverlight:

Excel can now be viewed directly within the browser with the help of Excel Services. The below Excel Sheet connects to Analysis Services:

Coming up next is Vision Services. I will write that in my next blog post.