Search & Suggest Prompt in Cognos8.4

If you need a Google-like Select and Suggest Prompt like this:



(type in the first characters and Cognos will complete it with suggestions), just perform the following steps:

1. Create a prompt page
2. Insert a value prompt object
3. Name it something like 'dummy'
4. Skip the step 'Create a parameterized filger' (uncheck the box).
5. Create a new Query (Selecion Query for dummy)
and select the value to use and the value to display
(for example, city code and city name).
6. Set the properties of the value prompt:
- Required: No
- Visible: No
= Name: prmt
7. Insert a text box prompt (do not use the wizard, click op finish). Name: p_txt
8. Add three HTML items to the right of the text box:

1:



2:



3:



9. Complete it by adding a query with the filter (like city code = ?city?)
10. Configuration of the first Javascript:
 this.actb_lim = 4;    // Number of elements autocomplete can show (-1: no limit)
 this.actb_firstText = false; // should the auto complete be limited to the beginning of keyword?
 this.actb_startcheck = 1; // Show widget only after this number of characters is typed in.

Ad hoc Mini Mart

If you need to improve performance for complex reports, think about implementing a minimart on top of your datamart.

On of my customers was specifically interested in analysing linehauls with a certain origin and a certain destination.

I created a stored procedure that can be kicked off via a cognos report.

CREATE procedure [dbo].[pLoadVolumeAnalysisMiniMart]
@origin varchar(6)
, @destination varchar(6)
as
declare @loaddate datetime
set @loaddate = getdate()

truncate table VolumeAnalysis MiniMart

insert into VolumeAnalysisMiniMart
select
,Sector
,Sectortype
,Week
,Weight
,Pieces
,Sender
,Receiver
,Origin
,Destination
,Service
,Departuretime
,Arrivaltime
,@loaddate
,Runid
,Rundate
from VolumeAnalysisDataMart
where Origin = @origin
and Destination = @destination

select 'Mini Mart has been loaded' as Result

end

This procedure loads a table with a specific origin and a destination (in casu the number of records decreased from 21 million to several thousands). The dataload takes about 1-2 seconds.

In framework manager I created a star schema with the mini mart as a fact table and aliases of the (original) dimension tables.

I also added the stored procedure as query object:


call pLoadReportPromptSector;1(
#prompt('origin')#,
#prompt('destination')#
);


In Report Studio I created a query with data item 'Result', prompts 'Origin' and 'Destination'.
As soon as the parameters are filled out, the procedure will be kicked off and the data will be loaded.

I also created hyperlink buttons on the report that links the user to several reports which retrieve the data from the minimart.

If you do not have schema's for each user, you can create separate tables or add column 'user' with #account. You can pass this variable to the procedure, so every user has it's own set of data.

See also:

http://www.sqlserverclub.com/pdfs/minimarts-organizational-segmentation-in-datawarehousing.pdf :

Many decisions makers not only need to analyze data from within the business unit,
but also across business units or the many segments within a business unit. Think of
it as market segmentation – cubes for more a selective audience.
Your customers can be grouped by segments of business units or smaller functional
groups with unique needs for reporting. Most segments will contain unique, pertinent
subsets of data along with business unit-wide data for decision making. Therefore,
cubes can be made from subsets of cubes in what I would call “minimarts.” These
are datasets that can be made “precise” for more targeted queries thereby
increasing performance, especially across a network, intranet, extranet (or even the
general public internet).
The pros are pretty clear by targeting your audience. Performance will be greater
than querying vast cubes of data that can go unused for most members of your
customer base.

Scheduled Cognos Reports

If you are not able to access Cognos Auditing reports
and you need to know which reports are scheduled, you
can query the Content Store. This script includes
the source of report views and the name of the package.

select
r.path+r.name report_or_report_view
, r.created
, r.modified
, r.disabled
, r.class
, r.description
, p.startdate
, p.enddate
, case when r.class = 'reportview' then s.path+s.name else 'N/A' end as source_report_view
, case when r.class = 'report' then s.name else t.name end as package
from reports r
join cmobjects j on r.cmid = j.pcmid
join cmobjprops2 p on p.cmid = j.cmid
join cmrefnoord1 n on n.cmid = r.cmid
join reports s on s.cmid = n.refcmid
left join cmrefnoord1 q on q.cmid = s.cmid
left join reports t on t.cmid = q.refcmid
where p.active=1
and (p.endtype = 0 or p.enddate >getdate())

You can fill the 'reports' table by executing the procedure
as described in my earlier post in

Content Store Reporting

Multiple years in a Cognos ReportNet Graph

If you visualize data to a line chart with 'zero-values' (or in my case, they do have values but they are excluded because of the fact that they belong to weeks with a public holiday) and you need to create a distinction between the years, there will be gaps in the line. See the graph below:



I needed to create a graph starting in week 44 (the start of the impact of the financial crises) without any gaps. The periods (200844-200943 and 200744-200843) had to have different to colours. If you create a chart without series, there is no distinction anymore between
the years.



Query:



Tabular data: (the data item 'Serie'' is defined in Report Studio).



To place all historical data on one line, I added a week_key column which is the concatination of the year and the week. I added the 'serie column' as a serie (instead of year). In the properties of the level item of the week_key I set 'Sort' on 'ascending':





It didn't work immediately as the 'key property' of the level item 'week_key' had to be set on 'Yes':



After this, the result was:



Another usage of this:



Query:

Automated Cognos Report Documentation

If you need a list with all reports (Cognos ReportNet or Cognos8), including the location of the report you can query the content store. It's easy to retrieve a list like:

Location	Name	Created	Modified	Type	Contact	Description
GO Data Warehouse/Employee Profile	Employee Profile	2005-09-07 18:06:45.790	2007-01-04 20:43:34.617	powerplay cube	Stewart James	Human resources cube showing data for each employee.
GO Data Warehouse/Sales Target by Region	Sales Target by Region	2005-07-17 20:04:09.890	2007-01-04 20:44:12.587	powerplay report	Stewart James	Report showing sales target by region using a prompt to provide values for the report.
GO Data Warehouse/Tool Tips	Tool Tips	2005-08-23 19:26:18.127	2007-01-04 20:44:30.040	report	Stewart James	Report that shows tool tips and baselines in chart.
GO Sales and Retailers/Actual Sales against Target Sales	Actual Sales against Target Sales	2005-05-27 20:46:43.640	2006-12-14 15:41:06.260	report	Melvin John	The report shows a simple list with conditional formatting that drills through to the Sales Representative Contact List report.
GO Sales and Retailers/Actual Sales against Target Sales-burst	Actual Sales against Target Sales-burst	2005-05-27 20:50:06.910	2006-12-14 15:41:15.463	report	Melvin John	Report that is set up for bursting to email addresses. The email addresses point to sales representatives in the Sales reps table in the GOSALES database.
GO Sales and Retailers/Banded Report	Banded Report	2005-05-27 20:57:01.480	2006-12-14 15:41:26.433	report	Melvin John	Banded report that shows Product name Quantity and Revenue with sales opportunities for each Product line category.
GO Sales and Retailers/Business Details Drill Through	Business Details Drill Through	2005-05-27 19:12:28.627	2006-12-14 15:42:07.150	report	Melvin John	Report that shows product details in charts. This report is a drill-through target report for the GO Business View report. You can also run it as a product-prompted report.
GO Sales and Retailers/Consumer Trends	Consumer Trends	2007-01-05 15:35:24.003	2007-01-05 16:24:21.317	report	Melvin John	This complex report shows a list chart bar chart and product images to illustrate revenue by product type.
GO Sales/Conditional Display	Conditional Display	2006-05-30 18:42:25.157	2007-01-04 20:45:26.793	report	Dropster Tina	The report uses a prompt page to query a chart and a list report.
GO Sales/Custom Legend	Custom Legend	2005-05-19 18:57:04.317	2007-01-04 20:45:34.840	report	Dropster Tina	The report shows that the legend can be customized in a similar way to the titles.
GO Sales/Orders Report	Orders Report	2005-06-13 12:50:18.680	2007-01-04 20:45:52.907	report view	Dropster Tina	This report shows the default style applied to List Column Title and the List Column Body in a single step.
GO Sales/Product Report	Product Report	2005-05-19 18:56:43.683	2007-01-04 20:47:20.690	report	Dropster Tina	The report displays combination charts with drills through options.
GO Sales/Retailer Report (Multiple Prompt Values)	Retailer Report (Multiple Prompt Values)	2005-06-16 16:50:21.937	2007-01-04 20:56:16.270	report	Dropster Tina	This list report accepts multiple prompt values.
GO Sales/Returns by Order Method -Prompted Chart	Returns by Order Method -Prompted Chart	2005-08-05 17:00:31.907	2007-01-04 20:56:56.177	report	Dropster Tina	This report uses a bar chart and a list to show the reasons why products are returned. It uses a prompt to filter on the return description. It is illustrated in the Getting Started tutorial.
GO Sales/Revenue by Sales Territory	Revenue by Sales Territory	2005-06-14 18:19:12.653	2007-01-04 21:00:04.703	report	Dropster Tina	The report shows a chart and a crosstab display with conditional formatting that drills through to the Orders Report report.
GO Sales/Top 5 Sales Staff	Top 5 Sales Staff	2005-07-22 23:44:55.907	2007-01-04 21:00:13.737	report	Dropster Tina	This list report embeds a bar chart that shows the top five sales representatives by sales targets and revenue.
GO Sales/Top Revenue (Conditional)	Top Revenue (Conditional)	2005-06-16 19:22:10.160	2007-01-04 21:00:27.347	report	Dropster Tina	A list report that conditionally hides the Product Line group footers for the revenue lower than the specified value.

The only hard part is to create the location. You have the implement a user defined function to join the parent cmid (pcmid) to the cmid. A stored procedure fills a table with all the info you need. I wrote a script that works for ReportNet and Cognos8:

First create the UDF and the stored procedure:

CREATE FUNCTION [dbo].[ufn_GetParentPath] ( @pCurrentNodeID INT )
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @vCurrentNodeName VARCHAR(50)
DECLARE @vParentID NVARCHAR(100)
IF @pCurrentNodeID IS NULL OR @pCurrentNodeID = 0
RETURN NULL
SELECT @vCurrentNodeName = [name], @vParentID = [pcmid],@pCurrentNodeID =[cmid]
FROM [dbo].[reports]
WHERE [cmid] = @pCurrentNodeID
RETURN ISNULL([dbo].[ufn_GetParentPath] ( @vParentID ) + '/', '') + @vCurrentNodeName
END

CREATE procedure [dbo].[pLoadReportData]
as
begin
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[reports]') and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table reports
CREATE TABLE [dbo].[reports](
[cmid] [int] NOT NULL,
[localeid] [smallint] NOT NULL,
[mapdlocaleid] [smallint] NULL,
[isdefault] [bit] NOT NULL,
[name] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[pcmid] [int] NOT NULL,
[version] [int] NOT NULL,
[created] [datetime] NOT NULL,
[modified] [datetime] NOT NULL,
[disabled] [tinyint] NULL,
[class] [nvarchar](50) NOT NULL,
[dispseq] [int] NOT NULL,
[contactemail] [varchar] (128) NULL,
[contact] [varchar] (128) NULL,
[description] [nvarchar] (3772) NULL,
[owner] [int] NULL,
[path] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[spec] XML NULL
)

create index ix_cmid on reports (cmid)

insert into reports
(
cmid
, localeid
, mapdlocaleid
, isdefault
, name
, pcmid
, version
, created
, modified
, disabled
, class
, dispseq
, owner
, path
)
select
n.cmid
, n.localeid
, n.mapdlocaleid
, n.isdefault
, n.name
, o.pcmid
, o.version
, o.created
, o.modified
, o.disabled
, c.name
, o.dispseq
, o.owner
, '' path
from cmobjnames n join cmobjects o
on n.cmid = o.cmid and o.classid in (0,1,10,18,19,33,81,82)
join cmclasses c on o.classid = c.classid and n.isdefault = 1

update reports
set contact = c.contact
, contactemail = c.contactemail
from cmobjprops10 c
join reports on c.cmid = reports.cmid

update reports
set description = d.value
from cmobjprops3 d
join reports on d.cmid = reports.cmid
and d.isdefault = 1

update reports
set path = x.path
from
(SELECT [dbo].[ufn_GetParentPath] ( [cmid] ) AS [Path], [name],[cmid]
FROM [dbo].[reports]) x
where reports.cmid = x.cmid

end

Run (and schedule) the stored procedure.

Create a view to exclude the folder, package, root and content items:

create view [dbo].[v_reports]
as
select
path
, name
, created
, modified
, class
, description
, contact
, contactemail
from reports
where class in ('report','reportview','powerplaycube','powerplay report')

Query

'select * from v_reports order by path'

will do the final part of the job.

I implemented this for a customer with >37000 (!) reports, report views, cubes and cube reports. In this case, the script runs in about 10-15 seconds.

Date range filter in Cognos8 ReportStudio based on dynamic date/fixed time.

Today I needed to report data in Cognos8.2 Report Studio with a date range filter, based on the current day and yesterday (dynamic) and a designated (fixed) time.

Values had to be extracted with a DateTime between yesterday 9 AM and today 9 AM. As we use an Oracle database it should be easy like:

[DateTime] >= TO_TIMESTAMP (to_char({sysdate}-1) || ' 09:00:00.000000', 'DD-Mon-RR HH24:MI:SS.FF')
AND
[DateTime] < TO_TIMESTAMP (to_char({sysdate}) || ' 09:00:00.000000', 'DD-Mon-RR HH24:MI:SS.FF')

However, on this particular reporting environment, we are not permitted to use native functions. Hence, I wrote the Cognos8 syntax (in the Detail Filter expression):


[DateTime]

-

_make_timestamp
(
extract(year,(_add_days(current_date,-1) ) )
, extract(month, (_add_days(current_date,-1) ) )
, extract(day, (_add_days(current_date,-1) ) )

)

>=9 hour

AND

[DateTime]

-

_make_timestamp
(
extract(year,current_date )
, extract(month,current_date )
, extract(day,current_date )

)

<9 hour

Migration of Cognos7.4 Transformer to Cognos8.4

Today I needed to migrate Cognos Transformer 7.4 models to Cognos 8.4.

The iqd's have been created with the externalize method in Framework Manager.

Series 7 IQD bridge is not installed.

First test was to run a model by using the Open With ... function (together with browsing to

the cogtr.exe application). That failed:

That's a confusing error message because Cognos.INI is not used anymore in this version (it's the CS7G.ini, where I entered the correct database connection strings).

Off course, the batch file kicked off in SSIS containing "c:\program files\cognos\c8\bin\cogtr.exe" -n -s -kLOGON=%DBCRED%"d:\cube\cube model\model.mdl" also failed.

After that I opened the application cogtr.exe itself. From Transformer I browsed to a model and opened it. Guess what? No problems where shown. This means, the following change in the SSIS-execute process task should have the same impact:

Executable: c:\program files\cognos\c8\bin\cogtr.exe

Arguments: -n -s -kLOGON=%DBCRED%"d:\cube\cube model\model.mdl

WorkingDirectory: c:\program files\cognos\c8\bin\

This change was successful indeed.

Honestly, I have no clue about the root cause of this misbehaviour.

Anyway, the 'workaround' works.