Dynamic Column generation

Sujit1234 · 2010-06-14T00:52:55+00:00

Hi<br/><br/> Request you to help me to generate dynamic columns based on row data.<br/><br/> My requirement as follows:<br/><br/> I have the final data like this...<br/><br/> locid asetid AttributeName Attribute value <hr></hr><hr></hr><hr></hr><hr></hr> 101 221 Color' Red<br/> 101 221 ProductType orange<br/> 101 221 size…

Sujit1234

Hi

Request you to help me to generate dynamic columns based on row data.

My requirement as follows:

I have the final data like this...

locid asetid AttributeName Attribute value

101 221 Color' Red
101 221 ProductType orange
101 221 size 30
102 221 Color Red
102 221 ProductType orange
102 221 size 30
103 222 ProductType orange
103 222 size 30
104 222 ProductType orange
104 222 size 30
105 223 ProductType orange

The above data should be displayed in 5 columns since I have 3types of attribute names (Color,ProductType,Size)

for example if I have 'n' different Attribute names , then I have to dynamically display n columns .

LOCid assettype ProductType color Size ---- Attribute name
---- --- ---- ---- ---
101 221 Orange Red 30 ---- Attribute Value

Thanks
Sujit

Find more posts tagged with

Comments

bhanley

You are going to want to build a scripted data source to handle this. It is a very straightforward operation that will allow you to process the underlying data and populate your data set at run-time using POJOs and some simple scripting. There are many examples on the DevShare to help get you started. if you have any specific questions, reply to this thread and I will give you a hand.

Sujit1234

<blockquote class='ipsBlockquote' data-author="'bhanley'" data-cid="65530" data-time="1276526613" data-date="14 June 2010 - 07:43 AM"><p>
You are going to want to build a scripted data source to handle this. It is a very straightforward operation that will allow you to process the underlying data and populate your data set at run-time using POJOs and some simple scripting. There are many examples on the DevShare to help get you started. if you have any specific questions, reply to this thread and I will give you a hand.<br /></p></blockquote>
<br />
<br />
Hi<br />
<br />
Thanks for your reply..<br />
<br />
I tried the above requirement by using cross tab oprion,but I could not able to display rows to columns.<br />
<br />
Please provide some examples links or files if available.<br />
<br />
Thanks<br />
Sujit

bhanley

<blockquote class='ipsBlockquote' data-author="'Sujit1234'" data-cid="65571" data-time="1276603046" data-date="15 June 2010 - 04:57 AM"><p>
Hi<br />
<br />
Thanks for your reply..<br />
<br />
I tried the above requirement by using cross tab oprion,but I could not able to display rows to columns.<br />
<br />
Please provide some examples links or files if available.<br />
<br />
Thanks<br />
Sujit<br /></p></blockquote>
<br />
<br />
Here are some links to samples that build scripted data sources:<br />
<br />
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/index.php?topicid=0&filtercontent=scripted&contenttype=&timeperiod=0&results=10#'>http://www.birt-exchange.org/org/devshare/index.php?topicid=0&filtercontent=scripted&contenttype=&timeperiod=0&results=10#</a><br />
<br />
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/index.php?topicid=0&filtercontent=scripted&contenttype=&timeperiod=0&results=10#'>http://www.birt-exchange.org/org/devshare/index.php?topicid=0&filtercontent=scripted&contenttype=&timeperiod=0&results=10#</a><br />
<br />
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/all-topics/filtercontent-scripted/contenttype/timeperiod0/results10/pageno2/sortby-postedon/type1/#'>http://www.birt-exchange.org/org/devshare/all-topics/filtercontent-scripted/contenttype/timeperiod0/results10/pageno2/sortby-postedon/type1/#</a>

johnw

You wont be able to do this without some sort of scripting. A scripted data source would be the most straightforward approach. If that doesn't work, you can set the the query in question as a dynamic parameter, and use a parameter extraction task to pull the values, then build a dynamic table. I just did this for a client who could not use a scripted data source due to the environment they run in dynamically replacing data sets, and this approached worked out well, if not a bit complicated to implement.

Sujit1234

<blockquote class='ipsBlockquote' data-author="'johnw'" data-cid="66048" data-time="1277934147" data-date="30 June 2010 - 02:42 PM"><p>
You wont be able to do this without some sort of scripting. A scripted data source would be the most straightforward approach. If that doesn't work, you can set the the query in question as a dynamic parameter, and use a parameter extraction task to pull the values, then build a dynamic table. I just did this for a client who could not use a scripted data source due to the environment they run in dynamically replacing data sets, and this approached worked out well, if not a bit complicated to implement.<br /></p></blockquote>
<br />
Hi<br />
<br />
Kindly provide sample design or steps,if you have.<br />
<br />
Thank you<br />
<br />
Regards<br />
Sujit

johnw

You can get an example of building a scripted data source if you go into BIRT, go to Window/ShowView/Report and Chart Design/Report Examples, and it is located under Scripting.

I can't post the design I used, but I will post the script. This is only in the case where scripted data source will not work, and you need to use a dynamic report paraemter to drive it. Basically it dynamically creates a grid with the number of rows in the parameter to serve as the "column" headers, then creates sub tables, filtering based on the value.

This script goes into the beforeFactory method.
/////////////////////////////////////////////////////////////////////////////////////////////////////
//get the list of managers from dynamic parameter
var managerList = getManagersList();

//create an element factory for use throughout this event
var elementFactory = reportContext.getDesignHandle().getElementFactory();

//get the size of the managers list
var managerSize = managerList.size();

//create the outer grid, based on the number of managers, with a single row
//which will house all the sub tables with the lines of succession
var newGrid = elementFactory.newGridItem("NewGrid", managerSize, 1);

//iterate through the managers list, adding in a table for each one
for (x = 0; x < managerSize; x++)
{
//get the current cell in the outter grid
var myCell = newGrid.getCell(0, x);

//create a new table, 1 column, 1 detail row and add to the outter grids
//current cell
var newTable = elementFactory.newTableItem("TempNewTable", 1);
myCell.getContent().add(newTable);

//get the header row, and insert the managers name
var headerRow = newTable.getHeader().get(0);
var currentCell = headerRow.getCells().get(0);
setBorder(currentCell);

//get the current managers information
var managerInfoArray = managerList.get(x);
var managerText = managerInfoArray.getValue();

//add a new label to store the managers name
addNewLabel(managerText, currentCell);

//Now, add the table binding for the dataset
var dataSet = reportContext.getDesignHandle().getDataSets().get(0);
newTable.setDataSet(dataSet);

//now, get reference to the detail rows cell
var detailCell = newTable.getDetail().get(0).getCells().get(0);
setBorder(detailCell);

//create the expression for the data binding for manager name
var managerBinding = createColumnBinding("managerColumnBinding",
"dataSetRow[\"FIRSTNAME\"] + \" \" + dataSetRow[\"LASTNAME\"] ");
newTable.getColumnBindings( ).addItem(managerBinding);

//create a new data item and set to the single column binding
var dataItem = elementFactory.newDataItem("newdataItem");
dataItem.setResultSetColumn("managerColumnBinding"); //this gets set to expression name
detailCell.getContent().add(dataItem);

//add filter to table
myProperty = newTable.getPropertyHandle(Packages.org.eclipse.birt.report.model.api.TableHandle.FILTER_PROP);
addFilter(myProperty, managerText);
} //end for

//add the new grid item
reportContext.getDesignHandle().getBody().add(newGrid);

///////////////////////////////////////////////////////////////////////////////////////////////
/*
Responsible for passing back a list of managers
*/
function getManagersList()
{
//create a new paraemter definition task on the current report
var parameterTask = reportContext.getReportRunnable().getReportEngine().createGetParameterDefinitionTask(reportContext.getReportRunnable());

//run it, and get the Succession List parameter List, then create a collection
var parameterDefinition = parameterTask.getParameterDefn("rprmSuccessionList");
paramChoices = parameterTask.getSelectionList(parameterDefinition.getName());

//convert the collection to a List and pass back the results
return (new Packages.java.util.ArrayList(paramChoices));
}

/////////////////////////////////////////////////////////////////////////////////////////////
/*
This method will add the filtering condtion to the filter properties passed in
*/
function addFilter(filterProperties, newLabelText)
{
fc = Packages.org.eclipse.birt.report.model.api.StructureFactory.createFilterCond();
fc.setExpr("dataSetRow[\"succession_by_position#incumbent#USERS_SYS_DIVISION\"] + \" - \" + dataSetRow[\"incumbent_position_VFLD1\"] + \" - \" + dataSetRow[\"succession_by_position#incumbent#USERS_SYS_FIRSTNAME\"] + \" \" + dataSetRow[\"succession_by_position#incumbent#USERS_SYS_LASTNAME\"]");
fc.setOperator("eq");
fc.setValue1("\"" + newLabelText + "\"");
filterProperties.addItem(fc);
}

////////////////////////////////////////////////////////////////////////////////////////////
/*
This method will add a new label element to the current cell
*/
function addNewLabel(newLabelText, currentCell)
{
//add a new label to store the managers name
newLabel = elementFactory.newLabel(newLabelText);
newLabel.setText(newLabelText);
currentCell.getContent().add(newLabel);

}

///////////////////////////////////////////////////////////////////////////////////////////////
/*
this method will create a column binding
*/
function createColumnBinding(name, expression)
{
var computedBinding = Packages.org.eclipse.birt.report.model.api.StructureFactory.createComputedColumn();
computedBinding.setName(name);
computedBinding.setExpression(expression);

return computedBinding;
}

///////////////////////////////////////////////////////////////////////////////////////////////////
/*
utilitiy method for appending the expression to the end of the string builder
*/
function appendStringBuilder(sb, rowExpression, testValue, replaceValue)
{
sb.append("if (" + rowExpression + ".toUpperCase().equals(\"" + testValue + "\".toUpperCase()))");
sb.append("{");
sb.append(" " + replaceValue + ";");
sb.append("}");
}

///////////////////////////////////////////////////////////////////////////////////////////////////
/*
This method will set the border on a cell
*/
function setBorder(currentCell)
{
currentCell.setProperty("borderBottomStyle", "solid");
currentCell.setProperty("borderBottomWidth", "medium");
currentCell.setProperty("borderBottomColor", "#000000");

currentCell.setProperty("borderLeftStyle", "solid");
currentCell.setProperty("borderLeftWidth", "medium");
currentCell.setProperty("borderLeftColor", "#000000");

currentCell.setProperty("borderRightStyle", "solid");
currentCell.setProperty("borderRightWidth", "medium");
currentCell.setProperty("borderRightColor", "#000000");

currentCell.setProperty("borderTopStyle", "solid");
currentCell.setProperty("borderTopWidth", "medium");
currentCell.setProperty("borderTopColor", "#000000");
}

Sujit1234

Hi

I will try this...Thanks for your help.

Regards
Sujit

hgu1

<blockquote class='ipsBlockquote' data-author="'Sujit1234'" data-cid="66106" data-time="1278063000" data-date="02 July 2010 - 02:30 AM"><p>
Hi<br />
<br />
I will try this...Thanks for your help.<br />
<br />
Regards<br />
Sujit<br /></p></blockquote>

hgu1

Hi John,

I've actually used the same approach for dynamically adding columns to the report based on database data. I've run into an issue recently and haven't found a fix yet, hope you can shed some light on it. The problem is, the parameterTask.getSelectionList(...) call does not always make the database call and sometimes returns an empty collection, even though there is data in the database. I'm on BIRT 2.3.2.

I cannot use scripted data source because the database connection is set by the calling application.

Thanks in advance,
Harry

johnw

Is there data in the drop down list?

hgu1

I've found why I'm not getting the selection list populated correctly at run time. It's due to database connection. The newly created ParameterDefinitionTask is using the default database url (set at design time pointing to a development database) to connect to the database rather than using the database connection set by the calling app at run time. Because the data is not present on the development database, the selection list is returned empty.

Now the question is, how can I force the newly created ParameterDefinitionTask to use the connection set by the calling app instead?

rodrigomac

<div>Hi,</div><div> </div><div>I also need to create dynamic columns in a table and, the columns will be created according to the data returned from a dataset.</div><div> </div><div>The problem is that I can only create the columns in the events "initialize", "onPrepare", "beforeFactory". So, I can't get the data in the dataset using the "onFetch" event.</div><div> </div><div>Is there any way to create elements of a table with the data that return from a dataset?</div><div> </div><div>Thanks,</div><div> </div><div>Rodrigo Machado</div>

tanvi_openText

Hello Friends,

I want to add dynamic columns exactly in the same way Sujit wanted to add. But I am not able to access any links given above, could you please help me to access those links? Or anyone can give me the sample code or report template file that I can refer?

Thanks in advance,
Tanvi.