Ad-hoc report of SpagoBI is powerful yet easy to create. In fact, it is the best ad-hoc reporting tools among free and open source BI solutions to date. Ad-hoc report is report that user can create by themselves without touching any code. The hardest part would be to get the SQL that retrieve data from database. The rest will be drag and drop kind of action to create various kind of reports, e.g., List Report, Crosstab Report, Pie Chart, Bar Chart, etc.
- Admin create DataSet by SQL, validate the data set and declare it for public use.
- User use DataSet to create their own report using drag and drop. If satisfied, save it to public or private folder.
We will be using a SQL being used in Sales Analysis of OpenERP to create DataSet. Then we will be creating couple of reports from this DataSet. Here is the example SQL that will retrieve sales information.
select air.*, product.name product_name, product.default_code product_code, cat.name product_category, sale.name sale_person from account_invoice_report air left outer join res_partner cust on air.partner_id = cust.id left outer join product_template prd on air.product_id = prd.id left outer join product_product product on product.product_tmpl_id = prd.id join product_category cat on air.categ_id = cat.id left outer join res_users usr on air.user_id = usr.id left outer join res_partner sale on usr.partner_id = sale.id where air.type = 'out_invoice' and air.state in ('open','paid') and year is not null and year::int < 2500;
Step 1: Create DataSet
- Login as Admin, open Data set (1)
- Create a new DataSet (2) and fill in DataSet’s detail (3)
- Update SQL in tab Type (4)
- Preview Data (5)
- Setup Field Metadata (6). This is an important step. To create Ad-hoc report, each field must be defined whether it is Attribute or Measure. Attributes are labels on the axis such as Product Name, Product Category, Year, Month, etc. Measures are value to be measured such as Total Amount, Quantity.
- Final step is to make sure it is Public (7) so other user can use it. Then save and log out.
Step 2: Create Report
- Login as Business User, click on Create docuemnt icon on the left menu. This will show the list of public DataSet.
- Click into the DataSet, the ad-hoc report builder will open up.
- You can now choose any kind of report you want from the Palette. This time, lets create what I think most difficult, the Crosstab report (which you will see it is pretty easy).
- Drag Static Pivot Table to the right panel.
- Click Selected fields tab under the Palette will open up all the fields available for use. Please note the difference between Attribute and Measure fields.
- Note that, Layout tab is also available for some simple layout options.
- Steps to create report,
- Drag Attribute fields into Rows and Column area.
- Drag Measure field into Measure area.
- Specify how value will be measured, e.g., Sum, Average, Count, etc.
- If you want filters, drag the Attribute fields to Filters area.
- Set filtering options.
- Click on Preview to run the report. If you as ok with the report, you can save it.
- Note also form the following picture that, for Crosstab report, you can set more Crosstab details on how the sub-totals will be displayed (other kind of report will have their own options).
- If you still not satisfied with the report, you can always click on Designer to revise it.
- Otherwise you save it for future use, choose the folder you want to save. Report is considered a document in the system.
- You can also export it to Excel or PDF.
As you can see, creating the Ad-hoc report in SpagoBI is pretty simple. You can use the same DataSet to create as many reports as you want, based on the given data. Once they are save, by its security, user can access them from the Document Browser.
Some sample ad-hoc reports created from the same DataSet