Home » Developer & Programmer » Reports & Discoverer » Date Calculation in Discoverer Report
Date Calculation in Discoverer Report Wed, 28 November 2007 14:03
 tmdbb Messages: 6Registered: 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 #284190 is a reply to message #284066] Thu, 29 November 2007 02:34
 Littlefoot Messages: 21122Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator

What about days in July? Why do you not want to calculate number of days in July?

Re: Date Calculation in Discoverer Report [message #284235 is a reply to message #284190] Thu, 29 November 2007 03:48
 Littlefoot Messages: 21122Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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: 6Registered: 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 #284463 is a reply to message #284462] Thu, 29 November 2007 14:52
 Littlefoot Messages: 21122Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
No problem, you have put the question to the right place. However, as I don't know anything about Discoverer and you may not write queries, I believe you'll have to wait until someone who knows Discoverer (Sabine, for example) sees your question and, hopefully, answer it.

In the meantime, read the OraFAQ Forum Guide. A huge image (almost 2 MB) is simply not acceptable and has been deleted. Attach another image in format that is welcome here (which one? You'll find it in the Guide).
Re: Date Calculation in Discoverer Report [message #284654 is a reply to message #284463] Fri, 30 November 2007 07:29
 skooman Messages: 913Registered: 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: 6Registered: 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 #285225 is a reply to message #285164] Tue, 04 December 2007 00:46
 Littlefoot Messages: 21122Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
 Quote: I apologize for the underlining; I can't figure out how to tab so it looks like columns
If you had read the link I provided in the second message in this topic, you would have been able to figure that out. So, as far as I'm concerned, apology not accepted.
Re: Date Calculation in Discoverer Report [message #285425 is a reply to message #285225] Tue, 04 December 2007 09:01
 tmdbb Messages: 6Registered: 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: 913Registered: 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: 6Registered: 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: 913Registered: 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: 6Registered: 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: 913Registered: 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
 Previous Topic: Date issue Next Topic: new record
Goto Forum:

Current Time: Sun Jun 25 09:05:21 CDT 2017

Total time taken to generate the page: 0.04039 seconds