[Solved] Repeating Parameters in Report

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Repeating Parameters in Report

Postby One Big Yawn » Thu Aug 02, 2012 11:00 am

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   Expand viewCollapse view
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? :idea:
Last edited by Hagar Delest on Thu Aug 02, 2012 9:48 pm, edited 1 time in total.
Reason: tagged [Solved].
LibreOffice 3.5, MySQL 5.5, Windows 7
One Big Yawn
 
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: Repeating Parameters in Report

Postby Villeroy » Thu Aug 02, 2012 11:36 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26391
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[SOLVED]Re: Repeating Parameters in Report

Postby One Big Yawn » Thu Aug 02, 2012 9:40 pm

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

:fist:
LibreOffice 3.5, MySQL 5.5, Windows 7
One Big Yawn
 
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am

Re: [Solved] Repeating Parameters in Report

Postby Villeroy » Thu Aug 02, 2012 10:23 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26391
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Repeating Parameters in Report

Postby Sliderule » Thu Aug 02, 2012 10:51 pm

One Big Yawn wrote:
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
User avatar
Sliderule
Volunteer
 
Posts: 1162
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Repeating Parameters in Report

Postby One Big Yawn » Fri Aug 03, 2012 1:27 am

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.
LibreOffice 3.5, MySQL 5.5, Windows 7
One Big Yawn
 
Posts: 24
Joined: Sat Jul 14, 2012 6:09 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest