MySQLMove 1.2 Adds PivotWizard…Works with SQL Too..

In addition to the lack of effective database migration components  in the free software management tools for use in MySQL, there was another piece missing that SQL Server developers have had forever. That is the lack of a PIVOT function. Yes with various hacks you can achieve the same idea in your MySQL query but never to the power that SQL gives us. So since MySQLMove was intended to offer some easy to use tools to achieve what is lacking there, I decided to add pivot functionality for data in MySQL to the toolbox here. I also decided to make it available for use with SQL Server. You may ask why I did that since it is an easy matter to use the PIVOT function there or some of the other excellent tools afforded us in SQL Server Management Studio, SSRS and Excel. The reason for that is this. While experienced developers are very happy to use these tools, some less so experienced and the average person wanted a tool that didn’t seem so ….. huge to them. The idea of drag and drop and seeing all these options put out there that have a learning curve to them intimidates and makes them not want to use tools provided there. Plus the fact, not everyone is comfortable with visually based environments. It also does not hurt that as for me, a primarily SQL Server based back end guy, I can use or teach this tool to be used in minutes.

So lets talk about the MySQLMove Pivot Wizard for a bit…..

First you must get the installation media here…. If you have installed a version of MySQLMove previously, you might want to uninstall it first. The Microsoft provided template installer is buggy when it comes to removing previous versions of anyway. But here is the location of the installation file.

After install, double click the icon MySQLMove on your desktop and you will see:

Click the “Pivot Wizard” button.

 

It is pretty straightforward. Keep in mind if you are using non Windows based MySQL Servers the case of these properties are a bigger deal then some Windows based systems. Notice the checkboxes on the bottom to choose your server type. You have to choose “MySQL” or “SQL”.

Also note the “?” button. If you need help anytime during the pivot wizard, you can click on these buttons to get that. I am trying something different here. Basically I am trying out Facebook for use as a tool to get end users support. When you click on these blue buttons, it will take you to a specific post on a Facebook group called “MySQLMove Help”. Ok you don’t have to be in awe of the such original naming lol. You don’t need to join to use for the help buttons to work. In fact, I don’t know why you would. I have disabled comments so it isn’t a discussion forum. A software developer who isn’t overly social. There’s a shock right?

After your login information has been accepted you will see this:

After you enter your statement, you click the “Parse” button. One of three things will happen. It will either tell you

1. Your statement could not be compiled

2. It compiles correctly and move on to the next screen of the wizard.

3. It compiles correctly but there were issues with the data. MySQL can be finicky this way if  you doing a lot of joins and there are duplicate columns or data that has constraints. It will inform you of the issues, show you a report but if it is not fatal it will allow you to continue. I do this because not every situation we encounter in real life has perfect data.

Also know if you are concerned about injection attacks, I have taken measures to ensure that no such thing can occur. I have barred use of several keywords that would be needed to do such. So rest easy on that.

After that is done you encounter the screen that asks you what columns you want to see depicted as rows. You use the arrows in the middle to add or remove columns that will be used as rows. In this example, I have four breakdowns occurring…. the last of the salesman is king seeing it as first. It then breaks it down into customers, categories of products sold and then the actual product.

You can use the arrows to the right to move your selected row items to different positions of priority. Once you are happy with what you have you click the Next button.

The next screen is the data headers button. This is the data that will actually be presented in the report. You have several options available to you to choose how to show that data. They are:

Average
The average of the values.
Count
The number of values (excluding Null and DBNull values).
Max
The largest value.
Min
The smallest value.
StdDev
An estimate of the standard deviation of a population, where the sample is a subset of the entire population.
StdDevp
The standard deviation of a population, where the population is all of the data to be summarized.
Sum
The sum of the values.
Var
An estimate of the variance of a population, where the sample is a subset of the entire population.
Varp
The variance of a population, where the population is all of the data to be summarized.

For this example we counting the number of orders that meet our row criteria. You can have up to eight data fields.

Once you are satisfied with the data field selection click the Next button.

You then have a screen that asks you what columns do you want to appear in your pivot report and how to group that data in relation to your data field of how many orders were submitted. In this example we have asked for order date and we selected from the drop down to group that information by quarter. You can choose as many columns as your heart desires or the size of your paper you are printing on will allow.

Other grouping methods available here are:

Alphabetical
Combines field values into categories according to the character that the values start with.

Date
This option is in effect only for fields that store date/time values.
Field values are grouped by the date part. The time part of the values is ignored.

DateDay
This option is in effect only for fields that store date/time values.
Field values are grouped by the day part. The following groups can be created: 1, 2, 3,…,31.

DateDayOfWeek
This option is in effect only for fields that store date/time values.
Field values are grouped by the days of the week. Examples of such groups: Sunday, Monday, Tuesday (the actual names of the days of the week are determined by the current culture).

DateDayOfYear
This option is in effect only for fields that store date/time values.
Field values are grouped by the number of the day in which they occur in a year. The following groups can be created: 1, 2, 3,…,365 (,366 in a leap year).

DateHour
This option is in effect only for fields that store date/time values.
Field values are grouped by the date part with the hour value. Examples of such groups: 3/4/2012 0:00, 3/4/2012 1:00, 3/4/2012 2:00, …

DateHourMinute
This option is in effect only for fields that store date/time values.
Field values are grouped by the date part with the hour and minute values. Examples of groups: 3/4/2012 0:00, 3/4/2012 0:01, 3/4/2012 0:02, …

DateHourMinuteSecond
This option is in effect only for fields that store date/time values.
Field values are grouped by the date part with the hour, minute and second values. Examples of groups: 3/4/2012 0:00:00, 3/4/2012 0:00:01, 3/4/2012 0:00:02, …

DateMonth
This option is in effect only for fields that store date/time values.
Field values are grouped by the month part. Examples of groups: January, February, March (the actual names of the months are determined by the current culture).

DateMonthYear
This option is in effect only for fields that store date/time values.
Field values are grouped by months and years. Examples of groups: August 2013, September 2014, January 2015, …

DateQuarter
This option is in effect only for fields that store date/time values.
Field values are sorted by the quarterly intervals of the year. The following groups can be created: 1, 2, 3 and 4. Each quarter includes three months.

DateQuarterYear
This option is in effect only for fields that store date/time values.
Field values are grouped by the year and quarter. Examples of groups: Q3 2012, Q4 2012, Q1 2013, Q2 2013, …

DateWeekOfMonth
This option is in effect only for fields that store date/time values.
Field values are grouped by the number of the week in which they occur in a month. The following groups can be created: 1, 2, 3, 4 and 5. The first week is the week containing the 1st day of the month.

DateWeekOfYear
This option is in effect only for fields that store date/time values.
Field values are grouped by the number of the week in a year in which they occur. The following groups can be created: 1, 2, 3,…,52, 53.
Week numbers are calculated based on the following current culture’s settings.

DateYear
This option is in effect only for fields that store date/time values.
Field values are grouped by the year part. Examples of such groups: 2003, 2004, 2005.

DayAge
This option is in effect only for fields that store date/time values. Field values are grouped by the number of full days that have elapsed till the current date.

Default
Groups combine unique field values.

Hour
This option is in effect only for fields that store date/time values.
Field values are grouped by the hour part, regardless of the date to which the current date/time value belongs.

Minute
This option is in effect only for fields that store date/time values.
Field values are grouped by the minute part, regardless of the date to which the current date/time value belongs.

MonthAge
This option is in effect only for fields that store date/time values.
Field values are grouped by the number of full months that have elapsed till the current date.

Numeric
This option is in effect only for fields that store numeric values.
Field values are grouped into intervals.

Second
This option is in effect only for fields that store date/time values.
Field values are grouped by the second part, regardless of the date to which the current date/time value belongs.

WeekAge
This option is in effect only for fields that store date/time values.
Field values are grouped by the number of full weeks that have elapsed till the current date.

YearAge
This option is in effect only for fields that store date/time values.
Field values are grouped by the number of full years that have elapsed till the current date.

You then click the Next button.

You then see a screen that lets you know it is ready to produce your report. You can still at this point go back and change any parameter you have chosen. Click Finish to produce the report.

You then see a preview of your report. You can see the rows broken down as we have selected. What I want you to pay attention to here is the various ways to export your report. Obviously if you can’t take it with you this data is no good to you.

You choose the format you want and a save dialog will appear. It will then ask you if you want to view it. Go ahead and admire your handiwork.

I have saved the example we created here just in case you want to review it.

Future modifications that I would like to make to this report will be to allow you to save a report setting so you don’t have to redo it each time, give you more appearance options, and integrate a chart wizard type interface that will allow you to build charts based on the data you have produced here. I am also considering building a web interface for the Migration and Pivot Wizard functions.

If you find the tool useful and would like to continue to see it developed, please don’t be a cheap bastard like me  and make a donation here using my email address – kellyjmartens@hotmail.com . Also feel free to send feedback, suggestions, critiques and praise to the same email address. All are read.

Once again here is the download link if you are too lazy to scroll back up and find it….Here is the installation file.

That about covers it. Thank you for reading this and have a great day!