| Date Calculation in Discoverer Report [message #284066] |
Wed, 28 November 2007 14:03  |
tmdbb
Messages: 6
Registered: November 2007
|
Junior Member |
|
|
I would like to create columns in my report that have the number of days in a specific month, input somehow as a variable, between to dates that are represented in columns on the report. Example:
Columns on Report Calculation Calculation
From Date To Date Days in May Days in June
05-21-07 07-19-07 11 30
|
|
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #284235 is a reply to message #284190] |
Thu, 29 November 2007 03:48  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here is a query which will return information you need (actually, I *think* this is what you need); start_date = 21.05.2007, end_date = 19.07.2007):SELECT TRUNC(x.datum, 'mm'), COUNT(*) number_of_days
FROM (SELECT (TO_DATE('&start_date', 'dd.mm.yyyy') - 1 + LEVEL) datum
FROM dual
CONNECT BY LEVEL <= TO_DATE('&end_Date', 'dd.mm.yyyy') + 1
- TO_DATE('&start_date', 'dd.mm.yyyy')
) x
GROUP BY TRUNC(x.datum, 'mm')
ORDER BY 1;
TRUNC(X. NUMBER_OF_DAYS
-------- --------------
01.05.07 11
01.06.07 30
01.07.07 19
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #284462 is a reply to message #284235] |
Thu, 29 November 2007 14:40 |
tmdbb
Messages: 6
Registered: November 2007
|
Junior Member |
|
|
Thank you. This is my first time to use the forum and I believe I may be in the wrong place. I am an end user of Discoverer, not a programmer (as you noticed). In creating queries, there is an area called "Calculations" where selections from functions can be entered pulling data items from the query and manipulating them to columns. I have attached a screen shot of the area with in the program. I do not have access to actually write the code. If I need to ask this question somewhere else, please let me know.
[EDITED by LF: removed a huge BMP file]
[Updated on: Thu, 29 November 2007 14:53] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #284654 is a reply to message #284463] |
Fri, 30 November 2007 07:29 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
To be honest, I think this is a pretty weird requirement. But okay, if you want it, I'll think with you to find a way.
Those two dates (begin- and enddate), are they in the database or do you enter them as parameters? And you only show one row, whereas Discoverer is usually used for showing more then one row, is this the complete result or are there more rows? If so, where does the data come from?
And could it be that these months (like may, june) are hardcoded? What do you want to show when the begin- and end date are, let's say, 1-JAN-2007 and 15-SEP-2007?
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #285164 is a reply to message #284654] |
Mon, 03 December 2007 16:18 |
tmdbb
Messages: 6
Registered: November 2007
|
Junior Member |
|
|
The two dates come from the database as a beginning and ending date from the condition the Invoice start date and end date during a specific month period. Each invoice runs 60 days and it can start at anytime during the month. Example, the condition would be for all invoices that started in July. The result would be one that started 01-Jul-07 and runs 60 days until 29-Aug-2007; the next one would start 05-Jul-2007 and run 60 days until 02-Sep-2007, etc. For the report I am doing I want to know how many days would fall in each of those months (July, August and September) so in the calculation section of Discoverer, I thought I could write 3 separate calculations that would give me 3 separate columns but I don't know how to write the calculation. Example of how I would like it to look:
Invoice________Invoice
Begin Date_____End Date_________July Days_____August Days_____Sep Days
01-Jul-2007____29-Aug-2007_______31__________29_____________0
05-Jul-2007____02-Sep-2007_______27__________31_____________2
Then I could add more calculations to break the invoice down by amounts that would fall in July, August, and September. I hope this is more clear. (I apologize for the underlining; I can't figure out how to tab so it looks like columns). Thank you so much for your help.
|
|
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #285425 is a reply to message #285225] |
Tue, 04 December 2007 09:01 |
tmdbb
Messages: 6
Registered: November 2007
|
Junior Member |
|
|
Okay - point taken. I did read it but did not realize that what I was trying to do was considered "code". I have reformatted how I would like to have the columns on the report look (however, tab still does not work in that mode but it did retain the spacing -- learning something new every minute here).
Invoice Invoice
Begin Date End Date July Days August Days Sep Days
01-Jul-2007 29-Aug-2007 31 29 0
05-Jul-2007 02-Sep-2007 27 31 2
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #285432 is a reply to message #285425] |
Tue, 04 December 2007 09:35 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Okay, and what if you have let's say 200 records, the first dated 1-1-2006 and the last dated 01-12-2007? Would you show 24 columns then?
Before we go any further, this can not be done solely in Discoverer, I would create a view showing the data something like:
incoice_id invoice_date invoice_due_date month days_remaining
1 01-jul-2007 29-aug-2007 jul 31
1 01-jul-2007 29-aug-2007 aug 29
2 05-jul-2007 02-sep-2007 jul 27
2 05-jul-2007 02-sep-2007 aug 31
2 05-jul-2007 02-sep-2007 jul 02
Then in Discoverer you should be able to have the required output using a crosstab layout (not entirely sure though, would have to try if it works).
Would you be able to create the view I described?
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #285796 is a reply to message #285432] |
Wed, 05 December 2007 11:59 |
tmdbb
Messages: 6
Registered: November 2007
|
Junior Member |
|
|
I only have access to the database through Discoverer so if it can not be done there, then I would not be able to create.
Typically, I would just run the report for one month which would create 3 columns (since the data spans 60 days). But, yes, if it were to pull data for a 12 month period, there would be more columns.
I think I could make the report as you suggest but I have never used the cross tab in Discoverer. The problem appears to be how to truncate the dates to pull just a specific month, right? Discoverer has the drop down functions in the "calculations" tab but I do not know which one to use, if there is one.
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #286107 is a reply to message #285796] |
Thu, 06 December 2007 08:26 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
To trunc the date can be done in a calculation:
trunc(date_column, 'MON-YYYY')
However, the example data I showed will not be possible to generate in Discoverer, since it requires pretty neat sql... Bottomline is that the columns vary (depending on the dates you select), and that this is something Discoverer can do nicely in a crosstab, but that you have to generate the date per month instead of per invoice_id, and that is something Discoverer can not do.
At least, not that I can think of. Sorry I can't help!
Isn't there someone within your company who can generate the data for you in a way that you can put it into a crosstab?
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #286127 is a reply to message #286107] |
Thu, 06 December 2007 09:27 |
tmdbb
Messages: 6
Registered: November 2007
|
Junior Member |
|
|
|
Thank you so much for trying. I import the data into a spreadsheet in Excel and then put formulas in to take the date from the beinning date in the month minus the start date, then ending date against the end date of the report, etc. However, doing it this way, I have to redo the report every month, copying all of the formulas, etc. It would be much faster, and I'm sure more accurate due to the human error factor, to have the report run automatically in Discoverer. No, we do not have anyone in our organization that even knows as much about Discoverer as I do--the programmer is under contract, so that is not an option at this time. Again, thank you for trying.
|
|
|
|
| Re: Date Calculation in Discoverer Report [message #286131 is a reply to message #286127] |
Thu, 06 December 2007 09:55 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Oh well, if you give us an example of the excel sheet, I'll try to find some time to see if we can do something in discoverer after all. Might not be technically advanced or something, but who knows 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
