Add multiple columns based on data in one column

Hi,

I am having a complex problem of needing to add multiple columns dynamically to the report based on the values of one particular column.

Here is an example of my query result and existing report design

select * from event_registrations

gives the columns first_name, last_name, address, properties_xml as the result. Now my report has the table with the columns to show the 4 columns from the query. But the complicated part is that I have to parse the properties_xml, split it up into nodes and depending on the number of nodes, add the necessary number of columns to the report output.

For example: The xml is of this form

<properties>
<property name="phone" value="1234567890"/>
<property name="fax" value="1234567890"/>
</properties>

So I have to add 2 new dynamic columns named Phone and Fax and show the values in them. Is it possible to do this? I have seen posts on how to add dynamic columns but I am unable to figure out how to access the dataSetRow value in the javascript.

Note: I have tried the add dynamic column code using javascript in the beforeFactory method and I am able to add the dynamic column but like I said I don't know how to access the dataSetRow["properties_xml"] value to parse to create the required number of dynamic columns.

I would appreciate any help with this problem.

Find more posts tagged with

Comments

mwilliams

So, you bring in all your data into one column? How does it look in your dataset? Can you provide a sample of what your dataset looks like and what you wanna see from that?

phanivas

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="72520" data-time="1295663832" data-date="21 January 2011 - 07:37 PM">
So, you bring in all your data into one column? How does it look in your dataset? Can you provide a sample of what your dataset looks like and what you wanna see from that? </blockquote>
 
Hi, 
 
No, not one column but there is one column which has the xml and needs to be split into several columns. My Dataset looks like above, the dataset has 4 columns first_name, last_name, address and properties_xml. So my output report contains a table(grid) with those 4 columns. However, I want to parse the last column (properties_xml) and create additional table(grid) columns in the report to display the nodes (property) as columns 
 
so for example, based on the above query, my output report looks something like this 
<pre class='_prettyXprint _lang-auto _linenums:0'>
FirstName LastName Address PropertiesXML
ABC DEF GHI <properties>
<property name="phone" value="1234.."/>
<property name="fax" value="1234...."/>
</properties>
</pre>
I want to make the table(grid) dynamic and add additional columns (Phone & Fax) on the fly so that it looks like this 
<pre class='_prettyXprint _lang-auto _linenums:0'>

FirstName LastName Address PropertiesXML Phone Fax
ABC DEF GHI <properties>
<property name="phone" value="1234.."/> 12345... 12345....
<property name="fax" value="1234...."/>
</properties>
</pre>

phanivas

Hello,

Does anyone know if this is doable?

Thanks

mwilliams

As long as you have a column in your dataSet for phone/fax when they exist, you should be able to add the column to the table dynamically in the beforeFactory of the report.

http://www.birt-exchange.org/org/devshare/index.php?topicid=0&filtercontent=add+column&contenttype=&timeperiod=0&results=10

Hope this helps.

phanivas

Hi Williams, 
 
Thanks for the link. However, the phone/fax are not a part of my dataset, they are derived values from my dataset properties_xml column. So in that case, would I still be able to create dynamic columns and fill the derived values in the extra columns on a row by row basis? 
 
here is how my dataset query looks 
 
select first_name, last_name, email, properties_xml from events 
 
The phone/fax are strings inside the properties_xml column value which need to be parsed and displayed as columns on the report. 
 
Thanks 
Phani 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="72641" data-time="1296079454" data-date="26 January 2011 - 03:04 PM">
As long as you have a column in your dataSet for phone/fax when they exist, you should be able to add the column to the table dynamically in the beforeFactory of the report. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/index.php?topicid=0&filtercontent=add+column&contenttype=&timeperiod=0&results=10'>http://www.birt-exchange.org/org/devshare/index.php?topicid=0&filtercontent=add+column&contenttype=&timeperiod=0&results=10</a> 
 
Hope this helps. </blockquote>

mwilliams

Phani,

Ah. Ok. If you were to create a column that pulled the name values and one that pulled the values, you could probably create something similar to what you're wanting with a crosstab with the first 3 fields as row dimensions, the @name field as the column dimension, and the @value field as the measure, using the FIRST function rather than the default SUM one. Or, you could do a table grouped on the first 3 columns, then use an aggregation with the concatenate function to display the @name and @value fields how you'd like them in the appropriate header vs detail row.