<p>Hi,</p>
<p>I have to dynamically generate columns i.e computed columns from two csv file. one csv file contains raw data and second file contains mapping "column header" names joined with colon i.e ":".</p>
<p> </p>
<p><strong>Ex</strong>:</p>
<p> </p>
<p><strong>1) Mapping.csv</strong></p>
<p> </p>
<div><strong>managedelement, content </strong></div>
<div>nltcom,last_minute_cpu_utilization_kpi:last_5_minute_cpu_utilization_kpi:last_30_minute_cpu_utilization_kpi:last_hour_cpu_utilization_kpi</div>
<div> </div>
<div><strong>2) DummyData.csv</strong></div>
<div> </div>
<div><strong>dataset_date,measurement,managedelement,content</strong></div>
<div>2015-05-26 21:45:00.0,cpqHoCpuUtilTable,nltcom04,20:7:5:5</div>
<div>2015-05-26 21:45:00.0,cpqHoCpuUtilTable,nltcom04,17:8:5:5</div>
<div>2015-05-26 21:45:00.0,cpqHoCpuUtilTable,nltcom01,20:4:7:7</div>
<div>2015-05-26 21:45:00.0,cpqHoCpuUtilTable,nltcom03,8:12:5:5</div>
<div>2015-05-26 21:45:00.0,cpqHoCpuUtilTable,nltcom02,11:5:5:6</div>
<div> </div>
<div>content column data joined with colon ( i.e ":") in both csv file. where i have to split content column data and render in computed column.</div>
<div> </div>
<div>Sample code :</div>
<div>
<pre class="_prettyXprint _lang-js">
//mappingColumnList = ['last_minute_cpu_utilization_kpi','last_5_minute_cpu_utilization_kpi','last_30_minute_cpu_utilization_kpi','last_hour_cpu_utilization_kpi'];
i=0;
importPackage(Packages.java.lang);
importPackage( Packages.org.eclipse.birt.report.model.api );
importPackage(Packages.org.eclipse.birt.report.data.adapter.api);
importPackage(Packages.org.eclipse.birt.data.engine.api.querydefn);
logger = java.util.logging.Logger.getLogger("birt.report.logger");
//starting for second dataset
var config = reportContext.getReportRunnable().getReportEngine().getConfig();
var dataSource = reportContext.getDesignHandle().findDataSource("CSVDataSource");
var dataSet = reportContext.getDesignHandle().findDataSet("MappingDS");
logger.warning ('your log message is here');
mySession = DataRequestSession.newSession(config, new DataSessionContext(DataSessionContext.MODE_DIRECT_PRESENTATION));
mySession.defineDataSource(mySession.getModelAdaptor().adaptDataSource(dataSource));
mySession.defineDataSet(mySession.getModelAdaptor().adaptDataSet(dataSet));
queryDefinition = new QueryDefinition(true);
queryDefinition.setDataSetName(dataSet.name);
var pq = mySession.prepare(queryDefinition);
query = pq.execute(null);
recordsIterator = query.getResultIterator();
var mappingColumn;
var mappingColumnList;
if (recordsIterator.next()) {
mappingColumn = recordsIterator("content");
mappingColumnList = mappingColumn.split(":");
}
//end of second dataset
//Dynamically creating computed columns
elementFactory = reportContext.getDesignHandle().getElementFactory()
var mytable = reportContext.getDesignHandle().findElement("mytable");
var colbinds = mytable.getColumnBindings( );
var cs1 =[]; StructureFactory.createComputedColumn( );
while(i < mappingColumnList.length) {
cs1[i] = StructureFactory.createComputedColumn( );
cs1[i].setName(mappingColumnList[i]);
cs1[i].setDataType("String");
cs1[i].setExpression("dataSetRow[\"content\"].split(\":\")["+i+"]");
logger.warning(" data value is "+cs1[i].getExpression());
colbinds.addItem( cs1[i] );
//second parameter is before(-1) or after(1)
mytable.insertColumn(4+i,-1);
//get first detail row
mydetail = mytable.getDetail( ).get( 0 );
//get first column and add detail data item
var tcell = mydetail.getCells( ).get( 3+i );
var mydata = elementFactory.newDataItem( null );
mydata.setResultSetColumn( mappingColumnList[i]);
tcell.getContent( ).add( mydata );
//get header and add label
var myheader = mytable.getHeader( ).get( 0 );
tcell = myheader.getCells( ).get( 3 +i );
var mylabel = elementFactory.newLabel( null );
mylabel.setText( mappingColumnList[i] );
tcell.getContent( ).add( mylabel );
i++;
}
</pre>
<p>I am getting "org.eclipse.datatools.connectivity.oda.flatfile.InvalidResourceException: Invalid table name:Resources/\Mapping.csv" error message while executing reports.</p>
<p> </p>
<p>if I commented the below code :</p>
<pre class="_prettyXprint _lang-js">
query = pq.execute(null);
recordsIterator = query.getResultIterator();
var mappingColumn;
var mappingColumnList;
if (recordsIterator.next()) {
mappingColumn = recordsIterator("content");
mappingColumnList = mappingColumn.split(":");
}
</pre>
<p>and commented below line : </p>
</div>
<div><br><pre class="_prettyXprint _lang-js">
mappingColumnList = ['last_minute_cpu_utilization_kpi','last_5_minute_cpu_utilization_kpi','last_30_minute_cpu_utilization_kpi','last_hour_cpu_utilization_kpi'];
</pre>
<p>it is working fine if I have hard coded the mapping columns in array without reading mapping.csv dataset.</p>
<p> </p>
<p> </p>
<p>What was wrong in my code to fetch mapping columns dynamically from second data set i.e. MappingDS where content column contains data. </p>
<p> </p>
<p>How to resolve this problem. </p>
<p> </p>
<p> </p>
<p>Please find the sample code in the attachments.</p>
<p> </p>
<p>I'm using Actuate BIRT Designer Professional 4.4.0 (commercial)</p>
<p> </p>
<p>Thanks,</p>
<p>Srinivas</p>
<p> </p>
</div>
