OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Conditional expressions with reports

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Brother Damian
General User
General User


Joined: 04 Nov 2008
Posts: 27

PostPosted: Fri May 21, 2010 11:07 am    Post subject: [SOLVED] Conditional expressions with reports Reply with quote

Hello!

I am trying to have a text box in a report built with the SUN Report Builder to display “Still Active” if the data source produces an empty DATE cell. In the documentation I have found that SUNRB understands the following two functions:

IF(test;value_true;value_otherwise)
ISBLANK(value)

Their definitions say that all the parameters are required.

The SUNRB documentation also states:

- quote for [ is \
- \\
- [] as reference for formula or field name
- "" to quote strings
- . as decimal separator
- date as in java specific
parameter separator is ; (semicolon) and it is allowed to let some parameters empty inside a call like XYZ(;kk;;kkb)

Now, for me this is really cryptic. I still don't understand the second line and fail to appreciate when or how to use the info in the first line, for example.

Despite these limitations I mixed lemons with oranges and came with the following tentative expression:

Code:
IF(ISBLANK([MyDate]);"Still Active";[MyDate])


My logic is: “Return the string “Still Active” if the cell/attribute/column 'MyDate' is empty. If not, return the date found in the cell/attribute/column”.

Of course, it's not working. I do get the date if the cell is not empty but I get nothing if it is.

a) Is my syntax wrong?
b) Is this a glitch?
c) Is this because the data source corresponds to a DATE variable and not a STRING of some kind? Given this possibility I tried the following changes:

Code:
IF(ISBLANK([MyDate]);Today();[MyDate]) and

IF(ISBLANK([MyDate]);[Today()];[MyDate]) and

IF(ISBLANK([MyDate]);[TODAY()];[MyDate]) and

IF(ISBLANK([MyDate]);TODAY();[MyDate])


but it still does not work. The SUNRB seems to treat text boxes as one of a kind, without differentiating among data types, in which case I can print text where a DATE usually goes. Is this correct?

Please note that the documentation for the SUNRB states that:

Quote:
Conditional Print Expression: A conditional print expression allows the user to specify whether an element or section should or should not be visible when executing the report. If the print expression evaluates to true, then the element will be printed in the report. The syntax is the same as for functions.


But this is not exactly correct as I still get to print the value in the cell after the test throws FALSE.

I will be very grateful for any information that helps me understand how to use this feature.

Thanks in advance.
_________________
Brother Damian


Last edited by Brother Damian on Tue May 25, 2010 7:36 pm; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri May 21, 2010 4:46 pm    Post subject: Reply with quote

Brother Damian:

You are 'close' . . . so I hope this helps.

Let me attempt to explain.
Problem: First and foremost, the Report field type . . . must all be the same. For example, you canNOT mix, TEXT and DATE formats.

Two Solutions, a and b: Either:
  1. Use the COALESCE, and TO_CHAR functions, to 'change' the DATE field ( "MyDate" ) to a TEXT STRING in your Query, and, when NULL, use your string of, 'Still Active' . . . for example, in your Query . . . ( change 'YYYY-MM-DD' to your desired string display format Smile )

    Code:
    COALESCE(TO_CHAR("MyDate", 'YYYY-MM-DD'), 'Still Active')

  2. Use Sun Report Writer Functions to 'convert' the DATE to text.

    By 'concatenating' the pieces using the function YEAR, MONTH and DAY . . . and . . . adding leading zeros, if you want in your display . . . this will work.

    I hope the following 'code' will work for you . . . assuming . . . you want the date displayed in YYYY-MM-DD format. If anything else, modify it to reflect the date format you desire.

    You will also have to modify your report field definition, from, Date to Text.

    Code:
    IF(ISBLANK([MyDate]); "Still Active"; YEAR([MyDate]) & "-" & RIGHT("0" & MONTH([MyDate]); 2) & "-" & RIGHT("0" & DAY([MyDate]); 2))

    Explanation:
    1. The above will return "Still Active" when [MyDate] is blank.

    2. When [MyDate] is NOT blank, we are creating the date display as TEXT . . . by concatenating ( & )
      1. The YEAR function of the Date
      2. Followed by a dash
      3. Followed by the MONTH function (adding a leading zero for a length of two if needed )
      4. Followed by a dash
      5. Followed by the DAY of the month ( adding a leading zero for a length of two if needed ).

    3. Be sure in your Sun Report, that the Formatting on the field is TEXT ( @ )

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

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Brother Damian
General User
General User


Joined: 04 Nov 2008
Posts: 27

PostPosted: Sun May 23, 2010 11:00 am    Post subject: Report field types and conditional printing in SRB Reply with quote

You rule, sliderule, thank you for responding so promptly and with such detail.

I tried your second option: I changed the formatting of the text box to text (with the "Formatting" attribute found in the general tab in the object's properties dialog box) and then introduced the conditional code that you recommended (at the "Conditional Print Expression" box in the same tab).

Unfortunately, it didn't work. This time I did not even get the DATE data when the cell is not empty.

What you said about the report field type needing to be the same for the values returned got me thinking. I checked the documentation and discovered that functions like YEAR() and DAY() return INTEGER data types, not TEXT strings. This got me somewhat confused because it means that I con not treat their return as TEXT. Is this correct?

So I tried some variations. First, I found the function T(value) that changes value into TEXT. Second, I left the Text Box's formatting as TEXT. Then I changed the code to:

Code:
IF(ISBLANK([MyDate]);"Still Active";T([MyDate]))


According to this, if the cell is empty the function should return the string "Still Active". If not, it should return the date as a string. Because I left the text box as TEXT, it should display either. Right?

However, it also didn't work.

So I returned the formatting of the Text Box to DATE and attempted the following code:

Code:
IF(ISBLANK([MyDate]);TODAY();[MyDate])


[MyDate] holds DATE data, TODAY() returns DATE information and the Text Box is formatted for DATE, so everything should be OK. But it also didn't work.

Am I adjusting the right parameters in the right places?

I am working with Windows XP, OpenOffice.org 3.1.0 (build 9399) and JAVA 6, update 19. DO I have the wrong configuration?

I will appreciate any information that helps me make sense of the Conditional Printing Functions with the SUNRB.

In the mean time I will try option number one.
_________________
Brother Damian
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun May 23, 2010 12:29 pm    Post subject: Reply with quote

Brother Damian:

I did present two options . . . a and b. I do think, and, would suggest you use a . . . that is . . . to modify your Query ( data ) before it gets to the Report . . . so the Query will provide TEXT values . . . a Date as a Text String . . . using the TO_CHAR function to format the date when a Date is present, and, COALESCE will return your desired string 'Still Active' when NULL.

But, if you want to use option b . . . by having SUN Report Writer do it, you MUST enter the code I entered in b ( changing MyDate to the real Date field in your table ) . . . BUT . . . .it is entered:
  1. On the Data Tab
  2. Date Field Type should be Field or Formula
  3. Data Field - press the BUTTON to the right ( it has three dots )
  4. In the Formula pop-up, copy and paste the formula, it should start with an =:

    Code:
    IF(ISBLANK([MyDate]); "Still Active"; YEAR([MyDate]) & "-" & RIGHT("0" & MONTH([MyDate]); 2) & "-" & RIGHT("0" & DAY([MyDate]); 2))

  5. Be sure in your Sun Report, that the Formatting on the field is TEXT ( @ )

Just to re-iterate, the formula is NOT entered as a part of Conditional. Conditional was never mentioned in my first post. Smile

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

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Brother Damian
General User
General User


Joined: 04 Nov 2008
Posts: 27

PostPosted: Mon May 24, 2010 11:00 pm    Post subject: options A and B Reply with quote

Dear Sliderule:

The first solution option A you gave me works like a charm although it gave me some fight: I did have to relocate a closing parentheses in the condition of the CASEWHEN to before the "=10" bit which, for a newbie like me, was not immediately obvious. So the code that finally worked was:

Code:
CASEWHEN(LENGTH("MyDate")=10, TO_CHAR("MyDate",'YYYY-MM-DD'), 'Still Active')


The table created by the complete SQL code produced a column with the dates I needed and the text "Still Active" if they happened to be empty.

I have yet to try your new version for solution option A with the COALESCE function but you can be sure I will post my results.

Your solution B (working directly with the SUNRB) did not work, unfortunately. I did follow all your instructions, including making sure that the formatting of the box is set to TEXT; and copied and pasted your code. I got a column filled with "Still Active" even with non-blank fields.

However, your detailed explanation showed me that I was writing the code in the wrong place. I did try the following code in the Data tab, which worked well:

Code:
IF(ISBLANK([MyDate]);TODAY();[MyDate])


Here all the variables are of the DATE type, to which I set the formatting of the box. The report wrote the date in the data and wrote the current date if the field was empty.

Following the good streak, I wrote the following code after changing the formatting of the box to TEXT again:

Code:
IF(ISBLANK([MyDate]);"Still Active";T([MyDate]))


According to documentation, this should have returned the string "Still Active" if MyDate was empty, or the content of MyDate as string if it was not.

But it didn't work. I also tried:

Code:
IF(ISBLANK(T([MyDate]));"Still Active";T([MyDate]))


To see if changing all data types to TEXT had some effect. But it also didn't work.

So, from the point of view of printing "Still Active" in empty DATE fields, the option to write SQL code to the HSQL engine works fine and solves the problem.

But from the point of view of understanding how the SUNRB works, I am still in the dark. I would deeply appreciate if you could tell me why the code using the T(value) function has not worked or how could I fix it.

I will post my results for your new proposal with the COALESCE function when I have them.

Thank you very, very much for your help.
_________________
Brother Damian
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue May 25, 2010 7:18 am    Post subject: Reply with quote

Brother Damian:

I have NO IDEA what you are writing or talking about.

You said:
Brother Damian wrote:
The first solution option A you gave me works like a charm although it gave me some fight: I did have to relocate a closing parentheses in the condition of the CASEWHEN to before the "=10" bit which, for a newbie like me, was not immediately obvious. So the code that finally worked was:

Code:
CASEWHEN(LENGTH("MyDate")=10, TO_CHAR("MyDate",'YYYY-MM-DD'), 'Still Active')

I see no CASEWHEN in any Option A I suugested. I do see a COALESCE statement.

You said:
Brother Damian wrote:
Your solution B (working directly with the SUNRB) did not work, unfortunately.

Since, I do NOT know what you did, I do know it works for me.

See below . . . the difference . . . in my table, the name of the Date field is: SALES_DATE . . . the name of the field is not important . . . so long as you use the name that is defined in YOUR database.

You said:
Brother Damian wrote:
The table created by the complete SQL code produced a column with the dates I needed and the text "Still Active" if they happened to be empty.

A Query does NOT produce a table . . . that is . . . it does NOT write to a database additional data . . . it does NOT 'duplicate' any data. Rather, the Query produces 'a result set' that resides in memory until you close the Report Writer.

To show, with graphics what I did:

Query to produce a column of data from a DATE column . . . result is a text string . . . either the date, OR, the words 'Still Active' if NULL: Note that for ID 9, SALES_DATE is NULL, so, the COALESCE function returns 'Still Active'



In the SUN Report Writer, I used a formula to create the text string to display . . . stringing together Year, Dash, Month, Dash, Day . . . OR . . . "Still Active" if ISBLANK([SALES_DATE])



The Report Output when run.



I hope this helps, please be sure to let me / us know. I do not know what else to do to help you, nor to explain it any clearer. Perhaps someone else can help.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Brother Damian
General User
General User


Joined: 04 Nov 2008
Posts: 27

PostPosted: Tue May 25, 2010 7:34 pm    Post subject: Problem solved Reply with quote

Thank you Sliderule so much for the time and effort placed into helping me solve my problem. Also, thanks for the diagrams and the detailed explanations. I can tell you that I learned a whole lot from them (and not only about Base!). The solution that you offered me, based on producing a query that changes DATE info into text strings if the cell has data or to introduce the string "Still active" if not, is the one I am going to use. With some planning, I can use this solution with many similar situations. So I am going to consider this question solved.

By the way, you are absolutely right when you say that a query is not a table. I am sorry that the words I chose startled you and created confusion. When I said table I just meant to refer the organization of the result set in columns and rows but at no moment did I want to imply it's persistence. Actually, the good thing about a query is that it's result set will reflect any additions or deletions to data.

When I read the first reply to my initial question I printed the screen on paper so I could work with Base without flipping screens. This printing reads:

Quote:
Two Solutions, a and b: Either:

a. Use the TO_CHAR function, to 'change' the DATE field ( "MyDate" ) to a TEXT STRING in your Query . . . for example, in your Query . . .


Code:
CASEWHEN(LENGTH("MyDate"=10), TO_CHAR("MyDate",'YYYY-MM-DD'), 'Still Active')


etc..

I didn't even know the function CASEWHEN before this post. When I saw a change in that post using the function COALESCE I just assumed that you had edited your reply to show a more elegant solution or something like that. Now I understand that you had not posted this and that maybe someone else did and later decided to erase his/her contribution. In any event, the function works very well except that you need to move the first closing parenthesis to immediately after "MyDate" and not leave it between the number ten and the coma. I also recommend changing the test to: LENGTH("MyDate")>1 to identify not-null cells

Again, thank you very, very much Sliderule. I hope to read again from you in the posting of my next problem.
_________________
Brother Damian
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group