USING DASHBOARDS AND CHARTS IN SHAREPOINT SITE

This Post mainly Describes how to create sample Dashboard page and load sample charts (or) Excel Access work books in to Dashboard page.

Note 1:
Create kpis in analysis services cube and deploy them to analysis services server.For more information about how to create Kpis in analysis services Refer the following link.
http://www.databasejournal.com/features/mssql/article.php/10894_3604206_1
(Or)
Note 2:
If Note1 cant be followed then directly download analysis services database from following link.
http://www.4shared.com/file/49473200/78a82cda/Adventure_Works_DW.html
And restore downloaded database in analysis services server and proceed with this Document.

Here I am following Note 2

1. Open sharepoint site and select ReportsàView All Site ContentàCreate.
2. Select ReportLibrary under Libraries.
3. Specify name for the newly created ReportLibrary and click Create.
4. Select Site Actions à Create Dashboard.
5. Enter the DashBoard Name, Title and in DocumentLibrary DropDown List select newly created ReportLibrary name and leave rest of the fields Default.At bottom of the page under Key Performance Indicators Section select “Do not add a KPI list to this dashboard” option and click OK.
6. For viewing newly created DashBoard page, in left side menu list Under Dashboards Section select newly created Dashboard page name.
7. Open new Excel sheet and select Data tab at top of Excel sheet.
8. Under Get External Data select From Other Sources àFrom Analysis Services.
9. In the Data Connection wizard box Enter analysis server name and click Next.
10. Select analysis services Database and cube which contains kpis and click Next
Here I am selecting Adventure Works cube.
11. Enter any filename and click Finish.The odc file will store Defaultly in following location C:\Documents and Settings\ \My Documents\My Data Sources
12. Select “PivotChart and PivotTable Report” on the ImportData wizard box.
13. Click Properties Button on the Import Data wizard box and select Refresh Data When Opening the file check box and click OK.
14. Then Click OK.
15. In the Right side of Excel sheet Under Pivot Table Field List locate kpis
16. Here I am representing a sample kpi into chart Representation.for example Under Pivot Table Field List select KPIS à Financial Perspective àGrow Revenue àFinancial Variance.Here I am doing chart representation for Financial Variance KPI.
17. After locating Financial Variance kpi select the Value, Goal, Status, Trend attributes which is located under Financial Variance kpi.
18. The pivot chart contain graphical representation of kpi and pivot table contains the values in financial variance kpi.

Note:
We can also change chart type by Right Clicking on the chart which is inside the pivotix chart table and select Change Chart Type option and select the chart Template and click OK.

19. Then publish the created Excel workbook in to sharepoint site.
20. Click the Microsoft Office Button , point to Publish, and then click Excel Services under Distribute the document to other people.
21. In the File name box, enter the path to the server and the file name. For example, to save the file sampleworkbook1.xlsx to the sampleReportsLibrary in the Reports Center site, type: http://mossadmin:37064/personal/Reportcenter/sampleReportLibrary/ <> in the file name box.
22. Click the Excel Services Options button. Under show tab select “Items in Workbook” in Dropdownlist.It will display what are all the items present in Excel work book.here we are having pivot table and pivot chart on the workbook. Now I want to Display only chart in to my Dshboard page .Then select All charts option and click ok.
23. To verify that the viewable areas of the workbook appear correctly in the browser, select the Open in Excel Services check box.
24. Click Save.
25. In the Choose Document Type dialog box, in the Document Type list, select Report
. \click OK
26. The workbook has been published successfully to sample report library as shown in below snapshot.
27. Before loading charts in to Dash Boards please perform the following steps

Access the Central Administration console
· On the taskbar, click Start, point to All Programs, point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration.
Access the SSP administration console

1. From the Central Administration console, perform either of the following procedures:
2. Select the name of the SSP from the left-hand navigation bar. For example, SharedServices1 as shown in the following figure.

3. In the top navigation bar, click the Application Management tab.
a. On the Application Management page, click Create or configure this farm's shared services.
b. On the Create or Configure this Farm's Shared Services page, select the name of the SSP.

Enable external data access for a trusted file location

1. Launch the SSP administration console.
2. In the Excel Services Settings section, click Trusted file locations, as shown in the following figure.
3. On the Excel Services Trusted File Locations page, select the name of the trusted file location that you are attempting to load workbooks from, or click the Add Trusted File Location to create a new one, as shown in the following figure.
In the Address field we have to provide location where we want to store Excel workbooks.
For ex: http://mossadmin:37064/personal/Reportcenter/sampleReportLibrary/
4. On the details page for the trusted file location, scroll down to the External Data section
5. Select the Trusted data connection libraries only option button to allow access to connection files in a data connection library. Or, select the Trusted data connection libraries and embedded option button to enable connections embedded in a workbook and connections from a data connection library. This is also the location where you can toggle other external data settings that affect the server, such as refresh warnings, data cache time-outs, and so on.Leave rest of values as default.
Configure the unattended account settings
1. Launch the SSP administration console.
2. On the Shared Services home page, in the Excel Services Settings section, click Edit Excel Services settings, as shown in the following figure.

3. On the Edit Excel Services Settings page, scroll down to the Unattended Service Account section. Enter the domain user name and password of the account you want to use, as shown in the following figure.

4. Click OK.

Then follow below steps to load the chart into Dashboard page.
28. Open the newly created Dashboard page in left side menu of Dashboards section
29. Under Excel Web Access work book webpart on DashBoard select Click here to open the tool pane link.
30. Click the ellipses button next to the workbook textbox and select the published workbook from SampleReportLibrary and Click OK
31. Then Return to the Dashboard page and click Ok.
32.
a. If Excel Web Access work book webpart is present on Dashboard page then directly perform step 30 to 32.
(or)
b.Select SiteActionsàEditPage and click Add a Web Part and select Excel Web Access webpart from Add Web Parts – Webpage Dialog window and click Add.
Then perform steps 30 to 32.

Note:
For Displaying pivot table along with pivot chart we should follow one step. In Step 23 select All PivotTables check box and click ok and then continue from step 35.

33. In the Excel Web Access webpart on the tool bar next to View we can see both chart and PivotTable in the Dropdown.we can select either chart or pivot table to Display.

34. we can Design Dashboards according to our Requirement and also we can format Design according to our own customization.