Advanced SSRS Reporting Part 1

In my previous post, I made the following comment with regards to SSRS Reporting:

"However the sky is pretty much the limit when it comes to SSRS Reporting as you can leverage the full capability of both SSRS and SQL in order to manipulate the data in exactly the way you want."

In this post, I'm going to expand on this concept a little. Let's use a sample requirement to illustrate this:

• Report to be run against the contacts of a marketing list
• Report to be ordered by contact name (fullname)
• Contacts are related to one another by means of connections and we want spouses to break the ordering logic specified above. That is, whenever a contact has a spouse, the spouse record should appear immediately after the first spouse record (bear in mind, a spouse may not always have the same last name as their partner).
• We even want spousal contacts to appear in the report as long as a single spouse appears in the marketing list. That is, the report is against all contacts in the marketing list + spouses who may or may not be members of said marketing list.
• Include some tags to indicate whether the spouse was a member of the marketing list (or just brought in as a result of his/her connections!)

The problem with a requirement like the above is that it cannot be met by any simple query means. You simply cannot write a SQL query that will be able to retrieve the data and manipulate the data as specified.

So can something like the above be achieved using SSRS reporting? Absolutely!

Well of course you might say that this is fairly obvious as you can write a stored procedure to perform all the various querying, re-querying and data manipulation and finally output the results in a select statement that SSRS can subsequently manipulate. And if you said that, you would be right.

But such a report would not adhere to the CRM SDK which would not enable you to link the report into CRM forms, views and Advanced Find queries in a context sensitive manner. Nor would it be technically supported as saving stored procedures in the CRM database is... verboten.

So the follow up question is - can something like the above be achieved using SSRS reporting while accomodating the CRM SDK requirements? And the answer once again is... Absolutely!

The key point to recognize is that just as SSRS can consume a stored procedure as part of a data source it can similarly consume declarative statements as part of the data source (as opposed to queries) and those declarative statements can have all the complex processing power of Transact SQL. Once we realize this, we can leverage the full power of T-SQL to run multiple queries, inserts and updates into temp tables in order to get the data into the format that is desired before sending to SSRS for rendering.

As an aside - I personally find that manipulating the data prior to sending to SSRS and using SSRS mostly as a rendering rather than data manipulation tool is a much easier way of going about it. Especially when it comes to the more complex report requirements. And the above technique can come in most handy in this regard.

In my next post, I'll attempt to provide a template that can be used for constructing and testing the declarative statement for the report data source.