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
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
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).

Related Articles

Joes Revolver Map

Accreditation

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:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.