Table of Contents
3.0 Cognos Report Studio Approaches. 3
3.1 Reports having simple queries and direct table joins 3
3.2 Reports with queries having EXIST/NOT EXIST clause 4
Approach 4: Datasource Query Subjects in Framework Manager 4
3.3 Reports with queries having UNION/UNION ALL clause 5
Approach 3: Datasource Query Subjects in Framework Manager 5
3.4 Reports with queries using Save Data tables 5
Approach 1: Nested Tabular Sets & Tabular Models 5
Approach 2: Refer to section 3.9 5
3.5 Reports with queries having IN/NOT IN clause 6
Approach 1: Parent/Child tabular element 6
Approach 3: Datasource Query Subjects in Framework Manager 6
3.6 Reports with SQL’s having Inline Queries 6
3.7 Reports where the query has user inputs in outer join 6
Approach 1: #prompt# in Tabular SQL 6
Approach 2: #prompt# in Datasource Query Subjects in Framework Manager 7
3.8 Reports that require multiple frames 7
Approach 1: Multiple frames in a page 7
Approach 2: Frames on multiple pages 7
Approach 2: Stored Procedure 8
3.10 Report Query needs inputs from online applications/batch process 8
3.11 Reports having Inserts and Updates 8
6.0 Auto Printing of Cognos8 Reports. 9
7.0 Imaging of Cognos8 Reports. 9
1.0 Purpose
This document details the various design approaches to be used in RSG for generating COGNOS reports.
This is a technical paper that explains different report complexities and the various design options available to achieve them using Cognos Report Studio. The document explains each option and weighs the advantages and disadvantages for each option.
This document is a compilation of various options that were thought feasible keeping in mind the current environment and as per the requirements for reporting.
2.0 Scope
The design approaches considered in this document include
Ø COGNOS Report Studio Approaches
Ø COGNOS 8 SDK approaches
Ø Prompt screen creation
Ø Auto Printing of Reports
Ø Imaging of Reports
Note: The approaches considered for a particular functionality / operational concept / process may result in trade-off on part of the functionality but will be performance optimal.
3.0 Report Studio Approaches
This section details the various design approaches to be used in creating reports using Report Studio. Also, each approach has been listed/depicted in the order of priority in which the design needs to be applied.
3.1 Reports having simple queries and direct table joins
Approach 1: Tabular Model
Report Studio Tabular Model feature can be used when there is a need to create very simple reports having direct table joins.
3.2 Reports with queries having EXIST/NOT EXIST clause
Approach 1: Tabular Set
Report Studio Tabular Set (INTERSECT & EXCEPT) feature can be used when there is a need to create reports having queries with EXIST/NOT EXIST clause.
Approach 2: Modify Metadata
In Framework manager query subjects with outer joins can be created to mimic the functionality of the SQL containing the NOT EXIST clause. These query subjects can then be used in the Report studio to create reports.
Approach 3: Tabular SQL
Report Studio Tabular SQL feature can be used when there is a need to create reports having queries with EXIST/NOT EXIST clause. The SQL statement may be expressed by using syntax recognized by the native database, or by using Cognos SQL syntax.
Approach 4: Datasource Query Subjects in Framework Manager
Datasource query subject in Framework Manager can be used when there is a need to create reports having queries with EXIST/NOT EXIST clause. The SQL statement may be expressed by using syntax recognized by the native database, or by using Cognos SQL syntax. This query subject can be used in report studio to create reports.
Rules:
· Approach 1 cannot be used in the following scenarios:
1. If the query include summaries , then there could be a chance that due to multiplication or replication of the records incorrect calculation of summaries in either of the child elements of the Tabular SET and hence the INTERSECT/EXCEPT functionality will fail to not mimic the EXIST/NOT EXIST functionality.
· Approach 2 cannot be used in the following scenarios:
1. If the query includes an EXIST clause, then since functionality of EXIST clause cannot be implemented using OUTER JOIN, approach 2 cannot be used.
2. If the query has a NOT EXIST clause and if the metadata created to implement the functionality of NOT EXIST is not generic (i.e has any report specific user input filters in the metadata, then approach 2 will not be used.
· Approach 4 will be used instead of approach 3 if and only if the SQL can be completely reused for another report of the same application (without the need to have any additional joins in the metadata).
3.3 Reports with queries having UNION/UNION ALL clause
Approach 1: Tabular Set
Report Studio Tabular Sets can be used to create reports which have UNION/UNION ALL queries. The tabular set is basically a relational set expression that combines two or more tabular structures like tabular model, tabular SQL and so on. A tabular set produces a single result set, which is the UNION/UNION, ALL of the tabular structures.
Approach 2: Tabular SQL
Report Studio Tabular SQL feature can be used when there is a need to create reports having queries with UNION/UNION ALL. The SQL statement may be expressed by using syntax recognized by the native database, or by using Cognos SQL syntax
Approach 3: Datasource Query Subjects in Framework Manager
Datasource query subject in Framework Manager can be used when there is a need to create reports having queries with UNION/UNION ALL clause. The SQL statement may be expressed by using syntax recognized by the native database, or by using Cognos SQL syntax. This query subject can be used in report studio to create reports.
Rules:
· Approach 1 cannot be used in the following scenarios:
1. If the individual SQLs of the query cannot be implemented using either Tabular Sets or Tabular Models, then approach 1 will not be used.
· Approach 3 will be used instead of approach 2, if and only if the SQL can be completely reused for another report of the same application (without the need to have any additional joins in the metadata).
3.4 Reports with queries using Save Data tables
Approach 1: Nested Tabular Sets & Tabular Models
Report Studio Tabular Sets & Tabular models can be nested to create reports that query direct Database physical tables and also have unions with ‘minimal save data’ (maximum of 4).—Need to clarify
Approach 2: Refer to section 3.9
Rule:
· Approach 1 cannot be used in the following scenarios:
1. If there is a join between the save data tables(temp tables) themselves, then since ReportNet treats join between nested tabular elements as CROSS JOINS, approach 1 cannot be used.
3.5 Reports with queries having IN/NOT IN clause
Approach 1: Parent/Child tabular element
A Parent / Child tabular element combination can be used to create reports having queries with IN/NOT IN clause. The child tabular element will provide the range of values against which the corresponding value of the parent tabular element is compared with an IN/NOT IN clause.
Approach 2: Tabular SQL
Report Studio Tabular SQL feature can be used when there is a need to create reports having queries with IN/NOT IN clause. The SQL statement may be expressed by using syntax recognized by the native database, or by using Cognos SQL syntax.
Approach 3: Datasource Query Subjects in Framework Manager
Datasource query subject in Framework Manager can be used when there is a need to create reports having queries with IN/NOT IN clause. The SQL statement may be expressed by using syntax recognized by the native database, or by using Cognos SQL syntax. This query subject can be used in report studio to create reports.
3.6 Reports with SQL’s having Inline Queries
When a SELECT query is nested inside another SELECT, then it is an inline query.
Approach 1: Tabular SQL
Report Studio Tabular SQL feature can be used when there is a need to create reports having inline Query. The SQL statement may be expressed by using syntax recognized by the native database, or by using Cognos SQL syntax.
3.7 Reports where the query has user inputs in outer join
Approach 1: #prompt# in Tabular SQL
Macro function #prompt# can be used to define a parameter in the outer join definition in the Tabular SQL. The value to this prompt is set from the prompt page thereby mimicking the functionality of passing user inputs to the outer join.
Approach 2: #prompt# in Datasource Query Subjects in Framework Manager
Macro function #prompt# can be used to define a parameter in the outer join definition in the FM Metadata. The value to this prompt is set from the prompt page thereby mimicking the functionality of passing user inputs to the outer join.
Rules:
· Approach 2 will be used instead of approach 1, if and only if the metatdata can be completely reused for another report of the same application (without the need to have any additional joins in the metadata).
3.8 Reports that require multiple frames
Approach 1: Multiple frames in a page
It is possible to have multiple list frames in a report page with data from different queries. It is also possible to conditionally hide or show the frames if required by the report specification.
· If the report has different page break conditions for each frame, then the approach is not fixed.
Approach 2: Frames on multiple pages
In this approach, only one frame is put on each page. When the report is run, the pages appear sequentially.
· If the report has different page break conditions for each page, then the approach is not fixed.
· If the report requires that one or more of the frames be conditionally hidden, then the approach is not fixed.
Rules:
· If the frame 1 and frame 2 appears in the same page, approach 1 shall be used.
· If the consecutive frames needs to get generated in a new page,approach 2 shall be used.
3.9 Reports having complex logic (Unions with multiple save data, Complex Calculations, Loops, nested Conditions, etc).
Approach 1: Summary Table
Summary table can be created. This summary table can be joined with the required
tables in order to pull out the report in the report studio. The data in the table needs to be refreshed periodically.
NOTE: Summary tables should be used only in the following scenarios:
Ø If data retention/volume is less than 1million records.
Ø Data in the report is not real-time / not near real-time.
Ø User volume is high.
Approach 2: Stored Procedure
If the data in the report needs to be real time or near real time report or if the data retention is more than 1 million records, then SP approach would be considered. The stored procedure is defined in the FM Metadata and the report uses this stored procedure query subject to pull out the data. In some cases table variables might need to be used.
3.10 Report Query needs inputs from online applications/batch process
Approach 1:
ReportNet SDK can be used to pass parameters to the reports.
3.11 Reports having Inserts and Updates
Approach not available/not known.
Types of inserts and updates to be handled:
Ø User inputs updated to table.
Ø Update flags or dates based on report run.
Ø Update report data to table.
4.0 ReportNet SDK Approach
ReportNet is a Web product for creating and managing reports. The ReportNet Software Development Kit (SDK) provides a platform-independent automation interface for working with ReportNet services and components.
For RSG, ReportNet SDK will be used to connect to the ReportNet server and
Ø Invoke ReportNet reports from Prompt web pages
Ø Auto print ReportNet reports as PDF to Virtual Directory
Ø Save ReportNet reports as PDF to a physical location for imaging.
5.0 Prompt Pages
ASP .NET Page will be used to create Web Prompt pages. This will then use ReportNet SDK to connect to the ReportNet server and invoke ReportNet reports.
6.0 Auto Printing of ReportNet Reports
ReportNet SDK will be used to print ReportNet Reports to Virtual Printer as PDF. A third party auto printing tool ‘Portal-Sphere® Commander’ will then pick up the PDF from spool and distribute the reports to different locations. This tool also has built in features to print Banners.
NOTE:
· Development of interfaces (ex: templates, banners & forms) if any between Cognos and Portal-Sphere® Commander is not within the scope of RSG.
· Creation and maintenance of folders for the reports and archive / delete facilities for the folders are not in scope of this RSG.
7.0 Imaging of ReportNet Reports
A third Party tool ‘OnBase’ is used to image reports. This tool requires report output to be both in PCL/formatted text & PDF format. PCL/formatted text output is used for full text search & indexing.
ReportNet SDK will be used to save the ReportNet Report as a PDF to a physical location as well as to print the same to a Virtual Printer.
The auto print tool Portal-Sphere® Commander would then pick up the report from the virtual printer and save it as a PCL/text format onto the physical location.
The onBase imaging tool would then pick up both the PCL/formatted text as well as PDF files for imaging.
7.1 Golden Rules for simple and successful Report Development
1. Local Processing: In all queries, turn local processing off whenever possible - i.e. set up for Database Processing only - this forces work to be done in the database and if we've defined reporting tables properly will always be viable option
2. Auto Summarisation: In all queries which are putting one record out in the report per database record, always turn the Auto Summarise option for the query to No (default is Yes)- this can make a huge difference to query performance and complexity when picking up lots of fields, as otherwise the SQL consists of a mass of SUM() and MAX() and then GROUP BY for all dimensions/attributes - unnecessarily so. Again, in the majority of cases, we'll be using one record per output row so this is generally going to work well.
3. Setting Sizes - especially heights: Don't try and control sizes when you don't need to - particularly heights. It can be good practice to control the width of various columns sometimes to create a level of symmetry and in particular to control widths when headings have multiple words, or in tables that need to mutually line up - but in general web based apps should be allowed to do what they need to do to fit the output window (or paper) as best they can. Controlling heights is particularly messy and should rarely be necessary in multirow reports. There WILL always be a better way with the powerful alignment, padding and margin settings
4. Using our traffic light images: Be careful around images - our standard traffic light and arrow images are the right size to line up with our report text standards BUT you should make sure that they are padded 0px above and 0px below, and that the text items on the row are set to middle for vertical alignment. This allows the image to dictate the height of the row and everything to line up properly across a row. You should also ensure that the default padding for the whole row is set to either 1px above and 2px below or vice versa - then if the image is not displayed, the height is still ok
5. Controlling when objects appear or are hidden: Quite often it is necessary to control when an object appears and when it doesn't. If this is a simple yes or no, always use the rendering control rather than conditional blocks/formatting - much simpler and less hassle within report studio. If the display should be on 'no' create a second variable. Conditional blocks are generally more relevant when trying to remove a column altogether so that when not displayed there is no width (typically drill through hyperlinks)
6. Report Expressions vs Query Expressions: report expressions are quite limited in terms of functionality and construction - generally better if constructing something to display to do it in the query and use the query variable (in our older Branch InSight applications all images were typically defined as report expressions - easier now to put the full value into an item in the query subject in Framework Manager).
7. Tables are your friend: You really need to be comfortable with tables within tables within tables as a way of laying out reports which align multiple elements with the developer really in control. They can go anywhere - in list columns, in headings, on the background (and list controls should generally always sit within a table cell so you can add items above and below the list). There is no overhead with tables and they are a great way of keeping control of output if used well. Additionally, tables can be used in single cut and paste operations to replicate their content - table cells etc can't. And remember, when working within table cells, always set the vertical and horizontal alignment the cell to control what happens if the objects within the cell don't fill it - again, take control of the output. You can set cell attributes for a wide range of cells in a table by control-clicking on opposite corners (a great step forward from ReportNet!).
8. Tables can be your enemy: However, if you lose control of tables things can get into a real mess!!!! In particular, remember that the default width for tables is 100% of their parent or container object. This is generally desirable 'within' other tables and list columns and the like - however at the outer levels it is almost certainly something to delete and leave the width setting to blank. The table (and/or list) will then size correctly to be as wide or narrow as it needs to be rather than stretching unnecessarily.
9. Set formatting at the highest level: If you do have to override the default formats in a report and in list controls etc, do so at the highest possible level (eg table cell rather than every text item in the cell, and via the List Columns Body Style and List Columns Title Style options rather than for each individual column or heading) - also includes padding, alignment, border settings as well as colour and font etc. This simplifies your life as a developer, means that if new objects are added or columns included they inherit the correct style and will keep the size of the html passed into browsers down............
10. Keep it simple! Two basic elements here - on the data/query side put the complexity into the actual data if the complexity really is essential, or by second choice into the model, but keep it out of the reports; and on the report pages - avoid the use of Javascript or other complex solutions as they will be difficult to maintain and will almost certainly be a problem during Cognos upgrades - exception being the centrally provided and supported elements such as the 'Select Branch' prompt. The data/query elements can also be a problem at upgrade time if we complicate them - certainly was from ReportNet to Cognos 8 around sorting and the like
Number 11 is always know what your queries are doing - if the generated SQL is complicated then there is almost certainly something wrong with the model and/or the query!
No comments:
Post a Comment