Query having two column and both column required different months data.

[former_member494935]

Hi All,

My query consist of two column A & B. Data is coming from a single cube. Now i have  to implement below mentioned condition:

i) if run date of the report is during months January - March:

   Then report should display data of December of the previous year for column A and January - December from previous year for column B.

ii) if run date of the report is during months April - June:
    Then report should display data of March of the current year for column A and January - March from current year for column B.

iii) if run date of the report is during months July - September:

     Then report should display data of June of the current year for column A and January - June from current year for column B.

iv) if run date of the report is during months October - December:

     Then report should display data of September of the current year for column A and January - September from current year for column B.

Please guide me how to do this.

Thanks,

Sumadhur

[former_member494935 · Accepted solution]

Hi Seshu,

Thanks.

Actually we are having 2 columns A&B and data is present in cube.

Now there are four cases:

Case 1: When we run report anytime between 1 Jan 2015 to 31 Mar 2015, then column A will give data of Dec 2014 and column B will give data from Jan 2014 to Dec 2014.

Case 2: When we run report anytime between 1 Apr 2015 to 30 Jun 2015, then column A will give data of Mar 2015 and column B will give data from Jan 2015 to Mar 2015.

Case 3: When we run report anytime between 1 Jul 2015 to 30 Sept 2015, then column A will give data of Jun 2015 and column B will give data from Jan 2015 to Jun 2015.

Case 4: When we run report anytime between 1 Oct 2015 to 31 Dec 2015, then column A will give data of Sept 2015 and column B will give data from Jan 2015 to Sept 2015.

Hope this will clear.

[Loed · Accepted solution]

Hi,

So you have SELECTIONS A and B right?

Insert all the filters and the KF needed for both selections..

Insert CALDAY for both of them..

Create a variable for CALDAY in SELECTION A with customer exit as its processing by and with INTERVAL options, let's call it ZONE_MONTH..

Create another variable for CALDAY in SELECTION B with customer exit as its processing by and with INTERVAL options, let's call it ZRANGE_MONTH..

After doing the variables I mentioned above, do this in CMOD:

For ZONE_MONTH...

data: range_low type d,

       range_high type d,

       temp_day type d.

WHEN 'ZONE_MONTH'.

CONCATENATE sy-datum(4) '0101' into temp_day.

temp_day = temp_day - 1.

if sy-datum+4(2) >= '01' and sy-datum+4(2) <= '03'.

concatenate temp_day(6) '01' into range_low.

concatenate temp_day(6) '31' into range_high.

elseif sy-datum+4(2) >= '04' and sy-datum+4(2) <= '06'.

concatenate sy-datum(4) '0301' into range_low.

concatenate sy-datum(4) '0331' into range_high.

elseif sy-datum+4(2) >= '07' and sy-datum+4(2) <= '09'.

concatenate sy-datum(4) '0601' into range_low.

concatenate sy-datum(4) '0630' into range_high.

elseif sy-datum+4(2) >= '10' and sy-datum+4(2) <= '12'.

concatenate sy-datum(4) '0901' into range_low.

concatenate sy-datum(4) '0930' into range_high.

endif.

L_S_RANGE-LOW         = range_low.

L_S_RANGE-HIGH        = range_high.

L_S_RANGE-SIGN        = 'I'.

L_S_RANGE-OPT         = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

EXIT.

For ZRANGE_MONTH..

data: range_low type d,

       range_high type d,

       temp_day type d.

WHEN 'ZRANGE_MONTH'.

CONCATENATE sy-datum(4) '0101' into temp_day.

temp_day = temp_day - 1.

if sy-datum+4(2) >= '01' and sy-datum+4(2) <= '03'.

concatenate temp_day(4) '0101' into range_low.

concatenate temp_day(6) '31' into range_high.

elseif sy-datum+4(2) >= '04' and sy-datum+4(2) <= '06'.

concatenate sy-datum(4) '0101' into range_low.

concatenate sy-datum(4) '0331' into range_high.

elseif sy-datum+4(2) >= '07' and sy-datum+4(2) <= '09'.

concatenate sy-datum(4) '0101' into range_low.

concatenate sy-datum(4) '0130' into range_high.

elseif sy-datum+4(2) >= '10' and sy-datum+4(2) <= '12'.

concatenate sy-datum(4) '0101' into range_low.

concatenate sy-datum(4) '0930' into range_high.

endif.

L_S_RANGE-LOW         = range_low.

L_S_RANGE-HIGH        = range_high.

L_S_RANGE-SIGN        = 'I'.

L_S_RANGE-OPT         = 'BT'.

APPEND L_S_RANGE TO E_T_RANGE.

EXIT.

Just post here for question..

Regards,

Loed

[former_member494935]

Hi Loed,

I have written the code and its working fine.

Thanks.

Regards,

Sumadhur Saxena

[Loed]

Hi,

Good to know that it worked for you..

Regards,

Loed

[former_member494935]

Hi Loed,

My project is implemented with BADI. So i have to.

Hi Swati,

Thanks. Yes it my first badi implementation. This will help.

Best,

Sumadhur

[Loed]

Hi,

Ok, just update us later..

Regards,

Loed

[former_member494935]

Hi Leod/Swati,

Thanks a lot.

I need to implement BADI as we are having BADI here.

Once done will let you know.

Regards,

Sumadhur

[Loed]

Hi,

Why would you implement BADI? Above code can be done in CMOD..

Regards,

Loed

[swati_gawade]

Hi Loed,

I think he means that his project has CMOD implemented with BADIs. SAP now recommends CMOD implementation through BADI's.

Hi Sumadhur,

If this is your first badi implementation then u can check this out.

http://scn.sap.com/docs/DOC-31271

Hope this Helps.

-Swati.

[former_member494935]

Hi,

Yes no user entry is needed. We will use current date.

Thanks,

Sumadhur

[Loed]

Hi,

Ok I will try to do the code later at home since I'm already out..

But you can do this already, we will use CALDAY instead of CALMONTH..

So you have SELECTIONS A and B right?

Insert all the filters and the KF needed for both selections..

Insert CALDAY for both of them..

Create a variable for CALDAY in SELECTION A with customer exit as its processing by and with INTERVAL options, let's call it ZONE_MONTH..

Create another variable for CALDAY in SELECTION B with customer exit as its processing by and with INTERVAL options, let's call it ZRANGE_MONTH..

I will create the code later..

Regards,

Loed

[swati_gawade]

Hi Sumadhur,

If no Input is needed then We can work with system date.

Create a Variables Var 1 on Calmonth with not input ready and Customer Exit as Processing type.

In the TCODE CMOD, in the case statement code below logic.

Data:  Month(2) type C,

          Year(4) type C.

When "VAR1".

Month = Sy-datum+4(2).

Year = Sy-datum(4).

If month LE 3 and month GE 1.

Year = year -1.

Concatenate Year '012' into Var1.

Elseif month LE 6 and month GT 3.

Concatenate Year '003' into Var1.

Elseif month LE 9 and month GT 6.

Concatenate Year '006' into Var1.

Elseif month LE 12 and month GT 9.

Concatenate Year '009' into Var1.

[Loed]

Hi,

You can do this by creating three (3) variables: one variable for user entry and the other two (2) variables with customer exit..

May I ask what time char are you using? CALDAY? CALMONTH? FISCPER?

Regards,

Loed

[former_member494935]

Hi Loed,

Thanks.

Actually I don't have much knowledge about ABAP programming. I am new to this. So is there any other way to achieve the desired result?

0CALQUARTER, 0CALQUARTER1, 0CALMONTH, 0CALMONTH2, 0CALDAY AND 0CALYEAR time char are present.

[Loed]

Hi,

What I mean is what time char are you going to use for your requirement above? What will be your user entry? It it CALDAY? or CALMONTH? or FISCPER?

I will help you with the code..

Regards,

Loed

[former_member494935]

Hi Loed,

Actually there is no user entry for this report.

CALMONTH will be used.

Just consider a case:

Today is 10 Sept 2015 and we are running report today, So column A will show only June 2015 data and column B will show Jan 2015 to June 2015 data.

Thanks,

Sumadhur

[Loed]

Hi,

So no user entry is needed? The report should based on current date? Is it right?

Regards,

Loed

[former_member199945]

Hi ,

we are  having some standard variables for 0CALMONTH which will satisfy your requirement .

Today is 10 Sept 2015 and we are running report today, So column A will show only June 2015 data and column B will show Jan 2015 to June 2015 data : Can you please elaborate more on this .

0CALMONTH	0CML3LM	Last 3 Months Excluding the Current Month
0CALMONTH	0CMLMLY	Last Month of Previous Year
0CALMONTH	0CMLMON	Last Month
0CALMONTH	0CMLYTCM	Previous Year Cumulated to Current Month
0CALMONTH	0CMLYTLM	Previous Year Cumulated to Last Month
0CALMONTH	0CMONTH	Current Calendar Month
0CALMONTH	0CYTCM	Cumulated to current month
0CALMONTH	0CYTLM	Cumulated to last month
0CALMONTH	0I_CMY01	Current Year Cumulated to Current Month (SAP Exit

Thanks.