Returning a record from between dates based on a date prompt

[robo100]

I have the following data in 2 tables

Table 1
Store Start Date End Date Value
1 1/1/00 3/31/04 100
1 4/1/04 12/31/04 200

Table 2
Month Name
2/1/04 Feb 2004
4/1/04 April 2004

Based on a prompt from table 2 I want to report the record in table 1 where the dates fall between the Month selected from table 2.

I think I should be using an advanced filter but I am not sure.

Can anyone help.

Many thanks

 

[nlim]

here's one way.

1) Create a metric M1 that is defined as max(month), filtered by an element prompt, and dimensional set to none.
2) on your report put filter as StartDate attributeID >= M1, and EndDate attributeID <= M1.

 

[robo100]

Thanks for that. I think I am close but when I run the following sql it complains about the end date not being a group by.

select a12.BRCH BRCH
from PROMPT_MONTH_TEST a11,
F_LL_BUDG_BAL a12
where a11.MONTH in (To_Date('2004-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
group by a12.BRCH
having (a12.START_DATE >= max(a11.MONTH)
and a12.END_DATE <= max(a11.MONTH))

I have created attributes for the start and end dates and linked them to the f_ll_budg_bal table only and a month attribute linked to the prompt_month_test table only.

Is this correct or should there be a link there somewhere.

Thanks

 

[nlim]

Make sure your dimension for the metric is set to none, and not reportLevel.

In your final SQL, the prompt month test table should not be linked at all to the budget fact table...in fact they should not even show up in the same pass...

 

[robo100]

I've taken the level dimensionality out of the M1 metric and when I run the report it prompts for a month as it should but the sql comes back as follows.

select a11.BRCH BRCH,
sum(a11.VALUE) WJXBFS1
from F_LL_BUDG_BAL a11
group by a11.BRCH

Its now totally ignoring the month I selected and summing both records.

 

[nlim]

try putting month as parent of either startdate or enddate.

 

[robo100]

I've added month to the start and end date attributes and made it a parent of them both then tried it on just one of them but it still keeps coming back with the same sql as before.

I think I may take another look at my data and see if there is any other way of doing it.