Print

MySQL parameters in Excel 2007 PivotTables

Hopefully the title doesn't put you off but after much Googling and Bing-ing, I still couldn't figure out how to do this. Hopefully this article will help you more than my search engine skills do.

My Setup
Windows XP Workstation
      ...needs to open...
Excel 2007 SP2
      ...with ODBC to...
MySQL v5+
      ...hosting database...
ActivityLog
      ...contains activity, staffID, resourceID, start time, end time...


Report Specification
PivotTable Report
      ...resources in row (along the side)...
      ...staff in columns (along the top)...
      ...persondays in values (the number my bosses want - 7h 24m or 26640s is 1 person day)...
With date range as parameters
      ...ouch...
      ...and it was so easy up to here...

Tables of interest
StaffActivities
      ...the one users recorded time gets stored in...
Resources
      ...the one with the codes and resource names...
Users
      ...the one with users names...
Teams
      ...another lookup table for just the name value...

The one query to rule them all
copyraw
SELECT	
	CONCAT(s.LogID, ' ') AS ActivityID,
	t.TeamName AS Team,
	CONCAT(u.Firstname, ' ', u.Lastname) AS StaffName,
	CASE WEEKDAY(s.DateTimeCreated) WHEN 0 THEN 'Monday' WHEN 1 THEN 'Tuesday' WHEN 2 THEN 'Wednesday' WHEN 3 THEN 'Thursday' WHEN 4 THEN 'Friday' WHEN 5 THEN 'Saturday' ELSE 'Sunday' END AS ActivityDay,
	DATE(s.DateTimeCreated) AS ActivityDate,
	MIN(s.DateTimeCreated) AS DataFrom,
	MAX(s.EstimateTimeFinish) AS DataTo,
	SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish) - TIME_TO_SEC(s.DateTimeCreated))) AS ActivityDuration,
	((SUM(TIME_TO_SEC(s.EstimateTimeFinish) - TIME_TO_SEC(s.DateTimeCreated)))/26640) AS PersonDays,
	CONCAT(r.CapexCode, ' - ', r.ResourceName) AS Resource,
	s.StaffActivity AS Activity,
	s.AdditionalNotes AS Notes,
	CONCAT(SUBSTRING(YEARWEEK(s.DateTimeCreated,1) FROM 5), '/', SUBSTRING(YEARWEEK(s.DateTimeCreated,1), 1, 4)) AS ActivityWeek,
	CASE COUNT(*) WHEN 1 THEN '(1 Event)' ELSE '(Multiple Events)' END AS DataAccuracy
FROM	
	StaffActivities s
INNER JOIN Users u ON u.UserID=s.StaffUserID	
INNER JOIN Resources r ON r.ThisResourceID=s.CategoryID	
INNER JOIN Teams t ON t.TeamID=s.TeamID	
GROUP BY 	
	s.LogID
ORDER BY 	
	s.TeamID, u.Username, s.DateTimeCreated, Resource
  1.  SELECT 
  2.      CONCAT(s.LogID, ' ') AS ActivityID, 
  3.      t.TeamName AS Team, 
  4.      CONCAT(u.Firstname, ' ', u.Lastname) AS StaffName, 
  5.      CASE WEEKDAY(s.DateTimeCreated) WHEN 0 THEN 'Monday' WHEN 1 THEN 'Tuesday' WHEN 2 THEN 'Wednesday' WHEN 3 THEN 'Thursday' WHEN 4 THEN 'Friday' WHEN 5 THEN 'Saturday' ELSE 'Sunday' END AS ActivityDay, 
  6.      DATE(s.DateTimeCreated) AS ActivityDate, 
  7.      MIN(s.DateTimeCreated) AS DataFrom, 
  8.      MAX(s.EstimateTimeFinish) AS DataTo, 
  9.      SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish) - TIME_TO_SEC(s.DateTimeCreated))) AS ActivityDuration, 
  10.      ((SUM(TIME_TO_SEC(s.EstimateTimeFinish) - TIME_TO_SEC(s.DateTimeCreated)))/26640) AS PersonDays, 
  11.      CONCAT(r.CapexCode, ' - ', r.ResourceName) AS Resource, 
  12.      s.StaffActivity AS Activity, 
  13.      s.AdditionalNotes AS Notes, 
  14.      CONCAT(SUBSTRING(YEARWEEK(s.DateTimeCreated,1) FROM 5), '/', SUBSTRING(YEARWEEK(s.DateTimeCreated,1), 1, 4)) AS ActivityWeek, 
  15.      CASE COUNT(*) WHEN 1 THEN '(1 Event)' ELSE '(Multiple Events)' END AS DataAccuracy 
  16.  FROM 
  17.      StaffActivities s 
  18.  INNER JOIN Users u ON u.UserID=s.StaffUserID 
  19.  INNER JOIN Resources r ON r.ThisResourceID=s.CategoryID 
  20.  INNER JOIN Teams t ON t.TeamID=s.TeamID 
  21.  GROUP BY 
  22.      s.LogID 
  23.  ORDER BY 
  24.      s.TeamID, u.Username, s.DateTimeCreated, Resource 
Note there is no WHERE clause because we're going to use EXCEL filters as the parameters. Bit of a cop-out but this was just a workaround and never a solution.

Add in the ID of each entry as a field to pull from the database so that anyone can drilldown to the specific logged activity event (smallest unit of the drilldown).

What's the problem?
Enter this as a query in Excel without the parameters and it will work. Set MySQL parameters (using @ - same as T-SQL) and Microsoft Excel 2007 gets confused.

Putting it all together
  1. Create a SQL query that will return: staffname, activity date, duration, week/year, person days, resource names, and the activity (+ any others you may want to drill-down).
  2. Setup ODBC: Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC) > System DSN (get technical help if you don't know how to do this)...
  3. Open Excel 2007 > New > "Blank"
  4. Connect to data source: > Data tab > From Other Sources > From Microsoft Query > select Data Source
  5. Specify SQL Query: > Add Tables (not really just "close" this) > click on SQL button > Type/Paste your SQL Query > OK > Click on "Return Data" button
  6. Report Type: Select PivotTable Report > OK
  7. Design Table: Drag StaffName to Columns, Resource to Row and PersonDays to Values
  8. Tick a field that will be the parameter/filter/criteria (it will be automatically added to row or column), then click on it and select "Move to Report Filter".
  9. Shout in Gordon Ramsey-style: Done!

Better than... MS Reporting Services?
Yes by far. Fast, quick to setup and a big fat refresh button for the executives. SQL query returns individual items while Excel allows drag and drop grouping/drilldown pivot tables. Have managed to get this working with SQL Server, Oracle and MySQL databases... Now how to include parameters in non Microsoft technology.

Limitations of Excel 2007 PivotTables
Show this week date range by default
Alternative option... still in progress...
  1. Create two cells with the default from and to dates:
    1. The formula for the first date of this week was =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-WEEKDAY(TODAY())+2) Note the +2 is because our working week begins on Mondays and ends on Sundays. If the day date value is negative the formula still seems to work.
    2. The last date of this week was: =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-WEEKDAY(TODAY())+2)+7-1 Note how I'm simply adding 7 to the start of the week date (subtract 1 so this is not inclusive). Crude solution but it works in Excel 2007
  2. Now we want to filter the pivotTable data based on these two values... (I don't like the idea of requiring macros as not all users switch this on and teaching executives on how to do this is tough work.

What Next?
Category: MySQL :: Article: 378