Oct 10, 2013

Anatomy of the Group By Cube SOQL Query

Visit our website

Ahead of my Dreamforce session Custom Analytics Using SOQL Cubed Results (register here), I decided to write a preview post about the Group By Cube syntax you want to use.

Let's start with a simple SOQL statement that retrieves three fields from the Case object, and filter it to only bring back records that are closed. I always add aliases to the fields in Group By queries because it makes working with the aggregated results in Apex a little easier, and eliminates the need to include the namespace in Apex if you're packaging the code for the. AppExchange.

Next, add the GROUP BY CUBE Statement with all 3 fields that are being retrieved. The order of the fields here does not make a difference as groupings for all field combinations are returned in the query.

Next add some aggregate functions. Getting summarized information is why you use Group By Cube, so its you chance to go nuts with AVE(), MIN(), SUM(), COUNT(), etc. Don't forget to ad an alias for the function here as well.

Because cubed results return 8 types of groupings, we need a way to identify which fields are used for the subtotals of each row. For that we can use the SOQL Grouping() function, which is basically like asking the platform if the field values are included in the specific row.

The last thing that we want to add is a way to assure that the results are sorted in a predictable way. This will make processing in Apex more convenient and economical. For that we are going to add an Order By clause that will assure that each subtotal grouping will be presented together. We also want to sort the values within each grouping, so you should add the 3 fields from the select to the end of the Order By.

That's it! Here's how the query looks like:

This is what the query returns with some sample results:

Oct 2, 2013

Winter '14: Cron Job Name and Type in SOQL

A handy enhancement will be arriving in Winter '14 that makes working with Scheduled Jobs much cleaner. The update will allow you to query for the Name and Type of CronTrigger records.

In v28.0, you were limited to CreatedById, CreatedDate, CronExpression, EndTime, Id, LastModifiedById, NextFireTime, OwnerId, PreviousFireTime, StartTime, State, TimesTriggered, andTimeZoneSidKey.  Unfortunately, no way to query for the job name or type.

Winter '14's v29.0 brings with it a new object called "CronJobDetail" and a few fields (Id, JobType, and Name).  CronTrigger relates to a CronJobDetail through its CronJobDetailId field.

Sound semi-familiar?  Take a look at our earlier post, Syncing Salesforce Changes to an External System with Future/Schedule Architecture.  In this post, we schedule an Apex job to handle our future/schedule processing and create a small side object called CronJob__c.  We stored the ID of the scheduled CronTrigger so that we could remove the job afterwards.  This object is no longer necessary now that we can query for our CronTriggers by name.

The types that are provided back are numeric - here is what each value means:
  • 0 - Data Export
  • 3 - Dashboard Refresh
  • 4 - Analytic Snapshot
  • 7 - Scheduled Apex
  • 8 - Report Run
  • 9 - Batch Job