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

Query Designer; Structure with date interval

Hi there,

I have to create a report like that:

First, the user have to choose a date; normally they choose the current date.

After that the BEx Workbook appears with the following layout:

Activities
           CalDay     20.05.08  21.05.08  22.05.08  23.05.08  24.05.08  25.05.08u2026
Employees
C0xxxxx              onside  	onside      holiday         
C0xxxxx
C0xxxxx	            u2026.
D0xxxxx
D0xxxxx
D0xxxxx
D0xxxxx
D0xxxxx
I0xxxxx
I0xxxxx

It shows the employees activities per day. These data is stored in an InfoCube with the dimensions; Employee, Activity Type, Time.

For the columns I have to create a structure with a date interval (<choosendate> u2026 <chossendate+90>)

Does somebody know a simple way to get the requested layout, without creating the structure by hand?

Thanks a lot.

Add a comment
10|10000 characters needed characters exceeded

Related questions

7 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on May 30, 2008 at 12:43 PM

    Hi Tobias.

    With intervall you mean a restriction with a value range?

    If I do so, i get one column that aggregate 90 days.

    But I net 90 Columns with each single date.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      yes u are right i mean a value range.

      if u create a value range and drill down your coulums by date u get 1 row with the sum of this value range and ⚠️ u get for every date a specific column.

      I have this created for monthly values which are drilled down by 0FISCPER3 (Feiscal year period) and it works fine.

      I have an overall result period 01 - 12 and values posted for every period in this value range.

      Regards

      Tobias

  • author's profile photo Former Member
    Former Member
    Posted on May 20, 2008 at 01:02 PM

    Hi,

    It can be done by taking a new selection with 0CALDAY restricted with the Keyfigure and assign a customer exit variable with offset values 0 and 90.

    Hope this will help.

    Rgs,

    I.R.K

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 29, 2008 at 11:09 AM

    Hi,

    but how I create 90 columns?

    -


    At time I select the variable "Date" and move it into the "Columns" area,

    then I double click it,

    now I select the selection entry and choose offset value,

    there i type "1", (at the next time "2", "3", ..., "90")

    -


    And these steps I repeat 90 times.

    May there is one other solution to create 90 columns that contain the

    days from date x , date x+1, date x+3, ... date x + 90 like the example above.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 30, 2008 at 12:25 PM

    Hi,

    u dont need to do 90 offset in your selections.

    u can achieve it by using an intervall.

    a) intervall with fixed dates: For this u can do an intervall 0 - +90 , or

    b) u create an intervall with variable for the enddate 0 - Variable

    The 2nd solution provides the possibility to change the intervall in selection screen.

    If u know put date to the columns as u said, u will get a drill down by day for this intervall.

    Try it.

    Regards

    Tobias

    Edited by: Tobias on May 30, 2008 2:25 PM

    Edited by: Tobias on May 30, 2008 2:26 PM

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 30, 2008 at 01:43 PM

    Okay, it works 😊

    Thanks a lot.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 01, 2008 at 07:22 AM

    Hi!,

    I've got a similar problem, in my case I would like to show a column structure like this:

                      Total     Progress     Pending     Month 1     Month 2    Month 3     Etc.
    Sales Estimation   1000          200         800          50          60         70     ...

    I'm trying to create a planning query where you can see the total, the progress you've achieved, and pending to realize, and later a range of month created by an interval of variables.

    The problem is that when I make the selection interval it aggregates all in a column doen't show one for each month.

    Tobias, it seems you've got the solution but after reading the thread I could't manage to understand how to do it.

    Can you help me please?

    Thanks a lot!

    Edited by: Alfred Roig on Jul 1, 2008 9:22 AM

    Edited by: Alfred Roig on Jul 1, 2008 9:28 AM

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      as far as i understand u have created a value range in your key figure and just want to display monthly values.The key figure u created displays this value range as total.

      This is ok so far. U just need to do a horizontal drill down by your restricting characteristic. I dont know which u use so lets say 0FISCPER3. Add 0Fiscper3 as free characteristic in Query designer.

      Then go to the query properties in BEx Analyzer or BEx query designer and add this 0Fiscper3 in columns section.

      U now have a drill down by month for every KF. Open drill down for those KF u want to display monthly, close drill down for those which should only display totals.

      This is the way it works for "normal" queries. Planning queries are much more sensible on drill down. Maybe changes are necessary on data model.

      Regards

      Tobias

      Edited by: Tobias on Jul 2, 2008 11:55 AM

  • author's profile photo Former Member
    Former Member
    Posted on Jul 04, 2008 at 08:23 AM

    Hi Tobias,

    Is not exactly like this, we've created in columns a structure with 4 selections, the total, the progress, the pending and finally we'd like that appears all months to be able to plan.

    the problem is that when create a selection in the structure it accumulates the value and doesn't show each time period.

    if you see now my first question maybe you can see better what I mean.

    Thanks a lot!

    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.