Nested If-Else

0

 I'm trying to do something like ...

 

  <variable name="ConvertedDayInt" class="java.lang.String">

            <variableExpression><![CDATA[(($F{DAY_OF_WEEK}.equals("Sunday") ? "0"  ?

                                                ($F{DAY_OF_WEEK}.equals("Monday") ? "1") : "" )

                                )]]>

            </variableExpression>

         </variable>

 

 

 And break it down even further into Monday, Tuesday, Weds, etc. It's for sorting, ordering in a crosstab.

Right now if I sort it by Date, the end user doesn't like the fact that it's dynamic and the Days would change depending on when  You ran the report. AKA run it today and Monday shows up first. Run it tomorrow and Tuesday, etc.  
They want a standard Sun, Mon, Tues, Weds, Thurs, Fri, Sat   as the column header every time.   

The issue is if I run it normally by day of week, it'll sort the columns by alphabetical order.
AKA Friday, Monday, Sat, Sun, Thurs, Tues, Weds             Obvioulsy that's not good.

None of the sorting or ordering can be done in the database and the date and day_of_week are returned as separate strings. So when I try to convert date string just to EEE, it throws an error.  Any help ASAP would be appreciated.

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 8 years 1 month ago

4 Answers:

0

I don't exactly get why running it on a different day would re-order your output, but I at least get that you want the data to appear in an order that is not directly related to labels themselves...that is, you want the names of the days to appear, but not in alphabetical order.  I don't work with cross-tabs much, but I do run into this situation in my SQL queries, and here's how I solve it there:  when I want to return data in an "unnatural" order, I create an additional pseudo-field (that is, it's calculated in my query but not actually stored anywhere in the database) to define desired row order.  Let's say my table ORDERS has fields ORDER_DATE, COUNT, and AMOUNT, and I want to output all these columns for all rows, but I also want to return two additional rows: DAY_NAME and DAY_SORT_ORDER.  Whether I SUM or ORDER by one of these two new fields or just let the crosstab do the work depends on whether or not I'm also outputting any details on these.  At any rate, just to get these two extra fields to be returned in the result set might look something like this:

SELECT
  ORDER_DATE,
  COUNT,
  AMOUNT,
  DAY_NAME,
  CASE
    WHEN DAY_NAME='Sunday' THEN 0
    WHEN DAY_NAME='Monday' THEN 1
    WHEN DAY_NAME='Tuesday' THEN 2
    WHEN DAY_NAME='Wednesday' THEN 3
    WHEN DAY_NAME='Thursday' THEN 4
    WHEN DAY_NAME='Friday' THEN 5
    WHEN DAY_NAME='Saturday' THEN 6
    ELSE 7
  END AS DAY_SORT_ORDER
FROM
(
SELECT
  ORDERS.ORDER_DATE AS ORDER_DATE,
  ORDERS.COUNT AS COUNT,
  ORDERS.AMOUNT AS AMOUNT,
  DAYNAME(ORDERS.ORDER_DATE) AS DAY_NAME
FROM
  ORDERS
)
ORDER BY
  DAY_SORT_ORDER

Carl

cbarlow3's picture
1408
Joined: Mar 4 2010 - 8:59am
Last seen: 2 months 4 weeks ago
0

 Thanks for your help. That's how I've done it before, using SQL.
The problem is I have no access to the db, so I just call a Function, pass in parameters,and get fields returned which I have to calculate, manipulate, etc.   So the easy choice would be to do in it SQL.
The problem I have is I can't do it in SQL and the function isn't written like that.
In fact, the dates are returned as Strings as it is.   So I'm trying to figure a way to do it in Jasper. And converting a string to a Date as a variable expression in Jasper seemed the most logical. But it doesn't work.   So getting a natural order of Sunday, Monday, Tuesday, etc in a Jasper Crosstab has been  Hard to do so far.
 



Post Edited by pjamack at 08/23/2011 20:07



Post Edited by pjamack at 08/23/2011 20:12
pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 8 years 1 month ago
0
There is a quick and easy solution for this but takes a little work around - I will try to blog how to do this tonight
Portlight's picture
160
Joined: Jan 6 2011 - 9:11am
Last seen: 4 years 8 months ago
0

 Keep me updated.

pjamack's picture
137
Joined: Jul 6 2011 - 12:33pm
Last seen: 8 years 1 month ago
Feedback
randomness