Creating Your Own Ad Hoc View

This article focuses on showing how to create Ad Hoc views from scratch in Lambda Analytics.


Step 1: Reporting Statement

Before creating an Ad Hoc view, it is important to first narrow down your reporting needs by asking yourself and your team some key questions. These questions can help you create a solid reporting statement, which lists the objectives of your report.

What is the purpose of our report?: Will we be tracking compliance training? Will we be sharing results with stakeholders? Are we looking for ways to improve our eLearning system?

Who will be viewing our report, and where will they be viewing it?: Will we be sharing our report with learners? Instructors? Managers? Will they focus purely on our results, or will they need more information? Will they be viewing our report on their eLearning dashboard? Or will they be viewing it on a large presentation screen?

Asking these questions can help you determine which data sources you should select, what kinds of charts and visualizations are best, and how accessible you should be making your report. 

Creating a report is an iterative process that often requires a lot of feedback. Reports will never be perfect immediately. Therefore, it is always important to ask yourself and your audience these questions to narrow down your reporting needs and ensure that everyone is receiving the information that they need.

Examples of solid reporting statements include:

"Show me which users have completed the Feedback Activity of the Introduction to WHMIS course within the last 3 months, including their grades"

"Show me which users have self-enrolled in the Safety in the Workplace course over February and March, including their email address"

"Show me which users are struggling (grades lower that 65%) in the Quiz Activity of the Introduction to Marketing course" 

Back to TOPICS

Step 2: Data Selector

After creating a concise reporting statement, you must select your data through using the Data Selector.

To open the Data Selector:

1.  From the Lambda Analytics homepage, click Create under the Ad Hoc view header.

2.  Select your desired data source, and then click Choose Data.



When selecting a data source for your Ad Hoc view, note that certain data sources are optimized for certain kinds of reports. 

For example, the data source, 'Your Zoola Starter Data Source' is best suited for reports that provide some kind of overview on your eLearning system. This can include looking at enrollment records, course completion records, or Time Spent Learning.

However, if you wanted to create a report goes in-depth on a certain topic, like specific activity completion records, then it is best to select a data source that caters to that particular course activity. 

You can then access the Fields page, Pre-Filters page, and Display page, and of the data chooser. You must first select fields on the Fields page, but the other three pages are optional and can be completed in any order. 

database  Fields Page

The Fields page can be used to select fields and sets of fields to use in the Ad Hoc view or make available in the data source topic. 

To add your desired fields to the Ad Hoc view:

1.  In the Source panel, use the downward arrow icon and the rightward arrow icon to navigate through the different sets of fields.

2.  Once you have selected your desired set or field, you can drag it, double-click it, or select it and click the arrow button in the central column to move it to the Selected Fields panel. To move the entire data source, click the double arrow button. 


If you move an individual field out of a set, it will appear under a duplicate set name. If you do not want to use sets, use the settings on the Display page. 

3.  If you would like to move on to the Ad Hoc editor, then click OK at the lower-left corner of the page.

If you would like to move on to a different page of the Data Chooser, then navigate to the page through using the tabs on the left-hand side of the page.

filter-4  Pre-Filters Page

The Pre-Filters page can be used to pre-filter data in the Data Chooser before clicking OK to launch the Ad Hoc editor, or before clicking Save as Topic to create a data source topic. 

Pre-filtering data limits the data choices available in a data source topic, or the fields that ultimately appear in the Ad Hoc view. You can also define a filter on a field that does not appear in the final report. The filter is still applied and only data that satisfies all defined filters will appear in the final report. 

To define a specific filter:

1.  In the Fields panel, double-click on your desired field. A corresponding box will appear in the Filters panel. 

2.  In the corresponding box, use the options to establish the filtering parameters for the selected field.

If you have chosen a substring comparison operator, such as "starts with" or "contains," enter your desired value in the text field.

If you have chosen a whole string matching operator, such as "equals" or "is one of," select your value from the drop-down menu. 

If you would like to make the filter unavailable for end-users when they run a report, click on the Locked checkbox.

3.  Once you are complete, click OK and your new filter will be listed under the Filters panel.


4.  Under a populated Filters panel, you can click Change to edit a filter, or Remove to delete it from your list. 

important-2  NOTE

Data rows must match all conditions. In other words, the overall filter applied to the data is the logical "AND" of all the conditions that you have defined.

display-1  Display Page

The Display page can be used to change how the default label and order of fields appear in the list of fields in the Ad Hoc editor. You can always change the field labels and ordering in the Ad Hoc editor, but setting them in the Display page makes them available in a data source topic.

To edit how your fields are displayed in the Ad Hoc editor:

1.  If you would like to rearrange how fields are ordered, select your desired field and use the single arrow buttons to move it one spot, or use the double arrow buttons to move it to the top or bottom of the set list. 

Fields can only be moved within their set, but whole sets can also be rearranged.

2.  If you would like to add a custom label to a field or set, double-click its corresponding row and type in a new label in the text field.

3.  If you do not want to use sets in the Ad Hoc editor, select Flat List at the top of the Data Selection panel. 


4.  Once you are complete, click OK. You can then move on to the Ad Hoc editor. 

Back to TOPICS

Step 3: Ad Hoc Editor

The Ad Hoc editor supports the creation of views for various types of reports, primarily through tables, crosstabs, and charts. You can intuitively interact with the editor to create these views by simply dragging and dropping your desired elements onto the Ad Hoc canvas. You can also add and summarize fields, define groups, label and title your report, and format data for each field. You can also use the editor to explore and analyze data interactively. 

view-3  Ad Hoc View Panel

The Ad Hoc View panel provides the tools that allow you to control what data is included in a view, and how it is organized.

Along the top of the panel, there is a toolbar and two drop-down menus:

1.  From the drop-down menu, select whether you would like your Ad Hoc view to appear as a table, chart, or crosstab. You can later change the view type while designing, or when you reopen the design for editing. 

2.  If you have selected a table or a crosstab, then another drop-down menu will appear that contains options for displaying the sample data (a subset of the available data), full data (all available data), or no data (none of the available data) in the view.



We recommend that you select Sample Data from the drop-down menu when creating your initial design, as it can be quicker to manipulate your view while you are still editing. Once you are ready to refine your layout elements, then you can select Full Data from the drop-down menu.

database  Data Selection Panel

The Data Selection panel is the leftmost panel in the Ad Hoc editor.

This panel contains a list of available fields in the specified topic or data source. If you are using a data source, fields may appear in nested sets that can be expanded or collapsed. 

Available fields may be divided into two sections within the Data Selection panel: Fields and measures. 

1.  Navigate through the fields and measures sets to find your desired elements.

2.  Drag your desired fields or measures to the Columns/Groups/Rows boxes of the Layout Band to begin visualizing your data:

Tables: You can drag qualitative field data into either Columns or Groups. Quantitative measures data can only be dragged into Columns.

Charts: You can drag both fields and measures into either Columns or Rows, depending on how you would like to interpret your data. 

Crosstabs: You can drag both fields and measures into either Columns or Rows, depending on how you would like to interpret your data. 

Note that your arrangement of the fields and measures into the Columns/Groups/Rows boxes will affect their order in the corresponding column, group, or row. 



3.  You can further refine the visualizations of the specified data through the Ad Hoc editor features, depending on the type of visualization that you have selected.

Tables: You can sort, summarize, edit, or filter column data by right-clicking a column and selecting an option from the context menu. You can also select the level of data show by clicking the Details button.

Detailed Data: Displays table details only.

Totals Data: Displays table totals only.

Details and Totals: Displays both details and totals.

Charts: You can select or deselect chart data by clicking into the legend below the chart. You can also click the Details button to access a list of chart types and chart formats.

Chart Types: Displays the available chart types.

Chart Format: Displays formatting options.

Crosstabs: You can sort, summarize, edit, filters, or alter the groupings of any column or row by right-clicking a column or row and selecting an option from the context menu.

You can also use the Canvas Options selector to further control the level of detail displayed in your table or chart.

4.  Click the Display icon createadhocviewdisplayicon to toggle between Design and Display modes. Display mode allows you to see your Ad Hoc view as it would appear to an end user.

filter-4  Filters Panel

The Filters panel displays any filters defined for your view.  These filters allow the user to interact with data to narrow their view of the Ad Hoc view. Each field or measure has a corresponding filter that is added to the Filters panel, which you can then choose to configure.

To add a filter to your Ad Hoc view:

1.  Right-click on the field or measure that you would like to create a filter for, and select Create Filter from the context menu. 

2.  In the Filters panel, expand the corresponding section of the field or measure that you would like to create a filter for.

3. Click on the small downward arrow icon and select an appropriate operator for your filter.


4.  Select all of the data points necessary to fulfill your desired condition. Depending on your choice of operator, you will either have to select your data values or type them in. 

5.  Once you are complete, click Apply and your new filter will now be listed under the Filters panel.

4.  Use the Save icon to either save your Ad Hoc view, or save it and immediately open it in the Report Creator.



When you save your Ad Hoc view to the Repository, it is important to consider using appropriate naming conditions, as there are no sub-folders in either Public or Private Artifacts.

Thus, it is important to use informative names and descriptions to ensure that you can easily find your Ad Hoc view in the Repository list. 

Filter Operators

Numeric Date Text
Equals Equals Equals
Does not equal Is not equal to Is not equal to
Greater than Is between Contains
Greater than or equal to Is not between Does not contain
Less than Is on or before Starts with
Less than or equal to Is before Does not start with
Is between Is on or after Ends with
Is not between Is after Does not end with

Back to TOPICS

Data Visualization

If you are having difficulties determining how to visualize your data, then here are some basics behind the different kinds of tables, charts, and crosstabs that you can create in the Ad Hoc editor.

table  Tables

Tables are the simplest view type, and primarily consist of fields. Tables can easily be sorted, grouped, summarized, and filtered. You can also conditionally format tables when you have saved your Ad Hoc view as a report.

Tables are best for reports that require finding specific values quickly, and that display both qualitative and quantitative data.

Tables consist of columns, rows, and groups:

Columns in a table correspond to the columns in the data source. They are included by adding fields or measures to the table in the Ad Hoc view.

Rows correspond to rows in the database. The information in each row depends on which columns are included in the table. 

Using groups, rows can be grouped by identical values in any field with intermediate summaries for each grouped value. For example, a table view of product orders might contain columns to show the dates and amounts of each order, and its rows might be grouped by city and product. 


bar-chart-1  Charts

Charts are useful when you would like to visualize your data, and can summarize your data graphically. When using charts, you will need to identify what your measure will be, like your number of users, time spent learning, or course completions.

Charts are best for reports that require the illustration of quantitative data, as they can easily visualize trends and relationships.

Types of charts include bar charts, line charts, and pie charts. With the exception of time series and scatter charts, each type of chart compares summarizes values for a group. Time series and scatter charts use time intervals to group data.

If you are having difficulties selecting an appropriate chart type, then you can use Dr. Andrew Abela's chart families diagram as a guide.


table (1)  Crosstabs

Crosstabs are a more compact representation than tables, as they only show computed values rather than individual database values. With crosstabs, you can summarize and aggregate your measures in a similar way to using charts, but you can also provide the specific details and information that you would have when using tables.

Crosstabs are best for reports that require drilling down on your data, and that summarize and aggregate your results.

Crosstabs also require both a field and a measure, and can be used to sort results in ascending or descending order. Columns and rows specify the dimensions for groups, while cells contain the summarized measurements. 


Back to TOPICS

Case Studies

Listed here is an ever-growing collection of case studies focused on making use of the many available features of the Ad Hoc editor. Each case study has a specified outcome, a set of steps to follow, and an example created in full detail from start to finish.

Case Study: Converting Seconds to Regular Time

Case Study: Converting Unix Time to Natural Time

Case Study: Chart Styling for Bar, Column, Pie, Scatter, Line, and Area Charts

Back to TOPICS