How to make Dynamic, Variable # Top/Bottom, Ranked, Drill-down Report Using Dynamic AI

By Bob Cergol

It takes just one mouse click on a total column heading in a Dynamic AI report to make it a Top 10 or Bottom 10 report, and just a few mouse clicks can combine multiple Top 10 reports into a single presentation like this Top 10 dashboard. But what if you want to let the user choose the number to rank, and choose if the report ranks from the bottom instead of the top of the totals? What if, unlike the examples presented in the above Top 10 Dashboard, you also want the rank number to appear on each row? This article describes how to do all that, and more, while retaining the full drill-down path to the detail data in the totals.

The finished report shows the DAW Order entry example customers ranked by their total order sales. The user can specify how many customers to show in the ranking and whether to rank according to being at the top or the bottom in sales. In addition to the ranked totals, the report will show what percentage that top/bottom total is of sales for all customers, what the sales total is for the other or remaining customers not in the top/bottom, and what percentage that is of all customer sales. Drill down on a single customer will show those same KPIs for that single customer.

Other than a really simple SQL statement and some formula expressions there is no programming involved in creating this report with all its functionality. The techniques used in this innocent looking example illustrate a nice cross-section of functionality available in Dynamic AI.

• Dyn-Views
• Dyn-Parameters
• Dyn-Partitions
• Dyn-Relations
• Key-Figures
• Cross-report key-figure linking
• Formula columns
• Charting
• Drill-down to related report
• Custom report and chart titles
• Embedded URLs in report to call Dataflex WebApp view

I highly recommend you try running the actual report before reading this article to provide context for the ensuing explanations. You can run this report example yourself here: http://www.dataaccess.com/LiveDynamicAI, from this illustrated menu option:

 

Step 1: Create the dyn-View that generates the ranking numbers.

Dynamic AI can easily do Top#/Bottom# on top of any data source for any total in the first grouping of the drill-down path, so there is routinely no need to do this yourself in an SQL view. It’s almost always better to just let Dynamic AI do it for you!

However Dynamic AI won’t number the rows it outputs in the various group levels within a drill-down path; it will only generate row numbers on the output at the final, detail row level. That would be okay and we could just output the detail rows of the view we need to make, and let Dynamic AI number them, except we also want to show the key performance indicators for a single customer when selected. A detail row is not a grouping so there is no group drill down path for the key figures to follow. A version of the report shown above just showing the details without any grouping, where Dynamic AI numbers the rows would look like this:

But the above version has no drill-down path since it is already showing the most detail data rows from the data source (the dyn-view shown below). Therefore we want to define a grouping in our report that essentially duplicates the detail row, but we’ll keep the detail output in its own level as the screen shots above illustrate. So our view will also need to generate the row number values, or rankings, so we’ll have that data to output in the customer name grouping. This next screenshot shows how there is only a single detail row in the data source for each grouping in the report, so it would be redundant to show them both in the same level, and would also defeat the purpose of being able to select a single one to see the KPI totals for it, i.e the detail rows must be pushed down into their own level, which is the what the screen shots at the top of this article illustrate.

From the Dynamic AI Databases menu you can create a new dynamic SQL view, specifying a name for the view, the connection to be used and optionally, a group that ownership of the view (ability to edit it) will be shared with.

Below is the basic SQL to generate a data source delivering the top 10 customer IDs by sales total. Ordering the totals descending will make the largest total the first row in the output.

---

---

Next we use that query as a derived table, or sub-query within another query to generate the ranking values on each row and to join the order and customer tables to get the customer name. (Note that the SQL Rank and Row functions are interchangeable in this example because the inner query delivers exactly the number of rows being ranked, and ordered as specified, but Rank will behave differently in different use-cases. Rank returns the rank of each row within the partition (not used or shown in SQL below) of a result set. The rank of a row is one plus the number of ranks that come before the row in question. Row_Number returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. )

---

---

Finally, we replace the query constants “10” used by the “Top” operator, and the “desc” (descending) argument used in the order-by clauses with some Dynamic AI symbols that will be replaced with values provided by the user at runtime, when it generates the final dynamic SQL to run the report. We will use a dyn-parameter and a dyn-partition. You can define 10 dyn-parameters in a dyn-view, but only 1 dyn-partition. A dyn-parameter is for any single value (date or string of characters) entered by the user at runtime. (SQL Injection protection is of course applied!) A dyn-partition is for a developer-defined data list where each row in the list contains one or more parameter name-value pairs. The user can only select a single row from the list and cannot change, or even see, what those values are. The idea is to let the user specify any number for the ranking, i.e. Top 5, top 25, etc. and to specify whether it should be a top or bottom ranking. We could use a dyn-parameter and expect the user to enter the correct SQL keyword for sorting, but instead of asking the user to know the internal workings of an “order by Desc/Asc” clause, we want to present a user-friendly choice of top or bottom in a drop-down list. At runtime, Dynamic AI will automatically generate the user-interface objects that prompt the user for these values and it will replace the symbols in the SQL with these values.

(Note: dyn-partition has nothing to do with SQL partitions or window functions. It is simply a way to pass in a pre-defined set of values into the dynamic SQL. The Dynamic AI documentation mainly describes using dyn-partitions to dynamically specify table names to open in the “from” clause, but conceptually it is simply a technique for dynamic replacement of practically unlimited variables in a dyn-view.)

---

---

 Step 2: Define the dyn-Parameter that prompts the user for how many customers to show in the ranking.

Click the “@PARn@” button (See highlighted items in above screen shot.) to expose the screen to define the parameters, and enter a caption, and if desired a default value as shown. You can define a maximum of 10 parameters in any given dyn-view. Any entries for @PAR#@ that are not referenced at least once in the SQL script will not be saved.

Step 3: Define the data source on which a dynamic list report will be made that will be used to provide the name-value pairs for your dyn-partition.

A partition list is a Dynamic AI list report built on a data source with a least two columns. One column contains a description or name for the partition that is displayed to the user in a drop down prompt list. The other column contains all of the name-value pairs that are available for use in the dyn-view’s SQL script. The names recognized by Dynamic AI for the values are:

Partition – the name for the value that will replace @DYNPARTITION@ in the SQL
Desc – the name for the value that will be displayed in the header of the report that describes the partition being used for the report.
P1 .. P# -- names for optional, additional parameter values, i.e. P1, P2, P3, etc. In this example we don’t define any additional parameters.

Each of these names is delimited in a begin/end fashion using these special characters: starting tag: !¤ and ending tag: ¤! and immediately followed by the data value for that parameter name. Unless you are using a Danish keyboard, you’ll have to either copy the character or, on a US keyboard, enter it using the key combination <ALT> 164.

Since it is pretty unlikely you would have data like this ready-made in some existing table, you will likely generate it in a SQL view using expressions to concatenate these special characters and tags with actual table data. In this example I hard-coded it all in a simple SQL view that produces two rows of data.

---

---

Step 4: Build a Dynamic AI List report using the data source created in step 3. The result for our example will look exactly like the “Top/Bottom Partition” preview shown in the screenshot in step 5.

Step 5: Assign the partition list to the dyn-view.
When you click the “partition” button at the bottom of the Dynamic SQL View screen it will display a form where you can enter the caption the user will see at runtime, specify the report from the Dynamic AI repository that will supply the partition data list, and specify which columns from the data list will provide the parameter-value pairs and the drop down display values.

Step 7: Build the final report.

Covering all the steps to build a report is beyond the scope of this article but all the basics are nicely covered in the first 10 minutes of this video: http://media.dataaccess.com/tutorials/GettingStartedWithVDFWebReporting-1.mp4

Let’s just look at some unique specifics for this report example and that are illustrated in the subsequent screenshots:an extra column and key-figure calculations were added

• the extra column formula was used as a grouping, and also selected for output in details (not shown in screenshot)

• the ranking value in the extra column formula was padded with leading zeros so it would line up nicely, and was concatenated with the customer name, that was also surrounded by html bolding tags to make it stand out from the rank number in the displayed string.

• The key figure formulas reference key figures from another report (Dynamic AI assigned ID of 1085). The purpose of that other report is to provide the total of all customer sales. It is not a report intended for users to run, though it could be, and is simply a barebones report that totals all sales and defines a key figure named “Total Orders Amount” that you can see is referenced in the key figure formulas of our ranking report. (BTW, the on-screen formula editor is an easy way to build formulas using point-and-click on the live report.)

• Drill-down to another report – as seen on the screen shot showing the General tab in design. In Dynamic AI any report built on a data source that is defined to relate to the data source used by the “parent” report, becomes available to choose as the drill-down object from a detail row. Otherwise drill-down from detail row can be none or to a form. The form can be a custom layout, or auto-generated as floating or column format. Two data sources are related by defining a dyn-relation (see screenshot below). Keep in mind a data source can be a table or backend view, a dyn-view, or a dyn-join, and they can come from different database connections, so this is really powerful for linking data from disparate sources from various applications.

• An interactive horizontal bar chart was specified as seen on the screen shot showing the General tab in design.

• The report and chart titles displayed are defined to be different from the report name as shown in the screen shot of the Rules tab in design. The report title can include references to report filters so the tile could include the run-time filter values that are applied, though this is usually not needed, since by default Dynamic AI will display all of the filters in effect, including the values being applied in the drill-down path when users navigate into the available report levels.

Let’s have a look at how that related drill-down report generates a link to the Dataflex WebApp order view. (Note: the hardest part about making that formula was getting the quote delimiters right. All that you see are made up of the single quote character.)

Lastly, let’s show the source code that embeds and executes the report in the Dataflex WebApp.

---

---

And here is the relevant code from oDynamicAiRptView, the main source lines are bolded:

---

---