Hi,
I want to use SQL Server tables directly in Cognos PowerPlay Transformer as my datasources without using Impromptu. Please guide me how to go about that.
Quick response is appreciated.
Thanks in advance.
Hi,
You're going to have to code yourself an IQD to define the data to be retrieved, in that case. Or create the IQD from Framework Manager if you have ReportNet or Cognos 8.
Regards,
MF.
And here is content for a sample IQD file:
COGNOS QUERY
STRUCTURE,1,1
DATABASE,DWPRD
DATASOURCENAME,DatesCorp.imr
TITLE,DatesCorp.imr
BEGIN SQL
{
select
T1.PCTB_PRO_YEAR as c1,
T1.PCTB_PRO_WEEK as c2,
T1.PCTB_PRO_PERIOD as c3,
T1.PCTB_PER_WEEK as c4,
T1.PCTB_PRO_YEAR * 100 + T1.PCTB_PRO_PERIOD as c5,
100 * T1.PCTB_PRO_YEAR + T1.PCTB_PRO_PERIOD || '/' || T1.PCTB_PER_WEEK as c6,
'Period ' || (to_char(trunc(T1.PCTB_PRO_PERIOD))) as c7,
'P' || (to_char(trunc(T1.PCTB_PRO_PERIOD))) || '/W' || (to_char(trunc(T1.PCTB_PER_WEEK))) as c8,
'YEAR' || (to_char(trunc(T1.PCTB_PRO_YEAR))) as c9,
'PRD' || (to_char(trunc(T1.PCTB_PRO_YEAR))) || '_' || lpad(T1.PCTB_PRO_PERIOD,2,'0') as c10,
'WEEK' || (to_char(trunc((100 * T1.PCTB_PRO_YEAR + T1.PCTB_PRO_WEEK)))) as c11,
T1.PCTB_CAL_DATE as c12,
'BUSSD' || (to_char(T1.PCTB_CAL_DATE,'YYYY-MM-DD')) as c13,
(to_char(T1.PCTB_CAL_DATE,'YYYY-MM-DD')) as c14,
'Quarter ' || to_char(1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) as c15,
'QTR' || to_char(1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) as c16,
'YEAR' || (to_char(T1.PCTB_CAL_DATE,'YYYY')) || '_CM' as c17,
'QTR' || to_char(1 + floor( ( to_number(to_char( T1.PCTB_CAL_DATE,'MM' )) - 1 ) / 3 ) ) || '_CM' as c18,
'MONTH' || (to_char(T1.PCTB_CAL_DATE,'MM')) || '_CM' as c19,
(to_char(T1.PCTB_CAL_DATE,'Month')) as c20,
'QTR' || to_char(T1.PCTB_PRO_YEAR * 100 + to_char(1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) ) as c21,
CASE
WHEN T2.EON = 0 THEN
CASE
WHEN 100 * to_number(T1.PCTB_PRO_YEAR) + to_number(T1.PCTB_PRO_PERIOD) <= T2.CM THEN
'Period ' || (to_char(trunc(T1.PCTB_PRO_PERIOD))) || ' (A)'
ELSE
'Period ' || (to_char(trunc(T1.PCTB_PRO_PERIOD))) || ' (F)'
END
ELSE
CASE
WHEN 100 * to_number(T1.PCTB_PRO_YEAR) + to_number(T1.PCTB_PRO_PERIOD) < T2.CM THEN
'Period ' || (to_char(trunc(T1.PCTB_PRO_PERIOD))) || ' (A)'
WHEN 100 * to_number(T1.PCTB_PRO_YEAR) + to_number(T1.PCTB_PRO_PERIOD) = T2.CM THEN
'Period ' || (to_char(trunc(T1.PCTB_PRO_PERIOD))) || ' (A/F)'
ELSE
'Period ' || (to_char(trunc(T1.PCTB_PRO_PERIOD))) || ' (F)'
END
END as c22,
CASE
WHEN 100 * to_number(T1.PCTB_PRO_YEAR) + to_number(T1.PCTB_PRO_WEEK) > T2.CW THEN
'P' || (to_char(trunc(T1.PCTB_PRO_PERIOD))) || '/W' || (to_char(trunc(T1.PCTB_PER_WEEK))) || ' (F)'
ELSE
'P' || (to_char(trunc(T1.PCTB_PRO_PERIOD))) || '/W' || (to_char(trunc(T1.PCTB_PER_WEEK))) || ' (A)'
END as c23,
CASE
WHEN ( T2.EOP = 1 OR T2.EON = 1 ) THEN
CASE
WHEN 100 * to_number(T1.PCTB_PRO_YEAR) + (1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) < T2.CQ THEN
'Quarter ' || to_char(1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) || ' (A)'
WHEN 100 * to_number(T1.PCTB_PRO_YEAR) + (1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) = T2.CQ THEN
'Quarter ' || to_char(1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) || ' (A/F)'
ELSE
'Quarter ' || to_char(1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) || ' (F)'
END
ELSE
CASE
WHEN 100 * to_number(T1.PCTB_PRO_YEAR) + (1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) <= T2.CQ THEN
'Quarter ' || to_char(1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) || ' (A)'
ELSE
'Quarter ' || to_char(1 + floor( ( to_number( T1.PCTB_PRO_PERIOD ) - 1 ) / 3 ) ) || ' (F)'
END
END as c24,
CASE
WHEN T2.EOY = 1 THEN
CASE
WHEN T1.PCTB_PRO_YEAR <= T2.CY THEN
T1.PCTB_PRO_YEAR || ' (A)'
ELSE
T1.PCTB_PRO_YEAR || ' (F)'
END
ELSE
CASE
WHEN T1.PCTB_PRO_YEAR = T2.CY THEN
T1.PCTB_PRO_YEAR || ' (A/F)'
WHEN T1.PCTB_PRO_YEAR < T2.CY THEN
T1.PCTB_PRO_YEAR || ' (A)'
ELSE
T1.PCTB_PRO_YEAR || ' (F)'
END
END as c25
from
cal_dt_tbl T1,
(
SELECT
TO_NUMBER(PCTB_PRO_WEEK) as W,
100 * to_number(PCTB_PRO_YEAR) + to_number(PCTB_PRO_WEEK) as CW,
100 * to_number(PCTB_PRO_YEAR) + to_number(PCTB_PRO_PERIOD) as CM,
100 * to_number(PCTB_PRO_YEAR) + (1 + floor( ( to_number( PCTB_PRO_PERIOD ) - 1 ) / 3 )) as CQ,
to_number(PCTB_PRO_YEAR) as CY,
CASE WHEN TO_NUMBER(PCTB_PRO_WEEK) IN (52,53) THEN 1 ELSE 0 END as EOY,
CASE WHEN TO_NUMBER(PCTB_PRO_WEEK) IN (13,26,39) THEN 1 ELSE 0 END as EOQ,
CASE WHEN TO_NUMBER(PCTB_PRO_WEEK) IN (4,8,17,21,30,34,43,47) THEN 1 ELSE 0 END as EOP,
CASE WHEN TO_NUMBER(PCTB_PRO_WEEK) IN (52,53,13,26,39,4,8,17,21,30,34,43,47) THEN 0 ELSE 1 END as EON
FROM
cal_dt_tbl
WHERE
PCTB_CAL_DATE = to_date( next_day(sysdate,'sunday') - 8 )
) T2
where (TO_CHAR(T1.PCTB_CAL_DATE,'YYYY')>'2003')
}
END SQL
COLUMN,0,Year
COLUMN,1,Week
COLUMN,2,Period
COLUMN,3,Week of Period
COLUMN,4,YearAndPeriod
COLUMN,5,YearAndWeek
COLUMN,6,Period Label
COLUMN,7,Week Label
COLUMN,8,Year Code
COLUMN,9,Period Code
COLUMN,10,Week Code
COLUMN,11,Buss Date
COLUMN,12,Buss Date Code
COLUMN,13,Buss Date Label
COLUMN,14,Quarter Label
COLUMN,15,Quarter Code
COLUMN,16,Year Code_CM
COLUMN,17,Quarter Code_CM
COLUMN,18,Month Code_CM
COLUMN,19,Month Label
COLUMN,20,YearAndQtr
COLUMN,21,Period Descr
COLUMN,22,Week Descr
COLUMN,23,Qtr Descr
COLUMN,24,Year Descr