[Solved] Alternating Row Colors in Reports

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

[Solved] Alternating Row Colors in Reports

Postby Reversephase » Fri Oct 11, 2013 7:34 pm

Start off i'm using Openoffice 4.0.1 with an Apache Derby Database

Is there no way to alternate a row color in openoffice base Reports ?

I have played with the default report wizard, and the Oracle Report Builder, and after doing some research it looks like I need to use a conditional formatting.

With the oracle report builder i found where i can do conditional formatting.

now if i do Expression Is ISEVEN([ID]) IT works, Any ID number that is even gets the format applied to it, and any Number in the ID colum that is odd, does not get the color formatting i want.

However if I do ISEVEN(ROW()) No formatting gets applied, And I want to format the Rows, with alternating colors to make it easier to read the final report being generated.

Where am i going wrong ?
Last edited by Reversephase on Fri Oct 11, 2013 11:42 pm, edited 1 time in total.
OpenOffice 4.1 on Windows 7 Pro 64bit
Reversephase
 
Posts: 30
Joined: Tue Oct 01, 2013 8:51 pm

Re: Alternating Row Colors in Reports

Postby Sliderule » Fri Oct 11, 2013 7:37 pm

I am sending you a PM ( Private Message ).

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am

Re: Alternating Row Colors in Reports

Postby Reversephase » Fri Oct 11, 2013 10:07 pm

I have found a Solution!

Note: This requires the Oracle Report Generator Extension


Here is the trick, Add a New Column to your report, now i couldn't find a way to add a blank column so i just Added Another "ID" column which i changed the datatype one

Change the Data -> Data Field type to Counter And Scope: Report

This will give you a number beside each row of data you are returning in your report.
I made this Column As narrow as possible, and gave it a Text colour of white, so you don't see the numbers.

Now, Select the 2nd category, click on Menu - > Format -> Conditional Formatting

Under Condition 1, Use Expression is and put in ISEVEN([CounterReport])
Change the Background to what ever background you desire.

Note: You need to do this for each column separately, a bit of a hack-job, but the end result it you get the Alternating Coloured Rows :D

I have also attached an example for others to See, and play with
Attachments
Example.odb
Example
(28.23 KiB) Downloaded 303 times
OpenOffice 4.1 on Windows 7 Pro 64bit
Reversephase
 
Posts: 30
Joined: Tue Oct 01, 2013 8:51 pm

Re: [Solved] Alternating Row Colors in Reports

Postby Feelew » Tue Mar 25, 2014 7:41 pm

I’ve been playing around with Base reports only for a few days, trying both the default Wizard & Sun Report Builder, with so far a marked preference for the former (because I haven’t been able to auto-adjust text box heights with the Report Builder, for entries longer than the text box).

One of next items on my wish list would be to have alternating colors on the reports, preferably using the default Wizard, or else along with a recipe on how to conditionally adjust heights of text boxes in the Report Builder extension.

Thanks for any thoughts anyone could be putting into it, even fruitless, and even if I never become aware they have occured.
AOO 4 • Windows7

Because many might have tried to come up with an answer to my questions before one succeeded at it...
THANK YOU! to everybody who are trying to solve my Open Office problems, even if fruitlessly.
Feelew
 
Posts: 17
Joined: Tue Mar 25, 2014 7:04 pm

Re: [Solved] Alternating Row Colors in Reports

Postby Villeroy » Tue Mar 25, 2014 9:13 pm

Calc can be used as a report engine too, including conditional formatting.
[Tutorial] Using registered datasources in Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27709
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Alternating Row Colors in Reports

Postby Feelew » Tue Mar 25, 2014 9:40 pm

Thanks for your reply Villeroy.

I did consider the option of using Calc and/or Writer to create a report, and did actually pay a visit to the link you offered. It is possible that at some point I decide to go a little further down that road, but so far, I did enjoy the features of Base report wizard more than the equivalent work to be put into Calc or Writer to get something not even quite equivalent. Nevermind what I’m thinking for now anyways. I could be back at your solution before a few days, especially if I keep hitting cul-de-sacs with Base.
AOO 4 • Windows7

Because many might have tried to come up with an answer to my questions before one succeeded at it...
THANK YOU! to everybody who are trying to solve my Open Office problems, even if fruitlessly.
Feelew
 
Posts: 17
Joined: Tue Mar 25, 2014 7:04 pm

Re: [Solved] Alternating Row Colors in Reports

Postby F3K Total » Tue Mar 25, 2014 11:20 pm

Hi,
maybe this simple macro can help
Code: Select all   Expand viewCollapse view
Sub Main
    ocontroller = Thisdatabasedocument.currentController
    if not ocontroller.isconnected then ocontroller.connect
    oreportdoc = Thisdatabasedocument.reportdocuments.getbyname("myReport").open
    oTexttable = oreportdoc.Texttables(0)
    orows = oTexttable.rows
    for i = 1 to orows.count - 1 step 2
        orow = orows(i)
        orow.BackColor = RGB(240,240,240)
    next i
End Sub

Find attached an example, open the report by using the user-menue "Reports"
R
Attachments
Default_Report.odb
(43.27 KiB) Downloaded 147 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: [Solved] Alternating Row Colors in Reports

Postby Feelew » Wed Mar 26, 2014 5:19 pm

Hi F3K Total, and thanks for this helpful trick.

Since I grouped one of the fields in the report, I had to slightly modify the macro in order for it to run over every other table.

A couple questions for you, though.

• Is there a way to automatically run the macro when the report is generated?
• You haven't declared any of your variables. I could easily figure out that "i" could be declared as an integer, but I was a little more challenged when trying to figure out the exact type of data "ocontroller", "oreportdoc", "oTexttable", "orows" & "orow" were. Would you light my lantern on those ones? (I'm a firm believer in explicit variable declaration).

Thank you so much again for your reply.

~Edit~
I guess I forgot to ask one extra question: is there an easy way to have the macro to simply look for open reports and apply the macro to them if any is there available to receive it, regardless of its name? I mean to add flexibility to the macro by removing the hard-coded name of the report it is applied to...
AOO 4 • Windows7

Because many might have tried to come up with an answer to my questions before one succeeded at it...
THANK YOU! to everybody who are trying to solve my Open Office problems, even if fruitlessly.
Feelew
 
Posts: 17
Joined: Tue Mar 25, 2014 7:04 pm

Re: [Solved] Alternating Row Colors in Reports

Postby Feelew » Wed Mar 26, 2014 5:49 pm

Well, I guess I found some answers to my questions. If I run the macro PRIOR to generating the report, the macro generates it for me automatically. So that would take care of my first question, but would dramatically impair any extra flexibility I would thrive for through an answer to my third question...

At this point, I see two ways of resolving this conflict:
• The macro is attributed to the event of generating a report, and the name of the report it applies to is then automatically transmitted to the macro (which process permits it is so far unknown to me, but I suspect it should not be overly complex).
• A GUI allows the user to pick and choose which report the macro should apply to, and then the report is generated and properly configured with rows of alternating colours.

Anyone can help me a bit with this?
AOO 4 • Windows7

Because many might have tried to come up with an answer to my questions before one succeeded at it...
THANK YOU! to everybody who are trying to solve my Open Office problems, even if fruitlessly.
Feelew
 
Posts: 17
Joined: Tue Mar 25, 2014 7:04 pm

Re: [Solved] Alternating Row Colors in Reports

Postby F3K Total » Wed Mar 26, 2014 8:02 pm

find attached an example containing a dialog, listing all existing reports for opening
Code:
Code: Select all   Expand viewCollapse view
Sub S_open_Report
    ocontroller = Thisdatabasedocument.currentController
    if not ocontroller.isconnected then ocontroller.connect
    DialogLibraries.loadLibrary("Standard")
    odlgSelectReport = CreateUnoDialog(DialogLibraries.Standard.dlgSelectReport)
    olstReports = odlgSelectReport.getcontrol("lstReports")
    sListsource = Thisdatabasedocument.reportdocuments.Elementnames
    olstReports.Model.StringItemList = sListsource
    odlgSelectReport.execute
    sReport = olstReports.SelectedItem
    oreportdoc = Thisdatabasedocument.reportdocuments.getbyname(sReport).open
    oTexttable = oreportdoc.Texttables(0)
    orows = oTexttable.rows
    for i = 1 to orows.count - 1 step 2
        orow = orows(i)
        orow.BackColor = RGB(240,240,240)
    next i
End Sub

R
Attachments
Default_Report.odb
(63.96 KiB) Downloaded 170 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: [Solved] Alternating Row Colors in Reports

Postby Feelew » Wed Mar 26, 2014 10:15 pm

This works great! Thanks F3K_Total!

Still playing around with the code trying to add fancy stuff, but it is starting to be rather remotely related to this thread. Thanks a lot for your help & patience.
AOO 4 • Windows7

Because many might have tried to come up with an answer to my questions before one succeeded at it...
THANK YOU! to everybody who are trying to solve my Open Office problems, even if fruitlessly.
Feelew
 
Posts: 17
Joined: Tue Mar 25, 2014 7:04 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests