| Discoverer 4.1 - want to know if even possible to do something [message #141055] |
Thu, 06 October 2005 14:43  |
Old Dog, needs new tricks
Messages: 13
Registered: September 2005
Location: Iowa
|
Junior Member |
|
|
Here is the scenerio:
I have 2 tables.
The first table I'll call 'Billing Dates'. In that table there is a date column where each date in this column starts on the first of a month. We will call this 'Premium date'. The table also has a corresponding date that goes with this date that can vary, it is never on the same day of the month. we will call this 'statement date'.
The second table I'll call 'Statements' table which holds the data I want to display.
For the Discoverer report I want to be able to enter a beginning parameter date and an ending parameter date to establish a date range. I need to check the 'Billing Dates' table and look at the 'Premium date' to get the 'Statement date' I need to be used in the processing of the 'Statements' table to produce the report.
The discoverer report should be able to process the 'Statements' table and check a specific date field in that table to see if it is in the range of 'statement dates' we found in the first table and process the data in the 'Statements' table.
My problem is, the user would like to only key in the 'Premium dates', not the 'Statement dates' in the parameter dates because they are easier to use.
As a temporary measure, I've produced 2 sheets. One that lists the 'Premium month' and their corresponding 'Statement dates' so the user could find the 'Statement dates' fairly easily so on the 2nd sheet, they could key in the parameter dates they just looked up and then the Discoverer report is produced.
Is there a 'one-step' way of doing this without using 2 sheets? Maybe a SQL custom folder or something? I wasn't sure how to indicate parameter dates would be used.
Thanks,
Old Dog
|
|
|
|
|
|
| Re: Discoverer 4.1 - want to know if even possible to do something [message #141869 is a reply to message #141865] |
Wed, 12 October 2005 10:20  |
Old Dog, needs new tricks
Messages: 13
Registered: September 2005
Location: Iowa
|
Junior Member |
|
|
Close.
The Billing Dates table has only 1 entry per month. Here is an example:
Premium dt statement dt
1/1/2006 12/30/2005
11/1/2005 10/31/2005
10/1/2005 9/30/2005
9/1/2005 8/31/2005
8/1/2005 7/29/2005
7/1/2005 6/30/2005
6/1/2005 5/31/2005
5/1/2005 5/1/2005
4/1/2005 3/31/2005
3/1/2005 2/28/2005
2/1/2005 1/31/2005
1/1/2005 1/2/2005
12/1/2005 11/30/2005
the Statements table has records that has the same statement dt as the Billing Dates table. those are the records I want to display data from.
I need to be able to have date parameters (against BIlling Dates) to get a date span to check. It may be only one date or it may be for 2, 3 or more months.
will your solution still work?
Thanks, Sabine!!! I don't know what I'd do without you!!
Old Dog
|
|
|
|
| Re: Discoverer 4.1 - want to know if even possible to do something [message #142131 is a reply to message #141869] |
Thu, 13 October 2005 09:34 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Dog,
Yes, what I described will work. And you might want to make the condition with a "between" parameter#1 and parameter#2 (both referencing the billing date column). That way you can have a time span.
If users want to be able to have either one date or a time span, usually you would let them fill in the "from date" (paramter#1) and leave "to date" (parameter#2) empty. In Discoverer, you achieve this like:
- create the parameters 1 and 2 manually (tools, parameters), link them to the billing date
- create a condition: statement date between parameter 1 and <create calculation>, then fill in: NVL(parameter#2,statement date).
-> this means: statement date should be less than parameter2 but if parameter2 is empty than it should be less than statement date (it self, that is), which is obviously alway true and exactly what you want...
Hope this description makes any sense to you.
Regards,
Sabine
|
|
|
|
|
|
| Re: Discoverer 4.1 - want to know if even possible to do something [message #144432 is a reply to message #143732] |
Wed, 26 October 2005 07:47 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Old Dog,
That is strange... Are you sure the condition is on the ("not selected") billing date? And not on the statement date itself?
Because what we want is, if the from/to dates for billing date are 1-1-2005 and 1-2-2005, then:
billing date statement date
1-1-2005 30-12-2004 -- this row should be shown
1-2-2005 25-2-2005 -- this row should be shown
1-4-2005 1-4-2005 -- this row not
So, if you create a condition on billing date and then remove billing date from the sheet, the result should be:
parameter_from: 1-1-2005, parameter_to: 1-2-2005
statement date
30-12-2004
25-2-2005
This should work, so maybe you can check on which item the condition is?
Regards,
Sabine
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)