Hi,
I am using OVER command to develop a report using ESSBASE as the cube/database.
The code which I have written (and which needs attention) is:
SUM
MEASURE1 AS 'M1' OVER
MEASURE2 AS 'M2' OVER
MEASURE3 AS 'M3'
BY
VAR1
BY
VAR2
..............
The above code results in the following o/p:

VAR1 VAR2
A X M1 23
M2 33
M3 45
Y M1 12
M2 4
M3 22
B Z M1 2
M2 31
M3 14
.....................
For the above output, the problem which I am facing is that the columns for VAR1 and VAR2 corresponding to 'M2' and 'M3' are blank. The desired output is as follows:
VAR1 VAR2
A X M1 23
X M2 33
X M3 45
Y M1 12
Y M2 4
Y M3 22
..................

Please let me know a workaround for the above mentioned problem.

P.S. I have tried using 'SET BYDISPLAY = ON' for this, but it did not work for this problem.

Would this work?

SUM
VAR1
VAR2
MEASURE1 AS 'M1' OVER
MEASURE2 AS 'M2' OVER
MEASURE3 AS 'M3'
BY VAR1
BY VAR2

Oops! Meant to put a NOPRINT on the BY VAR1 BY VAR2

Special-K,

A simpler option to achieve what you are suggesting would be to SET BYDISPLAY = ON ....

but, it will not supply what Syed needs. The trouble is using the OVER. If you start using a BY it will really skew the report.

This may be able to be reduced slightly but this will provide what I think Syed requires -

APP FI TEMPFILE DISK TEMPFILE.FTM
TABLE FILE CAR
 SUM COMPUTE LABEL/A12 = 'Seats';
     COMPUTE VALUE/D12.2 = SEATS;
   BY COUNTRY
   BY CAR
   ON TABLE HOLD AS TEMPFILE
END
-RUN
APP FI TEMPFILE DISK TEMPFILE.FTM (APPEND
TABLE FILE CAR
 SUM COMPUTE LABEL/A12 = 'Dealer Cost';
     COMPUTE VALUE/D12.2 = DCOST;
   BY COUNTRY
   BY CAR
   ON TABLE HOLD AS TEMPFILE
END
-RUN
APP FI TEMPFILE DISK TEMPFILE.FTM (APPEND
TABLE FILE CAR
 SUM COMPUTE LABEL/A12 = 'Retail Cost';
     COMPUTE VALUE/D12.2 = RCOST;
   BY COUNTRY
   BY CAR
   ON TABLE HOLD AS TEMPFILE
END
-RUN
TABLE FILE TEMPFILE
PRINT CAR
      LABEL
      VALUE
   BY COUNTRY
   BY CAR NOPRINT
END

T

Hi,
Apologies for the delayed response!!
I had tried using Special-K and Tony's suggestions. Out of these, Tony's suggestion has worked for me. I am using a file to APPEND the data for the different measures M1,M2 and M3. Which means that I have to append thrice to achieve the required output. Though it is a compromise on the performance of the report, it is nevertherless giving me the desired result.
I would like to thank Tony for helping me with this.
Also, I request if you can offer a workaround in which I may not have to use APPEND thrice to do the same.
Thanks in advance for this
Regards
Syed

This may sound silly, but have you tried using the IN command to position where the field start combined with Special-K's comments? How would that make your report look

SUM VAR2 MEASURE1 AS 'M1' OVER
MEASURE2 AS 'M2' IN 20 OVER
MEASURE3 AS 'M3' IN 30
BY VAR1
BY VAR2 NOPRINT
END

This assumes of course that VAR2 is a non-numeric field.

Hi,
You suggestion of using the following piece of code did not work:
SUM VAR2 MEASURE1 AS 'M1' OVER
MEASURE2 AS 'M2' IN 20 OVER
MEASURE3 AS 'M3' IN 30
BY VAR1
BY VAR2 NOPRINT
END
Actually, I need to repeat all the data for the different rows generated by the OVER clause. And this is not happening in my case.
As of now I am going with Tony's suggestion and it is giving me the required o/p.

Regards
Syed

Hi Syed,

Glad the suggestion worked for you!

As for the reduction of code, I agree that three passes of the data (in the sample code) is excessive and an I/O overhead, however reducing it depends on what your source data is. If it is SQL based then you could use a UNION ALL within some passthru SQL thus making SQL do all the hard work and only leaving WF to read the single result set returned. I am currently using MS-SQL and perform all extracts (when I can) using SQL passthru - more efficient! If not SQL then give us a clue on what source it is and we might be able to assist.

T