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+ database...
      ...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
      ...and it was so easy up to here...

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

The one query to rule them all
	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
	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	
	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
  • Aesthetics: Drilldown bolded items are NOT the sum of a expanded node. (despite no line separator or the totalled value in the corresponding row)

Show this week date range by default
  • Use a WHERE clause: YEARWEEK(s.DateTimeCreated,1)=(YEARWEEK(NOW(),1)) Note the ,1 parameter is because our weeks start on Monday.
  • Use an EXCEL PivotTable filter: Move your YEARWEEK field to the "Report Filter" section

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?
  • Permissions on EXCEL filters: Need to control for different levels of management? Could add a WHERE clause to the query so own teams can only see own data (requires setting up a similar report for each team).
Category: MySQL :: Article: 378

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman

Related Articles

Joes Revolver Map


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 bc1qjtp4l4ra452wzvuk9a45yfj82zkahsyy2z379y
© 2022 Joel Lipman .com. All Rights Reserved.