1. Select location for your excel report, by this way, you can manage the report permission as the other documents
2. Access to the sheet, where you have the report. On the right hand, click on “Share” -> “Embed” (Or you can edit the workbook online, and click into File->Share)
Select the correct “What to show”
3. Adjust the “Appearance”, “Interaction”, “Dimensions” and copy the “Embed code”
4. Go to the page where you want to display the report. Add a content webpart with the embed code above
1. In the excel ribbon, select Data -> From Other Sources -> Select a fact table and all relation tables -> Finish the data connection wizard
2. Select “PivotTable Report” in “Import Data” dialog -> Click on “Properties”
3. Select “Refresh data when opening the file” and go to the definition tab -> checked on the “Save Password” and select “None” on “Authentication Settings…”
4. Select all the fields you need for the report
5. As I see on the image above, the selected fields are not in the correct area. I need to adjust them like this:
6. Yeah, look at the table, I have the total amount on each service activity in each separate month.
7. Rename the existing “Sum of Total Amount USD” to “Monthly”.
8. Drag the “Total Amount USD” to the value column for the accummulate. Then, update the “Value field settings” to make sure we have the right accummulate:
Now, I have Monthly value and Accummulate monthly on each month for each service activity
On the right, I have something called “Total Monthly”, mean “Yearly” in my report. I also have “Total Accummulate monthly” for the year.
What I need now is the percentage for the year as well for my report 🙂
9. Continue drag the “Total Amount USD” to “VALUES” area, change the name to “% Accummulate Monthly” and show value like this:
10. Perfect, now I have everything as the datasource for my report.
11. In the report, I want to show something like this:
12. So, I need to remove some of columns that not necessary for my report, in this case, they are “Month” and “Accumulate Monthly” (Or I should remove them at the beginning ?). Anyway, now I have the table like this:
13. Enter my mouse to inside the table, in the ribbon, click “Insert” -> PivotChart -> “Line” -> “Line with Markers”
Yeah, now I have a very nice chart like this:
14. But, wait, the percentage like seem not correct? Since we are missing the data series for it. Right click on the red line and select “Format Data Series…”
Select it like a secondary axis
Make my chart bigger and remove some buttons, move the legend to bottom.
Then I good to go 🙂
1. Open the excel file
2. On the ribbon, select “Data” -> “From Other Sources” -> “Connect to database server”
3. Select Database and Table
4. Save data connection file and finish
5. Update connection properties
6. Select pivot table fields and put them to correct areas
7. Remove subtotals on the field settings
8. Yeah, now you have a listing like this: