[Solved] Alternating Row Colors in Reports

[Reversephase]

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 ?

[Sliderule]

I am sending you a PM ( Private Message ).

Sliderule

[Reversephase]

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

I have also attached an example for others to See, and play with

[Feelew]

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.

[Villeroy]

Calc can be used as a report engine too, including conditional formatting.
[Tutorial] Using registered datasources in Calc

[Feelew]

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.

[F3K Total]

Hi,
maybe this simple macro can help

Code: Select all

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

[Feelew]

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...

[Feelew]

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?

[F3K Total]

find attached an example containing a dialog, listing all existing reports for opening
Code:

Code: Select all

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

[Feelew]

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.