By default Count is selected, change it to Sum and click OK
Now we see the table and the chart correctly
Now the next job is to Add a Slicer that will filter the chart and table based on the number of employees. Select the Chart by clicking it once and then Click Insert and select slicer
Select “numberofemployees” and click OK
You will now be able to see some meaning ful report
Now the real hero comes into picture, the Excel Services. Click on the file>Save & Send> Save to SharePoint > double click on “Browser for a location” and locate the SharePoint document library location where the Excel report will be saved
Navigate to shared document library
As soon as you save you see the report in the browser
Verify if its working by selecting the “number of employee” slicer
And yes it does J
Ok what happens when the CRM data changes ? Try it – Open an CRM account and change the revenue value , I did for “A store (sample)” frm 10000, to 12000.
Now go back to the excel service report and click Data> refersh all connection
And see the smooth update of data
And this way you can hopefully build complex and more meaning ful reports and publish it in SharePoint.
Enhance this ?
If you want to show/display the excel workbook anywhere in SharePoint, you can use the Built in “Excel Web Access web part”
Open your SharePoint site and edit the page and add a webpart “Excel Web Access”
Click Add and see the web part added. Now open web part properties
You will see the properties
In the workbook Box enter the excel work book location, in my case – \crmrpt/shareddocument/CRM report.xlsx. Click Apply.
Further we can just have the chart shown in the site
Change the web part property, Enter Named Item to Chart 1 (This is the chart object’s name), if you want to verify open excel and see the chart property
And It just shows the chart on the home page,
To get the chart name see in the Excel, click on the chart object to select it and see the name in ribbon.
Further Enhance this using Connected Web parts
If you have several reports in the library AND you want to just have click and see behavior we can create connections between web parts to have that kind of experience. Just ensure that you have multiple reports in the library.
Edit the home page, Add the library that has Excel Reports in it AND add the Excel Web Access webpart on that same page, Configure the Excel Web access to show a report (this would be the default report that it will show). Now click on the menu of the Library >Connections>Send Row of Data To > Excel Web Access Web part.
In the following popup menu
Got to Tab 2. Configure Connection> and set the Field name to Document URL> and Finish. Now save and Close the page.
See it in action, by selecting the report
With that I will come back with the 3rd and the 4th part soon. Thanks for reading.