Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Fiscal Report BIRT(Fiscal Report BIRT)
Fiscal Report BIRT [message #1055746] Wed, 08 May 2013 06:59 Go to next message
dhruba kumar is currently offline dhruba kumar
Messages: 40
Registered: September 2012
Member
We want to Design a report in BIRT with Scripted Data-source and data-cube.
Our requirement is to generate a summary report for a fiscal year, Say for 2011-12 starting from Apr'11 to Mar'12(as shown in report2.xls). But using Birt I am able generate report which shows data starting from Jan'11 to Dec'11 as shown in report1.xls

attach.zop contains report1.xls,report2.xls and .rptdesign



  • Attachment: attach.zip
    (Size: 134.92KB, Downloaded 36 times)
Re: Fiscal Report BIRT [message #1057747 is a reply to message #1055746] Wed, 08 May 2013 10:22 Go to previous messageGo to next message
Michael Williams is currently offline Michael Williams
Messages: 1560
Registered: July 2009
Senior Member

If you were to create computed columns in your dataSet, you could get what you're wanting. You'd just have to make a computed column for the "fiscal year" and also a computed column assigning values of 1-12 to April - March. Then, use these as your dimensions. That should help get you what you're wanting. Let me know.

Michael

Add interactivity, scheduling, sharing, live Excel export, and JS integration to your BIRT app with the new, free BIRT server, BIRT iHub F-Type.
Re: Fiscal Report BIRT [message #1057754 is a reply to message #1057747] Wed, 08 May 2013 11:12 Go to previous messageGo to next message
dhruba kumar is currently offline dhruba kumar
Messages: 40
Registered: September 2012
Member
Thanks for your quick response.
Can u please give a sample or example
Re: Fiscal Report BIRT [message #1057764 is a reply to message #1057754] Wed, 08 May 2013 11:39 Go to previous messageGo to next message
dhruba kumar is currently offline dhruba kumar
Messages: 40
Registered: September 2012
Member
Thanks for your quick response.
I am using BIRT 4.2.2.
How to create computed column in scripted datasource(POJO datasource)
Can u please give a sample or example to create computed column to achieve the reprt
Re: Fiscal Report BIRT [message #1057890 is a reply to message #1057764] Thu, 09 May 2013 11:11 Go to previous messageGo to next message
Michael Williams is currently offline Michael Williams
Messages: 1560
Registered: July 2009
Senior Member

In a scripted dataSet, you'd just define a couple new columns in your dataSet for "fiscal year" and "month" and in the fetch method of your dataSet, you'd simply check your date for what month/year it is and set values for your two fields appropriately. I'll gladly make an example if you still need one after this explanation. Let me know.

Michael

Add interactivity, scheduling, sharing, live Excel export, and JS integration to your BIRT app with the new, free BIRT server, BIRT iHub F-Type.
Re: Fiscal Report BIRT [message #1058154 is a reply to message #1057890] Sun, 12 May 2013 12:48 Go to previous messageGo to next message
dhruba kumar is currently offline dhruba kumar
Messages: 40
Registered: September 2012
Member
Hi thanks for your advice

I ve created a new column "FiscalYear".

Now in Fetch I ve written the following code to populate "FiscalYear"


row["creationDate"] = rowobj.get(count).getCreationDate();//Get the creationDate from POJO


cal = new Packages.java.util.Calendar.getInstance();
cal.setTime(row["creationDate"]);

FiscalMonth = cal.get(Packages.java.util.Calendar.MONTH)+1;
FiscalYear=cal.get(Packages.java.util.Calendar.YEAR);
//Logic for assigning Apr=1,May=2....Jan=10,Feb=11,Mar=12
if(FiscalMonth<4){
FiscalMonth=9+FiscalMonth;
}
else
FiscalMonth=FiscalMonth-3;
datestr1=FiscalYear+"/"+FiscalMonth+"/01";
row["FiscalDate"]= new Packages.java.util.Date(datestr1);



Next I ve created Group for FiscalDate in datacube and checked "year" and "Month" in group .Also edited "month" to format "May(MMM)".


Now generated report and in report Month strts from "April " and ends to "March" .But Instead of Showing "Apr", "may"..."jan","feb","mar" in report it is showing 1 for Apr,2 for May ..12 for Mar.

I ve attached(rpt.zip) my .rptdesign and generated report in xls
  • Attachment: rpt.zip
    (Size: 81.55KB, Downloaded 33 times)
Re: Fiscal Report BIRT [message #1058339 is a reply to message #1058154] Mon, 13 May 2013 11:31 Go to previous messageGo to next message
Michael Williams is currently offline Michael Williams
Messages: 1560
Registered: July 2009
Senior Member

You should be able to pass the actual date field as an attribute to the month measure and use it in the dimension label. Let me know.

Michael

Add interactivity, scheduling, sharing, live Excel export, and JS integration to your BIRT app with the new, free BIRT server, BIRT iHub F-Type.
Re: Fiscal Report BIRT [message #1059560 is a reply to message #1058339] Mon, 20 May 2013 09:05 Go to previous messageGo to next message
dhruba kumar is currently offline dhruba kumar
Messages: 40
Registered: September 2012
Member
Hi As per your suggestion I have done the following things:

1.In Now in Fetch I ve written the following code for Actual Date-time:

if(count < rowobj.size()){
row["ecnNo"]=rowobj.get(count).getEcnNo();
row["reason"] = rowobj.get(count).getReason();
row["plant"] = rowobj.get(count).getPlant();
row["creationDate"] = rowobj.get(count).getCreationDate();



cal = new Packages.java.util.Calendar.getInstance();
cal.setTime(row["creationDate"]);

FiscalMonth = cal.get(Packages.java.util.Calendar.MONTH)+1;
FiscalYear=cal.get(Packages.java.util.Calendar.YEAR);

//Logic for Apr'11 to Mar'12
if(FiscalMonth<4){

FiscalYear=FiscalYear-1; //For Jan'12 Feb'12 and Mar'12
}

datestr1=FiscalYear+"/"+FiscalMonth+"/01";
row["creationDate"]= new Packages.java.util.Date(datestr1);

count++;
return true;
}

return false;



2. Now in dimension Label for "Month" I ve used the following code:
if(dimension["Year-Month"]["Month"]<4){
dimension["Year-Month"]["Month"]=9+dimension["Year-Month"]["Month"];

}
else
{
dimension["Year-Month"]["Month"]=dimension["Year-Month"]["Month"]-3;

}

dimension["Year-Month"]["Month"]["DateTime"]


Now when the report is generated from 01-Apr-2011 to 31-Mar-2012 "Jan'12" is in first column instead of "Apr'11".


Please find the attached report amd .rptdesign.


PLease advise
  • Attachment: attch.zip
    (Size: 101.94KB, Downloaded 30 times)
Re: Fiscal Report BIRT [message #1059638 is a reply to message #1055746] Tue, 21 May 2013 01:03 Go to previous messageGo to next message
Michael Williams is currently offline Michael Williams
Messages: 1560
Registered: July 2009
Senior Member

Take a look at this example. I create a few computed columns in the dataSet, then in the dataCube, I use the integer fields for sorting purposes. However, in the dataCube, I create a couple new bindings to display the attributes for each dimension. The attributes are added to the dimensions in the dataCube editor by double clicking on the dimension. For the year display, I passed a custom string field I created in the dataSet. For the month display, I passed the actual date as an attribute and used formatting to get it to display the month string.

Hope this helps.


Michael

Add interactivity, scheduling, sharing, live Excel export, and JS integration to your BIRT app with the new, free BIRT server, BIRT iHub F-Type.
Re: Fiscal Report BIRT [message #1060636 is a reply to message #1059638] Mon, 27 May 2013 08:30 Go to previous messageGo to next message
dhruba kumar is currently offline dhruba kumar
Messages: 40
Registered: September 2012
Member
Hi Sorry for delay in reply.

Now my report is generating as per my expection .
The .rptdesign and Report is atttached.

In Month column I ve written the folowing expression to show the Heading "Apr", "May", "Jun" .."Mar":
BirtDateTime.month(BirtDateTime.date(1900,dimension["Year-Month"]["Month"]+2%12,1),3)+" '"+(new String(new Date(dimension["Year-Month"]["Year"]+dimension["Year-Month"]["Month"]/10,1,1).getFullYear())).substr(2,2);

And in "fetch" in DataSet written the following scriplet:

if(count < rowobj.size()){
rowval=rowobj.get(count).getAttrVector();;
row["ecnNo"]=rowval.get(0).toString();
row["reason"] = rowval.get(1).toString();
row["plant"] = rowval.get(2).toString();
stk= new Packages.java.util.StringTokenizer(rowval.get(3).toString(),"-");
odt = new Packages.java.text.SimpleDateFormat("yyyy/MM/dd").parse(stk.nextToken().toString());
row["creationDate"] = odt;



cal = new Packages.java.util.Calendar.getInstance();
cal.setTime(row["creationDate"]);

FiscalMonth = cal.get(Packages.java.util.Calendar.MONTH)+1;
FiscalYear=cal.get(Packages.java.util.Calendar.YEAR);
if(FiscalMonth<4){
FiscalMonth+=9;
FiscalYear=FiscalYear-1;
}
else
FiscalMonth-=3;

datestr1=FiscalYear+"/"+FiscalMonth+"/01";
row["creationDate"]= new Packages.java.util.Date(datestr1);

count++;
return true;
}

return false;



If possoble please incorporate this feature in OOTB BIRT.

Thanks for your help.

Re: Fiscal Report BIRT [message #1060638 is a reply to message #1059638] Mon, 27 May 2013 08:32 Go to previous messageGo to next message
dhruba kumar is currently offline dhruba kumar
Messages: 40
Registered: September 2012
Member
Hi Sorry for delay in reply.

Now my report is generating as per my expection .
The .rptdesign and Report is atttached.

In Month column I ve written the folowing expression to show the Heading "Apr", "May", "Jun" .."Mar":
BirtDateTime.month(BirtDateTime.date(1900,dimension["Year-Month"]["Month"]+2%12,1),3)+" '"+(new String(new Date(dimension["Year-Month"]["Year"]+dimension["Year-Month"]["Month"]/10,1,1).getFullYear())).substr(2,2);

And in "fetch" in DataSet written the following scriplet:

if(count < rowobj.size()){
rowval=rowobj.get(count).getAttrVector();;
row["ecnNo"]=rowval.get(0).toString();
row["reason"] = rowval.get(1).toString();
row["plant"] = rowval.get(2).toString();
stk= new Packages.java.util.StringTokenizer(rowval.get(3).toString(),"-");
odt = new Packages.java.text.SimpleDateFormat("yyyy/MM/dd").parse(stk.nextToken().toString());
row["creationDate"] = odt;



cal = new Packages.java.util.Calendar.getInstance();
cal.setTime(row["creationDate"]);

FiscalMonth = cal.get(Packages.java.util.Calendar.MONTH)+1;
FiscalYear=cal.get(Packages.java.util.Calendar.YEAR);
if(FiscalMonth<4){
FiscalMonth+=9;
FiscalYear=FiscalYear-1;
}
else
FiscalMonth-=3;

datestr1=FiscalYear+"/"+FiscalMonth+"/01";
row["creationDate"]= new Packages.java.util.Date(datestr1);

count++;
return true;
}

return false;



If possoble please incorporate this feature in OOTB BIRT.

Thanks for your help.

Re: Fiscal Report BIRT [message #1061039 is a reply to message #1060638] Wed, 29 May 2013 11:58 Go to previous message
Michael Williams is currently offline Michael Williams
Messages: 1560
Registered: July 2009
Senior Member

You can request enhancements for BIRT in the bugzilla. If you post the report info in here, others who come across this thread with the same problem can vote for the enhancement.

Michael

Add interactivity, scheduling, sharing, live Excel export, and JS integration to your BIRT app with the new, free BIRT server, BIRT iHub F-Type.
Previous Topic:Text Truncating and blank pages in BIRT
Next Topic:Report Server
Goto Forum:
  


Current Time: Thu Aug 21 10:23:41 EDT 2014

Powered by FUDForum. Page generated in 0.01967 seconds