Sales reporting Process - Options available

[winstonlui]

Hi all

I am new to this board and have only started to learn about Power BI through a friend's recommendation

We will be upgrading our ERP to the latest version in early 2017

However, this will mean that we will have to revamp our sales reporting process.  Currently, it is based in Excel 2010 (3 different spreadsheets are used) and it links to multiple spreadsheets and uses MS Query to pull from various tables in our ERP's SQL database.

One of the spreadsheets contains a macro that sends out various reports to each of our 10 sales reps.

These reports include basic sales reports such as the following, sales by sku, sales by store by branch, etc  

CURRENT ISSUES/PROBLEMS

• One of the sales reporting excel files is around 160 -180 MB and is prone to crashing and other errors due to the large size. 
• Another spreadsheet contains a complicated macro (that builds a series of reports in excel and .pdf format).  If this macro gets damaged, it is very difficult to trouble shoot, as there are no instructions to repair it as well I DON'T have any VBA experience
• Excel files pulls information from multiple SQL tables in our ERP as well as other spreadsheets in our network, leading to much inefficiency and time wasted each time this is refreshed.

SOLUTION/SUGGESTIONS

• I've asked our ERP company to help design a custom table within the database, so that all the information is contained on one single database table.  This means greater efficiency is achieved as it cuts out all these other tables and other spreadsheets where information must be drawn upon and updated.  The next step is where I am having some trouble
• Do you have any suggestions on what is a reliable way to create sales reports and send them to each of our sales rep via some type of automation (i am not proficient in macros)?  I would like to minimize my use of Excel as the performance starts to degrade once the data size increases
• Do you think MS Power BI would be a viable option to automate the process to send to each of our reps?  From what I can see, its strength is more to design dashboard type visuals.  Can it also create basic reports (sales by sku year over year, by store) that we can easily print or email to our rep? (Power BI is free )
• I have been told that there are other options out there such as Crystal Reports designer and what not, but would like to weigh the pros and cons of each of these reporting options.

Thank you

winston

[v-shex-msft]

Hi @winstonlui,

>>One of the sales reporting excel files is around 160 -180 MB and is prone to crashing and other errors due to the large size. 

You can use the "direct query" mode to connect the sql database then use it as the datasource to create the report.

>>Do you think MS Power BI would be a viable option to automate the process to send to each of our reps?  From what I can see, its strength is more to design dashboard type visuals.  Can it also create basic reports (sales by sku year over year, by store) that we can easily print or email to our rep? (Power BI is free )

You can share the dashboard to other users, group, organization , but it not support the subscribe function. but it support dynamic report, then you can get the newest report without the modify. For example, use power query/ dax to show the last 6 month data(base on date).

For detail information, you can take a look at below articles:

Use DirectQuery in Power BI Desktop

Share a dashboard and reports with colleagues and others

Power BI Dynamic Date Filtering

Regards,

Xiaoxin Sheng