[Solved] Repeating Parameters in Report

[One Big Yawn]

Sorry if it seems like I'm flooding, but searches don't yield anything similar.

I have a query using the same parameter for two columns and another for two contraints:

Code: Select all

SELECT `Benefit`, `NAMES`, `ID`, `Born`, `Sex`, `Bank`, `Suff`, `Place`, 
IF( `DirDate` > `EffDate`, DATEDIFF( :Enter_Report_Date, `DirDate` ) * `Rate`, 
DATEDIFF( :Enter_Report_Date, `EffDate` ) * `Rate` ) AS `Amount`, 
CONCAT_WS( ' - ', IF( `DirDate` > `EffDate`, `DirDate`, `EffDate` ), :Enter_Report_Date ) AS `SWNote` 
FROM `test`.`addvouchparam` AS `AddVouchParam` 
WHERE `DirDate` >= :Enter_Begin_Date AND `EffDate` >= :Enter_Begin_Date 
ORDER BY `Benefit` ASC, `Place` ASC, `Amount` ASC

When I run this query, it works fine... A window pops up asking for Enter_Report_Date and Enter_Begin_Date and the appropriate table is generated.

However, when I change this query into a report, this window pops up again after I just entered the dates in the first window. So, I'm assuming it's asking for the dates for every instance of each parameter in the report. I can't think of a better way formulate this... any ideas?

[Villeroy]

FilterData.odb: Form filtered list with filtered reports saves criteria in a distinct record so you can reuse the same criteria for other forms as well as for reports.

[One Big Yawn]

Wow... this looks incredibly convoluted, though very useful.

So no simple fix for this one....
Oddly, I only get this issue when generating the report as a Calc spreadsheet, so there may be some sort of bug in the conversion.

Thanks again Villeroy

[Villeroy]

Register my database under some name (Tools>Options>Base>Databases...)
Drag my filter form from the form document in edit mode into a spreadsheet and reconnect the database connection of the newly created form.
Drag the "qReport" query from the data source window into a spreadsheet.
Now you have the criteria input and the report in one spreadsheet.
Enter and save criteria, then refresh the import range.

[Sliderule]

LibreOffice 3.5, MySQL 5.5, Windows 7

However, when I change this query into a report, this window pops up again after I just entered the dates in the first window.

Just to be clear, I use Windows XP with OpenOffice 3.3.0 against various database back-ends, including HSQL Embedded ( version 1.8.0.10 ), HSQL 2.2.8 on a server, HSQL 2.2.8 'in-line', MySQL connected to ODBC driver, and, SQLite on ODBC driver.

And, I can report ( database pun intended ) that Oracle Report Builder ( ORB ) works successfully, against all of the above. If, a Query includes the same parameter / prompt name, it only appears for each unique parameter / prompt once, exactly as it should.

Sliderule

[One Big Yawn]

Perhaps it's the Win 7, JDBC, or something else, but I only get this behavior if I have the Report Output Format as a spreadsheet instead of a text document.