Use of SQL Server Tables In Transformer

[poornadeep]

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.

[MFGF]

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.

[Darek]

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