How to show variable (from query) at the report header?

[vitorsouza]

I have a query with a WHERE condition similar to: WHERE date >= :startDate AND date <= :endDate

Then I created a report on this query. LibreOffice Base correctly asks me for the values of :startDate and :endDate when I open the report and filter the table rows accordingly.

My question is: how do I show "From :startDate until :endDate" at the header of the report? I tried inserting fields of different kinds but I can't seem to make Base replace :startDate and :endDate with the values entered by the user when he/she opens the report.

Thanks in advance,
- Vítor

[Sliderule]

Vitor:

Welcome to the OpenOffice Base forum.

You said / asked:

My question is: how do I show "From :startDate until :endDate" at the header of the report? I tried inserting fields of different kinds but I can't seem to make Base replace :startDate and :endDate with the values entered by the user when he/she opens the report.

Answer: In your Query, add two additional columns in the result set . . . that is . . . the rows that are returned. The two additional columns are the value of :startDate and :endDate . . . with an Alias ( new column name ) you assign.

This way, you will have that value available for your report, from the content of the data.

You can download a SAMPLE report from the link below . . . demonstrating how this is accomplished.

http://www.mediafire.com/download/p4p1v ... DAREA3.odb

In the above . . . please look at two reports, showing how a list of Countries, grouped by Continent, is displayed, and, the Continent name is displayed at towards the top. And, the value is taken from the Query. Please see:

1. WorldAreaReport - By Continent - Area
2. WorldAreaReport - By Continent - Country Name

I hope this helps, please be sure to let me /us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[vitorsouza]

Sliderule:

Thanks for the welcome and the reply. I succeeded in including the variables as columns in the query. Strangely enough, it stopped accepting dates when I entered them in the format dd/mm/yyyy, but I switched to yyyy-mm-dd and it worked, so it's all good.

However, I couldn't add the field to the header of the report. When I add it as a user field (the query columns are listed there) their value does not get updated when I open the report and input the value of the variables.

Then I opened the example report in NeoOffice on my Mac and I saw the example, but it opened in a different report editor than the one I have in the Ubuntu virtual machine. So I opened the database in the Ubuntu VM with LibreOffice and when I tried to open the report to check it out it said it was not possible to open it. Maybe something about the version of the LibreOffice running in my Ubuntu VM?

Thanks again,
- Vítor

[Sliderule]

Vitor:

The report(s) in the sample database I provided above . . . are using the Oracle Report Builder extension, and, I assumed, perhaps, incorrectly, that you too, are using . . . Oracle Report Builder for your reports.

The Oracle Report Builder is available for download ( only if you want to use it ) from:

http://extensions.openoffice.org/en/pro ... rt-builder

If, you are not using Oracle Report Builder . . . perhaps some other Forum user can assist you.

Sliderule

[longi]

Hi!
I'm not sure you'll be able to understand me because my 'estrange expressions'.
Well. As everyone says, a report is a writer document, but also , a form is a writer document too.
If you open your report to edit it, and you get the form browser, you can see a Form with a Subform called ReportSource and in it you have some hidden controls.
If you clik in 'Forms' with the right button you can generate a new subform. The only thing you have to do is to assign a data source. In this case you'll use the query in which you have the fields you want to use in the report.
Then, you have to put into this subform two textfield which get data from the data offered by the query.
If you use this trick with other different queries you can put a calculation made by the query.
It could seem a bit strange or difficult, but at the end it is easier than read my words, and it works properly.

Good luck!