Background
Generating cubes and pivot tables in QBO 3 is a snap. Examples are available in all QBO 3 system from:
Workflow home page (Decision.ashx/Home)
Worklist home page (SmartWorklist.ashx/Home)
For the geeks in the crowd, the standard Dashboard statement (found in Abstract.config) handles a huge amount of work for you. It will handle N number of dimensions, and return the results as a SQL CUBE.
You can simply invoke the following: {Handler}/Dashboard?Dimension={Dimension1},{Dimension2},...,{DimensionN}&{Other Parameters}
Example: get a count of workflows by template
Raw output: Decision.ashx/Dashboard?Dimension=DecisionTemplateID&Output=Xml
Wrapped in an HTML table:
For those that find a SQL CUBE confusion, consider the Pivot method, which transforms a 2-dimensional cube into, well, a 'normal' table.
Date Dimensions
When specifying date-based dimensions (such as CreatedDate), the Dashboard statement will accept three 'extra' parameters per date dimension:
{Dimension}Start: (optional, defaults to NULL) specifies the earlier date to consider
{Dimension}End: (optional, defaults to DateTime.Now) specifies the latest date to consider
{Dimension}Intervals: (optional, defaults to the application setting TimeDimensionIntervalDefault, 50 by default): specified the maximum number of 'ticks' to render
The Dashboard statement will pass these parameters to dbo.TimeDimensionInterval(start, end, ticks) to produce a table containing a row of each date range. Examples:
SELECT * FROM dbo.TimeDimensionInterval('1/1/2014', '2/1/2014', 50)
-- Less than 50 days, so days are returned
TimeSpan BeginDate EndDate
2014-01-01 2014-01-01 00:00:00.000 2014-01-02 00:00:00.000
2014-01-02 2014-01-02 00:00:00.000 2014-01-03 00:00:00.000
2014-01-03 2014-01-03 00:00:00.000 2014-01-04 00:00:00.000
2014-01-04 2014-01-04 00:00:00.000 2014-01-05 00:00:00.000
2014-01-05 2014-01-05 00:00:00.000 2014-01-06 00:00:00.000
...Removed rows for brevity...
2014-01-29 2014-01-29 00:00:00.000 2014-01-30 00:00:00.000
2014-01-30 2014-01-30 00:00:00.000 2014-01-31 00:00:00.000
2014-01-31 2014-01-31 00:00:00.000 2014-02-01 00:00:00.000
SELECT * FROM dbo.TimeDimensionInterval('1/1/2014', '4/1/2014', 50)
-- More than 50 days, less than 50 weeks, so weeks are returned
TimeSpan BeginDate EndDate
2014-01-01 2014-01-01 00:00:00.000 2014-01-08 00:00:00.000
2014-01-08 2014-01-08 00:00:00.000 2014-01-15 00:00:00.000
2014-01-15 2014-01-15 00:00:00.000 2014-01-22 00:00:00.000
2014-01-22 2014-01-22 00:00:00.000 2014-01-29 00:00:00.000
2014-01-29 2014-01-29 00:00:00.000 2014-02-05 00:00:00.000
2014-02-05 2014-02-05 00:00:00.000 2014-02-12 00:00:00.000
...Removed rows for brevity...
2014-03-19 2014-03-19 00:00:00.000 2014-03-26 00:00:00.000
2014-03-26 2014-03-26 00:00:00.000 2014-04-02 00:00:00.000
SELECT * FROM dbo.TimeDimensionInterval('1/1/2012', '1/1/2014', 50)
-- More than 50 weeks, less than 50 months, so months are returned
TimeSpan BeginDate EndDate
2012-01 2012-01-01 00:00:00.000 2012-02-01 00:00:00.000
2012-02 2012-02-01 00:00:00.000 2012-03-01 00:00:00.000
2012-03 2012-03-01 00:00:00.000 2012-04-01 00:00:00.000
...Removed rows for brevity...
2013-11 2013-11-01 00:00:00.000 2013-12-01 00:00:00.000
2013-12 2013-12-01 00:00:00.000 2014-01-01 00:00:00.000
SELECT * FROM dbo.TimeDimensionInterval('1/1/2000', '1/1/2014', 50)
-- More than 50 months, so years are returned
TimeSpan BeginDate EndDate
2000 2000-01-01 00:00:00.000 2001-01-01 00:00:00.000
2001 2001-01-01 00:00:00.000 2002-01-01 00:00:00.000
2002 2002-01-01 00:00:00.000 2003-01-01 00:00:00.000
...Removed rows for brevity...
2012 2012-01-01 00:00:00.000 2013-01-01 00:00:00.000
2013 2013-01-01 00:00:00.000 2014-01-01 00:00:00.000
This function will compare the difference between the start and end dates to maximum ticks requested and return a row for each hour, four-hour block, day, week, month or year.
If you don't care much about ticks, and just want Days, Weeks, Months or Years, you can set the {Dimension}Intervals=Hours|Days|Weeks|Months|Years.