Archiv für Dezember 31, 2013

| Dezember 31, 2013
3 Kommentare

Cognos & JavaScript – Part II: Dropdown prompts

In the second part of the Java Script series we want to access drop down lists. That also includes list boxes, radio button groups and check box groups as they work based on the same principles.

Again we are focusing on dates to be selected. In todays’ issue we need a Drop Down Box with months. The USE Value should be some number like 201201, 201202, 201203 and so on. The display value can be as you want it to be. It doesn’t really matter if you add the values by static choices or a query. Let’s name the Box “DropDownTest”.  My examples are based on the three entries above as static choices.

The general syntax to access for example the 2nd item in the drop down box is as follows:

<script language=“JavaScript“>

var frm = getFormWarpRequest();

alert (frm._oLstChoicesDropDownTest.options[3].text);

</script>

With the above snippet you can read the display value of the second item in the list. options[] is the array that contains all the items of the list or dropdown box. The parameter name and a separator line ‘———–‘ are normally also part of this array. Array indexes in Java Script starts with 0. That’s why we have to use an index of 3 for accessing the first “real” item.

We have several properties in the options[] array that might be interesting:

  • text -> display value
  • value -> use value
  • selected -> true, if item is selected.
  • length -> method to return the number of items in the array

With the next script you can select an item of the list:

<script language=“JavaScript“>

      var frm = getFormWarpRequest();

      frm._oLstChoicesDropDownTest.options[3].selected=”true”;

</script>

Now the 3rd item of the list is selected, i.e. the first relevant item.

With the above scripts you can easily select the value that is relevant in your case. For example you could check for the current date (also look at the article: Cognos & JavaScript Part 1) and depending on that you can select the current month or one month before.

In the next part of this series I will explain how to set up an event handler to react on choices that the user made to certain prompts.

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:, , ,
| Dezember 18, 2013
Keine Kommentare

Inner joins explained

In today’s issue I want to explain the different options of combining data of multiple tables (Joins) in Oracle. There are several options, the following are the most common ones:

  • Inner join / equi join (this article)
  • Left/right outer join
  • Cross Join / cross product

Let’s say you have two tables with data:

ID Name Age
1 Gaussling 32
2 Smith 45
3 Meier 25

Table1: TBL_CUSTOMER

Date Customer_ID Turnover
2013-12-01 1 100 €
2013-12-02 1 50€
2013-12-02 2 200€
2013-12-03 3 75€

Table2: TBL_SALES

The most common case is the inner join. In the above example we maybe want to know how much turnover we made with each customer. For this we can use an inner join:

SELECT c.Name, SUM(t.Turnover)
FROM TBL_CUSTOMER c JOIN TBL_SALES t ON c.ID=t.Customer_ID
GROUP BY c.Name

The database now would go through the TBL_SALES table and for each row it would check in TBL_CUSTOMER whether an ID matching the Customer_ID of that row. If so it would connect that row of  TBL_CUSTOMER with the row of TBL_SALES. If it would find multiple rows with ID=1 in TBL_CUSTOMER it would combine each of these rows with the matching TBL_SALES row.

The syntax is pretty easy. I marked the relevant keywords bold. For joining two tables you just write the keyword JOIN between these tables. After the second table you have to write the key ON followed by the joining condition. The joining condition tells the database on which columns in the two tables the join is to be performed. You can also join over two or more columns. These conditions can be added by AND.

In the above example we want to join the two tables over the ID and Customer_ID column. So we just write …. ON c.ID=t.Customer_ID. After combining the two tables the results are aggregated.

Now imagine the Customer table looks like this (because of an error or whatever):

ID Name Age
1 Gaussling 32
2 Smith 45
3 Meier 25
2 Gaussling2 33

Table1: TBL_CUSTOMER

Our SQL looks like this

SELECT *
FROM TBL_CUSTOMER c JOIN TBL_SALES t ON c.ID=t.Customer_ID

Now the result set would look like this:

ID Name Age Date Customer_Id Turnover
1 Gaussling 32 2013-12-01 1 100€
1 Gaussling 32 2013-12-02 1 50€
2 Smith 45 2013-12-02 2 200€
3 Meier 25 2013-12-03 3 75€
2 Gaussling2 33 2013-12-02 2 200€

The green marked row is the one out of the TBL_SALES that is duplicated now because in the TBL_CUSTOMER there are two rows with the ID=2.

I hope it got a little clearer what an inner join is and how it works. Also I tried to figure how some strange results (with duplicated) rows might occur. In future articles I will also explain the other join types.

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: Oracle SQL, SQL Tutorial | Tags:, , , ,
| Dezember 12, 2013
Keine Kommentare

How to burst reports in Cognos

Cognos has an easy way to automatically distribute reports to different users and groups. In Cognos terms this is meant by “bursting” a report. Let’s assume that our report to be distributed looks like this:

Query Bursting

We have 3 shops, selling different products and the above report might be some kind of sales statistics. We now want to create three pdf files, one for each shop. This can be achieved by bursting this report. In our case we want to send the pdf via eMail. So we need a second query that gives us the emails for each of the shops.

Query eMails

In this query we have the SHOP_NO that matches with the SHOP_NO in the query above. For each SHOP_NO we have a target email address.

Now we have all the stuff we need to enable bursting in the reports. Busting can be activated under File->Burst options:

Dialog Burst Options

The checkbox has to be checked. Then on the left side a query must be selected on which base the report should be burst. Furthermore you have to define a group within the result query that is used to cut the report result into sub parts and these sub parts will be sent to the shops.

On the right side you then select the query that contains the target email addresses. The type can be set to automatic. A master-detail-relationship must be defined to connect both queries with each other. You can also use the same query for both the data and the email addresses. Then you don’t have to define a master-detail-relationship.

In our example we have chosen the QRY_SALES as our source for the burst data. The grouping is based on SHOP_NO. Our query QRY_SHOPS is taken as source for the target emails. We connect both queries with a master-detail-relationship on the column SHOP_NO.

Now everything is configured within the report. Now we can schedule our report. In the form for the schedule we have to activate the checkbox that the report should be bursted too. We chose PDF as file format and select “send by email”. Within the eMail options you can define whether to the pdf itself or a link to the pdf within cognos connection.

Then we just have to wait for the schedule to be executed…

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:, , , , ,
| Dezember 11, 2013
Keine Kommentare

Oracle Analytic Functions – Part II

As this is the second part of my series about Oracle’s analytic functions I would like to step ahead with two more functions:

  • LEAD-function
  • LAG-function

Sometimes you need to access the preceding or following data row. In these cases you can use the above functions. Let’s use the following table to build some examples:

MONTH QTY TURNOVER
1 201211 3 125
2 201212 5 500
3 201301 1 100
4 201302 2 150
5 201303 3 250
6 201304 4 450

Fig.1: TBL_SALES

 

If you want to show the turnover of every month compared with the turnover of the month before you can use the Lag-Function. The general syntax for both functions is the following:

Lead/Lag (<Value>, <No of Rows>, <Default>) Over (Partition by … Order by ….)

Please note that you have to use the order by clause for these two functions. Lead returns the following row and Lag the preceding row. <Value> describes the Column of which the following or preceding value is returned. <No of rows> sets how many rows in front or back the system should look for and return a value. If there isn’t a value available (within the partition) then the <default> value is returned.

Let’s develop the first example:

       SELECT MONTH,
                TURNOVER,
                LAG(TURNOVER,1,-1) OVER (ORDER BY MONTH ASC) AS TURNOVER_2
FROM TBL_SALES

The result should look like this:

MONTH               TURNOVER            TURNOVER_2
——————————————————-
201211                125                   -1
201212                500                  125
201301                100                   500
201302                150                   100
201303                250                   150
201304                450                   250

As you can see the TURNOVER_2  column contains the value of the turnover column of the row before. The very first row of our little example TURNOVER_2 is -1 cause there is no row before and because of this a value can’t be found and the -1 is returned instead. If you wouldn’t define a default value then NULL is returned.

Now we change our statement a little:

       SELECT MONTH,
                TURNOVER,
                LAG(TURNOVER,1,-1) OVER (PARTITION BY substr(MONTH,1,4)
                                                      ORDER BY MONTH ASC)
                   AS TURNOVER_2
FROM TBL_SALES

The result looks this way:

MONTH               TURNOVER            TURNOVER_2
——————————————————
201211                 125                 -1
201212                 500                 125
201301                 100                 -1
201302                 150                 100
…

The difference is the partition by clause. That means that the function is only used within the partitions we have defined. In our case we have partitioned our data by years. To do so we use the function substr on the column MONTH. Now the DBMS uses the LAG function only within the corresponding year. That means for 2012 the first row in our table is the 201211. We have a -1 there. The next row is the second row within the partition. So we see the value of the row before there. The third row in our table is the 201301. It’s a new partition because substr(201301,1,4)=2013 and that is different from 2012. So it starts again and the first row of this partition (i.e. the third row in the table) is -1 again.

I hope the concept of partitioning became clearer with this issue too. Have fun experimenting with the two new functions.

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: Oracle SQL, SQL Tutorial | Tags:, , , , , ,
| Dezember 2, 2013
Keine Kommentare

Level Number in Cognos reports

Sometimes it’s necessary to know the number of a level in a hierarchy, for example if you want to display or hide a column depending on the current level in a drillable hierarchy. Cognos Report Studio provides two ways to implement that behavior:

  1. roleValue() function
  2. with a combination of several functions

The roleValue(<role string>; <Hierarchy>) function in Cognos returns the values of defined roles in the data model. When you are creating a meta data model in framework manager you can define so called roles. These roles can be connected to different attributes on different levels. These different attributes then can be accessed by the using the same role on all levels.

There are some standard roles defined in Cognos. Two very common examples are the Business Key and Member Caption, which have to be defined for each level. Further standard roles are available as well as it is possible to define your own roles. For example you could create a “SortValue” role. Then you can access the different sorting attributes for each level easily by using one role and the roleValue() function. This can be pretty useful when developing reports with drill features.

Another standard role exists to receive the level no. This role is the _levelNumber role. The use is pretty simple: roleValue (‘_levelNumber’; <Level of a hierarchy>)

 

The second method is the combination of several functions:

Ordinal(level(item(<member of a hierarchy>;0)))

 

Item(<Level of a hierarchy>;0) returns the first item of the level of a hierarchy. Level () returns the level of the given member and ordinal() then returns a corresponding number. In case of levels this is the level number.

With these both methods one can receive the level number and based on this implement level dependent behavior.

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:, , , , ,