What is Oracle Reports?
Oracle Reports is an enterprise reporting tool used by information systems departments to produce high quality, production reports. These reports dynamically retrieve, format, and distribute database information reports in an unlimited number of formats to an unlimited number of recipients and are easily, dynamically generated via the web. Oracle Reports is a tool for developing reports against data stored in an Oracle database. Oracle Reports consists of Oracle Reports Developer (a component of the Oracle Developer Suite) and Oracle Application Server Reports Services (a component of the Oracle Application Server).
What are the main features of Oracle Reports?
Unlimited data Formatting
High quality data publishing in PDF, HTML, HTMLCSS, XML, Postscript, PCL, Delimited text, RT Unbanded layouts, Re-entrant wizards, Unlimited number of queries ODBC support, Charting, Pre-defined templates
Parameters and drill down (incl. chart drilldown), Runtime personalization, Exception/conditional formatting,
Out-of-the-Box web functionality (Hyperlinks, Bookmarks, Page-on-demand)
Application Server Reporting
Flexible Report Distribution and Bursting, Standard CGI/servlet interface
Reports dynamically generated, Output caching, Batch scheduling, Load balancing & clustering
Portal Integration
Secure reports, printers, report servers to specific users and/or times of usage, particular parameters, etc.
Schedule and push content into Portal
e-Business Intelligence Integration
Ad-hoc query integration - export Oracle Discoverer report to Oracle Reports
OLAP reporting - build Reports against Oracle Express OLAP database.
What platforms is Oracle Reports available on?
Oracle Reports is available on many platforms including: Windows 95/98/2000, Linux, Sun Solaris, HP/UX, Compaq Tru64, and IBM AIX
HISTORY
This is not the same product as Oracle Reports. RPT was an early, primitive predecessor to SQL*ReportWriter.
- Character based report writing tool.
- The software was purchased by Oracle from a third party
- New GUI mode IDE
- Major rewrite
New features added in 6i:
- WebDB integration
- XML Output
- HTML Parameter Form Extensions
- SQL Access to the Reports Server Queue
- EXEC_SQL Integration
New features added in 9i:
- XML report definition
- Query types: XML, JDBC, Oracle9i OLAP, text files
- Pluggable Data Sources
- Java Importer
- Oracle9i JDeveloper Integration
- Oracle9i SCM Integration
- Graphing
- Oracle9iAS Portal Report Import
- Edit Oracle9iAS Discoverer Worksheet Export
New features added in 10g:
- New output format SPREADSHEET, output to Microsoft Excel.
- Extended HTML formatting customization
- Compliant to HTML 4.01 and XML 1.1 standards
I switched the page size to 11x8.5, but the printer still prints in portrait.
Even though you set the page size in the report properties, there is a another variable in the system parameters section under the data model in the object navigator called orientation. This sets the printer orientation. Oracle starts by setting it to "default" which means that no matter how you set the page size, the user's default printer setup will be used. You can also set it to either "Landscape" or "Portrait" to force the printer orientation no matter what the user has set as default. These sorts of picky, minor details are the ones which are invariably forgotten when you are designing your report and are the reason I created our two report templates, reptmp_p and reptmp_l (portrait and landscape). For anyone who wants a consistent look in their reports I strongly recommend building a similar pair to save yourself an ulcer, unless you actually like starting from scratch every time!?!
I moved this field into that repeating frame, but I'm still getting a "frequency below it's group" error.
Moving fields around does not change what enclosing object is considered it's parent group. Oracle carefully remembers what repeating frame a field was originally placed in and assigns that as it's parent. If you then reference a column further down the line of the query structure it will return that error. If you are not exactly sure which repeating frame a field belongs to, try dragging it out of all of them. Whichever frame will not allow it to escape is its parent. To change a field's parent, first click on the lock button on the speed button bar. It should now look like an unlocked padlock. Now all of the fields on the layout can be repositioned regardless of their original parent items. When you are satisfied with the repositioning click the lock button again to lock the layout. Oracle will parse the layout and assumes that any item fully enclosed in a repeating frame is a child object of that frame. This can be confirmed again by trying to drag an object out of its parent. (Control - Z or edit.undo will put it back where it came from)
Sometimes, for unknown and mysterious reasons, this method does not work. The alternative in this case is to highlight the field (or fields), cut it (control-x), and then paste it into the desired frame. The paste does not initially set it into the right frame, but if you drag and drop it there before clicking on any other objects, and then click on something else, Oracle will usually figure what your intent was and assign the object(s) as a child of that frame. This is my preferred method of changing a field's parent as it works much more consistently then the unlock/lock method. One note though, if you are reassigning a group of fields, make sure the frame you are going to move them into is large enough to accept the whole group at once before you do the cut/paste. If you do the paste and then try to grow the frame to fit, you will have to cut and paste again. Once you de-select an object that has just been pasted, Oracle will assign it as a child of whatever it is in at the time.
If this technique also fails, you are probably going to have to delete and then recreate the objects within the desired frame. If the object has triggers attached, save yourself some typing by creating the new object in the right frame, copying over the trigger code, and then deleting the old object
I must put a repeating frame around these fields. How do I do this easily?
Well congratulations, you have just discovered one of the main reasons why good planning goes a long way. Oracle looks at the layout as a sort of layered inheritance model such that anything created on top of and completely inside another object is by definition a child of that object. Creation order is therefor critical to the layout process. This means that placing a repeating frame on top of a field but larger than that field fails the ownership criteria. At best, if the new frame is fully enclosed within the same higher level frame as the field then the two will be considered sibling children of the higher level frame.
From this point you have two options. First, you can place the new repeating frame in the correct place and then use the techniques shown above in the "I moved this field but am still getting a frequency error" to reassign the fields into the new frame. There is also a second choice (which can also be used as a solution to the above). Go ahead and draw the new frame around the fields you want to have placed in it. Now if you try to click on one of the fields you will not be able to as they are fully covered by the new frame. Now go to the "Arrange" menu. You will find the options Send to back, bring to front, move forwards, move backwards. These are used to alter an object position in the Reports layer ordering. You use the "send backwards" option to move the frame backwards until all of the fields have popped to the front and are now enclosed in it. Oracle reassigns the new repeating frame as each object's parent as they pop to the front.
Note that you can only move an object back and forth amongst it's siblings. You cannot set it back below it's parent, nor in front of its children. This means that once an object has popped to the front and had a reassignment of parent, you cannot move it back using these tools.
Why does part of a row sometimes get shifted to the next page, but not all of it?
This is due to the way the scan works when Oracle is parsing the layout. If the tops of all the fields in a row are aligned and the fields are all of the same height and font, they should all stay together. I suspect, however, that Reports bases its decision on the printed size rather than the field size you define to determine which objects are too large and must be shifted to the next page. This means that even if you set two fields top-aligned with the same height and font but one of them is bolded, the bolded field could get shifted to the next page due to it's bigger footprint. The solution is to put the whole row into a regular frame which is page protected.
What exactly does the "Print Condition" do?
The print condition types First, All, All but first; Last, All but last refer to the frequency with which you want to appear based upon the setting of the print condition object. A print condition object of Enclosing Object is whichever object encloses the current object (could be the parent or a frame within the parent), while Anchoring Object is the parent object (unless you have explicitly anchored the object in which case it is the object to which it is anchored). The key here is that this is about the pages on which the Print Condition Object appears, not the current object. Oracle views First as the first page on which any part of the Print Condition Object is printed, likewise Last is the last page on which any part of the Print Condition Object is printed. For objects inside a repeating frame, this condition is re-evaluated for each instance of the frame.
As an example, assume we have created a field inside a repeating frame with Print Condition Object set to 'anchoring object', and Print Condition Type set to 'All But First'. On every instance of that repeating frame which is printed entirely within a single page, our object will not print. However, if an instance of that frame spans more than one page then our object will print on the second and every subsequent page that these instances of the repeating frame spans.
For most objects you will not have to play with this print condition setting as the default setting is pretty good at determining what pages to print on, even though it only chooses between 'first' and 'last'. Only such things as heading objects you want reprinted on multiple pages are normally candidates for fooling around with this setting.
How do I create a truly dynamic 'where' condition which the user can input on the parameter form for my select statement
While setting a simple parameter for use in defining the select statement, such as a date, bill_period_id etc. is simple, there are times when you may wish to allow a user to add any "where" statement they wish. However, if you create a varchar user variable and try to reference it as an SQL condition ( e.g. Select * from account where :usercondition) you will get an error. The secret is that the variable must be initialized to a valid SQL condition before the Data Model will accept it. This is done in the "Initial Value" spot on the variable's properties form. The usual default is "1 = 1" which simply means all rows meeting whatever other conditions are included in the select statement will pass this condition if the user does not change it in the parameter form.
How do I change a user parameter at runtime from a layout object trigger?
Quite simply, you can't. Once the BeforeReport trigger has fired, Reports locks down the user parameters until the report is finished. Oh, I know you can put a statement into a layout trigger at design time and the compiler will accept it, but the moment you run the report you will get a nasty error and the report will die. Why they couldn't catch those problems at compile time I have no idea, except that it probably uses the same PL/SQL compiler as Forms which uses that same syntax for the perfectly acceptable function of changing field values.
That being said, there is valid technique to mimic having a user variable which can be changed over the course of the report execution. What you have to do is create a PL/SQL package that contains a variable as well as the functions to read and write to that variable. Since variables inside a package are both local to that package and persistent over the duration of the run, you use this to save and change your variable value. I know that this seems like overkill, but it is the most efficient way of handling an issue that is very rarely encountered. As you can probably guess, this technique is a last resort to finding an SQL work around if one exists.
How do I set the initial values of parameters for the parameter form at runtime?
This is what the BeforeForm trigger is primarily used for. Even if you have used a select statement to create a lookup list for the parameter, this statement is fully parsed before the parameter form is opened. Simply setting the parameter to a given value in the BeforeForm trigger will select that option as the default value displayed to the user. For example, assume you have a parameter called p_input_date which is intended to hold an invoice date. The following example will select the most recent invoice date as the default, and note that it properly handles exceptions to ensure that the report does not arbitrarily die if this default setting fails. Note also that like all report triggers, it must return a true or false value.
function BeforePForm return boolean is
begin
select max(bill_period_end_date + 1)
into :p_input_date
from billing_period
where bill_period_end_date <= (select trunc(sysdate)
from dual);
return (TRUE);
exception
when others then
:p_input_date := null;
return true;
end;
Why can't I highlight a bunch of fields and change all their format masks or print conditions at once?
You can. If you highlight a bunch of objects and then right click and select "properties", Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some things, it requires changing values individually for each object. However, instead you can select the group of fields and then select "Common properties" from the "Tools" menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.
How do I change the printed value of a field at runtime?
Triggers are intended to simply provide a true or false return value to determine whether an object should be printed. It is generally not allowed to change any values held in the cursor, make changes to the database, or change the value of it's objects value. That being said, there is a highly unpublicized method of doing just that using the SRW.Set_Field_Char procedure. The syntax is SRW.Set_Field_char(0,) and the output of the object that the current trigger is attached to will be replaced by. There are also SRW.set_fileld_num, and SRW.set_field_date for numeric or date fields.
While these options do work, they should only be used if a suitable NVL or DECODE statement in the original query is not possible as they are much, much slower to run. Also, note that this change of value only applies to the formatted output. It does not change the value held in the cursor and so cannot be used for evaluating summary totals.
Oracle Reports ships with the following default Report Styles: Tabular, Form-Like, Mailing Label, Form Letter, Group Left, Group Above, Matrix and Matrix with Group. Oracle Reports also gives you the flexibility to modify these default styles as well as supporting multiple styles in a single report.
I am printing statements on a double-sided stationary capable printer. To ensure that each new statement starts on a new physical piece of paper, I need to add a blank page if the previous statement had an odd number of page sides. How can I do this?
Place an invisible boilerplate line before the repeating frame that contains your data. Give it a Page Break before and anchor it to the repeating frame. Then write a format trigger that only prints the boilerplate object (and thus the page break) if it is an odd page number.
Will there be a way to control property settings in Reports like we can do with Forms? Or more generally, will there be more flexibility? For example, to dynamically rearrange columns or drop columns.
In Release 6i, the new feature called "Runtime Customizations" allows you to customize the look and feel of a report for different audiences. This is done through the use of XML that stores the layout information for a report and gets applied to the Report Definition File at runtime. So, one report can look different for different users with colors, format masks and columns defined before report generation.
In Release 6i, the new feature called "Runtime Customizations" allows you to customize the look and feel of a report for different audiences. This is done through the use of XML that stores the layout information for a report and gets applied to the Report Definition File at runtime. So, one report can look different for different users with colors, format masks and columns defined before report generation.
Are barcodes supported in Oracle Reports?
Barcodes are supported through the use of specific barcode fonts.
How do I prevent Oracle Reports from repeating the headers when using DESFORAMT=DELIMITED?
With 6i Patch 3 or later, use the command line parameter delimited_hdr=no
REPORTS SERVER AND THE REPORTS QUEUE
The Reports Server (Services) are part of Oracle9i Application Server.
With the Reports Server process, what determines when to add additional Reports engines?
The Reports Server controls the number of reports runtime engines dynamically. The Reports Server is a single process which dynamically manages a set of Reports runtime engines. Each Reports runtime engine is responsible for executing a single report at any given time and each engine can reside in memory to execute additional report requests over a period of time. To conserve application server resources, excess/idle Reports runtime engines (when invoked from the Reports Server) remove themselves from memory for one of three reasons:
1. When an engine is idle for a user-defined period of minutes [MAXIDLE], an engine will remove itself from the process table and free up any associated resources. This can be specified within the Reports Server configuration file usually located in ORACLE_HOME/report60/server/<servername>.ora, or can be viewed/changed via the Reports Queue Manager when you access adminstrator privileges (Options>Privileges>Administrator).
2. When an engine has run its user-defined maximum number of jobs [ENGLIFE], the Reports engine will die gracefully to be replaced by another engine. [ENGLIFE] is also set in the <servername>.ora file mentioned above.
3. When changes are made to the maximum number of engines [MAXENGINE], then excess engines will be removed.
Does the server command-line client require Net8 on the client?
Yes. This will be installed automatically with the command-line client.
Is it possible for the command-line client to kick off a job and then log off still having the report execute on the Reports Server?
Yes. Just set BACKGROUND=yes as one of your command line parameters. Then it will return immediately after submitting a job request to the reports server.
Will there be a way to trace past jobs like a Web listener is tracing the incoming HTTP requests from the web? This means to have some access to the Reports Server Queue.
In Reports Release 6i, the Reports Server Queue will be pushed via an open PL/SQL API into a database table. This has also been backported to Reports 6.0 patch 1 or later. In Reports 9i this will also be available as an XML feed over HTTP.
What are the load balancing features for the Reports Server?
Since the release of Reports 6.0, Reports Server Clustering and Load Balancing has been in existence. One Reports Server is nominated as the master server through which all requests are handled. Additional Reports Servers are configured and instantiated through the master as slave Reports Servers. A report request may be run directly by the master, or if it has reached capacity, the request is handed off to one or more slave Reports Servers. These slave servers are running on different machines from that of the master and all the servers may reside on different types of platforms. They all, however, share a common file system where the report definitions (RDFs) and the report cache are located. The slave servers fire up reports runtime engines to process a report request, run the report, and place the results (output) in the common cache area. The master server then returns the output to the end user.
What is Report Bursting?
Report Bursting is the ability for a single report to be split into multiple sections and for each section to be sent to a different destination. Report Bursting was introduced in Oracle Reports 6.0, comprised of Report Sectioning (multiple layouts for a given data model) and Report Distribution (output to multiple destinations and formats after the report is run once).
Can I run a report based on the occurrence of an event in the database (eg some data has been inserted into a table)?
Yes. Currently, with Reports 6i you can write some PL/SQL stored procedures in the database that use the UTL_HTTP package (available in 7.3 or later) to issue the HTTP request to the server to run the report. However in the next release of Oracle Reports (Reports 9i), some PL/SQL procedures will be supplied to accomplish this.
Where can I store report output once a report is executed?
If you're using the command line interface (rwcli60), you can set DESTYPE=localfile, and the report output will be routed directly back onto the client's machine. There is no direct way to store report output directly into the database. Report definitions can be stored in the database.
Is there a limit to the size of the cgicmd.dat file?
Oracle Reports imposes no limits on the size of these files.
Oracle Reports ships with an ActiveX control to embed into your Windows-based applications. If your application is Web-based, simply embed the URL to call your Web-deployed reports within your application. If you are using Oracle Forms, use the built-in RUN_REPORT_OBJECT procedure to communicate with the Reports Server. As of release 7.3 of the Oracle database onwards, you can also use the UTL_HTTP stored package to submit URLs to your Web-deployed reports (e.g., to schedule reports from a PL/SQL stored procedure).
What version of Net8 does Oracle Reports 6.x require?
The version of the component that is shipped with Reports on the Reports CD (including SQL*Net.). If you wish to use any other version, or wish to check if another product (e.g., RDBMS, OAS, etc.) will co-exist with Oracle Reports, you should contact Oracle Support Services.
What are the plans for support for Graphics in Reports in future releases?
Oracle Graphics will continue to be supported in Reports 6i. Graphics capabilities will be enhanced in future versions of Reports, beyond Reports 6i.
How do I embed charts into my reports?
Through the use of the Chart Wizard, you can easily create and embed a chart into your report with a simple point and click style interface. Supported styles include: Column, Bar, Pie, Line, and Mixed. Alternatively, you can place a chart object in the report design and specify the name of a previously created Oracle Graphics chart.
How do I build a report against an Oracle Express cube?
Since Oracle Reports 6.0, Express cubes have been added as another datasource. Simply use the Report Wizard to create a new report selecting Express Query as the source. The wizard guides you through attaching an Express database to your Express Server, picking the dimensions, and then specifying where each dimension fits in the format you have chosen. Please see Appendix A of the Getting Started Manual for detailed information on setting up the connection to your Express Server.
Is there anything else I need in order to support access to Oracle Express?
For Oracle Reports 6.0, an Oracle Express 6.2 Server is required and an intermediate Oracle 8.0.5 instance with object support running on NT. For Reports 6i, an Oracle Express 6.3 server is required and an intermediate Oracle 8.0.6 or 8i (8.1.6) instance with object support running on NT and most UNIX platforms. The Express server and the Reports runtime may be on any of the platforms they are ported to.
The Reports wizard (i.e. design time) running against Express is available on Windows 95/NT/2000 only, however at runtime it is available.
The Reports 6i Developer Getting Started Manual has detailed instructions on how to configure Reports for Oracle Express.
How is the integration with Discoverer (and Express) progessing for the future? Discoverer generates a rudimentary *.rdf file without any filters in terms of user defined parameters. This can be a show stopper for big data.
In future releases (9i and beyond), the integration with all of Oracle's business intelligence tools will be improved upon.
How does Oracle Reports integrate with Oracle Discoverer?
Oracle Discoverer is seamlessly integrated with Oracle Reports. Oracle Reports is one of the export options to which a workbook may be exported in Discoverer. Simply create/open your workbook in Discoverer, select File, Export To, and choose RDF. This creates an RDF file (Report Definition File) which may be modified in the Report Builder, if necessary, and then be published across the Enterprise using the Reports Server.
What are the plans for OEM support for Reports Server and Reports Server Queue?
This will be included in a future release.
Using Reports 6i, you can publish and secure your reports from within a Portal site. The Reports architecture has been tightly integrated with Oracle Portal. The Reports Server communicates with Portal to authenticate users and performs the necessary security checks to ensure the user is able to process the selected report. The out-of-box interface allows the administrator to easily enter the Reports access control information from within a Web browser. All of the access control data is then stored within the Portal repository, which is checked each time a user runs a report request.
I do not have Portal nor do I plan to use it. How can I secure my Reports 6i Reporting environment?
When you purchase Oracle9i Application Server Reports 6i as part of 9i Application Server, you get a license to Oracle Portal. This will allow the administrator to enter all of the access control information where Reports Servers and RDF's are registered. This access control data may still be used to authenticate users running reports outside of a Portal Site.
When you purchase Oracle9i Application Server Reports 6i as part of 9i Application Server, you get a license to Oracle Portal. This will allow the administrator to enter all of the access control information where Reports Servers and RDF's are registered. This access control data may still be used to authenticate users running reports outside of a Portal Site.
REPORTS ON THE WEB
Oracle Reports can dynamically display information via a web browser using the following industry standard formats: Adobe's Portable Document Format (PDF), HTML, HTML Cascading Style Sheets (HTMLCSS) and XML
What Web browser versions does Oracle Reports work with?
For HTML output, Oracle Reports works with any HTML browser capable of displaying HTML tables. For HTMLCSS output, Netscape Navigator 4.x and Microsoft Internet Explorer 4.x or higher, offer the best support. For PDF output, the Adobe Acrobat plug-in for the browser should be used. This is available for Netscape Navigator 3.x or Microsoft Internet Explorer 3.x or later.
What Web servers can be used with the Reports Server?
Reports comes configured to use the Oracle HTTP Listener powered by Apache with the industry standard CGI and servlet interfaces. However these interfaces can be used with any CGI-aware, industry standard Web server.
What do I need in order to dynamically display my reports on the Web?
Reports are executed in a multi-tier environment running on the Oracle Reports Server in the middle-tier. By simply entering a URL in a Web browser, users can dynamically run reports or retrieve previously run reports offered by the Reports Sever. The output is displayed back to the browser.
When building a report, how do I change the Web browser that Oracle Reports Developer uses for web previewing reports?
The default browser that Oracle Reports uses for previewing Reports is stored in the Windows 95/NT registry under: \\HKEY_CURRENT_USER\Software\ORACLE\Toolkit\TKBrowser\BrowserPath. The first time you preview the report in a browser the system registry will store the path and type of browser from what you've selected.
If a user runs a 200 long page report over the Web, is there a way to look at some of the pages rather than send all 200 back to the browser?
A new feature introduced with Oracle Reports 6.0 is page streaming of output for HTML and HTMLCSS. Simply set the command-line parameter PAGESTREAM=YES, then deploy the report on the Web as before. When the user selects the report, the report is run on the server and written as multiple HTML/HTMLCSS files, instead of one huge file. The first page is then sent back to the user's browser, along with a menu bar, which facilitates moving to the first/last page, next/previous page, or to a specific page number.
How do I force a user login before they run/view the output of a report?
With Oracle Reports 6.0, if the username and password are omitted (either from the URL or the mapping in the cgicmd.dat/owscmd.dat file), a user-customizable dialog requesting the connect string pops up. Once authorized, a cookie is sent to that user's browser so that logon is not re-requested (within a user-definable period of time) if the report is re-requested. If necessary, logon can be forced every time by adding a parameter. This has been enhanced in Reports 6i with the addition of application security to authenticate users.
How do I run a report to delimited output and invoke Excel automatically?
With Reports 6i Patch 3 run a report on the web with the following command line parameters:
desformat=delimited
mimetype=application/ms-excel
mimetype=application/ms-excel
Note: the mimetype to specify depends on what your browser understands.
In Netscape look under Edit à Preferences à Navigator à Applications and scroll down until you find 'Excel'. The mimetype to use will be displayed.
On the Windows platform, you can e-mail your reports via the Microsoft Messaging Application Programming Interface (MAPI). Any MAPI compliant server may be used. Bit-mapped reports are sent as e-mail attachments in encapsulated PostScript format. Character-mode reports are sent as ASCII text attachments. On Unix platforms, the 'sendmail' command is used which supports SMTP.
Can I change the subject line when emailing a report?
Not easily, and not if DESFORMAT=MAIL. With 6i you would need to:
1. run the report to file (not 'mail')
2. have an OLE-automatable mail client to email the file (win32 only) and set whatever properties you wanted there. On UNIX the 'sendmail' script is used by Reports
Email functionality is being enhanced in Reports 9i to allow users to set many email properties, including subject, with both static or dynamic (i.e. data-driven) values.
May I specify a distribution list for a given report?
Yes. Using the Report Distribution functionality of Report Bursting, you may specify email distribution lists for particular section(s) of any given report.
Can scheduled reports be emailed to a group of users without having to identify each user individually in the Distribution dialogue box?
Yes. You can create a distribution list on the email server and specify that list name in the DESNAME column in the Report Builder Distribution dialog box.
Are other email protocols supported other than MAPI?
With Reports 6i, only MAPI is supported on the Windows platform. SMTP will be support on Windows platform with Reports 9i. Note: On UNIX Reports uses 'sendmail' which supports SMTP.
Can a batch of multiple reports be scheduled to run simultaneously?
Yes. Oracle Reports submits each report for scheduling via the command line interface to the Reports Server. You could programmatically batch multiple reports by writing a shell script to do so.
Can the end user schedule their own reports to run?
Yes. There are two ways to accomplish this.
1. An HTML interface can be written, prompting the user to enter all the necessary scheduling data. Those designated parameters then get submitted via the command line to the Reports Server.
2. With the release of Reports 6i, Portal is tightly integrated with the Oracle Reporting environment. When registering an RDF within Portal, you can specify a Portal parameter form to display before the report is submitted for processing. This Portal parameter form contains a Scheduling tab where the end user may specify the scheduling requirements. The Reports Queue Manager also permits scheduling of jobs to be run on the server.
I have noticed that when I use the query wizard in the Report Builder, the SQL generated has the word 'ALL' after 'SELECT'. Why is this?
'ALL' is part of the SQL syntax. It differentiates a query that does a SELECT DISTINCT from one that does not. The following are equivalent: SELECT EMPNO FROM EMP and SELECT ALL EMPNO FROM EMP.
Does Oracle Reports work with non-Oracle datasources? What are the limitations, if any?
Yes. Through the use of Oracle's Open Client Adaptor (OCA), you can connect to any ODBC datasource. The only limitation of using ODBC is that you must be running the Reports Server in a Win32 environment.
May I use the EXEC_SQL package to base a report on a second datasource simultaneously?
The EXEC_SQL package uses PL/SQL to programmatically fetch a single row of data at a time. In Oracle Reports, you may have an unlimited number of queries based off of a single database connection. You may also have an unlimited number of PL/SQL connections for single data fetches. The EXEC_SQL package is not to be used for basing a report on more than one datasource. Rather it would be used to populate parameters on which fields are based to display the data, or use the data within a PL/SQL trigger, such as a Before Report trigger. For example, EXEC_SQL would be useful in a parameter form where the data fetches are coming from multiple datasources.
In 6i, reports outputs XML. In addition, customers may specify customizations to the reports in XML, either as a file that is read in via the customize command line parameter, or directly to the report whilst it is executing.
In Release 9i (the next release) we will support XML as a datasource (incl. XML-Schema and xsql result sets). In addition, you will be able to describe an entire report definition in XML, and Reports Developer will save the definition to either an RDF or an XML file.
JAVA SUPPORT
What is the direction of Reports in terms of Java? Will there be a Java Interface for communicating with the Reports Server from within other Java Applications (e.g Web Forms)?
Reports can be called from within a Java application today by specifying a URL to run a report request and opening a socket. In the next release (Reports 9i), there will be various plug-in interfaces written in Java, allowing data from multiple sources in a report. Additionally, Reports 9i is adopting JSP's to produce high fidelity web output (in addition to the 'paginated HTML' it currently produces) so customers may code in Java within the JSP. The Reports Server will also permit customization via Java in the way the cache and jobs in the job queue are managed (amongst many other things).
Will there be support for Java in the triggers (beyond Reports 6i)?
It is possible at this time to call a Java stored procedure wrapped in PL/SQL from within an Oracle 8i database, and there is a whitepaper on OTN describing how to do this with Reports.
How many different triggers are available in Report?
There are five types of triggers in report 6i
1) Before report trigger
2) After report trigger
3) Before Parameter trigger
4) After parameter trigger
5) Between pages trigger
What is the Firing sequence of report trigger?
First the before parameter trigger will raise, after firing this trigger parameter form will displayed, after passing parameter after parameter trigger will fire query will parsed & then before report trigger will fired then if there are number of pages in your report then the between pages trigger will fired but it will fire between first & second & so on pages but it will not fired in reverse condition the after report trigger will fire after closing the run time parameter form is closed.
What is the difference between After Parameter Trigger and Before Report Trigger?
After parameter Trigger: It will fire after the parameter form is displayed.here we can do validation on parameter values
Before Report Trigger: It will fire before the report is executed and after the query is parsed and date is fetched.
What is the Format Trigger?
Format Trigger is a PL/SQL function. This trigger is going to fire before an object is printed in report output. it return boolean-true then go to print -false then don't print.
What is the diff. when Flex mode is mode on and when it is off?
When flex mode is on, reports automatically resizes the parent when the child is resized.
What is the diff. when confine mode is on and when it is off?
When confine mode is on, an object cannot be moved outside its parent in the layout.
What is a lexical parameter?
Lexical Parameter is used to replace the where, order by conditions at run time.
What are bind variables?
Bind variables are used in report 6i for replacing the single parameter in the select statement
How many different layouts are available in Reports?
There are eight different layout formats:
1. Tabular
2. Form Like
3. Form Letter
4. Mailing Label
5. Group Left
6. Group Above
7. Matrix
8. Matrix with group
What is the minimum number of groups required for a matrix report?
The minimum of groups required for a matrix report are 4
What is the lock option in reports layout?
By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields.
What is the Anchoring in Reports?
Anchor is used to make fixed distance between two objects in Reports Layout.
What is the difference between Frame and Repeating Frame?
Frames are used to surround other objects and protect them from being overwritten or pushed by other objects. For example a frame might be used to surround all objects owned by a group to surround column headings or to surround summaries.
When you default the layout for a report Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.
Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.
When you default the layout for a report Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view
What are different types of column in reports?
There are three types of columns in the report 6i these are:
1) Placeholder Column – Placeholder column is used to store a value for a variable.
2) Formula Column
3) Summary Column
How many types of columns are there and what are they?
Formula columns: For doing mathematical calculations and returning one value
Summary Columns: For doing summary calculations such as summations etc.
Place holder Columns: These columns are useful for storing the value in a variable
Can u have more than one layout in report?
It is possible to have more than one layout in a report by using the additional layout option in the layout editor. Yes it is possible to run the report without parameter form by setting the PARAM value to Null
There are five types of triggers in report 6i
1) Before report trigger
2) After report trigger
3) Before Parameter trigger
4) After parameter trigger
5) Between pages trigger
What is the Firing sequence of report trigger?
First the before parameter trigger will raise, after firing this trigger parameter form will displayed, after passing parameter after parameter trigger will fire query will parsed & then before report trigger will fired then if there are number of pages in your report then the between pages trigger will fired but it will fire between first & second & so on pages but it will not fired in reverse condition the after report trigger will fire after closing the run time parameter form is closed.
What is the difference between After Parameter Trigger and Before Report Trigger?
After parameter Trigger: It will fire after the parameter form is displayed.here we can do validation on parameter values
Before Report Trigger: It will fire before the report is executed and after the query is parsed and date is fetched.
What is the Format Trigger?
Format Trigger is a PL/SQL function. This trigger is going to fire before an object is printed in report output. it return boolean-true then go to print -false then don't print.
What is the diff. when Flex mode is mode on and when it is off?
When flex mode is on, reports automatically resizes the parent when the child is resized.
What is the diff. when confine mode is on and when it is off?
When confine mode is on, an object cannot be moved outside its parent in the layout.
What is a lexical parameter?
Lexical Parameter is used to replace the where, order by conditions at run time.
What are bind variables?
Bind variables are used in report 6i for replacing the single parameter in the select statement
How many different layouts are available in Reports?
There are eight different layout formats:
1. Tabular
2. Form Like
3. Form Letter
4. Mailing Label
5. Group Left
6. Group Above
7. Matrix
8. Matrix with group
What is the minimum number of groups required for a matrix report?
The minimum of groups required for a matrix report are 4
What is the lock option in reports layout?
By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields.
What is the Anchoring in Reports?
Anchor is used to make fixed distance between two objects in Reports Layout.
What is the difference between Frame and Repeating Frame?
Frames are used to surround other objects and protect them from being overwritten or pushed by other objects. For example a frame might be used to surround all objects owned by a group to surround column headings or to surround summaries.
When you default the layout for a report Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.
Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.
When you default the layout for a report Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view
What are different types of column in reports?
There are three types of columns in the report 6i these are:
1) Placeholder Column – Placeholder column is used to store a value for a variable.
2) Formula Column
3) Summary Column
How many types of columns are there and what are they?
Formula columns: For doing mathematical calculations and returning one value
Summary Columns: For doing summary calculations such as summations etc.
Place holder Columns: These columns are useful for storing the value in a variable
Can u have more than one layout in report?
It is possible to have more than one layout in a report by using the additional layout option in the layout editor. Yes it is possible to run the report without parameter form by setting the PARAM value to Null
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.