Row Generators

Thank you Michael for inviting me to contribute to your blog. I thought I would start off by talking about row generators and how to use them with Discoverer.



A folder that you can use as a row generator is always useful to have in an EUL. A row generator is just a folder that always returns a fixed number of rows. You can define a row generator either by using a database view or by creating a custom folder as shown below. There is a dummy item included in this row generator so that the folder can be joined to other folders in the EUL.



Lists of Values

There are many uses for a row generator but they are often most useful when creating a list of values (LOV) where there are a fixed number of values, for example, a Yes/No list of values. To create you LOV first create a new (complex) folder, then drag the N item from the row generator folder into the new folder. You then need to create a mandatory condition in the new folder to restrict the number of rows, for example, in this case N<=2. You can then create a calculated item to return the text used in the LOV. You can use DECODE to convert the number into a text string as shown below.



Then create a LOV item class based on the calculated item so that you have a folder that returns the required values as shown below.



Of course this is a very simple example. But in general you will find it easier to have one row generator folder and have complex folders for each LOV. It is easier to use a CASE or DECODE expression than create a new custom folder for each LOV.


Other useful examples are:

List of Values	Calculation	Condition
Days of the week	TO_CHAR(TRUNC(SYSDATE, 'D')+n-1, 'Day')	N<=7
Previous 12 months	ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1-n)	N<=12
Letters of the alphabet	CHR(65 + n-1)	N<=26

This last example where you have a LOV containing the letters of the alphabet is useful when you want to show an index on a text field, for example, bookname. You need to create a bookname_char calculated item, UPPER(SUBSTR(bookname,1,1)) and put this in the alphabet item class. You then need to create a hierarchy from the bookname_char item to the bookname item which will let you select the first letter of the bookname and then drill down to see all names beginning with this letter.


Fixing the number of columns


The row generator is also useful if you need to have a fixed number of columns in a crosstab report. For example you wanted 12 columns, one for each month in the year summarising employee earnings. Now, there will be some employees who do not have earnings for all months in the year. When the report is run for these employees there will be less than 12 columns. If you create a LOV folder for the months you need as described above and outer join the LOV folder to the report folder then you will always get a fixed number of columns in your report.


Pivoting


A row generator can be used to pivot a table or part of table. This is where you want to show values that are in different columns on separate rows.

For example, if your employee table has columns for the hours worked on each day in the week and you need a report showing the total hours on each day of the week by department. So in this case you need to pivot the hours onto separate rows. You do this by creating a days of the week folder that always returns 7 rows as described above. Make sure you also include the dummy item from the row generator folder. Then create a dummy item in your employee folder as shown below.





You can then join the employee table to the days of the week folder using the dummy item. Then in the workbook create a calculation to show the total hours for any day using:


SUM(DECODE("Row Generator 100".N,
1, Employees.mon_hrs,
2, Employees.tue_hrs,
3, Employees.wed_hrs,
4, Employees.thu_hrs,
5, Employees.fri_hrs,
6, Employees.sat_hrs,
7, Employees.sum_hrs))


You can then select the day from the days of the week folder, the department from the employees folder and the hours calculated item to get the sum of the hours for each day of the week.


Counting records many times


The row generator can be used to multiply rows when you want to count the rows several times.

For example, if you have a report that shows when users logged on and off your system but you want a chart showing the how many users are logged on during each hour during the day. This means that if there is a record showing user A logged on at 8am and logged off at 11:50am then this record needs to be counted 4 times, once for each hour the user was logged on.

You need to create an hours row generator folder as described above that returns the 24 hours in the day using an hours calculation shown below :


TO_CHAR(n-1,'fm09')


Now if the user logon and logoff times were held in a user_timings folder then you need to join the hours row generator folder to this folder using a dummy item as described in the previous section. This will multiple each row 24 times. Then a condition in the workbook can be used to return only the hours when the user was logged onto the system:


Hours BETWEEN TO_CHAR(TRUNC(logon, 'HH24'), 'HH24') AND TO_CHAR(TRUNC(logoff, 'HH24'), 'HH24')


You can then count the records and group sort on the hours calculation to return the data you need for the chart.


This of course assumes that all users log on and off on the same day. You would need a slightly more complex condition if users where able to logon and off on a different day.

Pictures of Darlene and Michael

Many of you know Darlene, my wife, and I as the owners of Armstrong-Smith Consulting and the authors of the Oracle Discoverer Handbook. Here are some photographs of us taken in our offices recently:

Interesting condrum with indexed values

As you know from a previous posting it is possible to create Discoverer lists of values to display a lookup description e.g. Department Name and return the primary key code namely, Department Id when using a list of values in a parameter.

What I have discovered today is something very intersting in the way that Discoverer does this and something that you ought to be aware of.

Let's say a bottle manufacturing company has a whole series of bottles which they describe using the color of the top. Some descriptions would be 1 Litre Blue Top, some 1 Litre Red Top and so on. Let's also say that they start out with code 10 represting 1 Litre Blue Top, and code 11 representing 1 Litre Red Top.

Inside Discoverer Plus, if a user has an indexed item in use and they choose to select using the Values option (see my original posting if you are unsure what this looks like) they will be presented with a list of values like this:

• (1 Litre Blue Top) 10
• (1 Litre Red Top) 11

What you may not be aware of is what Discoverer submits to the database. This is what will get submitted:

(((o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top'
AND o100539.PRODUCT_CODE = '10'))

Yes, Discoverer actually adds an AND clause for BOTH parts of the equation. It searches for both the Code and the Description. I can see why this is happening. This is to solve the following problem:

Let's say the bottling company later decide to introduce an updated version of the 1 Litre Blue Top and decide to use code 20. Because there are two items with the same description, if Discoverer were to only submit this:

(o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top' )

then both items would be returned in the query. The only way to guarantee to get the right combination would be for Discoverer to add the AND clause.

Somebody recently asked me why Discoverer didn't use an OR connector, like this:

(((o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top'
OR o100539.PRODUCT_CODE = '10'))

If Oracle were to change the code to an OR condition then we would definitely get multiple rows returned so Oracle's choice of using an AND clause seems to be correct.

So why then am I bringing this to your attention. I'll tell you why.

Let's say the bottling company decides to rename the description for bottle 10 to this 1 Litre Top - Blue. No big deal you might think because there is still only one description for that code - wrong!

Let's say a user opens a workbook containing a worksheet that has a such an indexed parameter and that the last time it was used either the code 10 was selected or it is being supplied as the default value. Because the parameter already has the value displayed on screen all the user needs to do is to accept the current value and click Finish. Because the user does not reselect 10 and just clicks Finish what gets submitted to the database will still be the original AND clause:

(((o100539.PRODUCT_DESCRIPTION) = '1 Litre Blue Top'
AND o100539.PRODUCT_CODE = '10'))

Yes, now you can see the conundrum. No data is returned because there is no product called 1 Litre Blue Top in the database. Discoverer does not reassess the situation when an existing parameter is used. Is this a bug? Maybe. I will send this to Oracle for comment but I thought you would like to know.

I think the workaround would be to pick a new paramater value from the list, then go back and repick the original value. Assuming the worksheet now returns data the worksheet needs to be resaved. Of course, this also assumes that the user using the worksheet is the worksheet owner. If they are not the owner then the problem will persist until the owner can be notified.

As I say - an interesting conundrum!

Welcome to Rod West

I am delighted to be able to let you know that Rod West, a prolific answerer of questions on the OTN Discoverer Forum, will be joining me as a co-author on this blog.

Rod has been using Oracle databases since 1985 and is principal consultant at Cabot Consulting in the United Kingdom. He specializes in Oracle Applications 11i / 12i as well as Oracle Discoverer. Rod can be contacted at rodwest@cabotconsulting.co.uk

Rod has also submitted two white papers, both of which are available for immediate download from my website by clicking here

The papers are as follows:

• Scheduling through concurrent manager - this paper describes how Oracle Applications Concurrent Processing can be used to schedule Discoverer workbooks
  
• Using VPD to secure Discoverer reports - this article uses a series of examples to demonstrate how VPD can be used to secure Discoverer reports. The examples have been written with Discoverer in mind but VPD is a database centric approach and so can be applied to any reporting tool

Cumulative Patch 2 for 10.1.2.3

I am very pleased to announce that Oracle have released a second cumulative patch for Discoverer 10.1.2.3.

So far this has been released for the following platform:
Windows 32-bit

The following postings have been updated:

Useful Patch Numbers
Do not upgrade to Discoverer 10.1.2.3

Important Note: Neither the base 10.1.2.3 nor any of its cumulative patches are certified for use against E-Business Suite 11i or 12i, so please don't upgrade to 10.1.2.3 if you are using or intend to use Discoverer in Apps mode. 10.1.2.2 and all of its cumulative patches are certified so this is where you should be, at least for the time being.