Inserting a date variable in PseudoMDX D - SAP OLAP Connector

[#1]

Hi

My query looks something like this:

[Query]:
Load *;
Select PseudoMDX D (
Dimensions (
  (),
  (),
  (),
  (),
  [CALMONTH] ()),
Measures (
  [1],
  [2],
  [3],
  [4],

Variables (
  [0TYEAR] (I [CALMONTH].[201401]:[CALMONTH].[201403])),
From (Query));

Since there are limitations with the amount of data I can retrieve, I have to retrieve data every 3 months at a time.

There is also no Primary Key - hence I cannot do Incremental Loads.

Is there a way to loop through the query but then changing the date range (To the next three months)? - Maybe using a variable or any other way for that matter?

Any help would be appreciated - Thanks!

Magen

[Anonymous]

Hi

You can do this:

for vMonth=201401 to 201412

     DATA:

     Select PseudoMDX D (
     Dimensions (
       (),
       (),
       (),
       (),
       [CALMONTH] ()),
     Measures (
       [1],
       [2],
  ...

     Variables (
       [0TYEAR] (I [CALMONTH].[$(vMonth)]:[CALMONTH].[$(vMonth)])),
     From (Query));

next vMonth

This loop will return the query results for each calendar month. The data will be aggregated to table DATA.

[#3]

Hi Yaniv

Thanks that works great, but, what if i want to retrieve data over 2 years.

So for example 201301 to 201412.

I cannot declare this in the variable as it doesn't recognize it to be a year and month.

Hence if i attempt to run it, it would return 201301, 201302,...201312, 201313, 201314 and so on ...

Any idea?

Regards,

Magen

[Anonymous]

Hi Magen

Been busy during the last couple of days...

You should use nested loop, for example:

for year = year(today()) to year(today())+1

  for period = 0 to 12

  if len(period) = 1 then

  let v_period = '0'.

  else

  let v_period = period;

  endif

  Select PseudoMDX D (

      Dimensions (

        (),

        (),

        (),

        (),

        [CALMONTH] ()),

      Measures (

        [1],

        [2],

        [3],

        [4],

      Variables (

  [0VYEAR] (I = [0CALYEAR].[$(year)]),

  [0VCALMONTH] (I = [0CALMONTH].[$(v_period)])),

      From (Query));

  next period

next year

Thanks

Yaniv