Creating and formatting HTML reports
In this tutorial we are going to set up a basic report which lists the five newest users using our Report module and display it on our home dashboard.
Please navigate to Orange > Reports module > Click 'Add New'
You will be taken here:
Give it a name, select the color dashboard which you would like to put the report into (I am using blue in this example). Select a category, frequency and data source (for this we will use sql but the steps for using google analytics will be mostly the same).
Scroll down to the Report Query section. Copy and paste this query into the text area: "SELECT `user` AS "Name",`email` AS "Email" FROM `membership_users` ORDER BY `join_date` DESC LIMIT 5;" This sql statement selects the user names and email addreesses which are stored in the table 'membership_users', sorts them by when they joined with the newest first, and limits that selection to five entries.
At this point you have set up a SQL statement to fetch the appropriate data, but you still need to set up formatting for that data to be displayed properly. Scroll down to the Report Output section. Tick the HTML Layout button. You will see a typical Body Repeat Item section that will be familiar to anyone who has worked with listers before. Currently there is nothing in any field. In the body field, enter "[[repeat]]". In the repeat field enter "[[item]]".
In the item section you will want to enter the names of the fields which you are interested in displaying in the report. By default, these field names are the same as the column names which you selected from the table. In this specific example we have used aliases to rename the columns to "Name" and "Email". We therefore want to put into the Item section the includes [[Name]] and [[Email]]. If we had not used aliases these includes would be [[user]] and [[email]] - the names of the sql columns. Additionally, you can set up styling in various ways to format the report. In this example I have made the user name larger than the email field and forced a new line for each record: "<span style="font-size:20px;">[[Name]]:</span> [[Email]] <br />".
At this point you can click Save and the report is ready to go with a data source and formatting, but you will still have to tell the dashboard that you want to display the report. Go to the dashboard of the appropriate color which you selected near the start of this tutorial (you can get there by going to: color interface > Home > Dashboard tab). It will look like this:
Click Add a New Report. You will see a list of reports which were created in the Reports module in Orange and assigned to the color interface which you are in. Click Add next to the report we just made.
At this point you will see the reports as they will appear on the home page. However, on the home page you cannot make any changes to the way the reports are displayed - they are static there. In the dashboard tab however, you have the option of rearranging any way you like. You can click the leftmost button to move around reports, and you can stop displaying them by clicking the big X second from the right. If you now go back to the Home tab you will see the report added to the page beneath the 'Welcome to your Freedom Website' message. If you have more than one report you will see the arrangement is the same as how you set it up the Dashboard tab.
If you need a visual walkthrough of the process of setting up a report, you can find it on our Accrilabs page - March 20th, 2014.