Restrict on a "Date" type (key-figure)

[Former Member]

Hello, all

1. I need to define a query, to compare the planed-date and the current-date(today), if planed-date>today, then in the new column, the firstday of the month will be listed

(e.g. 1-12-2005).

could you give me some suggestion, how can I found the functions of reading date in Query Designer.(I didn't find any function in the Form).

2. Any how can I add Macro in the report? is there some document I can read in SDN?

Many thanks

Tony

[Former Member]

Hi Tony,

I don’t know details about your infoobjects design tech aspects. Hence, I propose that what will work. You can adjust your design accordingly.

I encountered a problem while working with dates determined as key figure attribute.

But with dates as reference to 0CALDAY attributes everything works.

So, we have a Project infoobject. Create plan-beg-date and plan-end-date characteristics with reference to 0CALDAY. Assign them as attributes to Project. For testing purposes enter manualy several master data for it. Insert it as a data target (RSA1, infoproviders area). If you have a cube with the Project, then you don’t need the previous step.

In BEx, create a V1 formula variable for plan-beg-date: replacement path, char = Project, Replace var with = Attribute value, Attribute = plan-beg-date, check ‘To value’.

Similarly, create V2 formula variable for plan-end-date.

Create in columns a new structure. If you work with Project infoobjects as infoprovider, put into structure ‘Number of records KF’, otherwise the following steps might not work. Add into the structure a new formula = V1, new formula = V2, new formula = V2 - V1. It will give you the the planned duration of the project.

Now, create X1 & X2, two customer exit variables on 0CALDAY. Based on either user entered reporting day or on current sys-day they should return the first and last days of the reporting/current month.

In the structure create another formula for corrected beginning date:

DATE ( ( 'V1' < 'X1' ) * 'X1' + ( 'X1' < 'V1' ) * 'V1' )

and a formula for corrected ending date:

DATE ( ( 'V2' > 'X2' ) * 'X2' + ( 'X2' > 'V2' ) * 'V2' )

Now you can calculate planned days for the reporting month or even the total number of project plan days.

About macro – you execute a query, embed it into a workbook, write a macro and save the workbook. Next time you’ll open the workbook in Analyzer.

Best regards,

Eugene

[Former Member]

Hi Tony,

Need you clarification:

Do you need this first day of the month for KFs restriction, or just for display purposes?

For getting dates in a query you may use replacement path variables or customer exit variables.

Macro in Excel you may use in a std way: menu Tools/Macro.

Best regards,

Eugene

[Former Member]

Hi, Eugene,

the scenario is,

we save the general information of project in an ODS. Planned-date means the planned-begindate of the project.

each month we need to make the report to calculate how many days has been planned in the reportmonth.

e.g One project, planned-begindate is 21-10-2005, the project planned-enddate is on 20-1-2006.

Now I make the report for december to see how many days we planned in december. That means the planned-begindate-in-the-month should be 1-12-2005 (because 21-10-2005 is less than 1-12-2005)

and the planned-enddate-in-the-month should be 31-12-2005.(because 20-1-2006 is larger than 31-12-2005)

therefore first I need to read the reportdate, and search project whose the planned-begindate or planned-enddate in the range of report month

and get such a list:

Planned-begindate | Planned-Enddate | |planned-enddate-in-thismonth

then I can use "planned-enddate-in-this-month" - "planned-begindate-in-this-month"

to get the planned days for reportMonth.

About the Macro, is it means the I add the Macro in BEx and save it as workbook?

Thanks a lot

Tony