Archiv für Januar 31, 2017

| Januar 31, 2017
Ein Kommentar

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)!

Kategorie: Cognos Reporting | Tags:, , , , ,
| Januar 25, 2017
Ein Kommentar

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

Kategorie: Cognos Reporting | Tags:, , ,
| Januar 25, 2017
Keine Kommentare

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

Kategorie: Oracle SQL | Tags:, , , , , ,
| Januar 16, 2017
Keine Kommentare

Group By extensions III: GROUPING SETS

Hi,

again something on the GROUP BY extensions. Today we will deal with GROUPING SETS. Probably the most flexible and easiest way to create multiple GROUP BYs within one query. In part 1 of this series we discovered the ROLLUP clause, which let’s us automatically calculate totals along some kind of dimension, which is defined by the grouping columns. Part 2 described the CUBE clause which just simply calculate all possible sums based on the grouping columns.

GROUPING SETS let you exactly specify the totals you want to see in your query. If you just want a report total and some monthly totals then you can just define that and you get it. If you need all the totals for all levels of a dimension a ROLLUP might be easier, but in most cases GROUPING SETS will probably be.

Now let’s get to coding. 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

 

In my first example I just want a monthly toal and a report total. With GROUPING SETS it looks like this:

SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,
            SUM(TURNOVER) AS TURNOVER
FROM TBL_TEST
GROUP BY GROUPING SETS( (to_char(DAY,’MM/YYYY‘)),
                                                    ()
                                           );

Result:

BLOG_0022_PIC01_Result_Query1

As you can see in this example you just put all the different GROUP BYs in different sets of brackets. All the columns that belong to a specific GROUP BY are put to one pair of brackets. The different GROUP BYs are then separated by comma. If a GROUP BY only consist of one column you could theoretically leave out the brackets. If you want a report total you leave the brackets empty.

Now another example with a monthly total, a report total and a total per month and product:
SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,
            PRODUCT_NO,
            PRODUCT_NAME,
            SUM(TURNOVER) AS TURNOVER
FROM TBL_TEST
GROUP BY GROUPING SETS( to_char(DAY,’MM/YYYY‘),
                                            (),
                                           (to_char(DAY,’MM/YYYY‘), PRODUCT_NO, PRODUCT_NAME)
                                          )
ORDER BY MONTH, PRODUCT_NO NULLS LAST

Result:

BLOG_0022_PIC02_Result_Query2

In this example we have calculated three sums as you can see directly in the GROUPING SETS section of the SQL. This example could have been produced also with a ROLLUP if you change the clauses and leave out the first two rows of the GROUPING SETS section. As you can see often there are several ways to reach the target. For larger reports and if you need a lot of sums maybe ROLLUP makes it easier for you and the database. But sometimes you only need a few specific totals out of a lot of different combinations then maybe GROUPING SETS are easier to use.

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 GROUP BY extensions.

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_0022_Testdata_Examples.zip

Kategorie: MySQL, Oracle SQL |
| Januar 9, 2017
Ein Kommentar

Group By extensions II: CUBE

This is the second part of the series about GROUP BY extensions. In the first part of this series we discovered the ROLLUP statement which lets you easily calculate totals on different levels on granularity without using several queries. ROLLUP just calculates the totals along an artificial dimension which can be defined through expressions for each level.

Beside ROLLUP there is another extension which is called CUBE. CUBE calculates all possible totals for the given expressions. If you have Month and Product in your query and using CUBE then there calculated for totals: Month, Product, Month and Product, Total Query.

Let’s think of the following table with test data:

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 first example creates a cube for day and product (no + name):

SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,
              PRODUCT_NO,
              PRODUCT_NAME,
              SUM(TURNOVER)
FROM TBL_TEST
GROUP BY CUBE ( to_char(DAY,’MM/YYYY‘),
                                (PRODUCT_NO, PRODUCT_NAME)
                              );

Result:

BLOG_0020_PIC01_Result_Query1

This query generates 20 rows. The first 12 rows are the most detailed totals, i.e. a total for month and product. Line 13 – 15 are the totals per product, line 16 – 19 are the totals per month and line 20 is the query total.

To get this result you only have to use the CUBE statement instead of ROLLUP. CUBE then generates all possible sums. If you want two columns to be treated as one (e.g. product no and product name) then you use extra brackets for it ( take a look at the example). Of course you could add more expressions to the CUBE but this always leads to a lot more sums. If you have three expressions in the CUBE then you’ll get 2³=8 totals.

That leads sometimes to the question what to do if you need only specific totals but not all. You could use a CUBE then and use the HAVING to filter out the not needed totals based on values in the grouping columns.

The second example shows how to do that:

SELECT to_char(DAY,’MM/YYYY‘) AS MONTH,
             PRODUCT_NO,
             PRODUCT_NAME,
             SUM(TURNOVER)
FROM TBL_TEST
GROUP BY CUBE ( to_char(DAY,’MM/YYYY‘),

                                (PRODUCT_NO, PRODUCT_NAME)

                               )
HAVING PRODUCT_NO IS NOT NULL AND to_char(DAY, ‚MM/YYYY‘) IS NULL
                OR PRODUCT_NO IS NULL AND to_char(DAY, ‚MM/YYYY‘) IS NOT NULL;

Result:

BLOG_0020_PIC02_Result_Query2

In this SQL I have added the two lines for the HAVING clause. With these two lines I tell the database what sums out of all possible sums I am really interested in. In order to do that I just check that either the month is null and product not or month is not null but product is. Only in these two cases I want the line to be displayed.

Like the ROLLUP CUBE is available on Oracle, MS SQL Server, DB2, Postgres, etc., but not on MySQL. I hope you enjoyed today’s lesson again. 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 the so called GROUPING SETS which let you define sums and totals in a very detailed way.

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_0020_Testdata_Examples_zip

Kategorie: Oracle SQL |
« Ältere Beiträge