Display Excel chart in SharePoint online

1. Select location for your excel report, by this way, you can manage the report permission as the other documents

ReportLocation

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)

EmbedMenu

Select the correct “What to show”

WhatToShow.png

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

YourReport

Good luck!!!

 

% Accummulate YTD on Excel Pivot Report

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

Select_columns

5. As I see on the image above, the selected fields are not in the correct area. I need to adjust them like this:

AdjustFieldsToAreas.png

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:

Accummulate_monthly.png

Now, I have Monthly value and Accummulate monthly on each month for each service activity

Monthly.png

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:

Percentage_Accummulate_Monthly.png

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:

Table_Total_Accummulate_monthly

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:

Chart1.png

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…”

FormatDataSeries.png

Select it like a secondary axis

SecondaryAxis.png

Make my chart bigger and remove some buttons, move the legend to bottom.

MoveLegend.png

Then I good to go 🙂

Working with Excel Pivot Table

1. Open the excel file

2. On the ribbon, select “Data” -> “From Other Sources” -> “Connect to database server”

3. Select Database and Table

SelectDatabaseAndTable

4. Save data connection file and finish

SaveDataConnectionFile.png

5. Update connection properties

ImportData

UpdateConnectionProperties

6. Select pivot table fields and put them to correct areas

SelectColumnsAndPutToAreas.png

7. Remove subtotals on the field settings

NoSubTotal

LayoutAndPrint

8. Yeah, now you have a listing like this:

VesselLoadListing.png