In this blog page I will show how I get reporting from MySQL database.

It is possible that there are other ways to get reporting too. This is just how I did it.

To start with, you have a simple sales report.

The dimensions are running ID number, material code, sales region and sales volume.

We could simply use the Excel as source of data for our data model. Instead of copy pasting the data to Excel, we use MySQL database.

Create a database and a table corresponding to data in the report.

Right-click the table and select table data import wizard.

Select the database table.

The fields in the file are mapped with fields in the empty table.

Now the table is populated. A good thing with SQL database, is that we can check the database with an SQL script. Note, that the database is called excel_demo.

Now we should have connection with database and Excel to get reporting done in Excel.

https://dev.mysql.com/downloads/connector/odbc/

I downloaded here the open database connector.

This is how the page looked like for me.

Search for ODBC data sources.

Press add.

Add the excel_demo database.

Select MySQL ANSI driver and press finish.

Data source is our database. TCP/IP and port are fixed values.

Press test.

The test was successful.

Excel_demo appeared to the list.

Now we can open Excel and start working on reporting side.

In Excel take data – get data – from other sources – from ODBC.

Select excel_demo from the dropdown list. Click OK.

Select database from left hand side. Enter the user name and password. Press connect.

All the available databases in MySQL are visible. We are interested in excel_demo.

Press transform data.

Check the data and data types for fields.

Close & Load – Close & Load To

Only create connection and add this data to the data model.

Create a measurement to sum up the sales volumes.

Sales per region.

Let’s check the data in MySQL.

The values match.

Another option is to use data directly from MySQL database without ODBC.

Select get data – from database – from MySQL dataset.

Localhost as server and excel_demo as database.

Transfrom data.

Close & Load – Close & Load to.

Only create connection and add this data to the data model.

Now the table name is database name + table name.

Creating a measure to sum up the sales volume.

The process is pretty similar in Power BI side, too.

Connection between Excel/Power BI and MySQL can be done via both ODBC and directly to database.

An advantage with MySQL is that you can paste data with CSV-files to MySQL tables. When you have the data in MySQL, the data can be manipulated and reviewed with SQL script. Still, the reporting can be done in Excel or in Power BI.

After the connection is in place, we can briefly analyse data.

Even though the sales were divided quite evenly between the regions, there are differences per product. None of the products are sold evenly between the regions. Also volumes per per product are varying between 9 and 45.

This blog post is an extension to my earlier blog post about accounting data in BI reporting.

Here is some accounting data. Accounts starting with three are income accounts increasing value in credit and accounts starting with four cost accounts increasing value in debit side.

The dimensions are account, cost center, date, credit value and debit value.

It would be useful to see profit and loss dynamically per cost center or per time dimension like month.

Create sum measure to count debit balance.

Summing the credit side.

These measures do not take yet correction postings into consideration. Account 3000 should be 2282 minus 250.

Sum debit total is sum debit minus sum credit.

Sum credit total is sum credit minus sum debit.

Now the balance for 3000 is 2032 in credit, but -2032 in debit should be removed.

Create an if sentence. IF sentence in DAX is pretty similar as in traditional Excel. If sum debit minus sum credit is positive, then count normally sum debit minus sum credit. If sum debit minus sum credit is negative, then the formula returns zero value. Separator is comma not semicolon.

The same operation for credit side as for the debit.

Now the results are as they should be.

The selections.

Adding the cost center.

This looks better but cost center totals are missing.

Then select subtotals – show all subtotals at bottom of group.

The highest profit was made in cost center S2.

Same measures can be created to BI also.

For me Excel work better in this case.