[Solved] Display minimum & maximum dates listed in a report

[FrenchyDude]

Hi guys,
I've looked around but I can't find anything (usually it means that it's very simple so nobody talks about it... )

I have a report (Sun Report Builder) that is populated from a query.
My report is made of lines each containing a transaction date.

As a quick way for the user to check that he has the right date-span in the report,
In my report's header, I want to display what is the smallest date listed in the report, and in a separate box, what is the biggest date listed.

I have tried playing with some functions and formulas but it looks like the only way would be with some code like (this is where I miserably fail):

-Read first date, and store.
-is there another date?
-if yes, test to see if the new date is bigger than date stored.
-if bigger, store the bigger date. else, go to check if there is another line.
until no more line...

The ideal would be a function of a formula, you tell me
Thanks in advance!
Frenchy

[MikeytheMagnificent]

Simply set up a query with a filter
date >oldest date AND date < newest date
sort it by date ASC (if you want to)
then send your result to your report
You'll be done in 30 about secs
I hope I'm not missing your meaning

[FrenchyDude]

Hi Mike,
Thanks for your reply,

The query is already set with
BETWEEN #01/01/2008# AND #30/12/2009#
for the date field.

But the data in the report has to be grouped by "transaction type" before it gets sorted by ASC dates.
That makes it unfriendly to see and check what are the start date and the last date of the data listed as dates get shuffled.

For easy paperwork filling, the header has to indicate the date-span covered by the report.

It would be great to set the query on dates and sort ASC, but in this case, I can't.

[MikeytheMagnificent]

Sorry for not listening to what you were saying
Is this any better
get your accounting period as before
use MIN and MAX aggregate functions on your date column to give the values, sort by whatever you need to, trouble is it loses date format and dumps you with 2 numbers around 35000 about 100 years from whenever data was invented!!. Dont know how to reformat to proper date, and doing it by calculation is just too much fun for a cold thursday evening. I'm sure one of the regulars will help you out

[eremmel]

The issue is that you can collect the earliest and latest date in your report with use of functions, but than that information is available only at the very end of the report (SRB has at the moment a one-pass approach, so it can not go back).

The other way to accomplish this is to add to the query for each row the max and min date of the total report date range. As noted by MikeytheMagnificent the presentation might become a number, but that can be corrected by the right format of the form-label (any way a date is just a number like in Calc).

The schematic approach is shown in the query:

Code: Select all

SELECT "field1", "field2", ....
   , (SELECT MIN("date") FROM "table1" WHERE "date" BETWEEN #01/01/2008# AND #30/12/2009#) AS "EarliestDateInReport"
   , (SELECT MAX("date") FROM "table1" WHERE "date" BETWEEN #01/01/2008# AND #30/12/2009#) AS "LatestDateInReport"
FROM "table1" ....
WHERE "date" BETWEEN #01/01/2008# AND #30/12/2009# ....
....

I'm not sure if you can run a separate query to fill header-fields.

[FrenchyDude]

Hi Mike,
Here's what I've done so far.

I've create two functions in the report section: BiggestDate and SmallestDate

The "funny" thing is, both functions report the same number so its either that the MIN and MAX functions don't like working with dates or there is something I didn't set properly.

FYI
BiggestDate =MAX([DateFieldName])
SmallestDate = MIN([DateFieldName])

I have tried experimenting with the Initial Value (set as [DateFieldName]), Deep Traversing and Pre Evaluation with not great result.
And I'm still always getting the same identical value from the MIN and the MAX functions: around 12 Aug 2009 (40037)

I've changed the DateField column to display as numbers instead of Dates, the values are all around 40000.
It's almost like the MIN and MAX functions are both returning the average of the Dates...

I'm going to try the functions on the Price column and see if the work properly this time.

Still searching

[FrenchyDude]

Hi Eremmel,

SRB has at the moment a one-pass approach, so it can not go back

Thanks for that information, I'm not sure I grasp the full extend of what you are saying here.
Are you saying the functions in the Report section will collect the information, but if I want the real final information displayed, I'll have to display it, say, in the footer, because it come after the Functions ran through the all data report?

Maybe I could group so that all the data gets in one group, and get my functions in that group. But that would not fix the issue of getting the MAX and MIN results in the header. I think.

If you guys have another report builder to suggest, let me know.

For your idea #2,
I'm looking into it. So far, I've only created Queries using OOBase's GUI... Newbs' don't you just love them?!
I'm going to see where/how to access the query code, what it looks like as it is now, etc.

The other thing I'm thinking of is how to set up the date range after changing the query's code;
So far, every time I need a report, I go and edit the query with the new date span and a couple of other parameters (in the GUI, save and exit...). Then I run my report.
Later, I will be looking at how to get a pop-up to populate the query from drop-downs and so on.
For now, if I alter the Query's code, I need to make sure I won't have to edit the code every time I will need to set up new parameters for my query.

Sorry for the number of lines, trying to keep it short.

[Villeroy]

I'm looking into it. So far, I've only created Queries using OOBase's GUI... Newbs' don't you just love them?!

Forget the query designer. It is by far underdeveloped and faulty. What this tool is able to do can be learned within a few hours for all relational databases on the market.
http://www.1keydata.com/sql/sql.html
SELECT, FROM (JOIN), WHERE, GROUP BY and ORDER BY. That's all it can do.
Quite often it creates wrong queries from simple and valid SQL: http://www.openoffice.org/issues/show_bug.cgi?id=107104
I use to use the designer for extremely simple queries or clicking together a first draft when I don't know all the field names by heart. Then I continus in the SQL view or in a professional text editor.

The ideal would be a function of a formula, you tell me

Well, you asked for a formula and got SQL. SQL is the formula langague to describe operations on row sets in a human readable way.
Copy&paste the SQL and adjust the naming to your needs. Imagine how you want to describe such an operation by means of GUI steps. That would be horrible.

[eremmel]

BiggestDate =MAX([DateFieldName])
SmallestDate = MIN([DateFieldName])

The function MAX()/MIN() you are using is applied to the single current row and not across all rows. You have the function name as a variable to preserve results from one row to the next row. It is common mistake .
This is what you should do for a function:
variable: BiggestDate
initial value : [DateFieldName]
BiggestDate : MAX([BiggestDate];[DateFieldName])

[FrenchyDude]

Forget the query designer. It is by far underdeveloped and faulty

Point taken! I'm going to take the SQL tutorial (thanks for the link) and slowly cut myself away from the Query Designer...
I also need to find an SQL editor, ... I can't the SQL syntaxes to properly display in the OOBase's SQL mode. It's all in one line and I can't find any options for the text to auto sort-itself (and teach me the good habits/standards) (other than editing by hand).

SQL is the formula language to describe operations on row sets in a human readable way. Copy&paste the SQL and adjust the naming to your needs.

By "Formula" I meant "Formulas in the report". But I think you guys are talking about working-out the Min and Max on the fly at the Query time, before the "Report-time".
I thought the "normal" way would be to first query the data from the DB and THEN work on the data in the report.
You guys would be querying the data from the DB, and at the same time figuring-out the Min and Max, then store the Min and Max values. That way, even if SDB create the report in a "scan fashion", the Min and Max values are already ready and can be displayed at any time, even at the start of the report building process.

The function MAX()/MIN() you are using is applied to the single current row and not across all rows. You have the function name as a variable to preserve results from one row to the next row. It is common mistake .

Thanks for that, I'm going to fiddle with that as well, and see if I can get any result at all, or maybe as a quick fix, until I can do better.
Until then, expect a few more similar mistakes from me

I'm going to try these two leads, and will keep you posted.
I won't say it enough: Thanks again for your help!

[FrenchyDude]

okay, here's an update on the progress using a function in the SRB,

Using the sentence the right way, as per Eremmel's correction does help BUT still doesn't give the right minimum and maximum dates.
The min and max dates are: 15/01/08 and 15/12/09 and the reported dates are 13/01/08 and 13/12/09.
So the days are wrong but the rest is okay.

A small recap':
Name : BiggestDate
Formula : MAX([BiggestDate];[DateFieldName])
Initial Value : [DateFieldName]
Deep Traversing : No
Pre Evaluation : Yes

Result : reporting 13/01/08 instead of 15/01/08

[FrenchyDude]

That's it! working!

I was playing around in the SRB, experimenting different settings for the MAX and MIN Functions when I found this:

In the Sun Report Builder,
-Select the Formatted Field in which you want to display the Maximum date.
-In the properties, go to the Data tab
-Data Field Type : Function
-Data Field : select your DateFieldName
-Function : Maximum
-Scope : Report (Look at the data in the all-report)
-Configure your field format to show the values as a date.

This now looks so simple I can't believe I didn't pick it up earlier, but I was trying in the Function's properties, and the solution was in the Formatted Field properties.
So there you go, this function is built-in SRB.

Here's the function SRB automatically creates:
=IF([DateFieldName] > [MaximumDate OpérationReport];[DateFieldName];[MaximumDate OpérationReport])

[eremmel]

This IF statement is the same as a MAX() function with two arguments.
I tested it and got the same error: The date is two days of.
So this looks like a bug in MAX() / MIN() implementation.

Edit: Reported a issue In SRB MAX() MIN() functions two days off BTW: From the attached document in the issue one can learn that I was wrong with the 'one pass' remark, because the MAX/MIN values are shown correctly on each row in the report.

[FrenchyDude]

This IF statement is the same as a MAX() function with two arguments.
I tested it and got the same error: The date is two days of.
So this looks like a bug in MAX() / MIN() implementation.

One thing is sure, it's got to be date-related.
I tried with numbers and it worked fine.

[ahsanmisbah]

You Can also do this simple act.

WHERE tb.date BETWEEN @Startdate AND @Enddate