Oracle Analytic Functions III: RANK() & DENSE_RANK()

Today I’ve prepared a lesson in Oracle analytic functions. Basicly these functions are an enhancement of the aggregation functions like SUM, MAX, MIN, etc. With the help of this enhancement it’s possible to calculate different aggregations within the same query. With a classic group by you can only have one aggregation per query. With analytic functions you can have more than this. It’s also possible to calculate windows, i.e. to access rows before or after the current row which gives you the ability to create dynamic window sums, etc. This basic functionality is described in part 1 and part 2 of this tutorial. Beside these basics functions there are a lot of additional functions in the database.

Today I want to demonstrate the use of the functions RANK() and DENSE_RANK(). These are used to calculate rankings in lists. The ranking depends on the sorting order which you can define within each calculation, i.e. you can create one ranking based on turnover descending and another on turnover ascending within the same query. As with all analytical functions you can also use partitioning with the ranking functions. This gives you the ability to start a new ranking for each partition. We will see that in the example. Basicly RANK() and DENSE_RANK() work in the same way but the difference lays in the way how they deal with rows that have the same ranking. If you have two rows with the same turnover they receive the same rank. The function RANK() then skips the next rank and continues with the following one. In comparision with that DENSE_RANK() continues with the next rank. Here comes the first example to demonstrate the basic stuff. This example is based on the TBL_TEST table (it consists of four columns: DAY, PRODUCT_NO, PRODUCT_NAME, TURNOVER. See also the example files):

SELECT x.*,
       RANK() OVER(ORDER BY PRODUCT_NO) AS RANK_NO,
       DENSE_RANK() OVER(ORDER BY PRODUCT_NO) AS DRANK_NO
FROM (
      SELECT to_char(DAY,’YYYY-MM’) AS MONTH,
             PRODUCT_NO,
             SUM(TURNOVER) as TURNOVER
      FROM TBL_TEST
      GROUP BY to_char(DAY,’YYYY-MM’), PRODUCT_NO
) x

Result:

BLOG_0028_PIC01_Example_RANK_DENSE_RANK

This example works with a sub-query to aggregate the data to a month based level. Then in the main query RANK() and DENSE_RANK() are used to calculate a ranking based on the product-no column. For that PRODUCT_NO appears in the ORDER BY of the RANK and DENSE_RANK functions. In the results you can see that the first four rows have the same rank. The function RANK() continues with rank 5 then but DENSE_RANK with 2. That’s the difference between these two functions when rows have the same rank.

I’ve mentioned that it’s possible to create a new ranking which each partition. The next example deals with that:

SELECT x.*,
       RANK() OVER(PARTITION BY MONTH ORDER BY PRODUCT_NO) AS RANK_NO
FROM (
      SELECT to_char(DAY,’YYYY-MM’) AS MONTH,
                    PRODUCT_NO,
                    SUM(TURNOVER) as TURNOVER
      FROM TBL_TEST
      GROUP BY to_char(DAY,’YYYY-MM’), PRODUCT_NO
) x;

Result:

BLOG_0028_PIC02_Example_RANK_PARTITION_BY

Again we have a sub-query to aggregate daily values in the table to monthly values. In the main query we just added a PARTITION BY MONTH to the definition of the RANK_NO column and that changes the list in that way that we have a ranking per month now.

To finish todays article I’ve created a small task. The solution can be found in the download area:

Produce a list with products, days and turnover. Create a ranking based on the turnover (of product and day). The data should be filtered to January 2017. And in the result the 3 top days should be displayed. The list should look like this one:

BLOG_0028_PIC03_Task1_Result

That’s it for RANK and DENSE_RANK. I hope you enjoyed today’s lesson.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Download

BLOG_0028_RANK_DENSE_RANK_Examples_TASK.zip

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

Macro functions in Cognos Report Studio

Hi,

today I will deal with macro functions. I had a customer who wanted to switch between two dimensions to drill up & down in a dimensional report. That means you have to dynamically change the MUN(=member unique name) for this element to decide whether to use dimension A or dimension B.

The two MUNs for the dimensions looked like this on the highest level:

  1. [Dimensional].[PG].[PG].[Product Group]
  2. [Dimensional].[AX].[AX].[Target Group]

To solve this task I used the following macro functions:

  • Operator +
  • Function substitute (<search pattern>, <replace text>, <orig text>)
  • Function prompt (<p_name>, <data type>, <default>)

To use macro functions you have to define a  macro block like this:  # ….… #. Between the # symbols you will place the macro functions. The macro then defines the expression throughout the macro functions. In our case it will result in one of the two MUNs described above.

Example: You could create a query with a query item that contains the following macro function: # ‘Test1‘+‘Hello‘ #. This will result in the string Test1Hello at runtime in this query item. It’s like you would place this string in the query item by yourself but with macro functions you have the opportunity to dynamically define such expressions.

If we take a look at our example. We’ve create a text: Test1Hello. If we placed this into the query item we would receive an error because of the syntax. We need to put ‘  ‘ around it to  make it a string. For this we can use the macro function sq( … ). Sq stands for single quotes. We will enhance our little example to this: # sq(‘Test1‘ + ‘Hello‘) #. This will result in the string ‘Test1Hello‘ and no error will occur. By the way the operator + concats two strings.

To get back at my customer’s problem. The function substitute(…) will replace the string <search pattern> within the string <orig text> by the string <replace text>. Prompt(…) will ask for parameters and returns the value of a parameter. First argument of this function is the name oft he parameter, second the datatype and third a default value. The third argument is optional. Regarding the datatype. There is the string which is text with single quotes around it and there exists token which is a text too but without single quotes. If you have a prompt page with this parameter it won’t be asked for it again but the value is returned here as well.

With the knowledge of these functions we can now come to the solution for my problem:

# ‘[Dimensional].[' +  prompt('p_proddim', 'token' ,'PG')  +  '].['  +  prompt('p_proddim', 'token' ,'PG')  +  '].['  +  substitute('AX', 'Target Group',  substitute('PG', 'Product Group', prompt('p_proddim', 'token' ,'PG')))  +  ']‘   #

The parameter p_proddim contains the selected dimensions (‘PG’ or ‘AX’). First we take the constant text at the beginning (‘[Dimensional.[‘) then we concat the content of p_proddim then the string ‘].[‘ and then again the content of p_proddim. The result is: [Dimensional].[PG].[PG].[ Instead of PG could also be AX depending on the parameter’s selection. The next part is something like an if-then-else. But it’s realized via a nested substitute call. The inner substitute takes the parameter value of p_proddim and checks the value for ‘PG’. If it is found it’s replaced by ‘Product Group’. If not nothing happens. The result of this substitute is the text to be checked in the outer substitute. This one is checked for ‘AX’ and is then replaced by ‘Target Group’.

This is one example how to use macro functions. There are a lot others. In future articles I will deal with macro functions again.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

How to filter a time interval in a dimensional report

Hi,

today we will filter on a time intervall in a dimensional report with the help of two parameters. When you are developing a dimensional report (DMR or OLAP) then you have to create so called member sets, which you then can query. Example: You are creating a meber set fo rthe current year and then you can calculate the turnover for this member set. The code looks like this: total([Umsatz] within set [<MemberSet>]

With this style of developing reports you are facing a problem right now, i.e. that you have to define explicitly the items you need for the calculations. For example if you want to query februrary to november of a certain year then you have to explicitly define all these months. In case of a time dimension this is not common and also not very practical. What you want is to just select the start and end month and months in between should be selected automatically.

If you were creating a relational report then you would have used the BETWEEN operator. This is not possible in dimensional report development, but what you can do is to create the meber set with the help of some functions. We will use the following functions:

  • Lastperiods(<no. of periods>, <last item>)
  • Operator  ->
  • Intersect (<Set1>, <Set2>)

The function lastperios (…) lets you define a set, which ends with <last item> and contains the <no. of periods> previous members. Lastperiods(12, [Dec. 2013]) would contain all months in 2013. If you chose a negative value for <no. of periods> then you receive the following months.

The operator -> picks a specific meber in a hierarchy. You can also use parameters with it, e.g. [Dimensional layer].[Date (Month)].[Date (Month)].[Month]->?pMonth? It possible to combine the function and the operator in order to receive for example 3 months before the selected month.

To eventually define our interval we a third function: intersect(…). This functions returns the intersection set of two given sets., i.e. the mebers that are existing a both sets are returned. If we have two sets: Set1=(1,2,3) & Set2=(3,4,5) then the intersection is (3) because this ist he only item which is contained by both sets.

To filter on a time inteval we can now build two sets:

  • Set1 = lastperiods (-120; [Dimensional].[…..].[Month]->?pMonthStart?)
  • Set2 = lastperiods (120; [Dimensional].[…..].[Month]->?pMonthEnd?)

Presented in a picture it looks like this:

BLOG_0026_PIC01_Time_Interval_Set

To create the result set we use the intersect function on the two sets. And then we can do the calculation on the result set.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

How to show/hide a parameter section in a Report

Hi,

For a customer I was developing a new report this week. He asked me to implement a section in the report header where you can see what parameters are selected in the report. Because there  are a lot of parameters in my customer’s report he wanted some fancy function so you can easily show or hide this parameter section.

The solution works with two html items, which just contain a <div …> and a </div> tag and a Java Script function which shows or hides the div block. The example report doesn’t contain a list or something. It’s just having the parameter section on the report page and a link to show/hide it. But you can easily implement it in your reports as well. It’s also not very beautiful. For example you could use a nice picture instead of a link.

As I’ve written before you need two html blocks.  These blocks contain the following code:

HTML Block 0(JavaScript)
<script type=”text/javascript” >
 
function ShowHideDIV(){
   if (document.getElementById(‘testdiv’).style.display != ‘none’) {
        document.getElementById(‘testdiv’).style.display = ‘none’;
   }
   else {
       document.getElementById(‘testdiv’).style.display = ‘block’;
   };
};
</script>
HTML Block 1
<a href=”javascript: ShowHideDIV();”> Show/Hide parameters </a>
 
<div id=’testdiv’>
 
HTML Block 2 </div>

This solution uses the CSS (Cascading Style Sheets) which describe formatting properties of an HTML document.  As the Cognos report results are normally HTML documents as well you can use this knowledge to dynamically set or unset the property for hiding a div block. If you want to enhance this solution you can pass the name of the block via a variable to the function.

Anyway these HTML blocks must then be inserted to the report like this:

BLOG_0024_PIC01_Overview_Report_Page

 

The very first block in the page content area must be the HTML item with the Javascript. Then around the table or text-items with the parameter description you have to place HTML Block 1 and HTML Block 2. I’ve chose a table to represent the prompt parametes. After the HTML Block 2 you can insert the report data itself.

The examples used in this article can be found at the very end of this article as zip-file. I will continue this series soon and we will then deal with some useful functions you can use if you are working with the rollup or cube statement.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Download

BLOG_0025_Example_Report.zip

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter

Group By extensions IV: Functions

Hi,

This is the last part of my article series on SQL’s Group By extensions. In the first part we dealt with the ROLLUP statement, the second part then covered the CUBE statement and in the third part we discovered flexibility with the GROUPING SETS. Today we will take a look at two functions that are sometimes used to filter some of the auto-generated sums of ROLLUP and CUBE. The names of the functions are:

  • GROUPING(<expression>)
  • GROUPING_ID(<expression1>, <expression2>, …, <expression n>)

The examples are based on the test data of part 2, i.e. the table looks something like this:

Day Product No Product Name Turnover
01.01.2017 1 Product 1 20
2 Product 2 30
3 Product 3 40
02.01.2017 1 Product 1 30
2 Product 2 40
3 Product 3 50
….
30.04.2017 3 Product 3 330

 

The GROUPING() function is used to check whether an expression was used in the grouping or not. Here comes a small example for that:

SELECT  to_char(DAY, ‘YYYY-Q’) AS QUARTER_ID,
              to_char(DAY, ‘YYYY-MM’) AS MONTH_ID,
              SUM(TURNOVER) AS TURNOVER,
              GROUPING(to_char(DAY, ‘YYYY-Q’)) AS GRP_QUARTER,
              GROUPING(to_char(DAY, ‘YYYY-MM’)) AS GRP_MONTH
     
FROM TBL_TEST
GROUP BY ROLLUP(to_char(DAY, ‘YYYY-Q’), to_char(DAY, ‘YYYY-MM’))
ORDER BY QUARTER_ID, MONTH_ID;
 

Result:

BLOG_0023_PIC01_Result_Query_1

In this example you can see how the GROUPING() function basically works. For the most detailed level (I.e. month) both functions return 0 as the month_id and the quarter_id columns are grouped by. For the quarter sums the month_id is not grouped by and therefore the GROUPING() function returns 1 for this column. For the total sum both id-columns are not in the GROUP BY and that leads to a result of 1 for both GROUPIN() functions.

You can use these results to decide what sums you want to keep or remove. For that you usually need these functions when you use CUBE or ROLLUP. With GROUPING SETS you normally don’t have the need for these functions as you can exactly define what sums you need.

The GROUPING_ID() function is the combination of several GROUPING() functions in the way that if you take several GROUPING results (which are always 1 or 0 = binary) and put them in a row you receive a binary string which can be interpreted as a decimal number and that one is returned by GROUPING_ID. You receive a different ID for each different kind of sum. You then only have one column with the ID instead of several columns as with the GROUPING() function. Now here is an example for that:

SELECT  to_char(DAY, ‘YYYY-Q’) AS QUARTER_ID,
              to_char(DAY, ‘YYYY-MM’) AS MONTH_ID,
              SUM(TURNOVER) AS TURNOVER,
              GROUPING(to_char(DAY, ‘YYYY-Q’)) AS GRP_QUARTER,
              GROUPING(to_char(DAY, ‘YYYY-MM’)) AS GRP_MONTH,
              GROUPING_ID(to_char(DAY, ‘YYYY-Q’), to_char(DAY, ‘YYYY-MM’)) AS GRP_ID
 
FROM TBL_TEST
GROUP BY ROLLUP(to_char(DAY, ‘YYYY-Q’), to_char(DAY, ‘YYYY-MM’))
ORDER BY QUARTER_ID, MONTH_ID;

Result:

BLOG_0023_PIC02_Result_Query_2

 

I’ve take the first example and added only the last row in the SELECT. This is the GROUPING_ID() function which takes several expressions. In this example we put the expressions of the QUARTER_ID and MONTH_ID columns. The order of the expressions is important. It defines on what position of the binary string the result of the GROUPING of the column is put. If you take a look at row number 4. The GROUPING of Quarter is 0 and the GROUPING of Month is 1. As an binary string this would be 01 and if you transform it to a decimal number it’s: 0*2+1*2° = 0 + 1 = 1. If we check line no. 7 we have 11 as a binary string and decimal it’s: 1*2 + 1*2° = 2 + 1 = 3.

Now another example for the GROUPING():

SELECT  to_char(DAY, ‘YYYY-Q’) AS QUARTER_ID,
              to_char(DAY, ‘YYYY-MM’) AS MONTH_ID,
              DAY,
              SUM(TURNOVER) AS TURNOVER,
              GROUPING(to_char(DAY, ‘YYYY-Q’)) AS GRP_QUARTER,
              GROUPING(to_char(DAY, ‘YYYY-MM’)) AS GRP_MONTH,
              GROUPING(DAY) AS GRP_DAY,
              GROUPING_ID(to_char(DAY, ‘YYYY-Q’), to_char(DAY, ‘YYYY-MM’),
                                      DAY) AS GRP_ID     
 
FROM TBL_TEST
WHERE to_char(DAY,’DD’) IN (’01′, ’02′, ’03′)
GROUP BY ROLLUP(to_char(DAY, ‘YYYY-Q’), to_char(DAY, ‘YYYY-MM’), DAY)
ORDER BY QUARTER_ID, MONTH_ID, DAY;

Result:

BLOG_0023_PIC03_Result_Query_3

This example is based on the second example. In addition I’ve added the DAY column in the SELECT and ROLLUP. Also I’ve added a GROUPING() for DAY and added it to the GROUPING_ID() function. A filter in the WHERE clause just limit the rows in the result. It’s pretty much the same as in the second example. But you have one column more in this example. The daily sums have a GROUPING_ID of 0, the monthly SUMS of 1, the quarterly sums of 3 and the total sum a GROUPING_ID of 7. The 7 is calculated like this: 111 = 1*2² + 1*2 + 1*2° = 4 + 2 + 1 = 7.

I hope it became clear a little how the functions GROUPING() and GROUPING_ID() works and what they can be used for. These functions can be used in the HAVING clause to filter out some sums. Also these functions can be used with CUBE and also GROUPING SETS.

The examples used in this article can be found at the very end of this article as zip-file. I will continue this series soon and we will then deal with some useful functions you can use if you are working with the rollup or cube statement.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

 

Downloads

BLOG_0023_Testdata_&_Examples.zip

Share and Enjoy: Diese Icons verlinken auf Bookmark Dienste bei denen Nutzer neue Inhalte finden und mit anderen teilen können.
  • LinkedIn
  • XING
  • Facebook
  • Google Bookmarks
  • Twitter