CouchRS – Creating SSRS Reports against CouchDB Views

In my previous post we looked at how to build a simple report in SQL Reporting Services using CouchDB as the backend.  While reports against a single CouchDB document might useful in some instances, the real power comes into play when you are querying CouchDB views.  (Refer to my introductory post and to “CouchRS – Building Your First Report…” for examples on how to install CouchRS, and how to configure a Reporting Services project Data Source and Dataset to use CouchRS.)

In order for us to query a CouchDB view, we need to create one.  You can edit CouchDB views directly in the Futon web interface, but I’m using “LoveSeat” – an open source editor for CouchDB.  The data I’m using is a subset of the AdventureWorks2008R2 database, loaded into Couch.  To simplify things, my object model consists of Customers, who have Orders, and Orders have OrderDetails.  Each document in my CouchDB is a Customer object.  The point here is to illustrate that you can use CouchDB to store your domain model aggregate root(s), and still perform a wide range of reporting against this data without the need for it to be stored relationally.

So, for our first view, let’s create something that returns territory and total sales information.  Each order in my object model has a “Territory” object, and I’d like to include the Territory’s “GroupName”, “CountryRegionCode” and “Name” in my view, along with the year and month of each Order, and the “TotalDue” amount of each order.  Here’s a screen shot of my “map” function for this view:

This function takes a document as its argument (map functions are called for each document in the database).  Then we iterate over the Orders collection for the given document and emit a record that has an array key of [GroupName, CountryRegionCode, Name, Year, Month] and a value equal to the “TotalDue” for that Order.  Note that this mapping function can emit multiple records per document passed to it.  If we query this view via a browser, we’ll see results similar to this:

Once this view has been built and queried for the first time, subsequent requests are instantaneously answered.  We could go ahead and use this data to drive our report, but I want to take advantage of another feature of CouchDB views, the ”reduce” function.  The reduce function on a Couch view is called on the set of data produced by the view.  In our case, it can be used to aggregate the data by the key or even just a portion of the key.  This reduce function in this example reads:

function(keys,values) { return sum(values); }

This function aggregates (sums) the value (“TotalDue”, in our case) by the key.  If you’re wondering why I made the key complex, and the value scalar, it’s because I wanted to take advantage of the “reduce” (reduce functions should, as a general rule, return a scalar value).  Fairly simple stuff, right?  Later on, we’ll touch on how “SeeNoSQL” supports CouchDB’s query options, enabling us to specify a “group_level” on a reduced view, effectively allowing us to aggregate the values by partial keys (i.e. – what if you wanted see the totals for all regions by year, and leave “month” out of it?).

Now that we have a reduced view, let’s open a new report up and create a data set that points to our view:

The command text presented here has quite a bit more going on that our easy example from my last post:

• FROM VIEW(aw,sales_by_region_year_month) – indicates we’re querying a view.  The first argument of the VIEW() function is the name of the design document that the view resides in, and the second argument is the name of the view.
• USING QUERYOPTIONS(Group=true) – this tells Couch to Group the results by the full key, otherwise we’d get a result set with only one record that summed all the values in the view (we have to include this because this view has a “reduce” function – this isn’t necessary for non-reduced views).  Other query options that can be used here are:
  • ALLOW_STALE (true/false) – if true, CouchDB will not refresh the view even if it is stalled.
  • DESCENDING (true/false) – if true, the results are sorted in descending order by the key.
  • GROUP_LEVEL (int) – the level at which to group the results.  We have a 5 part key, so, a group level of 3 would only group it to the third key (GroupName, CountryRegionCode, Name), a group value of 4 would group it up to GroupName, CountryRegionCode, Name & Year, etc.
  • INCLUDE_DOCS (true/false) – if true, it includes the original document that was used to emit the record in the view.
  • INCLUSIVE_END (true/false) – Controls whether the endkey is included in the result.
  • REDUCE (true/false) – if false, the reduce function in a view is not executed.
  • SKIP (int) – skips the number of rows specified.
  • LIMIT (int) – limits the number of results returned to the number specified.
• WITH KEY[GroupName,CountryCode,Country,Year,Month] – this tells the SeeNoSQL parser that you are using a view with an array key with 5 elements.  You can call each element whatever you want for the sake of the query, as long as you understand that they are evaluated in the same order that the actual key is in.  If you don’t have a WHERE clause, the “WITH KEY” statement is not required.
• WHERE Year = @Year
  AND Month BETWEEN(@StartMonth,@EndMonth)
  AND GroupName BETWEEN(MIN_STRING,MAX_STRING)
  AND CountryCode BETWEEN(MIN_STRING,MAX_STRING)
  AND Country BETWEEN(MIN_STRING,MAX_STRING) – This specifies that user-driven parameters will provide the value for Year, and a range for month.  For the sake of this report, though, we don’t want to constrain the region information.  SeeNoSQL supports a “MIN_STRING” and “MAX_STRING” constant that, when evaluated and sent to Couch, corresponds to null and {} (empty object).  Submitting a range like this will return any string value in the key.

(For more information on the CouchDB View API, check out the CouchDB wiki and the “View Cookbook for SQL Jockeys”.)

If we click the “Query Designer” button and execute the query, we’ll get results similar to:

Notice that CouchRS has broken the array key into separate fields.  (In future versions of CouchRS, I plan to have it name the fields for you based on how you aliased them in the command text.  If you use an object key, it already names the fields using the field names on the object).  Although I could work with fields named “key0”, “key1”, etc., I’ve chosen to rename mine to make things more intuitive while building the report:

In the sample reports project (included in the GitHub repository), the above query drives the data for the “Territory Group Sales by Year” report:

And page 2 of the sample report:

Ok – so it’s very easy to get charted reports up and running against CouchDB.  One key thing to bear in mind is that since the results from Couch are returned to CouchRS as JSON, all the values are treated as text.  This means you will most likely have to convert numeric & date values to their respective types as you use them (I had to use CDec() and CInt() in these reports in order to properly format currency and to properly sort by month).

However – what if you wanted to compare a given region against the average of the other regions? Or in the case of a customer, see where they fall compared to the average customer?  Stay tuned….we’ll cover that in the next post….