How to have report builder call macro? Or Date Next Friday?

[bs27975]

How do I call a macro within the report builder?

e.g. =MyMacro() doesn't work, and I don't see any macro interface (insert macro?), let alone events to attach macros to.

Have various macro flavours around

Code: Select all

function DateNextFriday as date
	DateNextFriday = dateadd( "d", 6 - weekday(now), now )
end function

but don't see how to call it.


Further, where are the events in report builder?

Alternately, why does a formula of:

TODAY()+(6-WEEKDAY(TODAY()))*60*60*24

formatted as MM/DD/YY, show:

22/01/84 (January 22, 2284!)

Thanks.

[Villeroy]

Because your calculation assumes that spreadsheet times are measured in seconds (60*60*24). Actually, spreadsheet times are measured in days. Just remove the multiplication.

[r4zoli]

No macros in report builder.

You can use or the report Function wizard, if you click next to Data field on report field Data tab.

You or you can create Functions in it, see attached creenshot.

The Base short only available documentation could help you.

You could use only supported functions.


Edit: I missed the upload, now added screenshot.

[bs27975]

Sadly, not so. Not that dates aren't day based, thank you for that, if true. But that merely removing the multiplication doesn't accomplish the desired result.

e.g. today()+2 still shows todays date.

Even better, despite a format of MM/DD/YY, the above gives 15/12/10. <sigh>

TODAY()+48 returns 30/01/11, which may be right if, as you say, dates are by day.

TODAY()+10 returns 23/12/10. Something goofy going on here.

Thoughts?

[bs27975]

No macros in report builder.

You can use or the report Function wizard, if you click next to Data field on report field Data tab.

The Base short only available documentation could help you.

You could use only supported functions.

Yes, thanks, have seen those. I asked as insufficient, or so I thought. (Was looking for date arithmetic, until pointed out such are in days, not seconds, in report builder. Sure, why not be inconsistent!) <sigh>

You or you can create Functions in it, see attached screenshot.

I'm not seeing an attachment - am I just missing it?

If the shot is just of a blank Function Wizard dialogue box, thanks - have been using that. [Especially since discovered formulas are case sensitive.] <sigh>

[r4zoli]

The two days problem is known issue in report builder: http://qa.openoffice.org/issues/show_bug.cgi?id=108590

[Villeroy]

The formatting is not relevant. This can be changed at the end of the data pipeline (this is a matter of number format locale UK or US).

TODAY()+2 in a spreadsheet returns 17/Dec/2010.
The correct result for TODAY()+48 in a spreadsheet is 2/Feb/2011
TODAY()+10 is 25th Dec
I can't tell anything about the report builder because I'm fed up with it.

Base is broken in several places as far as dates are concerned. In this case they obvoiusly borrowed the formula wizard from Calc without adjusting the calculation options. For the sake of Excel compatibility, Calc uses 1899-12-30 as day zero. SQL databases assume 1900-01-01 when they convert dates to numbers. This may explain 2 days offset.

Since you are using spreadsheets as data sources anyway, you could do the date calculation in Calc (which does the job perfectly for all dates since Gregorian calendar reform 1582-10-15) and do all the reporting with groups and aggregations in Calc's data pilots. Calc is well tested and mature (given that you are able to keep your data consistent in spreadsheets, which can be difficult).

[bs27975]

The two days problem is known issue in report builder: http://qa.openoffice.org/issues/show_bug.cgi?id=108590

Sorry, this is now all feeling like report builder is a bad practical joke.

Yet you've said in other threads that the report wizard, an interesting attempt to be consistent with the rest of oo, has ceased development. I'm guessing the design goals were determined to be unsustainable and unreachable.

Leaving the report builder ...

Which can't do borders as far as I can see ...

[bs27975]

The formatting is not relevant. This can be changed at the end of the data pipeline (this is a matter of number format locale UK or US).

ABSOLUTELY WRONG. The whole point of a report is to hit open, print, walk away. Not keep *&%*^%*&^% with it after the fact. Why else have a report builder ... ah never mind.

TODAY()+2 in a spreadsheet returns 17/Dec/2010.
The correct result for TODAY()+48 in a spreadsheet is 2/Feb/2011
TODAY()+10 is 25th Dec
I can't tell anything about the report builder because I'm fed up with it.

Base is broken in several places as far as dates are concerned. In this case they obvoiusly borrowed the formula wizard from Calc without adjusting the calculation options. For the sake of Excel compatibility, Calc uses 1899-12-30 as day zero. SQL databases assume 1900-01-01 when they convert dates to numbers. This may explain 2 days offset.

Thanks for this.

Since you are using spreadsheets as data sources anyway, you could do the date calculation in Calc (which does the job perfectly for all dates since Gregorian calendar reform 1582-10-15) and do all the reporting with groups and aggregations in Calc's data pilots. Calc is well tested and mature (given that you are able to keep your data consistent in spreadsheets, which can be difficult).

This is in the header. Solved in writer and calc via macros (as can be seen in other threads.) Then I come to report builder ... I'm going in circles here.

You are going to drive me absolutely bonkers.

First a spreadsheet is not a database and you try to brow beat, over, and over, and over, again, to go native hsqldb, now you want me to go back again?

Granted, I get why you say what you say, and don't disagree. As I have said, the users aren't ready for it, but I'm trying to move them towards it. Currently, by getting the surrounding functionality in place, e.g. reports.

I take your points, and appreciate the intents behind the suggestions.

But ... this should all be easier. Pardon the expression of frustration.

I do understand what you're saying, and why.

[Villeroy]

Excuse me. I thought you were still using a spreadsheet.

Adding up to 68 days in HSQLDB 1.8 (the one that's integrated in OOo):
[Solved] Add days to a date

[bs27975]

No, you're right - not in spreadsheet yet. Working on it, for all sorts of good reasons.
And continually running into surrounding barriers, like the report builder, that make it take longer, and longer, and longer, to get there. I appreciate your thought, and that you are tracking all the different threads out there, and who's in what situation and ...

[Villeroy]

I added a query "Next Friday" to one of my example databases, created a simple report, adjusted the number format of the target cells and upload here.
Open the document, run the report.

[bs27975]

Guess I did it (solved the problem), based on your information, but neglected to tell you.

Thank you for the note that dates in base are days, not seconds. Based on the information in this thread my function works, as you suggested (no multiplication) by adding an additional 2 days.

FYI - Your 'Next Friday' query

Code: Select all

SELECT "Date", DAYNAME( "Date" ) AS "Day Name", CAST ( YEAR( "Date" ) || '-' || MONTH( "Date" ) || '-' || DAY( "Date" ) + ( 6 - DAYOFWEEK( "Date" ) ) AS DATE ) AS "Next Friday" FROM "List" AS "List"

pops up:

OpenOffice.org Base
Syntax error in SQL statement
syntax error, unexpected DATE

Running it gives an even more interesting pop up.

Definitely something for me to study, though. An interesting example database.

[r4zoli]

From Java 1.6_18 the date handling changed in Moth part, it is needs to be two character long, change month part to:

Code: Select all

right('0'||month(date),2)

[Villeroy]

The full query with the modification suggested by r4zoli is:

Code: Select all

SELECT "Date", DAYNAME( "Date" ) AS "Day Name", CONVERT ( YEAR( "Date" ) || '-' || RIGHT( '0' || MONTH( "Date" ), 2 ) || '-' || RIGHT( '0' || DAY( "Date" ) + ( 6 - DAYOFWEEK( "Date" ) ), 2 ) , DATE ) AS "Next Friday" FROM "List" AS "List"