day-of-week in crosstabs

I am having problems with grouping dates in a data cube. My week starts on Sunday for some reason. I tried changing the locale to no avail. See the attached screenshots.

I have a timestamp column in my database which I am using to create a weekly view of data. Normally, the grouping would work with just using the periodStamp as a column definition and does indeed do the job if I am dealing with just one week. But if I have a comparison of several weeks the weeks are separated from each other. What I would like to achieve is a grouping of days in a week, and the weeks according to the screenshot.

Now I got it working nicely, apart from having the first day of the week as a sunday. I have tried changing BIRT_VIEWER_LOCALE to en_US, it does not affect the order of days. What I would like to achieve is monday first at all times.

Thanks in advance for your advice!

Find more posts tagged with

Comments

mwilliams

Hi tapani108,

So, everything works fine with your report, you just want the days to be ordered Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday?

tapani108

Yes, that is correct. If I group the days in the data cube using a timestamp, the week will start on Sunday. Not good for a bunch of locales. I had a difficult time getting that to work properly.

Here's the workaround:

- I added an integer called TimeUnit to my data and used that for the grouping instead of Day-Of-Week in the timestamp. So that would mean Monday as 1, Tuesday as 2, and so on.
- I then created a mapping for each weekday. The mapping is shown in the attachment.

This approach is bubble gum and paper clips, I know, but the tradeoff is better control over the order as well as localization.

Having said that, I will still highly appreciate a best-practices approach to doing this using the timestamp grouping features instead.

tapani108

Posting an alternate solution: 
 
Instead of the previous posts column TimeUnit value, you can of course create a computed column in the data series if you have a timestamp. 
 
You can do it with code like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>BirtDateTime.weekDay(row["theNameOfYourTimestamp"], 2)</pre>
 
The weekDay() function accepts an optional integer which works like this: 
 
1: return numbers 1(Sunday) to 7(Saturday) 
2: return numbers 1(Monday) to 7(Sunday) 
3: return numbers 0(Monday) to 6(Sunday) 
 
So, using this you get an integer, which is easier to sort in the crosstab grouping. 
 
You can then use the Map tab in the data field's property editor to create mappings for each integer. That's only seven mappings, so the sky won't be full of thunder.. 
 
This solves my problem one notch better, because I don't have to worry about my backend coming up with the right TimeUnit integers, only the correct timestamp.