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: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience 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 bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.