Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Replace Data Set Columns Dynamically Based on Parameter
Replace Data Set Columns Dynamically Based on Parameter [message #665260] Wed, 13 April 2011 22:12 Go to next message
Meghan Missing name is currently offline Meghan Missing nameFriend
Messages: 16
Registered: November 2010
Junior Member
I have a multi-select parameter that returns the columns a customer would like to see displayed in a table. I have a data set that grabs all of those columns (in addition to ones that are potentially NOT selected). I need to replace the data with the appropriate columns.

In the beforeOpen I have the following code:

var param=params["paramField"].toString().split(",");

this.queryText = this.queryText.replaceAll("Field1", param[0]);

This works just fine BUT for a static replacement BUT I want to replace x number of fields with my x number of parameter selections. Ideally I want a 'for loop' that would look something like this:

for (i=0; i <= param.length; i++)
{
this.queryText=this.queryText.replaceAll(getColumnName(i+1), param[i]);}

I've tried the above, even without the 'for loop' and hard coding the getColumnName to the first column and the param to the first selection but I get an error. What code is needed to make this work?
Re: Replace Data Set Columns Dynamically Based on Parameter [message #665396 is a reply to message #665260] Thu, 14 April 2011 14:07 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Meghan,

To do you loop do something like the following (which modifies a query
with in clause).

var parmcount = params["parmorders"].value.length
var whereclause = "";
if( parmcount > 0 ){
whereclause = " where customernumber in ( ";
}
for( i=0; i < parmcount; i++ ){
if( i == 0 ){
whereclause = whereclause + params["parmorders"].value[i];
}else{
whereclause = whereclause + " , " + params["parmorders"].value[i];
}
}
if( parmcount > 0 ){
this.queryText = this.queryText + whereclause + " ) ";
}


Jason

On 4/13/2011 6:12 PM, Meghan wrote:
> I have a multi-select parameter that returns the columns a customer
> would like to see displayed in a table. I have a data set that grabs all
> of those columns (in addition to ones that are potentially NOT
> selected). I need to replace the data with the appropriate columns.
>
> In the beforeOpen I have the following code:
>
> var param=params["paramField"].toString().split(",");
>
> this.queryText = this.queryText.replaceAll("Field1", param[0]);
>
> This works just fine BUT for a static replacement BUT I want to replace
> x number of fields with my x number of parameter selections. Ideally I
> want a 'for loop' that would look something like this:
>
> for (i=0; i <= param.length; i++)
> {
> this.queryText=this.queryText.replaceAll(getColumnName(i+1), param[i]);}
>
> I've tried the above, even without the 'for loop' and hard coding the
> getColumnName to the first column and the param to the first selection
> but I get an error. What code is needed to make this work?
Re: Replace Data Set Columns Dynamically Based on Parameter [message #665447 is a reply to message #665396] Thu, 14 April 2011 16:05 Go to previous messageGo to next message
Meghan Missing name is currently offline Meghan Missing nameFriend
Messages: 16
Registered: November 2010
Junior Member
Jason,

For some reason my reply didn't take so here it is again (I apologize if this shows up as a duplicate). I'm not trying to dynamically limit my data via the WHERE clause based on the parameter that is passed. I actually need to change the SELECT clause dynamically based on the multi-select parameter. I'm using the example report below as a model although this report only uses a single select parameter and replaces only one column. I need to change 'x' number of columns based on the number of fields selected in the multi-select parameter. I would link to the original forum post for this sample report but I can't find it.

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.22" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.6.1.v20100902 Build &lt;2.6.1.v20100915-1750></property>
<property name="units">in</property>
<method name="beforeFactory"><![CDATA[//param = reportContext.getParameterValue("pDateGrouping");
param = params["pDateGrouping"].value;

chart = this.getReportElement("NewChart");
chart.getCategory().getGrouping().setGroupUnit(param);
]]></method>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="layoutPreference">auto layout</property>
<property name="bidiLayoutOrientation">ltr</property>
<parameters>
<scalar-parameter name="parmDateField" id="10">
<text-property name="promptText">Select Date Field</text-property>
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="distinct">true</property>
<simple-property-list name="defaultValue">
<value type="constant">orderdate</value>
</simple-property-list>
<list-property name="selectionList">
<structure>
<property name="value">orderdate</property>
<property name="label">Order Date</property>
</structure>
<structure>
<property name="value">requireddate</property>
<property name="label">Required Date</property>
</structure>
<structure>
<property name="value">shippeddate</property>
<property name="label">Shipped Date</property>
</structure>
</list-property>
<property name="paramType">simple</property>
<property name="controlType">list-box</property>
<property name="mustMatch">true</property>
<property name="fixedOrder">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
<scalar-parameter name="parmStringField" id="12">
<text-property name="promptText">Select String Field</text-property>
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="distinct">true</property>
<simple-property-list name="defaultValue">
<value type="constant">productline</value>
</simple-property-list>
<list-property name="selectionList">
<structure>
<property name="value">country</property>
<property name="label">Country</property>
</structure>
<structure>
<property name="value">productline</property>
<property name="label">Product Line</property>
</structure>
</list-property>
<property name="paramType">simple</property>
<property name="controlType">list-box</property>
<property name="mustMatch">true</property>
<property name="fixedOrder">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
<scalar-parameter name="pStatus" id="13">
<text-property name="promptText">Select Order Status</text-property>
<property name="valueType">dynamic</property>
<property name="dataSetName">StatusList</property>
<expression name="valueExpr">dataSetRow["STATUS"]</expression>
<property name="dataType">string</property>
<property name="distinct">true</property>
<simple-property-list name="defaultValue">
<value type="constant">Shipped</value>
</simple-property-list>
<property name="paramType">simple</property>
<property name="controlType">list-box</property>
<property name="mustMatch">true</property>
<property name="fixedOrder">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
<scalar-parameter name="pFromDate" id="37">
<text-property name="promptText">From Date</text-property>
<property name="valueType">static</property>
<property name="dataType">date</property>
<property name="distinct">true</property>
<simple-property-list name="defaultValue">
<value type="constant">2000-01-01</value>
</simple-property-list>
<property name="paramType">simple</property>
<property name="controlType">text-box</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
<scalar-parameter name="pToDate" id="38">
<text-property name="promptText">To Date</text-property>
<property name="valueType">static</property>
<property name="dataType">date</property>
<property name="distinct">true</property>
<simple-property-list name="defaultValue">
<value type="constant">2009-01-01</value>
</simple-property-list>
<property name="paramType">simple</property>
<property name="controlType">text-box</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
<scalar-parameter name="pDateGrouping" id="9">
<text-property name="promptText">Select Date Grouping</text-property>
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="distinct">true</property>
<simple-property-list name="defaultValue">
<value type="constant">Years</value>
</simple-property-list>
<list-property name="selectionList">
<structure>
<property name="value">Years</property>
<property name="label">Years</property>
</structure>
<structure>
<property name="value">Quarters</property>
<property name="label">Quarters</property>
</structure>
<structure>
<property name="value">Months</property>
<property name="label">Months</property>
</structure>
<structure>
<property name="value">Days</property>
<property name="label">Days</property>
</structure>
</list-property>
<property name="paramType">simple</property>
<property name="controlType">list-box</property>
<property name="mustMatch">true</property>
<property name="fixedOrder">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
<scalar-parameter name="pShowTable" id="140">
<text-property name="promptText">Show Table?</text-property>
<property name="valueType">static</property>
<property name="dataType">boolean</property>
<property name="distinct">true</property>
<simple-property-list name="defaultValue">
<value type="constant">true</value>
</simple-property-list>
<property name="paramType">simple</property>
<property name="controlType">check-box</property>
<structure name="format"/>
</scalar-parameter>
</parameters>
<data-sources>
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="ClassicModelsSource" id="7">
<property name="odaDriverClass">org.eclipse.birt.report.data.oda.sampledb.Driver </property>
<property name="odaURL">jdbc:classicmodels:sampledb</property>
<property name="odaUser">ClassicModels</property>
<property name="OdaConnProfileName"></property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet " name="StatusList" id="8">
<list-property name="columnHints">
<structure>
<property name="columnName">STATUS</property>
<text-property name="displayName">STATUS</text-property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">STATUS</property>
<property name="dataType">string</property>
</structure>
</list-property>
</structure>
<property name="dataSource">ClassicModelsSource</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">STATUS</property>
<property name="nativeName">STATUS</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select distinct status
from orders]]></xml-property>
<xml-property name="designerValues"><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<model:DesignValues xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design" xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
<Version>1.0</Version>
<design:ResultSets derivedMetaData="true">
<design:resultSetDefinitions>
<design:resultSetColumns>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>STATUS</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>STATUS</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>STATUS</design:label>
<design:formattingHints>
<design:displaySize>15</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>]]></xml-property>
</oda-data-set>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet " name="DynamicDataSet" id="11">
<list-property name="columnHints">
<structure>
<property name="columnName">DATEFACT</property>
<text-property name="displayName">DATEFACT</text-property>
</structure>
<structure>
<property name="columnName">STRINGFACT</property>
<text-property name="displayName">STRINGFACT</text-property>
</structure>
<structure>
<property name="columnName">ITEMCOUNT</property>
<text-property name="displayName">ITEMCOUNT</text-property>
</structure>
</list-property>
<list-property name="parameters">
<structure>
<property name="name">param_1</property>
<property name="paramName">pStatus</property>
<property name="nativeName"></property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
<property name="position">1</property>
<property name="isOptional">true</property>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">param_2</property>
<property name="paramName">pFromDate</property>
<property name="nativeName"></property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
<property name="position">2</property>
<property name="isOptional">true</property>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
<structure>
<property name="name">param_3</property>
<property name="paramName">pToDate</property>
<property name="nativeName"></property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
<property name="position">3</property>
<property name="isOptional">true</property>
<property name="allowNull">true</property>
<property name="isInput">true</property>
<property name="isOutput">false</property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">DATEFACT</property>
<property name="dataType">date</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">STRINGFACT</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">ITEMCOUNT</property>
<property name="dataType">integer</property>
</structure>
</list-property>
</structure>
<method name="beforeOpen"><![CDATA[this.queryText = this.queryText.replaceAll("productline",params["parmStringField "].value);
this.queryText = this.queryText.replaceAll("orderdate",params["parmDateField "].value);

]]></method>
<property name="dataSource">ClassicModelsSource</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">DATEFACT</property>
<property name="nativeName">DATEFACT</property>
<property name="dataType">date</property>
<property name="nativeDataType">91</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">STRINGFACT</property>
<property name="nativeName">STRINGFACT</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">ITEMCOUNT</property>
<property name="nativeName">ITEMCOUNT</property>
<property name="dataType">integer</property>
<property name="nativeDataType">4</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select orderdate as DateFact, productline as StringFact, count(*) as ItemCount
from customers, orders, orderdetails, products
where customers.customernumber = orders.customernumber
and orders.ordernumber = orderdetails.ordernumber
and orderdetails.productcode = products.productcode
and status = ?
and orderdate between ? and ?
Group By orderdate, productline]]></xml-property>
<xml-property name="designerValues"><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<model:DesignValues xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design" xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
<Version>1.0</Version>
<design:DataSetParameters>
<design:parameterDefinitions>
<design:inOutMode>In</design:inOutMode>
<design:attributes>
<design:name></design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>15</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:inputAttributes>
<design:elementAttributes>
<design:optional>true</design:optional>
</design:elementAttributes>
</design:inputAttributes>
</design:parameterDefinitions>
<design:parameterDefinitions>
<design:inOutMode>In</design:inOutMode>
<design:attributes>
<design:name></design:name>
<design:position>2</design:position>
<design:nativeDataTypeCode>91</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:inputAttributes>
<design:elementAttributes>
<design:optional>true</design:optional>
</design:elementAttributes>
</design:inputAttributes>
</design:parameterDefinitions>
<design:parameterDefinitions>
<design:inOutMode>In</design:inOutMode>
<design:attributes>
<design:name></design:name>
<design:position>3</design:position>
<design:nativeDataTypeCode>91</design:nativeDataTypeCode>
<design:precision>10</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
</design:attributes>
<design:inputAttributes>
<design:elementAttributes>
<design:optional>true</design:optional>
</design:elementAttributes>
</design:inputAttributes>
</design:parameterDefinitions>
</design:DataSetParameters>
</model:DesignValues>]]></xml-property>
</oda-data-set>
</data-sets>
<cubes>
<tabular-cube name="Data Cube" id="41">
<property name="dimensions">
<tabular-dimension name="Group" id="44">
<property name="isTimeType">true</property>
<property name="defaultHierarchy">NewTabularHierarchy</property>
<property name="hierarchies">
<tabular-hierarchy name="NewTabularHierarchy" id="45">
<property name="levels">
<tabular-level name="year" id="46">
<property name="dataType">integer</property>
<property name="dateTimeLevelType">year</property>
<list-property name="attributes">
<structure>
<property name="name">DateTime</property>
<property name="dataType">date-time</property>
</structure>
</list-property>
<property name="columnName">DATEFACT</property>
</tabular-level>
<tabular-level name="quarter" id="47">
<property name="dataType">integer</property>
<property name="dateTimeLevelType">quarter</property>
<list-property name="attributes">
<structure>
<property name="name">DateTime</property>
<property name="dataType">date-time</property>
</structure>
</list-property>
<property name="columnName">DATEFACT</property>
</tabular-level>
<tabular-level name="month" id="48">
<property name="dataType">integer</property>
<property name="dateTimeLevelType">month</property>
<list-property name="attributes">
<structure>
<property name="name">DateTime</property>
<property name="dataType">date-time</property>
</structure>
</list-property>
<property name="columnName">DATEFACT</property>
</tabular-level>
<tabular-level name="day-of-year" id="49">
<property name="dataType">integer</property>
<property name="dateTimeLevelType">day-of-year</property>
<list-property name="attributes">
<structure>
<property name="name">DateTime</property>
<property name="dataType">date-time</property>
</structure>
</list-property>
<property name="columnName">DATEFACT</property>
</tabular-level>
</property>
</tabular-hierarchy>
</property>
</tabular-dimension>
<tabular-dimension name="Group1" id="57">
<property name="defaultHierarchy">NewTabularHierarchy1</property>
<property name="hierarchies">
<tabular-hierarchy name="NewTabularHierarchy1" id="58">
<property name="levels">
<tabular-level name="STRINGFACT" id="59">
<property name="dataType">string</property>
<property name="levelType">dynamic</property>
<property name="columnName">STRINGFACT</property>
<expression name="displayColumnName">params["parmStringField"].value </expression>
</tabular-level>
</property>
</tabular-hierarchy>
</property>
</tabular-dimension>
</property>
<property name="measureGroups">
<tabular-measure-group name="Summary Field" id="42">
<property name="measures">
<tabular-measure name="ITEMCOUNT" id="43">
<expression name="measureExpression">dataSetRow["ITEMCOUNT"]</expression >
<property name="dataType">integer</property>
</tabular-measure>
</property>
</tabular-measure-group>
</property>
<property name="dataSet">DynamicDataSet</property>
</tabular-cube>
</cubes>
<styles>
<style name="report" id="4">
<property name="fontFamily">"Verdana"</property>
<property name="fontSize">10pt</property>
</style>
<style name="crosstab-cell" id="5">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="crosstab" id="6">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
</styles>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<property name="topMargin">0.25in</property>
<property name="leftMargin">0.25in</property>
<property name="bottomMargin">0.25in</property>
<property name="rightMargin">0.25in</property>
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
<body>
<extended-item extensionName="Chart" name="NewChart" id="39">
<xml-property name="xmlRepresentation"><![CDATA[<model:ChartWithAxes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:attribute="http://www.birt.eclipse.org/ChartModelAttribute" xmlns:data="http://www.birt.eclipse.org/ChartModelData" xmlns:layout="http://www.birt.eclipse.org/ChartModelLayout" xmlns:model="http://www.birt.eclipse.org/ChartModel" xmlns:type="http://www.birt.eclipse.org/ChartModelType">
<Type>Bar Chart</Type>
<SubType>Stacked</SubType>
<Block>
<Children xsi:type="layout:TitleBlock">
<Bounds>
<Left>0.0</Left>
<Top>0.0</Top>
<Width>0.0</Width>
<Height>0.0</Height>
</Bounds>
<Insets>
<Top>3.0</Top>
<Left>3.0</Left>
<Bottom>3.0</Bottom>
<Right>3.0</Right>
</Insets>
<Row>-1</Row>
<Column>-1</Column>
<Rowspan>-1</Rowspan>
<Columnspan>-1</Columnspan>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
<Visible>false</Visible>
</Outline>
<Visible>true</Visible>
<Label>
<Caption>
<Value>Bar Chart Title</Value>
<Font>
<Size>16.0</Size>
<Bold>true</Bold>
<Alignment>
<horizontalAlignment>Center</horizontalAlignment>
<verticalAlignment>Center</verticalAlignment>
</Alignment>
</Font>
</Caption>
<Background xsi:type="attribute:ColorDefinition">
<Transparency>0</Transparency>
<Red>255</Red>
<Green>255</Green>
<Blue>255</Blue>
</Background>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
</Outline>
<Insets>
<Top>0.0</Top>
<Left>2.0</Left>
<Bottom>0.0</Bottom>
<Right>3.0</Right>
</Insets>
<Visible>true</Visible>
</Label>
</Children>
<Children xsi:type="layout:Plot">
<Bounds>
<Left>0.0</Left>
<Top>0.0</Top>
<Width>0.0</Width>
<Height>0.0</Height>
</Bounds>
<Insets>
<Top>3.0</Top>
<Left>3.0</Left>
<Bottom>3.0</Bottom>
<Right>3.0</Right>
</Insets>
<Row>-1</Row>
<Column>-1</Column>
<Rowspan>-1</Rowspan>
<Columnspan>-1</Columnspan>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
<Visible>false</Visible>
</Outline>
<Visible>true</Visible>
<HorizontalSpacing>5</HorizontalSpacing>
<VerticalSpacing>5</VerticalSpacing>
<ClientArea>
<Outline>
<Style>Solid</Style>
<Thickness>0</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
<Visible>false</Visible>
</Outline>
<Insets>
<Top>0.0</Top>
<Left>0.0</Left>
<Bottom>0.0</Bottom>
<Right>0.0</Right>
</Insets>
</ClientArea>
</Children>
<Children xsi:type="layout:Legend">
<Bounds>
<Left>0.0</Left>
<Top>0.0</Top>
<Width>0.0</Width>
<Height>0.0</Height>
</Bounds>
<Insets>
<Top>3.0</Top>
<Left>3.0</Left>
<Bottom>3.0</Bottom>
<Right>3.0</Right>
</Insets>
<Row>-1</Row>
<Column>-1</Column>
<Rowspan>-1</Rowspan>
<Columnspan>-1</Columnspan>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
<Visible>false</Visible>
</Outline>
<Visible>true</Visible>
<ClientArea>
<Outline>
<Style>Solid</Style>
<Thickness>0</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
<Visible>false</Visible>
</Outline>
<Insets>
<Top>2.0</Top>
<Left>2.0</Left>
<Bottom>2.0</Bottom>
<Right>2.0</Right>
</Insets>
</ClientArea>
<Text>
<Value></Value>
<Font>
<Size>8.0</Size>
<Alignment/>
</Font>
</Text>
<Orientation>Vertical</Orientation>
<Direction>Top_Bottom</Direction>
<Separator>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
<Visible>true</Visible>
</Separator>
<Position>Right</Position>
<ItemType>Series</ItemType>
<Title>
<Caption>
<Value></Value>
<Font>
<Alignment/>
</Font>
</Caption>
<Background xsi:type="attribute:ColorDefinition">
<Transparency>0</Transparency>
<Red>255</Red>
<Green>255</Green>
<Blue>255</Blue>
</Background>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
<Visible>false</Visible>
</Outline>
<Insets>
<Top>0.0</Top>
<Left>2.0</Left>
<Bottom>0.0</Bottom>
<Right>3.0</Right>
</Insets>
<Visible>false</Visible>
</Title>
<TitlePosition>Above</TitlePosition>
</Children>
<Bounds>
<Left>0.0</Left>
<Top>0.0</Top>
<Width>568.5</Width>
<Height>314.25</Height>
</Bounds>
<Insets>
<Top>3.0</Top>
<Left>3.0</Left>
<Bottom>3.0</Bottom>
<Right>3.0</Right>
</Insets>
<Row>-1</Row>
<Column>-1</Column>
<Rowspan>-1</Rowspan>
<Columnspan>-1</Columnspan>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
<Visible>false</Visible>
</Outline>
<Background xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>255</Red>
<Green>255</Green>
<Blue>255</Blue>
</Background>
<Visible>true</Visible>
</Block>
<Dimension>Two_Dimensional</Dimension>
<Script>
function beforeGeneration(chart, icsc)

{


importPackage( Packages.org.eclipse.birt.chart.model.attribute.impl );
importPackage( Packages.org.eclipse.birt.chart.model.attribute );

xAxis = chart.getBaseAxes()[0];

if ( xAxis.getType() == AxisType.DATE_TIME_LITERAL){
var groupparm = icsc.getExternalContext().getScriptable().getParameterValue( &quot;pDateGrouping&quot;);
if( groupparm == &quot;Days&quot;){
xAxis.setFormatSpecifier( JavaDateFormatSpecifierImpl.create(&quot;MM/dd/yyyy& quot;) );
}
else if( groupparm == &quot;Years&quot; ){
xAxis.setFormatSpecifier( JavaDateFormatSpecifierImpl.create(&quot;yyyy&quot;) );
}
else if( groupparm == &quot;Months&quot; ){
xAxis.setFormatSpecifier( JavaDateFormatSpecifierImpl.create(&quot;MM/yy&quot; ) );
}
else if( groupparm == &quot;Quarters&quot; ){
xAxis.setFormatSpecifier( JavaDateFormatSpecifierImpl.create(&quot;'Q'Q/yy&quo t;) );
}

}

}

</Script>
<Units>Points</Units>
<SeriesThickness>10.0</SeriesThickness>
<SampleData>
<BaseSampleData>
<DataSetRepresentation>01/05/2000,02/01/2000,04/12/2000,03/12/2000,02/29/2000 </DataSetRepresentation>
</BaseSampleData>
<OrthogonalSampleData>
<DataSetRepresentation>5,4,12</DataSetRepresentation>
<SeriesDefinitionIndex>0</SeriesDefinitionIndex>
</OrthogonalSampleData>
</SampleData>
<Interactivity/>
<EmptyMessage>
<Caption>
<Value></Value>
<Font>
<Alignment/>
</Font>
</Caption>
<Background xsi:type="attribute:ColorDefinition">
<Transparency>0</Transparency>
<Red>255</Red>
<Green>255</Green>
<Blue>255</Blue>
</Background>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
</Outline>
<Insets>
<Top>0.0</Top>
<Left>2.0</Left>
<Bottom>0.0</Bottom>
<Right>3.0</Right>
</Insets>
<Visible>false</Visible>
</EmptyMessage>
<Axes>
<Type>DateTime</Type>
<Title>
<Caption>
<Value>X-Axis Title</Value>
<Font>
<Size>14.0</Size>
<Bold>true</Bold>
<Alignment>
<horizontalAlignment>Center</horizontalAlignment>
<verticalAlignment>Center</verticalAlignment>
</Alignment>
</Font>
</Caption>
<Background xsi:type="attribute:ColorDefinition">
<Transparency>0</Transparency>
<Red>255</Red>
<Green>255</Green>
<Blue>255</Blue>
</Background>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
</Outline>
<Insets>
<Top>0.0</Top>
<Left>2.0</Left>
<Bottom>0.0</Bottom>
<Right>3.0</Right>
</Insets>
<Visible>false</Visible>
</Title>
<TitlePosition>Below</TitlePosition>
<AssociatedAxes>
<Type>Linear</Type>
<Title>
<Caption>
<Value>Y-Axis Title</Value>
<Font>
<Size>14.0</Size>
<Bold>true</Bold>
<Alignment>
<horizontalAlignment>Center</horizontalAlignment>
<verticalAlignment>Center</verticalAlignment>
</Alignment>
<Rotation>90.0</Rotation>
</Font>
</Caption>
<Background xsi:type="attribute:ColorDefinition">
<Transparency>0</Transparency>
<Red>255</Red>
<Green>255</Green>
<Blue>255</Blue>
</Background>
<Outline>
<Style>Solid</Style>
<Thickness>1</Thickness>
<Color>
<Transparency>255</Transparency>
<Red>0</Red>
<Green>0</Green>
<Blue>0</Blue>
</Color>
</Outline>
<Insets>
<Top>0.0</Top>
<Left>2.0</Left>
<Bottom>0.0</Bottom>
<Right>3.0</Right>
</Insets>
<Visible>false</Visible>
</Title>
<TitlePosition>Left</TitlePosition>
<SeriesDefinitions>
<Query>
<Definition>row[&quot;STRINGFACT&quot;]</Definition >
</Query>
<SeriesPalette>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>80</Red>
<Green>166</Green>
<Blue>218</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>242</Red>
<Green>88</Green>
<Blue>106</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>232</Red>
<Green>172</Green>
<Blue>57</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>128</Red>
<Green>255</Green>
<Blue>128</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>64</Red>
<Green>128</Green>
<Blue>128</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>128</Red>
<Green>128</Green>
<Blue>192</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>170</Red>
<Green>85</Green>
<Blue>85</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>128</Red>
<Green>128</Green>
<Blue>0</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>192</Red>
<Green>192</Green>
<Blue>192</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>255</Red>
<Green>255</Green>
<Blue>128</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>128</Red>
<Green>192</Green>
<Blue>128</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>7</Red>
<Green>146</Green>
<Blue>94</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>0</Red>
<Green>128</Green>
<Blue>255</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>255</Red>
<Green>128</Green>
<Blue>192</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>0</Red>
<Green>255</Green>
<Blue>255</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>255</Red>
<Green>128</Green>
<Blue>128</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>0</Red>
<Green>128</Green>
<Blue>192</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>128</Red>
<Green>128</Green>
<Blue>192</Blue>
</Entries>
<Entries xsi:type="attribute:ColorDefinition">
<Transparency>255</Transparency>
<Red>255</Red>
<Green>0</Green><
Re: Replace Data Set Columns Dynamically Based on Parameter [message #665462 is a reply to message #665447] Thu, 14 April 2011 16:24 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Can you email me the report? It did not come through all the way.
jasonweathersby at windstream dot net.

Jason

On 4/14/2011 12:05 PM, Meghan wrote:
> Jason,
>
> For some reason my reply didn't take so here it is again (I apologize if
> this shows up as a duplicate). I'm not trying to dynamically limit my
> data via the WHERE clause based on the parameter that is passed. I
> actually need to change the SELECT clause dynamically based on the
> multi-select parameter. I'm using the example report below as a model
> although this report only uses a single select parameter and replaces
> only one column. I need to change 'x' number of columns based on the
> number of fields selected in the multi-select parameter. I would link to
> the original forum post for this sample report but I can't find it.
>
> <?xml version="1.0" encoding="UTF-8"?>
> <report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.22"
> id="1">
> <property name="createdBy">Eclipse BIRT Designer Version 2.6.1.v20100902
> Build <2.6.1.v20100915-1750></property>
> <property name="units">in</property>
> <method name="beforeFactory"><![CDATA[//param =
> reportContext.getParameterValue("pDateGrouping");
> param = params["pDateGrouping"].value;
>
> chart = this.getReportElement("NewChart");
> chart.getCategory().getGrouping().setGroupUnit(param);
> ]]></method>
> <property name="iconFile">/templates/blank_report.gif</property>
> <property name="layoutPreference">auto layout</property>
> <property name="bidiLayoutOrientation">ltr</property>
> <parameters>
> <scalar-parameter name="parmDateField" id="10">
> <text-property name="promptText">Select Date Field</text-property>
> <property name="valueType">static</property>
> <property name="dataType">string</property>
> <property name="distinct">true</property>
> <simple-property-list name="defaultValue">
> <value type="constant">orderdate</value>
> </simple-property-list>
> <list-property name="selectionList">
> <structure>
> <property name="value">orderdate</property>
> <property name="label">Order Date</property>
> </structure>
> <structure>
> <property name="value">requireddate</property>
> <property name="label">Required Date</property>
> </structure>
> <structure>
> <property name="value">shippeddate</property>
> <property name="label">Shipped Date</property>
> </structure>
> </list-property>
> <property name="paramType">simple</property>
> <property name="controlType">list-box</property>
> <property name="mustMatch">true</property>
> <property name="fixedOrder">true</property>
> <structure name="format">
> <property name="category">Unformatted</property>
> </structure>
> </scalar-parameter>
> <scalar-parameter name="parmStringField" id="12">
> <text-property name="promptText">Select String Field</text-property>
> <property name="valueType">static</property>
> <property name="dataType">string</property>
> <property name="distinct">true</property>
> <simple-property-list name="defaultValue">
> <value type="constant">productline</value>
> </simple-property-list>
> <list-property name="selectionList">
> <structure>
> <property name="value">country</property>
> <property name="label">Country</property>
> </structure>
> <structure>
> <property name="value">productline</property>
> <property name="label">Product Line</property>
> </structure>
> </list-property>
> <property name="paramType">simple</property>
> <property name="controlType">list-box</property>
> <property name="mustMatch">true</property>
> <property name="fixedOrder">true</property>
> <structure name="format">
> <property name="category">Unformatted</property>
> </structure>
> </scalar-parameter>
> <scalar-parameter name="pStatus" id="13">
> <text-property name="promptText">Select Order Status</text-property>
> <property name="valueType">dynamic</property>
> <property name="dataSetName">StatusList</property>
> <expression name="valueExpr">dataSetRow["STATUS"]</expression>
> <property name="dataType">string</property>
> <property name="distinct">true</property>
> <simple-property-list name="defaultValue">
> <value type="constant">Shipped</value>
> </simple-property-list>
> <property name="paramType">simple</property>
> <property name="controlType">list-box</property>
> <property name="mustMatch">true</property>
> <property name="fixedOrder">true</property>
> <structure name="format">
> <property name="category">Unformatted</property>
> </structure>
> </scalar-parameter>
> <scalar-parameter name="pFromDate" id="37">
> <text-property name="promptText">From Date</text-property>
> <property name="valueType">static</property>
> <property name="dataType">date</property>
> <property name="distinct">true</property>
> <simple-property-list name="defaultValue">
> <value type="constant">2000-01-01</value>
> </simple-property-list>
> <property name="paramType">simple</property>
> <property name="controlType">text-box</property>
> <structure name="format">
> <property name="category">Unformatted</property>
> </structure>
> </scalar-parameter>
> <scalar-parameter name="pToDate" id="38">
> <text-property name="promptText">To Date</text-property>
> <property name="valueType">static</property>
> <property name="dataType">date</property>
> <property name="distinct">true</property>
> <simple-property-list name="defaultValue">
> <value type="constant">2009-01-01</value>
> </simple-property-list>
> <property name="paramType">simple</property>
> <property name="controlType">text-box</property>
> <structure name="format">
> <property name="category">Unformatted</property>
> </structure>
> </scalar-parameter>
> <scalar-parameter name="pDateGrouping" id="9">
> <text-property name="promptText">Select Date Grouping</text-property>
> <property name="valueType">static</property>
> <property name="dataType">string</property>
> <property name="distinct">true</property>
> <simple-property-list name="defaultValue">
> <value type="constant">Years</value>
> </simple-property-list>
> <list-property name="selectionList">
> <structure>
> <property name="value">Years</property>
> <property name="label">Years</property>
> </structure>
> <structure>
> <property name="value">Quarters</property>
> <property name="label">Quarters</property>
> </structure>
> <structure>
> <property name="value">Months</property>
> <property name="label">Months</property>
> </structure>
> <structure>
> <property name="value">Days</property>
> <property name="label">Days</property>
> </structure>
> </list-property>
> <property name="paramType">simple</property>
> <property name="controlType">list-box</property>
> <property name="mustMatch">true</property>
> <property name="fixedOrder">true</property>
> <structure name="format">
> <property name="category">Unformatted</property>
> </structure>
> </scalar-parameter>
> <scalar-parameter name="pShowTable" id="140">
> <text-property name="promptText">Show Table?</text-property>
> <property name="valueType">static</property>
> <property name="dataType">boolean</property>
> <property name="distinct">true</property>
> <simple-property-list name="defaultValue">
> <value type="constant">true</value>
> </simple-property-list>
> <property name="paramType">simple</property>
> <property name="controlType">check-box</property>
> <structure name="format"/>
> </scalar-parameter>
> </parameters>
> <data-sources>
> <oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc"
> name="ClassicModelsSource" id="7">
> <property
> name="odaDriverClass">org.eclipse.birt.report.data.oda.sampledb.Driver
> </property>
> <property name="odaURL">jdbc:classicmodels:sampledb</property>
> <property name="odaUser">ClassicModels</property>
> <property name="OdaConnProfileName"></property>
> </oda-data-source>
> </data-sources>
> <data-sets>
> <oda-data-set
> extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
> name="StatusList" id="8">
> <list-property name="columnHints">
> <structure>
> <property name="columnName">STATUS</property>
> <text-property name="displayName">STATUS</text-property>
> </structure>
> </list-property>
> <structure name="cachedMetaData">
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">STATUS</property>
> <property name="dataType">string</property>
> </structure>
> </list-property>
> </structure>
> <property name="dataSource">ClassicModelsSource</property>
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">STATUS</property>
> <property name="nativeName">STATUS</property>
> <property name="dataType">string</property>
> <property name="nativeDataType">12</property>
> </structure>
> </list-property>
> <xml-property name="queryText"><![CDATA[select distinct status
> from orders]]></xml-property>
> <xml-property name="designerValues"><![CDATA[<?xml version="1.0"
> encoding="UTF-8"?>
> <model:DesignValues
> xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design"
> xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
> <Version>1.0</Version>
> <design:ResultSets derivedMetaData="true">
> <design:resultSetDefinitions>
> <design:resultSetColumns>
> <design:resultColumnDefinitions>
> <design:attributes>
> <design:name>STATUS</design:name>
> <design:position>1</design:position>
> <design:nativeDataTypeCode>12</design:nativeDataTypeCode>
> <design:precision>15</design:precision>
> <design:scale>0</design:scale>
> <design:nullability>Nullable</design:nullability>
> <design:uiHints>
> <design:displayName>STATUS</design:displayName>
> </design:uiHints>
> </design:attributes>
> <design:usageHints>
> <design:label>STATUS</design:label>
> <design:formattingHints>
> <design:displaySize>15</design:displaySize>
> </design:formattingHints>
> </design:usageHints>
> </design:resultColumnDefinitions>
> </design:resultSetColumns>
> </design:resultSetDefinitions>
> </design:ResultSets>
> </model:DesignValues>]]></xml-property>
> </oda-data-set>
> <oda-data-set
> extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
> name="DynamicDataSet" id="11">
> <list-property name="columnHints">
> <structure>
> <property name="columnName">DATEFACT</property>
> <text-property name="displayName">DATEFACT</text-property>
> </structure>
> <structure>
> <property name="columnName">STRINGFACT</property>
> <text-property name="displayName">STRINGFACT</text-property>
> </structure>
> <structure>
> <property name="columnName">ITEMCOUNT</property>
> <text-property name="displayName">ITEMCOUNT</text-property>
> </structure>
> </list-property>
> <list-property name="parameters">
> <structure>
> <property name="name">param_1</property>
> <property name="paramName">pStatus</property>
> <property name="nativeName"></property>
> <property name="dataType">string</property>
> <property name="nativeDataType">12</property>
> <property name="position">1</property>
> <property name="isOptional">true</property>
> <property name="allowNull">true</property>
> <property name="isInput">true</property>
> <property name="isOutput">false</property>
> </structure>
> <structure>
> <property name="name">param_2</property>
> <property name="paramName">pFromDate</property>
> <property name="nativeName"></property>
> <property name="dataType">date</property>
> <property name="nativeDataType">91</property>
> <property name="position">2</property>
> <property name="isOptional">true</property>
> <property name="allowNull">true</property>
> <property name="isInput">true</property>
> <property name="isOutput">false</property>
> </structure>
> <structure>
> <property name="name">param_3</property>
> <property name="paramName">pToDate</property>
> <property name="nativeName"></property>
> <property name="dataType">date</property>
> <property name="nativeDataType">91</property>
> <property name="position">3</property>
> <property name="isOptional">true</property>
> <property name="allowNull">true</property>
> <property name="isInput">true</property>
> <property name="isOutput">false</property>
> </structure>
> </list-property>
> <structure name="cachedMetaData">
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">DATEFACT</property>
> <property name="dataType">date</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property name="name">STRINGFACT</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">3</property>
> <property name="name">ITEMCOUNT</property>
> <property name="dataType">integer</property>
> </structure>
> </list-property>
> </structure>
> <method name="beforeOpen"><![CDATA[this.queryText =
> this.queryText.replaceAll("productline",params["parmStringField "].value);
> this.queryText =
> this.queryText.replaceAll("orderdate",params["parmDateField "].value);
>
> ]]></method>
> <property name="dataSource">ClassicModelsSource</property>
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">DATEFACT</property>
> <property name="nativeName">DATEFACT</property>
> <property name="dataType">date</property>
> <property name="nativeDataType">91</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property name="name">STRINGFACT</property>
> <property name="nativeName">STRINGFACT</property>
> <property name="dataType">string</property>
> <property name="nativeDataType">12</property>
> </structure>
> <structure>
> <property name="position">3</property>
> <property name="name">ITEMCOUNT</property>
> <property name="nativeName">ITEMCOUNT</property>
> <property name="dataType">integer</property>
> <property name="nativeDataType">4</property>
> </structure>
> </list-property>
> <xml-property name="queryText"><![CDATA[select orderdate as DateFact,
> productline as StringFact, count(*) as ItemCount
> from customers, orders, orderdetails, products
> where customers.customernumber = orders.customernumber
> and orders.ordernumber = orderdetails.ordernumber
> and orderdetails.productcode = products.productcode
> and status = ?
> and orderdate between ? and ?
> Group By orderdate, productline]]></xml-property>
> <xml-property name="designerValues"><![CDATA[<?xml version="1.0"
> encoding="UTF-8"?>
> <model:DesignValues
> xmlns:design="http://www.eclipse.org/datatools/connectivity/oda/design"
> xmlns:model="http://www.eclipse.org/birt/report/model/adapter/odaModel">
> <Version>1.0</Version>
> <design:DataSetParameters>
> <design:parameterDefinitions>
> <design:inOutMode>In</design:inOutMode>
> <design:attributes>
> <design:name></design:name>
> <design:position>1</design:position>
> <design:nativeDataTypeCode>12</design:nativeDataTypeCode>
> <design:precision>15</design:precision>
> <design:scale>0</design:scale>
> <design:nullability>Nullable</design:nullability>
> </design:attributes>
> <design:inputAttributes>
> <design:elementAttributes>
> <design:optional>true</design:optional>
> </design:elementAttributes>
> </design:inputAttributes>
> </design:parameterDefinitions>
> <design:parameterDefinitions>
> <design:inOutMode>In</design:inOutMode>
> <design:attributes>
> <design:name></design:name>
> <design:position>2</design:position>
> <design:nativeDataTypeCode>91</design:nativeDataTypeCode>
> <design:precision>10</design:precision>
> <design:scale>0</design:scale>
> <design:nullability>Nullable</design:nullability>
> </design:attributes>
> <design:inputAttributes>
> <design:elementAttributes>
> <design:optional>true</design:optional>
> </design:elementAttributes>
> </design:inputAttributes>
> </design:parameterDefinitions>
> <design:parameterDefinitions>
> <design:inOutMode>In</design:inOutMode>
> <design:attributes>
> <design:name></design:name>
> <design:position>3</design:position>
> <design:nativeDataTypeCode>91</design:nativeDataTypeCode>
> <design:precision>10</design:precision>
> <design:scale>0</design:scale>
> <design:nullability>Nullable</design:nullability>
> </design:attributes>
> <design:inputAttributes>
> <design:elementAttributes>
> <design:optional>true</design:optional>
> </design:elementAttributes>
> </design:inputAttributes>
> </design:parameterDefinitions>
> </design:DataSetParameters>
> </model:DesignValues>]]></xml-property>
> </oda-data-set>
> </data-sets>
> <cubes>
> <tabular-cube name="Data Cube" id="41">
> <property name="dimensions">
> <tabular-dimension name="Group" id="44">
> <property name="isTimeType">true</property>
> <property name="defaultHierarchy">NewTabularHierarchy</property>
> <property name="hierarchies">
> <tabular-hierarchy name="NewTabularHierarchy" id="45">
> <property name="levels">
> <tabular-level name="year" id="46">
> <property name="dataType">integer</property>
> <property name="dateTimeLevelType">year</property>
> <list-property name="attributes">
> <structure>
> <property name="name">DateTime</property>
> <property name="dataType">date-time</property>
> </structure>
> </list-property>
> <property name="columnName">DATEFACT</property>
> </tabular-level>
> <tabular-level name="quarter" id="47">
> <property name="dataType">integer</property>
> <property name="dateTimeLevelType">quarter</property>
> <list-property name="attributes">
> <structure>
> <property name="name">DateTime</property>
> <property name="dataType">date-time</property>
> </structure>
> </list-property>
> <property name="columnName">DATEFACT</property>
> </tabular-level>
> <tabular-level name="month" id="48">
> <property name="dataType">integer</property>
> <property name="dateTimeLevelType">month</property>
> <list-property name="attributes">
> <structure>
> <property name="name">DateTime</property>
> <property name="dataType">date-time</property>
> </structure>
> </list-property>
> <property name="columnName">DATEFACT</property>
> </tabular-level>
> <tabular-level name="day-of-year" id="49">
> <property name="dataType">integer</property>
> <property name="dateTimeLevelType">day-of-year</property>
> <list-property name="attributes">
> <structure>
> <property name="name">DateTime</property>
> <property name="dataType">date-time</property>
> </structure>
> </list-property>
> <property name="columnName">DATEFACT</property>
> </tabular-level>
> </property>
> </tabular-hierarchy>
> </property>
> </tabular-dimension>
> <tabular-dimension name="Group1" id="57">
> <property name="defaultHierarchy">NewTabularHierarchy1</property>
> <property name="hierarchies">
> <tabular-hierarchy name="NewTabularHierarchy1" id="58">
> <property name="levels">
> <tabular-level name="STRINGFACT" id="59">
> <property name="dataType">string</property>
> <property name="levelType">dynamic</property>
> <property name="columnName">STRINGFACT</property>
> <expression name="displayColumnName">params["parmStringField"].value
> </expression>
> </tabular-level>
> </property>
> </tabular-hierarchy>
> </property>
> </tabular-dimension>
> </property>
> <property name="measureGroups">
> <tabular-measure-group name="Summary Field" id="42">
> <property name="measures">
> <tabular-measure name="ITEMCOUNT" id="43">
> <expression name="measureExpression">dataSetRow["ITEMCOUNT"]</expression >
> <property name="dataType">integer</property>
> </tabular-measure>
> </property>
> </tabular-measure-group>
> </property>
> <property name="dataSet">DynamicDataSet</property>
> </tabular-cube>
> </cubes>
> <styles>
> <style name="report" id="4">
> <property name="fontFamily">"Verdana"</property>
> <property name="fontSize">10pt</property>
> </style>
> <style name="crosstab-cell" id="5">
> <property name="borderBottomColor">#CCCCCC</property>
> <property name="borderBottomStyle">solid</property>
> <property name="borderBottomWidth">1pt</property>
> <property name="borderLeftColor">#CCCCCC</property>
> <property name="borderLeftStyle">solid</property>
> <property name="borderLeftWidth">1pt</property>
> <property name="borderRightColor">#CCCCCC</property>
> <property name="borderRightStyle">solid</property>
> <property name="borderRightWidth">1pt</property>
> <property name="borderTopColor">#CCCCCC</property>
> <property name="borderTopStyle">solid</property>
> <property name="borderTopWidth">1pt</property>
> </style>
> <style name="crosstab" id="6">
> <property name="borderBottomColor">#CCCCCC</property>
> <property name="borderBottomStyle">solid</property>
> <property name="borderBottomWidth">1pt</property>
> <property name="borderLeftColor">#CCCCCC</property>
> <property name="borderLeftStyle">solid</property>
> <property name="borderLeftWidth">1pt</property>
> <property name="borderRightColor">#CCCCCC</property>
> <property name="borderRightStyle">solid</property>
> <property name="borderRightWidth">1pt</property>
> <property name="borderTopColor">#CCCCCC</property>
> <property name="borderTopStyle">solid</property>
> <property name="borderTopWidth">1pt</property>
> </style>
> </styles>
> <page-setup>
> <simple-master-page name="Simple MasterPage" id="2">
> <property name="topMargin">0.25in</property>
> <property name="leftMargin">0.25in</property>
> <property name="bottomMargin">0.25in</property>
> <property name="rightMargin">0.25in</property>
> <page-footer>
> <text id="3">
> <property name="contentType">html</property>
> <text-property name="content"><![CDATA[<value-of>new
> Date()</value-of>]]></text-property>
> </text>
> </page-footer>
> </simple-master-page>
> </page-setup>
> <body>
> <extended-item extensionName="Chart" name="NewChart" id="39">
> <xml-property name="xmlRepresentation"><![CDATA[<model:ChartWithAxes
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:attribute="http://www.birt.eclipse.org/ChartModelAttribute"
> xmlns:data="http://www.birt.eclipse.org/ChartModelData"
> xmlns:layout="http://www.birt.eclipse.org/ChartModelLayout"
> xmlns:model="http://www.birt.eclipse.org/ChartModel"
> xmlns:type="http://www.birt.eclipse.org/ChartModelType">
> <Type>Bar Chart</Type>
> <SubType>Stacked</SubType>
> <Block>
> <Children xsi:type="layout:TitleBlock">
> <Bounds>
> <Left>0.0</Left>
> <Top>0.0</Top>
> <Width>0.0</Width>
> <Height>0.0</Height>
> </Bounds>
> <Insets>
> <Top>3.0</Top>
> <Left>3.0</Left>
> <Bottom>3.0</Bottom>
> <Right>3.0</Right>
> </Insets>
> <Row>-1</Row>
> <Column>-1</Column>
> <Rowspan>-1</Rowspan>
> <Columnspan>-1</Columnspan>
> <Outline>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> <Visible>false</Visible>
> </Outline>
> <Visible>true</Visible>
> <Label>
> <Caption>
> <Value>Bar Chart Title</Value>
> <Font>
> <Size>16.0</Size>
> <Bold>true</Bold>
> <Alignment>
> <horizontalAlignment>Center</horizontalAlignment>
> <verticalAlignment>Center</verticalAlignment>
> </Alignment>
> </Font>
> </Caption>
> <Background xsi:type="attribute:ColorDefinition">
> <Transparency>0</Transparency>
> <Red>255</Red>
> <Green>255</Green>
> <Blue>255</Blue>
> </Background>
> <Outline>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> </Outline>
> <Insets>
> <Top>0.0</Top>
> <Left>2.0</Left>
> <Bottom>0.0</Bottom>
> <Right>3.0</Right>
> </Insets>
> <Visible>true</Visible>
> </Label>
> </Children>
> <Children xsi:type="layout:Plot">
> <Bounds>
> <Left>0.0</Left>
> <Top>0.0</Top>
> <Width>0.0</Width>
> <Height>0.0</Height>
> </Bounds>
> <Insets>
> <Top>3.0</Top>
> <Left>3.0</Left>
> <Bottom>3.0</Bottom>
> <Right>3.0</Right>
> </Insets>
> <Row>-1</Row>
> <Column>-1</Column>
> <Rowspan>-1</Rowspan>
> <Columnspan>-1</Columnspan>
> <Outline>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> <Visible>false</Visible>
> </Outline>
> <Visible>true</Visible>
> <HorizontalSpacing>5</HorizontalSpacing>
> <VerticalSpacing>5</VerticalSpacing>
> <ClientArea>
> <Outline>
> <Style>Solid</Style>
> <Thickness>0</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> <Visible>false</Visible>
> </Outline>
> <Insets>
> <Top>0.0</Top>
> <Left>0.0</Left>
> <Bottom>0.0</Bottom>
> <Right>0.0</Right>
> </Insets>
> </ClientArea>
> </Children>
> <Children xsi:type="layout:Legend">
> <Bounds>
> <Left>0.0</Left>
> <Top>0.0</Top>
> <Width>0.0</Width>
> <Height>0.0</Height>
> </Bounds>
> <Insets>
> <Top>3.0</Top>
> <Left>3.0</Left>
> <Bottom>3.0</Bottom>
> <Right>3.0</Right>
> </Insets>
> <Row>-1</Row>
> <Column>-1</Column>
> <Rowspan>-1</Rowspan>
> <Columnspan>-1</Columnspan>
> <Outline>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> <Visible>false</Visible>
> </Outline>
> <Visible>true</Visible>
> <ClientArea>
> <Outline>
> <Style>Solid</Style>
> <Thickness>0</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> <Visible>false</Visible>
> </Outline>
> <Insets>
> <Top>2.0</Top>
> <Left>2.0</Left>
> <Bottom>2.0</Bottom>
> <Right>2.0</Right>
> </Insets>
> </ClientArea>
> <Text>
> <Value></Value>
> <Font>
> <Size>8.0</Size>
> <Alignment/>
> </Font>
> </Text>
> <Orientation>Vertical</Orientation>
> <Direction>Top_Bottom</Direction>
> <Separator>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> <Visible>true</Visible>
> </Separator>
> <Position>Right</Position>
> <ItemType>Series</ItemType>
> <Title>
> <Caption>
> <Value></Value>
> <Font>
> <Alignment/>
> </Font>
> </Caption>
> <Background xsi:type="attribute:ColorDefinition">
> <Transparency>0</Transparency>
> <Red>255</Red>
> <Green>255</Green>
> <Blue>255</Blue>
> </Background>
> <Outline>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> <Visible>false</Visible>
> </Outline>
> <Insets>
> <Top>0.0</Top>
> <Left>2.0</Left>
> <Bottom>0.0</Bottom>
> <Right>3.0</Right>
> </Insets>
> <Visible>false</Visible>
> </Title>
> <TitlePosition>Above</TitlePosition>
> </Children>
> <Bounds>
> <Left>0.0</Left>
> <Top>0.0</Top>
> <Width>568.5</Width>
> <Height>314.25</Height>
> </Bounds>
> <Insets>
> <Top>3.0</Top>
> <Left>3.0</Left>
> <Bottom>3.0</Bottom>
> <Right>3.0</Right>
> </Insets>
> <Row>-1</Row>
> <Column>-1</Column>
> <Rowspan>-1</Rowspan>
> <Columnspan>-1</Columnspan>
> <Outline>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> <Visible>false</Visible>
> </Outline>
> <Background xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>255</Red>
> <Green>255</Green>
> <Blue>255</Blue>
> </Background>
> <Visible>true</Visible>
> </Block>
> <Dimension>Two_Dimensional</Dimension>
> <Script>
> function beforeGeneration(chart, icsc)
>
> {
>
>
> importPackage( Packages.org.eclipse.birt.chart.model.attribute.impl );
> importPackage( Packages.org.eclipse.birt.chart.model.attribute );
>
> xAxis = chart.getBaseAxes()[0];
>
> if ( xAxis.getType() == AxisType.DATE_TIME_LITERAL){
> var groupparm =
> icsc.getExternalContext().getScriptable().getParameterValue(
> "pDateGrouping");
> if( groupparm == "Days"){
> xAxis.setFormatSpecifier(
> JavaDateFormatSpecifierImpl.create("MM/dd/yyyy& quot;) );
> }
> else if( groupparm == "Years" ){
> xAxis.setFormatSpecifier( JavaDateFormatSpecifierImpl.create("yyyy") );
> }
> else if( groupparm == "Months" ){ xAxis.setFormatSpecifier(
> JavaDateFormatSpecifierImpl.create("MM/yy" ) );
> }
> else if( groupparm == "Quarters" ){
> xAxis.setFormatSpecifier(
> JavaDateFormatSpecifierImpl.create("'Q'Q/yy&quo t;) );
> }
> }
>
> }
>
> </Script>
> <Units>Points</Units>
> <SeriesThickness>10.0</SeriesThickness>
> <SampleData>
> <BaseSampleData>
> <DataSetRepresentation>01/05/2000,02/01/2000,04/12/2000,03/12/2000,02/29/2000
> </DataSetRepresentation>
> </BaseSampleData>
> <OrthogonalSampleData>
> <DataSetRepresentation>5,4,12</DataSetRepresentation>
> <SeriesDefinitionIndex>0</SeriesDefinitionIndex>
> </OrthogonalSampleData>
> </SampleData>
> <Interactivity/>
> <EmptyMessage>
> <Caption>
> <Value></Value>
> <Font>
> <Alignment/>
> </Font>
> </Caption>
> <Background xsi:type="attribute:ColorDefinition">
> <Transparency>0</Transparency>
> <Red>255</Red>
> <Green>255</Green>
> <Blue>255</Blue>
> </Background>
> <Outline>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> </Outline>
> <Insets>
> <Top>0.0</Top>
> <Left>2.0</Left>
> <Bottom>0.0</Bottom>
> <Right>3.0</Right>
> </Insets>
> <Visible>false</Visible>
> </EmptyMessage>
> <Axes>
> <Type>DateTime</Type>
> <Title>
> <Caption>
> <Value>X-Axis Title</Value>
> <Font>
> <Size>14.0</Size>
> <Bold>true</Bold>
> <Alignment>
> <horizontalAlignment>Center</horizontalAlignment>
> <verticalAlignment>Center</verticalAlignment>
> </Alignment>
> </Font>
> </Caption>
> <Background xsi:type="attribute:ColorDefinition">
> <Transparency>0</Transparency>
> <Red>255</Red>
> <Green>255</Green>
> <Blue>255</Blue>
> </Background>
> <Outline>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> </Outline>
> <Insets>
> <Top>0.0</Top>
> <Left>2.0</Left>
> <Bottom>0.0</Bottom>
> <Right>3.0</Right>
> </Insets>
> <Visible>false</Visible>
> </Title>
> <TitlePosition>Below</TitlePosition>
> <AssociatedAxes>
> <Type>Linear</Type>
> <Title>
> <Caption>
> <Value>Y-Axis Title</Value>
> <Font>
> <Size>14.0</Size>
> <Bold>true</Bold>
> <Alignment>
> <horizontalAlignment>Center</horizontalAlignment>
> <verticalAlignment>Center</verticalAlignment>
> </Alignment>
> <Rotation>90.0</Rotation>
> </Font>
> </Caption>
> <Background xsi:type="attribute:ColorDefinition">
> <Transparency>0</Transparency>
> <Red>255</Red>
> <Green>255</Green>
> <Blue>255</Blue>
> </Background>
> <Outline>
> <Style>Solid</Style>
> <Thickness>1</Thickness>
> <Color>
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>0</Green>
> <Blue>0</Blue>
> </Color>
> </Outline>
> <Insets>
> <Top>0.0</Top>
> <Left>2.0</Left>
> <Bottom>0.0</Bottom>
> <Right>3.0</Right>
> </Insets>
> <Visible>false</Visible>
> </Title>
> <TitlePosition>Left</TitlePosition>
> <SeriesDefinitions>
> <Query>
> <Definition>row["STRINGFACT"]</Definition >
> </Query>
> <SeriesPalette>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>80</Red>
> <Green>166</Green>
> <Blue>218</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>242</Red>
> <Green>88</Green>
> <Blue>106</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>232</Red>
> <Green>172</Green>
> <Blue>57</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>128</Red>
> <Green>255</Green>
> <Blue>128</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>64</Red>
> <Green>128</Green>
> <Blue>128</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>128</Red>
> <Green>128</Green>
> <Blue>192</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>170</Red>
> <Green>85</Green>
> <Blue>85</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>128</Red>
> <Green>128</Green>
> <Blue>0</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>192</Red>
> <Green>192</Green>
> <Blue>192</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>255</Red>
> <Green>255</Green>
> <Blue>128</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>128</Red>
> <Green>192</Green>
> <Blue>128</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>7</Red>
> <Green>146</Green>
> <Blue>94</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>128</Green>
> <Blue>255</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>255</Red>
> <Green>128</Green>
> <Blue>192</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>255</Green>
> <Blue>255</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>255</Red>
> <Green>128</Green>
> <Blue>128</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>0</Red>
> <Green>128</Green>
> <Blue>192</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>128</Red>
> <Green>128</Green>
> <Blue>192</Blue>
> </Entries>
> <Entries xsi:type="attribute:ColorDefinition">
> <Transparency>255</Transparency>
> <Red>255</Red>
> <Green>0</Green>
Re: Replace Data Set Columns Dynamically Based on Parameter [message #667463 is a reply to message #665462] Thu, 28 April 2011 22:01 Go to previous messageGo to next message
Meghan Missing name is currently offline Meghan Missing nameFriend
Messages: 16
Registered: November 2010
Junior Member
Jason,

So this appears to work 'Sometimes' but not conistently:

//here is my static column array
var staticColumnArray = new Array("Username", "First_Name", "Last_Name", "Email", "City", "State_Province", "Company", "Excluded", "Investor", "Firm", "Organization", "UserTitle", "ClearingFirm", "CredDesired");

//here is my parameter array
var param1=params["paramField1"].toString().split(",");

for (var i=0; i<param1.length; i++)
{this.queryText = this.queryText.replaceAll(staticColumnArray[i], param1[i]);}
Re: Replace Data Set Columns Dynamically Based on Parameter [message #667473 is a reply to message #667463] Thu, 28 April 2011 23:34 Go to previous messageGo to next message
Meghan Missing name is currently offline Meghan Missing nameFriend
Messages: 16
Registered: November 2010
Junior Member
Jason,

Okay, nevermind (maybe). I think I figured it out. Here is the code that I put into the beforeOpen method of the data set

function paramstring(str) {

var staticColumnArray = new Array("Username", "First_Name", "Last_Name", "Email", "City", "State_Province", "Company", "Excluded", "Investor", "Firm", "Organization", "UserTitle", "ClearingFirm", "CredDesired");
var paramArray=params["paramField1"].toString().split(",");
var strOut = "" ;
var strOut2 = "";
for (i=0; i< paramArray.length; i++)
{
param=paramArray[i].substring(0);
paramnum=i+1;

strOut += param + " AS Field" + paramnum + ", " ;

}

for (j=paramArray.length; j<staticColumnArray.length; j++)
{
column=staticColumnArray[j].substring(0);
columnnum=j+1;

strOut2 += column + " AS Field" + columnnum + ", ";

}

return strOut.substring(0, strOut.length - 1) + strOut2.substring(0, strOut2.length - 2);

}

var string=params["paramField1"].toString();



this.queryText = "SELECT " + paramstring(string) + " FROM dbo.LMS_UserData";
Re: Replace Data Set Columns Dynamically Based on Parameter [message #667546 is a reply to message #667473] Fri, 29 April 2011 13:15 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Meghan,

I do not see anything wrong with this. Is it working?

Jason

On 4/28/2011 7:34 PM, Meghan wrote:
> Jason,
>
> Okay, nevermind (maybe). I think I figured it out. Here is the code that
> I put into the beforeOpen method of the data set
>
> function paramstring(str) {
>
> var staticColumnArray = new Array("Username", "First_Name", "Last_Name",
> "Email", "City", "State_Province", "Company", "Excluded", "Investor",
> "Firm", "Organization", "UserTitle", "ClearingFirm", "CredDesired");
> var paramArray=params["paramField1"].toString().split(",");
> var strOut = "" ;
> var strOut2 = "";
> for (i=0; i< paramArray.length; i++)
> {
> param=paramArray[i].substring(0);
> paramnum=i+1;
>
> strOut += param + " AS Field" + paramnum + ", " ;
>
> }
>
> for (j=paramArray.length; j<staticColumnArray.length; j++)
> {
> column=staticColumnArray[j].substring(0);
> columnnum=j+1;
>
> strOut2 += column + " AS Field" + columnnum + ", ";
>
> }
>
> return strOut.substring(0, strOut.length - 1) + strOut2.substring(0,
> strOut2.length - 2);
>
> }
>
> var string=params["paramField1"].toString();
>
>
>
> this.queryText = "SELECT " + paramstring(string) + " FROM
> dbo.LMS_UserData";
Re: Replace Data Set Columns Dynamically Based on Parameter [message #667597 is a reply to message #667546] Fri, 29 April 2011 20:12 Go to previous messageGo to next message
Meghan Missing name is currently offline Meghan Missing nameFriend
Messages: 16
Registered: November 2010
Junior Member
I have it working for one data set and am trying it in another report. So far, it's looking good.

I still don't understand why the code -

this.queryText = this.queryText.replaceAll...

didn't work. Any ideas? It seems like there is a limitation - it would only successfully replace 2 columns. Any more than that and it would work sporadically and unreliably.

[Updated on: Fri, 29 April 2011 20:13]

Report message to a moderator

Re: Replace Data Set Columns Dynamically Based on Parameter [message #668683 is a reply to message #667597] Fri, 06 May 2011 17:16 Go to previous messageGo to next message
Meghan Missing name is currently offline Meghan Missing nameFriend
Messages: 16
Registered: November 2010
Junior Member
So I've run into an issue with the code that I posted earlier. It works just fine with a MS SQL Server database as the source table that I'm manipulating in the beforeOpen method BUT I need it to work with a MySQL database view and/or table.

It appears that the difference is when setting the field name to a generic name (e.g. Field1) in the original data set SELECT list it doesn't actually change the column name. So here is an example:

In MS SQL:
SELECT
username as Field1,
lastname as Field2,
...

This shows up in the output columns as 'Field1' (column binding) and 'Field1' (column alias).

In MySQL:
SELECT
username as Field1,
lastname as Field2,
...

This shows up in the output columns as 'username' (column binding) and 'Field1' (column alias)

This second version is a problem because the column is still referred to as 'username'. When I put the data into a table I get an error because the column has been replaced (the beforeOpen script is working) and thus the reference to 'username' no longer exists and it fails.

Any ideas?
Re: Replace Data Set Columns Dynamically Based on Parameter [message #668689 is a reply to message #668683] Fri, 06 May 2011 17:58 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Meghan,

What version of the MySql driver are you using because when I put in:
select productCode as pc
from orderdetails where ordernumber = 10101

both the output column and alias show up as pc

Jason

On 5/6/2011 1:16 PM, Meghan wrote:
> So I've run into an issue with the code that I posted earlier. It works
> just fine with a MS SQL Server database as the source table that I'm
> manipulating in the beforeOpen method BUT I need it to work with a MySQL
> database view and/or table.
> It appears that the difference is when setting the field name to a
> generic name (e.g. Field1) in the original data set SELECT list it
> doesn't actually change the column name. So here is an example:
>
> In MS SQL:
> SELECT
> username as Field1,
> lastname as Field2,
> ...
>
> This shows up in the output columns as 'Field1' (column binding) and
> 'Field1' (column alias).
>
> In MySQL:
> SELECT
> username as Field1,
> lastname as Field2,
> ...
>
> This shows up in the output columns as 'username' (column binding) and
> 'Field1' (column alias)
>
> This second version is a problem because the column is still referred to
> as 'username'. When I put the data into a table I get an error because
> the column has been replaced (the beforeOpen script is working) and thus
> the reference to 'username' no longer exists and it fails.
>
> Any ideas?
Re: Replace Data Set Columns Dynamically Based on Parameter [message #668739 is a reply to message #668689] Fri, 06 May 2011 22:59 Go to previous messageGo to next message
Meghan Missing name is currently offline Meghan Missing nameFriend
Messages: 16
Registered: November 2010
Junior Member
Jason,

Version 5.1
Re: Replace Data Set Columns Dynamically Based on Parameter [message #669249 is a reply to message #668739] Mon, 09 May 2011 15:04 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Meghan,

I am using mysql-connector-java-5.0.4-bin and it works well.

Jason

On 5/7/2011 8:39 PM, Meghan wrote:
> Jason,
>
> Version 5.1
icon4.gif  Re: Replace Data Set Columns Dynamically Based on Parameter [message #671628 is a reply to message #669249] Wed, 18 May 2011 14:18 Go to previous messageGo to next message
Meghan Missing name is currently offline Meghan Missing nameFriend
Messages: 16
Registered: November 2010
Junior Member
Jason,

Any update on this? I still am unable to get this to work with a MySQL database.
Re: Replace Data Set Columns Dynamically Based on Parameter [message #671646 is a reply to message #671628] Wed, 18 May 2011 14:29 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Did you try the 5.0.3 driver?

Jason

On 5/18/2011 10:18 AM, Meghan wrote:
> Jason,
>
> Any update on this? I still am unable to get this to work
> with a MySQL database.
Previous Topic:BIRT Dashboard
Next Topic:Add Subtotal to the BIRT report before page break
Goto Forum:
  


Current Time: Tue Dec 23 03:13:08 GMT 2014

Powered by FUDForum. Page generated in 0.02361 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software