Try ERPNext Buy Support Partners Foundation

Microsoft Power BI Tutorial - How to generate great reports with ERPNext

Hello,

I was recently looking for options to generate beautiful reports with ERPNext and found out about Microsoft Power BI Desktop which is free for personal use and very easy to use. The trickyest part was to be able to gather live data from ERPNext. Here is how I was able to do it (using a bunch of tutorials on the Internet) even though it’s not perfect (I will update this post one I find a better way to do it).

1 - Download Microsoft Power BI Desktop:

2 - Use ERPNext Rest API to load your data:

  • In Power BI, click on “Get Data”
  • Choose “Blank Query”
  • Use the “Advanced Editor”

Here is the code I use to get my list of “Sales Invoice”:

Note: To be able to get the data from the Rest API, you need to specify a valid cookie session ID to the API. As of now, I was unable to do it all with Power BI. I will let you know if I find a solution. For now, use Chrome and login into your ERPNext session. Once logged in, press F12 to show Developer Options. Click on the “Application” tab. Double-click on “Cookies” in the left sidebar and click on your ERPNext URL. Copy the value of the “sid” field to the clipboard and paste it after “sid=” in the “Source” url in the following code:

let
Source = Json.Document(Web.Contents(“http://[your erpnext url]/api/resource/Sales Invoice/?fields=[”“name”", ““customer””, ““posting_date””, ““total””, ““customer_group””, ““territory””]&limit_page_length", [Headers=[Cookie=“sid=your cookie session information”]])),
data = Source[data],
#“Converted to Table” = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Expanded Column1” = Table.ExpandRecordColumn(#“Converted to Table”, “Column1”, {“customer”, “territory”, “name”, “customer_group”, “posting_date”, “total”}, {“customer”, “territory”, “name”, “customer_group”, “posting_date”, “total”}),
#“Reordered Columns” = Table.ReorderColumns(#“Expanded Column1”,{“posting_date”, “name”, “territory”, “customer_group”, “customer”, “total”}),
#“Sorted Rows” = Table.Sort(#“Reordered Columns”,{{“posting_date”, Order.Descending}}),
#“Renamed Columns” = Table.RenameColumns(#“Sorted Rows”,{{“posting_date”, “Date”}, {“customer”, “Client”}, {“customer_group”, “Groupe de clients”}, {“name”, “# Facture”}, {“territory”, “Représentant”}, {“total”, “Montant AT”}}),
#“Changed Type” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Date”, type date}}),
#“Changed Type with Locale” = Table.TransformColumnTypes(#“Changed Type”, {{“Montant AT”, Currency.Type}}, “fr-CA”),
#“Renamed Columns1” = Table.RenameColumns(#“Changed Type with Locale”,{{“Montant AT”, “Total”}})
in
#“Renamed Columns1”

Use and customize this example to get all the data you need. I just found out about Power BI and I love it. It’s very intuitive and easy to use. Let me know if you find a way to capture the cookie from within Power BI or Chrome. I found a way to parse Internet Explorer Cookies but it’s not ideal in my environement.

I hope it will help some of you!

G.

6 Likes

Thanks for sharing.

An open source alternative might be http://widestage.com/en

1 Like

Also interesting - Python based Cubes

http://cubes.databrewery.org

2 Likes
2 Likes

@bardbq thanks for this .Do update when you are able to link erpnext successfully to the power BI.It will greatly enhance reporting in ERPnext.Thanks
Fred

1 Like

Is this mature yet? Would really like to see it take off. I know a rewrite was in the works at a point.

@Chude_Osiegbu, I dont’ know much about the project itself. There have been updates in the last year but not much activity on Github.

I just really like the concept and it has enabled me to solve some complicated reporting requirements. And it’s in Python too.

looks great will try this. I love Microsoft Onedrive

Hello there, any update on this?