WP Tiny Statistics

tiny_statistics by Mr.CrazyappleThis is a small piece of code to create a tiny blog statistic.
Two queries counts all blog posts and pages and the second counts all used images.

The third query selects the last four entries from the wordpress posts table.

SQL Part:
Selects the last four modified entries (LIMIT 0, 4). If you want to see more post increase the last number.

require_once( ABSPATH . 'wp-includes/query.php');
global $wpdb;
$table__posts = $wpdb->prefix . "posts";

$hpe_count = $wpdb->get_var("SELECT count(*)FROM ".$table__posts." WHERE post_status ='publish' and (post_type ='post' OR post_type ='page') ");
$hpe_imgcount = $wpdb->get_var("SELECT count(*)FROM ".$table__posts." WHERE post_mime_type = 'image/png'");
$sql = "SELECT `ID`,`post_modified_gmt`,`post_title`,`post_status`,`guid`, `post_type`
FROM ".$table__posts."
WHERE post_status ='publish' and (post_type ='post' OR post_type ='page')
ORDER BY `post_modified` DESC LIMIT 0, 4";
$get_articles = $wpdb->get_results($sql, ARRAY_A);

HTML / PHP Part:

<b>Last update:</b>
<?php foreach ($get_articles as $hpe_current_article) { ?>
	<br><strong><?php echo $hpe_current_article['post_type']; ?></strong>:&nbsp;<a href="<?php echo $hpe_current_article['guid']; ?>" target="_self"><?php echo $hpe_current_article['post_title'];?></a> <em><?php echo substr($hpe_current_article['post_modified_gmt'],0,10); ?></em></a> 
<?php }?>
<br> published articles/pages: <b>
<?php echo $hpe_count; ?> </b>
<br> embedded images: <b>
<?php echo $hpe_imgcount; ?> </b>

Needful things Part B

Helpful dummy objects

Example: You want to update many objects with the same attributes, but some of your result sets should not be updated. Or you must update a selection of objects with different attributes.

One of the possible solutions is to create many unique update statements or WHERE conditions. Or you create a big IN or NOT IN condition with many object_ids.
The best practice is to create a dummy dm_document object in your repository (i.e. with the DQL Tester). The included or excluded objects_ids are stored in the keywords of the dummy dm_document object. The dm_document object can store a huge number of keywords (> 10 000). In practice I have saved more than 30k object_ids in one document. The SELECT or UPDATE statement is very easy and is issued like this:

SELETCT r_object_id, r_lock_owner, object_name, r_lock_date 
 FROM r_and_d_document (ALL) 
 WHERE r_object_id 
 NOT IN (select keywords from dm_document WHERE object_name=<dummy name>)

This statement selects all checked out documents except a number of predefined documents. You can execute many select statements to select the excluded documents. This has the advantage that all of your results are documented.

Needful things Part A

Some helpful tiny DQL queries:

Select all Documents which are currently running in a Workflow

select distinct
d.object_name as document_name,
d.r_object_id as doc_identifier,
f.object_name as wfl_name,
f.r_act_name as wf_type,
f.supervisor_name as started_by,
f.r_object_id as wf_id 

from dmi_package p, dm_workflow f, dm_document (all) d
where r_workflow_id in (select r_object_id from dm_workflow)
AND p.r_workflow_id = f.r_object_id
AND p.r_component_id = d.r_object_id
enable (row_based)

Documents attached to workflow tasks in a user’s Inbox that are overdue

SELECT DISTINCT r_object_id,due_date 
FROM dm_dbo.inbox_docs i, dm_document (ALL) d
i.r_object_id = d.r_object_id 
AND i_latest_flag = 1 
AND DATEFLOOR(day, due_date)<=DATEFLOOR(day, DATE(NOW)) 
ORDER BY due_date,r_object_id

How to create a single drill-down report

In this case I defined two simple reports, one as a target (contains the data to be shown in addition of the base report’s data) and the other as a base report (contains the data to be improved by the data from the target report) thereby following the best practice to design the target report first and the base report afterwards. The base report is defined as a table chart type and the target report is defined as a pie chart type.

Defining the reports

1. In Process Reporting Services define a simple report which is supposed to be the target report.

Creating a new simple report

 2. Select a data source

Data Sources are selected by pulling items from the palette area on the left side into the white area in the middle. After pulling the items there one can select how to query the items and how to treat them  in the report.The different items selected and processed here influence what is usable in the next step.

3. Select either Pie, Bar, or Table as the chart type.

Process Reporting Services offers Table by default. Attention: The selection made in step 2 influences what is displayed here. Depending on what you selected before you get different information messages (red text in this screenshot saying: “no data available”)

There are combinations of items that are not compatible with every chart type. In this case an error message is issued warning against the compatibility problem.

If you get error messages there – e.g. “y-axis must be numeric value” -, you will have to change that within Chart Data. There you can steer which data belongs to the different axes. If everything works fine, it should look as follows:

The drill-down itself

First create another simple report which will be the base report. For this follow steps 1 – 3. For a better visualization I chose table type as chart type.

4. Within the Chart Data tab locate the series or column for which you are defining a drill-down report.

Click in the Drilldown field. To find it you must open the columns first:

5. To define a drill-down to another report:

a. Select the Report radio button.

  1. b. Browse to and select a target report.

The report selected here is the one created in steps 1-3. It can be selected from a picklist after creation.





The column the drill-down has been designed for changes in color. So the link to the target report is shown. Clicking on the coloured items take the user to the target report.






c. Click the Filter button(to be found under the drill-down button) to define a filter expression.







d. Select a filter tab and filter tree item and move it to the Filter Expression field by double-clicking.


e. Place your cursor within the single quotes and press the Ctrl and spacebar keys on your keyboard simultaneously. This provides a list of dynamic filter values.

f. Double-click a dynamic filter value and click Ok to close the Filter window.

6. To define a drill-down to a URL, select the URL radio button and enter a URL in the field


Note: The URL must be absolute and specify the location of a web page in full. For example:


The result of a functioning drill-down looks as follows: clicking on the changed columns directly takes the user to the drill-down target report. If the drill-down works, it shows in the header like this: base report > target report.