Skip to Content
author's profile photo Former Member
Former Member

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

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


Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Dec 01, 2005 at 10:41 AM

    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,


    Add a comment
    10|10000 characters needed characters exceeded

    • 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


  • Posted on Dec 02, 2005 at 09:24 AM

    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,


    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.