By Tony Marasco

As I was reorganizing my work area in my home a few weeks ago, I discovered a pile of old 80 Micro magazines – the magazine for TRS-80 computers. When I saw them, one particular monthly column from that magazine came to mind. The column was titled, “The Next Step,” and it focused on technical extensions of your TRS-80.

In that tradition, this series of Precision.BI Newsletter articles will be written in the spirit of “The Next Step” – extending Precision.BI.

Using Aggregates in a Worksheet - OVER

Analysis is the best way to retrieve aggregated data in Precision.BI. However, there are instances where an aggregate value would be useful in a Worksheet. In this example, the number of transactions per invoice will be used.

The OVER command may be used in an expression to accomplish this goal. The expression would be: COUNT( [fact_transac].[trninvnum]) OVER(PARTITION BY [fact_invoice].[invnum]). The end result shows an invoice number and the number of transactions (per invoice) in a Worksheet.

The OVER command is available in SQL Server 2005 and later. In this installment of our Newsletter, we focused on the OVER command in our “Tips and Tricks” column.

Using Aggregates in a Worksheet - Subqueries

Another way to accomplish this task is by using a correlated subquery. A correlated subquery allows the user to link results (such as an aggregate result) to a row returned by a query. For users with SQL Server 2000, the correlated subquery would be used in place of the OVER command. The results of the OVER command may be replicated with a correlated subquery using this code in an expression:

(SELECT COUNT(TrnInvNum)
FROM fact_transac t1
WHERE t1.TrnInvNum = [fact_invoice].[invnum])

Please note the beginning and ending parenthesis – these are required. In this example, data are linked to the row (invoice number) using the WHERE clause. The logic is similar to that of a Linked Group or Nested Search. Correlated subqueries require knowledge of SQL to complete.

The power of the subquery could be used to find the first payment date for an invoice. Please note that as the complexity of the query increases, the knowledge of the respective Precision.BI model and SQL increases. To find the first payment for the invoice, the expression below is used:

(SELECT MIN(act_date)
FROM fact_transac t1
JOIN dim_paycode ON TrnPayCodeID = PayCodePK
JOIN dim_date ON date_id = TrnPostDtID
WHERE t1.TrnInvNum = [fact_invoice].[invnum]
AND PayCodeCat = 'PAYMENTS')

In this example, two additional relationships (JOIN’s) are required – one for the paycode and another for the dim_date. The aggregate was changed from COUNT to MIN to compute the first (earliest) posting date of payments only. Changing the aggregate to MAX would find the most recent (latest) payment posting date.

Adding these relationships requires some additional knowledge of your Precision.BI data model. Alternatively, the Process Management administration tool may be used to determine the proper relationships needed to address fields selected from the Treeview. The recommended process is build a Worksheet that includes the fields you would like to use in your correlated subquery (in this case, transaction post date, invoice number, and paycode), run the query, copy the SQL from Process Management, and link the SQL to you main query as desired.

Next Steps

If you are interested in learning more about correlated subqueries, we are offering advanced expressions as a Web training course. Please see our Web course listing for current offerings. For consulting on these topics, please contact Karen Basham (kbasham@precisionbi.com).

We will continue with more examples of these types of advanced expressions and searches in future newsletter installments.

As a final comment, the TRS-80 is not a supported platform of Precision.BI.