Automated URL generator for Cognos Reports using Cognos Report Studio

Requirement:
Users request to provide all report name with Report URL and possible prompt default selection so that they can embed in their external web page for user publishing in the way they want to organize.

Report Search path that can be obtained from Audit database:
/content/folder[@name=’Samples’]/folder[@name=’Models’]/package[@name=’GO Data Warehouse (analysis)’]/folder[@name=’Report Studio Report Samples’]/report[@name=’Revenue by Year’]

Case I:

When XSS checking is not enabled, below Cognos Proven Practice document can be used to understand how URL is formed:
http://public.dhe.ibm.com/software/dw/dm/cognos/reporting/scripting_techniques/using_urls_in_cognos_8.pdf


Case II:

When XSS checking is enabled for highly secured intranet environments. Below link provides what needs to be done to encode URL to be compatible:-
http://queryvision.com/custom-cognos-url-support-and-cross-site-scripting/


Design:

You can use Cognos to create a report to do the encoding and creating URL.

Step 1: Get Search Path

Using Audit database:

Report list and search path can be restricted by Audit Report and in final query, you can bring
Report Name
Report Search Path
then create multiple transformation to generate final URL.

Using Native SQL for single URL convertor:
select
#promptmany('pURL','')# as URLPath from dummyTable

Using NativeSQL for multiple search paths [already downloaded in excel] :

select
'/content/folder[@name=''Sample Reports'']/folder[@name=''Models'']/folder[@name=''Dashboard'']/report[@name=''Revenue by Year'']' as URLPath
from DummyTable
Union
select
'/content/folder[@name=''Sample Reports'']/folder[@name=''Models'']/folder[@name=''Dashboard'']/report[@name=''Revenue by Month'']' as URLPath
from DummyTable


Please note that escape characters are required to include quotes in this case.

Step 2: Transformation
Once we get search path in URLPath data item, transformations can be applied [ example]

Conversion [if needed for XSS checking enabled scenario] - more conditions based on characters used:
replace([SQL1].[URLPATH],'/','*2f')
replace([Data Item1], '[', '*5b')replace([Data Item2],'@', '*40')replace([Data Item3],'=', '*3d')replace([Data Item4],']','*5d')replace([Data Item5], ' ', '*20')replace([Data Item6], '''','*27')

Report Path:
substring([URLPATH],position('report[@name=', [URLPATH])+14)position(']',
substring([URLPATH],position('report[@name=', [URLPATH])+14)
) -2
replace(substring([ReportStart],1,[ReportEnd]), ' ', '*20')
Final URL:
'http://localhost/cognos/cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=XSSSTART' + [Data Item7] + 'XSSEND&ui.name=XSSSTART' || [ReportName] || 'XSSEND&run.outputFormat=HTML&run.prompt=true' + '&cv.header=false&cv.toolbar=false'

This should generate URL and can be used in html item and downloaded in Excel.




Popular posts from this blog

Cognos Excel Text Wrapping Issue - Row Merge and Column Merge

Below are the properties to set to keep column size and width with text wrapping: Column Merge issue  http://www-01.ibm.com/support/docview.wss?uid=swg21674298 Set the advanced server property RSVP.EXCEL.XLS2007_COLUMN_WIDTH_CONTROL to TRUE for both BatchReportService and ReportService services.        Row Merge issue http://www-01.ibm.com/support/docview.wss?uid=swg21440556 RSVP.EXCEL.XLS2007_ALLOW_WRAPPING_SINGLE_CELL also to be set to TRUE  for both BatchReportService and ReportService services.   Once done, you can set any column width to be fixed and see same result in Excel as well as HTML. Please note that above properties will not be honored if you have unchecked Grouping repeating cells Report property in Report Properties menu. 
Read more

Cognos: How to replace an existing report without breaking links?

Below IBM document explains behavior of copy/move or overwriting impact of objects:- https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.wig_cr.doc/c_gtstd_copy_move_items.html If you copy and overwrite an existing report, the copied report ID and links replace those of the existing report. In this case, you might need to update links, such as links to job schedules. If you move and overwrite an existing report, the existing report ID and links are maintained. In this case, references to the moved entry are broken. However, this doesn't work in all cases and we see drill through links broken in some cases. Best approach in such case is to use cognos deploy method where you update reports in separate folder in same environment and import it to destination location. You can create single export and import tasks to use for all such updates.
Read more

How to change package for a report in IBM Cognos Analytics (ICA)?

In Cognos 10.x and 8.x , this was the simple to handle on Cognos Connection or via Repot Studio interface.  In Cognos 11.0.6, You  need to load classic view (Cognos Connection) to go back to this interface to change the package. In Cognos Cloud environment, we have single environment for Development and UAT. In such cases, when we move content from Dev folder to UAT area, we need to switch to classic mode to change the package.
Read more