Custom Reports

[eswbitto]

fcorti,

I installed the report designer and took a look at the reports that already come witn AAAR. I want to dive into creating my own custom reports, but need to pointers on actually to go about doing that. Do you plan on making any type of tutorial that would go step by step in creating a generic report? I think the biggest challenge for me is actually writing the queries to gather the information.

[fcorti]

Hi ESWBitto,

If you want some hints on how to develop your custom reports using Pentaho report designer, I suggest you to take a look here:
https://www.youtube.com/user/opensourceecm

If you want a complete and detailed tutorial on Pentaho Report I submit to your attention this video course I have developed:
https://www.packtpub.com/big-data-and-business-intelligence/pentaho-reporting-video

And this is the more general answer.

To be more closer to your need, I can support to develop the query on the AAAR_DataMart (you can see documented here http://fcorti.com/alfresco-audit-analysis-reporting/aaar-audit-data-mart/).
I will appreciate if you will share the final report with the community.
If you are agree, please contact me privately.

Thanks.

[eswbitto]

Hello fcorti,

Thanks you for the reference material I will look it over. and YES! I will share reports with the community. I'm sure a collaborative mindset when creating reports would benefit everyone.


Edit: I'm also running into an issue where when I try to create a query I get an error that says. "Starting the Query-Designer failed." I did some googling on it and apparently I need to put the same mysql driver into the plugin folder. Which I can't find.

Do you have any knowledge on this error?



Edit: OK I figured out that I had to put the same jar file that I used for the mysql driver in the biserver I also had to put it into the lib folder of the report-designer directory.

So looking at the AAAR_DataMart database I don't see where you have pulled information for the sites.

My primary purpose for creating this report is to populate all sites and then have information pertaining to each site populated. I want to create a comparison of which site is being used the most and which sites are hardly ever used. I can't find where sites are being put into the AAAR_DataMart database.

[fcorti]

Hi ESWBitto,

I confirm you have to put the mysql lib also in the Pentaho Reporting environment.

The site of a document is not stored in clear in the AAAR_DataMart but you have to "read it" from the path.
My suggestion is to parse the path of a document di extract the string after the '/…./Sites/<siteName>/…/document.extention'.
Do develop it you can use a complex query or a batch that extract the informations during the night time.

Let us know how it goes.

[eswbitto]

Hello fcorti,

I'm running into an issue where when I run a query I get a return value of 0 or 1 instead of the actual site name.

SELECT
  dm_dim_dates.day_desc AS day,
  t.path REGEXP '/sites/[[:alnum:]]+/documentLibrary/*.*' AS Site,
  coalesce(sum(t.num)) AS Documents_Created
FROM
(SELECT
dm_fact_actions.date_id as date_id, 
   dm_dim_paths.path as path, 
   dm_fact_actions.num as num
  FROM 
   dm_fact_actions,
   dm_dim_actions,
   dm_dim_paths
  WHERE 
   dm_fact_actions.action_id = dm_dim_actions.id AND
   dm_fact_actions.path_id = dm_dim_paths.id AND
   dm_dim_actions.desc in ('CREATE')) t
 RIGHT JOIN dm_dim_dates ON t.date_id = dm_dim_dates.id
WHERE
 dm_dim_dates.id BETWEEN curdate() - interval 29 day AND curdate()
GROUP BY
 dm_dim_dates.desc,
 coalesce('/sites/[[:alnum:]]+/documentLibrary/*.*')
ORDER BY 
 dm_dim_dates.desc ASC
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

My mysql scripting is rusty at best.


Edit: I have tried many different ways in trying to parse the path in order to get an accurate count of new documents or update content. I get some interesting results, but none that actually go towards what I'm wanting.

I guess my question to you is why isn't the site being imported from the alfresco database to the AAAR_DataMart when clearly it can be?

[eswbitto]

ok I think I finally got it working. Here is the query to run.

SELECT
      dm_dim_dates.day_desc AS day,
      SUBSTRING( t.path,LOCATE( 'sites', t.path) +6,
         IF(LOCATE( '/documentLibrary', t.path ) = 0,
            999,
            LOCATE( '/documentLibrary', t.path ) - 8
         )
      
      ) AS Site,
      coalesce(sum(t.num)) AS Documents_Created
    FROM
    (SELECT
    dm_fact_actions.date_id as date_id,
       dm_dim_paths.path as path,
       dm_fact_actions.num as num
      FROM
       dm_fact_actions,
       dm_dim_actions,
       dm_dim_paths
      WHERE
       dm_fact_actions.action_id = dm_dim_actions.id AND
       dm_fact_actions.path_id = dm_dim_paths.id AND
       dm_dim_actions.desc in ('CREATE','CONTENT UPDATE')) t
     RIGHT JOIN dm_dim_dates ON t.date_id = dm_dim_dates.id
    WHERE
     dm_dim_dates.id BETWEEN curdate() - interval 29 day AND curdate() AND
     t.path NOT LIKE '%surf-config%'
    GROUP BY
     dm_dim_dates.desc
    ORDER BY
     dm_dim_dates.desc ASC
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I haven't done anything else yet on the looks of the report, but the main function works.

[eswbitto]

So now that I got it working how do I get it to show up on the AAAR main dashboard listed under reports?

[fcorti]

Good job!

First of all you have to upload the prpt file in the '/public/AAAR' folder, using the Pentaho Server interface.
From now ahead you will be able to use your custom report in the AAAR environment.

If you will want to upload the pdf version of your report to Alfresco, you can follow this tutorial:
http://fcorti.com/alfresco-audit-analysis-reporting/aaar-faq/#q3

To add the report to the dashboard, you have to modify the source code of the main dashboard editing it directly in the '/public/AAAR' folder (please, make a copy before modifying it).

Let us now how it goes.

[eswbitto]

Hello fcorti,

I tried finding what file you are referencing about the editing the source code, but either I'm overlooking it or not looking in the right spot. Could you tell me what file particularly I need to edit in order to add this prpt file to the dashboard?


Edit: I think I found the correct file to edit.

/opt/biserver-ce/pentaho-solutions/system/AAAR/endpoints/kettle/src/MySql/Repository/main.cdfde


So there are two places that I modified line 650 and inserted this

<div class=\"AAAR menu menuItem\" style=\"display:table-row;\">\n
<div style=\"display:table-cell;\"><img src=\"/pentaho-style/active/folder.png\"/></div>\n
<div style=\"display:table-cell; vertical-align:middle;\"/><a id=\"menuItem_report30dayactivity\" href=\"/pentaho/api/repos/:public:AAAR:Reports:audit_creation30day.prpt/viewer\" target=\"_blank\">Monthly Site Usage</a></div>\n
</div>\n
‍‍‍‍‍‍

Also line 1886

"value": "function setTooltip(objId, tooltipContent) {\n    $(\"#\" + objId).qtip({\n        content: {\n            text: tooltipContent\n        },\n        hide: {\n            effect: function() { $(this).slideUp(); }\n        },\n        show: {\n            effect: function() { $(this).slideDown(); }\n        },\n        style: {\n            classes: \"qtip-rounded qtip-shadow\"\n        }\n    });\n}\n\nsetTooltip(\"menuItem_dashboardAudit\", \"<b>Coming soon:</b> Dashboard that analyse actions, users, documents and folders, types, dates and hours of execution.\");\nsetTooltip(\"menuItem_dashboardRepository\", \"<b>Coming soon:</b> Dashboard that analyse documents, folders, users, types, dates and hours of creation and modification.\");\nsetTooltip(\"menuItem_reportRepositorySize\", \"Report that shows the repository's growth during the last month and the last year.\");\nsetTooltip(\"menuItem_reportLoginPerDay\", \"Report that list accesses to the system.\");\nsetTooltip(\"menuItem_reportCreationPerDay\", \"Report that lists the last 10 days document and folder creation.\");\nsetTooltip(\"menuItem_report30dayactivity\", \"Report that shows monthly Site usage for document creation and modification.\");\nsetTooltip(\"menuItem_reportTopTen\", \"Report that lists the top ten users and documents of the last 10 days.\");\nsetTooltip(\"menuItem_reportActionTypes\", \"Report that lists the actions in the system in the last month.\");\nsetTooltip(\"menuItem_reportMostActiveUsers\", \"Report that shows the 10 most active users on the repository.\");\nsetTooltip(\"menuItem_reportHoursOfActivity\", \"Report that shows the number of creations in the repository.\");\nsetTooltip(\"menuItem_reportDocumentTypes\", \"Report that lists the number and size of document types.\");\nsetTooltip(\"menuItem_reportDocumentMimeTypes\", \"Report that lists the number and size of document mime types.\");\nsetTooltip(\"menuItem_reportFolderTypes\", \"Report that lists the number and size of folder types.\");\nsetTooltip(\"menuItem_reportAuditDetails\", \"Report that lists all the actions in details.\");\nsetTooltip(\"menuItem_removeAdvertising1\", \"<b>Tip:</b> Click to see how to remove incomplete analysis.\");\nsetTooltip(\"menuItem_removeAdvertising2\", \"<b>Tip:</b> Click to see how to remove incomplete analysis.\");\nsetTooltip(\"menuItem_removeAdvertising3\", \"<b>Tip:</b> Click to see how to remove incomplete analysis.\");\nsetTooltip(\"menuItem_freeAnalysisOnAuditTrail\", \"<b>Coming soon:</b> Free analysis on actions, users, documents and folders, types, dates and hours of execution.\");\nsetTooltip(\"menuItem_freeAnalysisOnRepository\", \"<b>Coming soon:</b> Free analysis on documents, folders, users, types, dates and hours of creation and modification.\");\n ",
‍‍‍

I'm not sure where else I need to make changes. All I know is when I re-install AAAR and try to go to the dashboard I get a wall of text.

[eswbitto]

fcorti,

I have been able to successfully add the Link to the report on the dashboard, but I'm running into a problem. When I run the report no data actually shows up. If I add the file from the public folder in the pentaho interface I can see the report. So I know that the report works.

From there I added a new row in the dm_reports table to reflect the new report and install AAAR again. This time it breaks everything and won't show many any data either from the default reports or the one that I created.

I need help with this one.