#reporting framework

I recently found a good, open source reporting framework, ready to install on the Sitecore Shared Source Modules, called Advanced System Reporter. 

ASR is a framework which allows creating many types of reports in a very short time, such as:

Get the Alias of an Item

Audit users Actions

Find broken links

Find broken link in publishable items

Expired media items

Item History

Items with invalid names

Items with security for an account

Links

Locked Items

Login errors

Logged In users

Multiple Versions

My owned Items

Not recently modified

Orphaned media assets

Owners of items

Workflow history

Items recently modified

Search with regular Expression (regex)

Renderings

Stale workflow items (Items in a workflow status for a long time)

Validation Errors

I will now explain how this module works, how to configure a scheduled task and how to send an email showing the list of items that have been modified during the past week. Install the module

You’ll find a ready-to-use Sitecore module by clicking the link above. You just need to run the installation Wizard (on Sitecore Desktop), upload the zip file and follow the wizard to the end. Keep in mind that according to the module’s documentation, this module needs Sitecore CMS V6.0, v.6.4.1, or v.6.5

Setup and test

By default, you’ll find a link to the application under main menu à All Applications à Reporting Tools.

This tool lets you configure, run, export and save previously generated reports. We’re going to use the report “Recently modified”, so let’s click on the Open button (upper left corner) and select the appropriate item.

We have to choose the root item, usually “content\home”, because we want to get the modified items. Now just press “Run” on the top menu bar and there is our list of modified items. If you’re an admin, it is possible to export all that valuable information as a .csv, .xls (or html file, but with a Microsoft Excel extension), or .xml by clicking on the button “Export”.

Once our report is ready, we only have to save it, so it can be run selecting the parameters manually. But what happens behind the scenes? By default, all the preconfigured items are stored under “/sitecore/system/Modules/ASR/Reports”. Now, the application has saved our “report” as a child of the “/sitecore/system/Modules/ASR/Reports/Recently Modified”, using the template “/sitecore/templates/System/ASR/Saved Report”. So, what we’ve saved is not a report, just a “report parameter set” associated to a report.

Set up the task to be done by the scheduler

In order to have the schedule execute the task, we need a specific item. The ASR comes with a template to simplify this task under “/sitecore/templates/System/ASR/Report Email Task”.

So, let’s select the commands folder under “/sitecore/system/Tasks”, right click and “Insert à Insert from template”, on the next window, we have to choose the mentioned item “/sitecore/templates/System/ASR/Report Email Task”. We must fill the fields under the “report Email section”.

Active: If the report must be executed.

To, Subject and text: The properties of the email to be sent.

Send if empty: By default, the application won’t send the email, if after running the report, it returns 0 items. If you want to send the email anyway, mark this option.

Report:  Here we can choose all the reports we need on the same email. Notice, it will only run “Saved Reports” because you can’t run reports without parameters.

Save it and the task is ready.

Schedule the task

The specific ASR steps are done. Now we only need to set up the default Sitecore Schedule Item. Select the “Schedules” folder under “/sitecore/system/Tasks/Schedules”, right click à Insert à Insert Schedule.

On the Command field, choose the command created in the previous step.

On the Schedule field, we set up when to run the command. This field is a bit strange: I will explain it with an example. In the value ||127|23:59:59 each “|”, is a separator; the first value, is blank and would normally be the first day to run the task in yyyyMMdd format. The second is the last day; again, this is blank because it means the task will be executed indefinitely. The third one is the day of the week, 127 means every day. Why? 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, and 64=Saturday, so everyday =1+2+4+8+16+32+64=127, and the fourth parameter is the minimum interval between invocations. 23:59:59 means once a day. Obviously, the report will not be executed at exactly the same time every day, but this is the best approach. If we try to use 24:00:00, the system will translate it to 00:00:00, so the task will be executed every second. That’s it; Sitecore will run the report and send the result via email, once a day.

You can read more about scheduling a task on Sitecore here.

Advanced configuration

At this point, you may have realised that the report will send you the email with the items modified since any given day we chose, but what if we want the items modified yesterday or last week? Well, this is possible too.

We have to modify the “Saved report”, so let’s go to: “/sitecore/system/Modules/ASR/Reports/Recently Modified” and click on the item created in step 3. There’s only one field, with all the parameters of the report separated by the character “^”. Find the parameter AgeISO and replace the value with one of these variables:

$sc_lastyear

$sc_lastweek

$sc_lastmonth

$sc_yesterday

$sc_today

$sc_now

A framework, why?

The application comes with 23 preconfigured reports, but we can create as many records as we want, adding filters, parameters, scanners, viewers, etc., so we have the tools to easily create our own custom report. How do you do this? Well, maybe this is for another blog post.

Some reports are not working: why?

First, check your logs and look for something like “WARN  ASR: cannot assign value to property…”. If you find this error you’re lucky since these errors are easy to fix.

The module is using Sitecore utilities to set the attributes of the different configuration items as filters, scanners, etc. Those elements have the assembly, class and attributes, configured on Sitecore fields for this purpose.  

If you open the filter /sitecore/system/Modules/ASR/Configuration/Filters/Audit, you’ll see the field “Attributes”, with this value: “verb={Verb}|user={User}”. This means that the code is going to try to set the attribute “verb” and the attribute “user”, of one object of type “AuditFilter” (namespace: ASR.Reports.Logs), but if you check the code (Downloading the code, or with a decompiler like “Telerik just decompile”), you’ll see that this type has two properties: “Verb” and “User”. So, as you would expect the method “Sitecore.Reflection.ReflectionUtil.SetProperty” is not able to find the property “verb” and “user”.

The solution: edit the ‘attributes’ field in the sitecore item, change the first letter of the parameters to uppercase and save. Your report will now run properly.

Feel free to ask me any questions or leave me a comment in the box below.