Customizing Reports

[oliviera]

Hi,

I've been asked to create reports for a specific kind of documents. I really don't know anything about the audit customization. Reading http://wiki.alfresco.com/wiki/Auditing_%28from_V3.4%29 is losing me 😕 Hope you can help

General picture : documents are imported (by email) to a site. Then a workflow (managed externatlly) changes the status of each documents. The requested reports should show how many new documents arrives, and how many documents of a given status exist… may be with a history of one week.

Basically, a report could look like this:
Week 10:
- new documents: 10
- documents with status A: 20
- documents with status B: 13

No on Alfresco part: every new documents gets an aspect with 2 fields. A rule on the incoming folder start a script adding this aspect.
Field 1: codename of the Site (there are 6 different codenames)
Field 2: status as [to be registered|registered|booked|archived]

So I should get a summary of a state of these documents tstus per codename.

How should I build the reports, on Pentaho/AAAR only ? or with Alfresco ? Do you have any example of specific reports I could take as example ? Where can I start for looking ?

Thanks

Olivier

[fcorti]

Hi Oliviera,

What you request are two different reports.

The first, is (quite) easy developed using the current AAAR_DataMart schema.
First of all I suggest you to take a look at the AAAR_DataMart structure here:
http://fcorti.com/alfresco-audit-analysis-reporting/aaar-audit-data-mart/
In particular, what you need is the Alfresco repository structure that you can analyze in the Alfresco content dimension documented here:
http://fcorti.com/alfresco-audit-analysis-reporting/aaar-audit-data-mart/aaar-alfresco-content-dimen...

As you can see, you have a table with all the documents of your repository ('dm_dim_documents') with the most common metadata.
One of those is the 'creation_date_id' that you can use for your purpose.
So…
The query you could use for your first report could be something like this:
SELECT *
FROM dm_dim_documents
WHERE creation_date_id between …last 10 days…

To develop the report, I suggest to install into your system the Pentaho Reporting Designer and open with it the report called 'audit_creation' from the Pentaho repository.
Below the mentioned report you can use as example:
http://fcorti.com/alfresco-audit-analysis-reporting/aaar-reports-dashboards-extractions/aaar-last-10...

As you probably know, saving the new report into the Pentaho repository, you could easily upload it into Alfresco using the AAAR_Publish.

For the second report, things are a bit more difficult because you need some custom metadata you don't have into the AAAR_DataMart.
I'm planning to develop the importation of the custom metadata but currently you will not find them.
So… my suggestion is to develop a custom flow of metadata into the AAAR_DataMart.
I can support you in the development (I know how to do it 😉 but, please, write me privately.
The strategy is to use CMIS Input plugin (http://fcorti.com/pdi-cmis-input/) to extend the dm_dim_documents table with more fields.

Let us know how it goes.