Filtering a Cross Tab using Date Time field

Hi Everybody, 
I want to apply date criterion to a crosstab in the report. 
 
For example, records created in the Last Month. 
Currently , in a table i use the 'Create Date' field in the filter where i have created two formuale : 
 
1. The first formula will make sure that the records are pertaining only to the current year : 
 
Formula : 
BirtDateTime.year(row["Dat_IssueCreatTime"]) In 
if(BirtDateTime.month(BirtDateTime.today()) == "January") 
{(BirtDateTime.year(BirtDateTime.addYear(BirtDateTime.today(), -1)));} 
else(BirtDateTime.year(BirtDateTime.today())) 
 
2. The second formula calculates the last month : 
 
BirtDateTime.month(row["Date/Time Field"]) In 
BirtDateTime.month(BirtDateTime.addMonth(BirtDateTime.today(), -1)) 
 
 
I tried to use the same formulae on the dimension 'Create Date' in the Crosstab, but the report gave an error saying : 
"org.eclipse.birt.report.service.api.ReportServiceException: Error happened while running the report" 
 
And yes, I am using Birt Designer 2.5.1 v20090903 
 
Kindly advise if you have come across anything of similar sort. 
 
Regards, 
Roshan.

Find more posts tagged with

Comments

kclark

Would you mind if I took a look at the rptdesign?

Rosh 69

Hi kclark,

Attached is the .rptdesign file.

You can refer to filters used in the table. It works fine.

But, the same cannot be applied to crosstab.

Thanks.

Regards,
Roshan.

Rosh 69

Hi kclark,
Hope you are fine.

Did you have a look at the report file attached previously?

Regards,
Roshan.

Hans_vd

Hi Roshan,

Did you try to add the filter to the cube or to the crosstab itself?

Also, if you did it on the crosstab itself, did you do it step by step using the dialog or did you just copy/paste the condition in the expression builder?

Rosh 69

Hi Hans_vd, 
 
I have used the qualification on the crosstab and not the cube itself. 
 
Below is the qualification used : 
 
 
BirtDateTime.month(data["Dat_IssueCreatTime"]) in if(BirtDateTime.month(BirtDateTime.today()) == "January") 
{(BirtDateTime.year(BirtDateTime.addYear(BirtDateTime.today(), -1)));} 
else 
{(BirtDateTime.year(BirtDateTime.today()));}

Hans_vd

Ok,

What happens if you remove that filter and instead apply it in the cube definition? In order to do this, press the "Filter" button next to the Primary dataset field in the Cross Tab Cube Builder.

Rosh 69

Alright, I will try that and get back to you with the result.

Thanks.