Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Archived » BIRT » Multiple values for dynamic parameter(Multiple values for dynamic parameter)
Multiple values for dynamic parameter [message #895174] Wed, 11 July 2012 21:25 Go to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
I have a dataset 'Library' with the following values :

ID ASSET_SOURCE_NAME
422:29 Library 1
422:31 Library 2
422:32 Library 3
422:33 Library 4
422:34 Library 5
422:36 Library 6
422:37 Library 7
422:38 Library 8
422:40 Library 9
422:42 Library 10
422:43 Library 11
422:44 Library 12
422:45 Library 13
422:46 Library 14
422:47 Library 15
422:48 Library 16


I have the following script :

var id = new String(params["ID"]);
var num = id.substr(id.indexOf(':',0)+1,id.length);
query = new String(this.queryText);
query = query.replace(/(RPT_[a-zA-Z_]+)(\d+)/g,"$1"+num);
this.queryText = query;

I am using the script when I am passing one value through the URL using &parmname=parmvalue.

Now I am working with a case where I need to pass all the values of ID.

Please help me with ur ideas and suggestions.

Thanks
AR

Report message to a moderator

Re: Multiple values for dynamic parameter [message #895175 is a reply to message #895174] Wed, 11 July 2012 21:25 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
please find the values of table below:

ID
422:29
422:31
422:32
422:33
422:34
422:36
422:37
422:38
422:40
422:42
422:43
422:44
422:45
422:46
422:47
422:48

ASSET_SOURCE_NAME
Library 1
Library 2
Library 3
Library 4
Library 5
Library 6
Library 7
Library 8
Library 9
Library 10
Library 11
Library 12
Library 13
Library 14
Library 15
Library 16

Thanks
AR

Report message to a moderator

Re: Multiple values for dynamic parameter [message #895607 is a reply to message #895175] Fri, 13 July 2012 18:49 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
I have a requirement for which I need to pass the table names dynamically to dataset. This is my understanding from the structure of my source system. Please find the DB Structure, it may help you to better understand the problem. I am having access to views only. Here I have 3 views for the current requirement.

• LIBRARY_SOURCE : LIBRARY_ID & name available
• ASSET_REQUEST_(1,2,3,4,5.... Etc )
• CLASSIFIER_(1,2,3,4,5.... Etc )

The feed to the database comes from front end which is SOA Repository Manager.

• LIBRARY_SOURCE: This has the information of all available libraries. Note : This view has no versions.
• ASSET_REQUEST_(1,2,3,4,5.... Etc ) : has the status of libraries and the information at library level. Each time a new library created one new version of the view will be created automatically.
• CLASSIFIER_(1,2,3,4,5.... Etc ) : has the business information across and data is available across all libraries. Each time a new library created one new version of the view will be created automatically.


My requirement is I need to capture the status of assets across all libraries. To capture the status of assets I need to join the ASSET_REQUEST and CLASSIFIER views. Problem is in CLASSIFIER view I have information globally but in ASSET_REQUEST I have the information at local level.

I am able to pass the library id as parameter and manipulate query in dataset . But I am not able to understand how to perform the same for pass multiple parameters.

Please help me with your suggestions and inputs.

Pleasae find the xml for the report design with single parameter.

[Updated on: Sat, 14 July 2012 02:04]

Report message to a moderator

Re: Multiple values for dynamic parameter [message #895648 is a reply to message #895607] Sat, 14 July 2012 02:04 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
<?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>
<list-property name="propertyBindings">
<structure>
<property name="name">queryText</property>
<property name="id">652</property>
</structure>
<structure>
<property name="name">queryTimeOut</property>
<property name="id">652</property>
</structure>
</list-property>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="layoutPreference">auto layout</property>
<property name="bidiLayoutOrientation">ltr</property>
<property name="imageDPI">96</property>
<list-property name="libraries">
<structure>
<property name="fileName">LogidexReports.rptlibrary</property>
<property name="namespace">LogidexReports</property>
</structure>
</list-property>
<parameters>
<scalar-parameter name="libraryID" id="94">
<text-property name="promptText">Library ID</text-property>
<property name="valueType">dynamic</property>
<property name="dataSetName">Library ID</property>
<expression name="valueExpr" type="javascript">dataSetRow["LIBRARY_ID"]</expression>
<expression name="labelExpr" type="javascript">dataSetRow["ASSET_SOURCE_NAME"]</expression>
<property name="dataType">string</property>
<property name="distinct">true</property>
<simple-property-list name="defaultValue">
<value type="constant">422:36</value>
</simple-property-list>
<property name="paramType">simple</property>
<property name="controlType">list-box</property>
<property name="mustMatch">false</property>
<property name="fixedOrder">true</property>
<structure name="format">
<property name="category">Unformatted</property>
</structure>
</scalar-parameter>
</parameters>
<data-sources>
<oda-data-source extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source" id="653"
extends="LogidexReports.Data Source"/>
</data-sources>
<data-sets>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="Library ID" id="626">
<list-property name="columnHints">
<structure>
<property name="columnName">LIBRARY_ID</property>
<text-property name="displayName">LIBRARY_ID</text-property>
<text-property name="heading">LIBRARY_ID</text-property>
</structure>
<structure>
<property name="columnName">ASSET_SOURCE_NAME</property>
<text-property name="displayName">ASSET_SOURCE_NAME</text-property>
<text-property name="heading">ASSET_SOURCE_NAME</text-property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">LIBRARY_ID</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">ASSET_SOURCE_NAME</property>
<property name="dataType">string</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">LIBRARY_ID</property>
<property name="nativeName">LIBRARY_ID</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">ASSET_SOURCE_NAME</property>
<property name="nativeName">ASSET_SOURCE_NAME</property>
<property name="dataType">string</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select LIBRARY_ID, ASSET_SOURCE_NAME
from RPT_ASSET_SOURCE
order by LIBRARY_ID ASC]]></xml-property>
</oda-data-set>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="LibCount" id="627">
<structure name="cachedMetaData"/>
<property name="dataSource">Data Source</property>
<xml-property name="queryText"><![CDATA[SELECT * FROM T1 , T2

select count(LIBRARY_ID) as LibraryCount from RPT_ASSET_SOURCE T1

CROSS JOIN

select view_name from all_views where view_name like 'RPT_ASSET_REQUEST_%' and view_name not like '%RPT_ASSET_REQUEST_ACTION_%'
and view_name not like '%RPT_ASSET_REQUEST_ACTION_%' and view_name not like '%RPT_ASSET_REQUEST_APPROVAL%'
and view_name not like '%RPT_ASSET_REQUEST_PROPERTY%'

T2]]></xml-property>
</oda-data-set>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="final PCD Dataset" id="623">
<property name="nullsOrdering">nulls lowest</property>
<list-property name="computedColumns">
<structure>
<property name="name">NON_COMPLIANT</property>
<expression name="expression">BirtMath.subtract(row["TOTAL_NO_ASSETS"],row["COMPLIANT"])</expression>
<property name="dataType">decimal</property>
</structure>
</list-property>
<list-property name="columnHints">
<structure>
<property name="columnName">SUBSTR(QUARTERS,4,4)</property>
<text-property name="displayName">SUBSTR(QUARTERS,4,4)</text-property>
<text-property name="heading">SUBSTR(QUARTERS,4,4)</text-property>
</structure>
<structure>
<property name="columnName">SUBSTR(QUARTERS,1,2)</property>
<text-property name="displayName">SUBSTR(QUARTERS,1,2)</text-property>
<text-property name="heading">SUBSTR(QUARTERS,1,2)</text-property>
</structure>
<structure>
<property name="columnName">QUARTERS</property>
<text-property name="displayName">QUARTERS</text-property>
<text-property name="heading">QUARTERS</text-property>
</structure>
<structure>
<property name="columnName">COUNT</property>
<text-property name="displayName">COUNT</text-property>
<text-property name="heading">COUNT</text-property>
</structure>
<structure>
<property name="columnName">QUARTER_START_DATE</property>
<text-property name="displayName">QUARTER_START_DATE</text-property>
<text-property name="heading">QUARTER_START_DATE</text-property>
</structure>
<structure>
<property name="columnName">QUARTER</property>
<text-property name="displayName">QUARTER</text-property>
<text-property name="heading">QUARTER</text-property>
</structure>
<structure>
<property name="columnName">TOTAL_NO_ASSETS</property>
<text-property name="displayName">TOTAL_NO_ASSETS</text-property>
<text-property name="heading">TOTAL_NO_ASSETS</text-property>
</structure>
<structure>
<property name="columnName">COMPLIANT</property>
<text-property name="displayName">COMPLIANT</text-property>
<text-property name="heading">COMPLIANT</text-property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">SUBSTR(QUARTERS,4,4)</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">SUBSTR(QUARTERS,1,2)</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">QUARTERS</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">COUNT</property>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">QUARTER_START_DATE</property>
<property name="dataType">date-time</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">QUARTER</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">TOTAL_NO_ASSETS</property>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="position">8</property>
<property name="name">COMPLIANT</property>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="position">9</property>
<property name="name">NON_COMPLIANT</property>
<property name="dataType">decimal</property>
</structure>
</list-property>
</structure>
<method name="beforeOpen"><![CDATA[var id = new String(params["libraryID"]);
var num = id.substr(id.indexOf(':',0)+1,id.length);
query = new String(this.queryText);
query = query.replace(/(RPT_[a-zA-Z_]+)(\d+)/g,"$1"+num);
this.queryText = query;]]></method>
<property name="dataSource">Data Source</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">SUBSTR(QUARTERS,4,4)</property>
<property name="nativeName">SUBSTR(QUARTERS,4,4)</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">SUBSTR(QUARTERS,1,2)</property>
<property name="nativeName">SUBSTR(QUARTERS,1,2)</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">QUARTERS</property>
<property name="nativeName">QUARTERS</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">COUNT</property>
<property name="nativeName">COUNT</property>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="position">5</property>
<property name="name">QUARTER_START_DATE</property>
<property name="nativeName">QUARTER_START_DATE</property>
<property name="dataType">date-time</property>
</structure>
<structure>
<property name="position">6</property>
<property name="name">QUARTER</property>
<property name="nativeName">QUARTER</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">7</property>
<property name="name">TOTAL_NO_ASSETS</property>
<property name="nativeName">TOTAL_NO_ASSETS</property>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="position">8</property>
<property name="name">COMPLIANT</property>
<property name="nativeName">COMPLIANT</property>
<property name="dataType">decimal</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select Substr(Quarters,4,4),Substr(Quarters,1,2), d.* from
(SELECT CASE
when to_char(b.dt,'MM') = 01 then 'Q1'
when to_char(b.dt,'MM') = 04 then 'Q2'
when to_char(b.dt,'MM') = 07 then 'Q3'
when to_char(b.dt,'MM') = 10 then 'Q4'
END
|| '-' || to_char(b.dt,'YYYY')as Quarters ,
nvl(a.cnt,0) as count,
a.quarter_start_date,
to_char(b.dt+93,'YYYY') AS QUARTER , nvl(a.cnt,0) AS "TOTAL_NO_ASSETS", NVL(C.CNT,0) AS COMPLIANT
from ( SELECT TRUNC (T4.CREATED,'Q') AS quarter_start_date,-- T4.ASSETCNT , T4.CREATED ,
COUNT (T4.ASSETCNT) AS cnt
FROM (
SELECT COUNT( DISTINCT ( T3.ASSET_ID)) AS ASSETCNT, T3.APPR_STATE , T3.CREATED
FROM
( SELECT DISTINCT (T1.ASSET_ID) AS ASSET_ID, T1.APPR_STATE , TO_DATE (TO_CHAR (T1.CREATED, 'MM/DD/YY'), 'MM/DD/YY') AS CREATED, T2.STATUS
from RPT_ASSET_REQUEST_36 T1,
(SELECT DISTINCT(ASSETID),STATUS,"ASSET-SOURCE-NAME" FROM RPT_CLASSIFIERS_36 WHERE "ASSET-SOURCE-NAME"= 'BIRT_TESTING') T2
WHERE T1.ASSET_ID (+) = T2.ASSETID
AND T2.STATUS != 'PLANNING AND ANALYZE'
AND T1.CREATED IS NOT NULL
AND T1.APPR_STATE IS NOT NULL
) T3
Group BY T3.APPR_STATE , T3.CREATED
) T4
WHERE T4.CREATED >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
GROUP BY TRUNC (T4.CREATED,'Q')
) a,
( SELECT TRUNC (T4.CREATED,'Q') AS quarter_start_date,
COUNT (T4.ASSETCNT) AS cnt
FROM (
SELECT COUNT( DISTINCT ( T3.ASSET_ID)) AS ASSETCNT, T3.APPR_STATE , T3.CREATED
FROM
( SELECT DISTINCT (T1.ASSET_ID) AS ASSET_ID, T1.APPR_STATE , TO_DATE (TO_CHAR (T1.CREATED, 'MM/DD/YY'), 'MM/DD/YY') AS CREATED, T2.STATUS
from RPT_ASSET_REQUEST_36 T1,
(SELECT DISTINCT(ASSETID),STATUS,"ASSET-SOURCE-NAME" FROM RPT_CLASSIFIERS_36 WHERE "ASSET-SOURCE-NAME"= 'BIRT_TESTING') T2
WHERE T1.APPR_STATE = 'Approved'
AND T1.ASSET_ID = T2.ASSETID
AND T2.STATUS != 'PLANNING AND ANALYZE'
AND T1.CREATED IS NOT NULL
AND T1.APPR_STATE IS NOT NULL
) T3
Group BY T3.APPR_STATE , T3.CREATED
) T4
WHERE T4.CREATED >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
GROUP BY TRUNC (T4.CREATED,'Q')
) c,
( SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9) as dt from dual UNION
SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -6) as dt from dual UNION
SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -3) as dt from dual UNION
SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , 0) as dt from dual
) b
where a.quarter_start_date (+) = b.dt AND C.quarter_start_date (+) = b.dt
order by b.dt ) d
order by Substr(Quarters,4,4) asc ,Substr(Quarters,1,2) asc ]]></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>QUARTER</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>17</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>QUARTER</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>QUARTER</design:label>
<design:formattingHints>
<design:displaySize>17</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>COUNT</design:name>
<design:position>2</design:position>
<design:nativeDataTypeCode>2</design:nativeDataTypeCode>
<design:precision>0</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>COUNT</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>COUNT</design:label>
<design:formattingHints>
<design:displaySize>22</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
<design:criteria/>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>]]></xml-property>
</oda-data-set>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="PCD" id="651">
<property name="nullsOrdering">nulls lowest</property>
<list-property name="computedColumns">
<structure>
<property name="name">NON_COMPLIANT</property>
<expression name="expression">BirtMath.subtract(row["TOTAL_NO_ASSETS"],row["COMPLIANT"])</expression>
<property name="dataType">decimal</property>
</structure>
</list-property>
<list-property name="columnHints">
<structure>
<property name="columnName">QUARTER_START_DATE</property>
<text-property name="displayName">QUARTER_START_DATE</text-property>
<text-property name="heading">QUARTER_START_DATE</text-property>
</structure>
<structure>
<property name="columnName">TOTAL_NO_ASSETS</property>
<text-property name="displayName">TOTAL_NO_ASSETS</text-property>
<text-property name="heading">TOTAL_NO_ASSETS</text-property>
</structure>
<structure>
<property name="columnName">COMPLIANT</property>
<text-property name="displayName">COMPLIANT</text-property>
<text-property name="heading">COMPLIANT</text-property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">QUARTER_START_DATE</property>
<property name="dataType">date-time</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">TOTAL_NO_ASSETS</property>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">COMPLIANT</property>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="position">4</property>
<property name="name">NON_COMPLIANT</property>
<property name="dataType">decimal</property>
</structure>
</list-property>
</structure>
<method name="beforeOpen"><![CDATA[var id = new String(params["libraryID"]);
var lib_count = new String(params["LibraryCount"]);
for ( i=1 ; i < lib_count ; i++)
var num = id.substr(id.indexOf(':',0)+1,id.length);
query = new String(this.queryText);
query = query.replace(/(RPT_[a-zA-Z_]+)(\d+)/g,"$1"+num);
this.queryText = query;]]></method>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">QUARTER_START_DATE</property>
<property name="nativeName">QUARTER_START_DATE</property>
<property name="dataType">date-time</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">TOTAL_NO_ASSETS</property>
<property name="nativeName">TOTAL_NO_ASSETS</property>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">COMPLIANT</property>
<property name="nativeName">COMPLIANT</property>
<property name="dataType">decimal</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[SELECT
a.quarter_start_date,
nvl(a.cnt,0) AS "TOTAL_NO_ASSETS", NVL(C.CNT,0) AS COMPLIANT
from ( SELECT TRUNC (T4.CREATED,'Q') AS quarter_start_date,
COUNT (T4.ASSETCNT) AS cnt
FROM (
SELECT COUNT( DISTINCT ( T3.ASSET_ID)) AS ASSETCNT, T3.APPR_STATE , T3.CREATED
FROM
( SELECT DISTINCT (T1.ASSET_ID) AS ASSET_ID, T1.APPR_STATE , TO_DATE (TO_CHAR (T1.CREATED, 'MM/DD/YY'), 'MM/DD/YY') AS CREATED, T2.STATUS
from RPT_ASSET_REQUEST_36 T1,
(SELECT DISTINCT(ASSETID),STATUS,"ASSET-SOURCE-NAME" FROM RPT_CLASSIFIERS_36 WHERE "ASSET-SOURCE-NAME"= 'BIRT_TESTING') T2
WHERE T1.ASSET_ID (+) = T2.ASSETID
AND T2.STATUS != 'PLANNING AND ANALYZE'
AND T1.CREATED IS NOT NULL
AND T1.APPR_STATE IS NOT NULL
) T3
Group BY T3.APPR_STATE , T3.CREATED
) T4
WHERE T4.CREATED >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
GROUP BY TRUNC (T4.CREATED,'Q')
) a,
( SELECT TRUNC (T4.CREATED,'Q') AS quarter_start_date,
COUNT (T4.ASSETCNT) AS cnt
FROM (
SELECT COUNT( DISTINCT ( T3.ASSET_ID)) AS ASSETCNT, T3.APPR_STATE , T3.CREATED
FROM
( SELECT DISTINCT (T1.ASSET_ID) AS ASSET_ID, T1.APPR_STATE , TO_DATE (TO_CHAR (T1.CREATED, 'MM/DD/YY'), 'MM/DD/YY') AS CREATED, T2.STATUS
from RPT_ASSET_REQUEST_36 T1,
(SELECT DISTINCT(ASSETID),STATUS,"ASSET-SOURCE-NAME" FROM RPT_CLASSIFIERS_36 WHERE "ASSET-SOURCE-NAME"= 'BIRT_TESTING') T2
WHERE T1.APPR_STATE = 'Approved'
AND T1.ASSET_ID = T2.ASSETID
AND T2.STATUS != 'PLANNING AND ANALYZE'
AND T1.CREATED IS NOT NULL
AND T1.APPR_STATE IS NOT NULL
) T3
Group BY T3.APPR_STATE , T3.CREATED
) T4
WHERE T4.CREATED >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
GROUP BY TRUNC (T4.CREATED,'Q')
) c,
( SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9) as dt from dual UNION
SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -6) as dt from dual UNION
SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -3) as dt from dual UNION
SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , 0) as dt from dual
) b
where a.quarter_start_date (+) = b.dt AND C.quarter_start_date (+) = b.dt
order by b.dt ]]></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>QUARTER</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>17</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>QUARTER</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>QUARTER</design:label>
<design:formattingHints>
<design:displaySize>17</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
<design:resultColumnDefinitions>
<design:attributes>
<design:name>COUNT</design:name>
<design:position>2</design:position>
<design:nativeDataTypeCode>2</design:nativeDataTypeCode>
<design:precision>0</design:precision>
<design:scale>0</design:scale>
<design:nullability>Nullable</design:nullability>
<design:uiHints>
<design:displayName>COUNT</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>COUNT</design:label>
<design:formattingHints>
<design:displaySize>22</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
<design:criteria/>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>]]></xml-property>
</oda-data-set>
<oda-data-set extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" name="ASSET_REQUEST" id="652">
<list-property name="columnHints">
<structure>
<property name="columnName">VIEW_NAME</property>
<text-property name="displayName">VIEW_NAME</text-property>
<text-property name="heading">VIEW_NAME</text-property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">VIEW_NAME</property>
<property name="dataType">string</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">VIEW_NAME</property>
<property name="nativeName">VIEW_NAME</property>
<property name="dataType">string</property>
<property name="nativeDataType">12</property>
</structure>
</list-property>
<xml-property name="queryText"><![CDATA[select view_name from all_views where view_name like 'RPT_ASSET_REQUEST_%' and view_name not like '%RPT_ASSET_REQUEST_ACTION_%'
and view_name not like '%RPT_ASSET_REQUEST_ACTION_%' and view_name not like '%RPT_ASSET_REQUEST_APPROVAL%'
and view_name not like '%RPT_ASSET_REQUEST_PROPERTY%'
]]></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>VIEW_NAME</design:name>
<design:position>1</design:position>
<design:nativeDataTypeCode>12</design:nativeDataTypeCode>
<design:precision>30</design:precision>
<design:scale>0</design:scale>
<design:nullability>NotNullable</design:nullability>
<design:uiHints>
<design:displayName>VIEW_NAME</design:displayName>
</design:uiHints>
</design:attributes>
<design:usageHints>
<design:label>VIEW_NAME</design:label>
<design:formattingHints>
<design:displaySize>30</design:displaySize>
</design:formattingHints>
</design:usageHints>
</design:resultColumnDefinitions>
</design:resultSetColumns>
<design:criteria/>
</design:resultSetDefinitions>
</design:ResultSets>
</model:DesignValues>
]]></xml-property>
</oda-data-set>
</data-sets>
<styles>
<style name="report" id="4">
<property name="fontFamily">sans-serif</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">
<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>
<table id="629">
<property name="width">4.072916666666667in</property>
<property name="dataSet">final PCD Dataset</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">TOTAL_NO_ASSETS</property>
<text-property name="displayName">TOTAL_NO_ASSETS</text-property>
<expression name="expression" type="javascript">dataSetRow["TOTAL_NO_ASSETS"]</expression>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="name">COMPLIANT</property>
<text-property name="displayName">COMPLIANT</text-property>
<expression name="expression" type="javascript">dataSetRow["COMPLIANT"]</expression>
<property name="dataType">decimal</property>
</structure>
<structure>
<property name="name">NON_COMPLIANT</property>
<text-property name="displayName">NON_COMPLIANT</text-property>
<expression name="expression" type="javascript">dataSetRow["NON_COMPLIANT"]</expression>
<property name="dataType">decimal</property>
</structure>
</list-property>
<column id="642">
<property name="width">1.65625in</property>
</column>
<column id="643">
<property name="width">1.0729166666666667in</property>
</column>
<column id="644">
<property name="width">1.34375in</property>
</column>
<header>
<row id="630">
<cell id="631">
<label id="645">
<text-property name="text">TOTAL_NO_ASSETS</text-property>
</label>
</cell>
<cell id="632">
<label id="647">
<text-property name="text">COMPLIANT</text-property>
</label>
</cell>
<cell id="633">
<label id="649">
<text-property name="text">NON_COMPLIANT</text-property>
</label>
</cell>
</row>
</header>
<detail>
<row id="634">
<cell id="635">
<data id="646">
<property name="textAlign">center</property>
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">TOTAL_NO_ASSETS</property>
</data>
</cell>
<cell id="636">
<data id="648">
<property name="textAlign">center</property>
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">COMPLIANT</property>
</data>
</cell>
<cell id="637">
<data id="650">
<property name="textAlign">center</property>
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">NON_COMPLIANT</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="638">
<cell id="639"/>
<cell id="640"/>
<cell id="641"/>
</row>
</footer>
</table>
</body>
</report>

[Updated on: Sat, 14 July 2012 02:07]

Report message to a moderator

Re: Multiple values for dynamic parameter [message #896034 is a reply to message #895648] Tue, 17 July 2012 02:40 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member
Why not just add all as the default parameter value and then check for
this value in the script before doing your replace?

Jason

On 7/13/2012 10:04 PM, A R wrote:
> <?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>
> <list-property name="propertyBindings">
> <structure>
> <property name="name">queryText</property>
> <property name="id">652</property>
> </structure>
> <structure>
> <property name="name">queryTimeOut</property>
> <property name="id">652</property>
> </structure>
> </list-property>
> <property name="iconFile">/templates/blank_report.gif</property>
> <property name="layoutPreference">auto layout</property>
> <property name="bidiLayoutOrientation">ltr</property>
> <property name="imageDPI">96</property>
> <parameters>
> <scalar-parameter name="libraryID" id="94">
> <text-property name="promptText">Library ID</text-property>
> <property name="valueType">dynamic</property>
> <property name="dataSetName">Library ID</property>
> <expression name="valueExpr"
> type="javascript">dataSetRow["LIBRARY_ID"]</expression>
> <expression name="labelExpr"
> type="javascript">dataSetRow["ASSET_SOURCE_NAME"]</expression>
> <property name="dataType">string</property>
> <property name="distinct">true</property>
> <simple-property-list name="defaultValue">
> <value type="constant">422:36</value>
> </simple-property-list>
> <property name="paramType">simple</property>
> <property name="controlType">list-box</property>
> <property name="mustMatch">false</property>
> <property name="fixedOrder">true</property>
> <structure name="format">
> <property name="category">Unformatted</property>
> </structure>
> </scalar-parameter>
> </parameters>
> <data-sources>
> <oda-data-source
> extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source"
> id="7">
> <list-property name="privateDriverProperties">
> <ex-property>
> <name>contentBidiFormatStr</name>
> <value>ILYNN</value>
> </ex-property>
> <ex-property>
> <name>metadataBidiFormatStr</name>
> <value>ILYNN</value>
> </ex-property>
> </list-property>
> <property
> name="odaDriverClass">oracle.jdbc.driver.OracleDriver</property>
> <property
> name="odaURL">jdbc:oracle:thin:@VDO1023.phx.aexp.com:1522:VDO1023</property>
>
> <property name="odaUser">ENTRMUSER</property>
> <encrypted-property name="odaPassword"
> encryptionID="base64">ZW50PzFybXVzZXI=</encrypted-property>
> </oda-data-source>
> </data-sources>
> <data-sets>
> <oda-data-set
> extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet"
> name="Library ID" id="626">
> <list-property name="columnHints">
> <structure>
> <property name="columnName">LIBRARY_ID</property>
> <text-property
> name="displayName">LIBRARY_ID</text-property>
> <text-property
> name="heading">LIBRARY_ID</text-property>
> </structure>
> <structure>
> <property
> name="columnName">ASSET_SOURCE_NAME</property>
> <text-property
> name="displayName">ASSET_SOURCE_NAME</text-property>
> <text-property
> name="heading">ASSET_SOURCE_NAME</text-property>
> </structure>
> </list-property>
> <structure name="cachedMetaData">
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">LIBRARY_ID</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property name="name">ASSET_SOURCE_NAME</property>
> <property name="dataType">string</property>
> </structure>
> </list-property>
> </structure>
> <property name="dataSource">Data Source</property>
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">LIBRARY_ID</property>
> <property name="nativeName">LIBRARY_ID</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property name="name">ASSET_SOURCE_NAME</property>
> <property
> name="nativeName">ASSET_SOURCE_NAME</property>
> <property name="dataType">string</property>
> </structure>
> </list-property>
> <xml-property name="queryText"><![CDATA[select LIBRARY_ID,
> ASSET_SOURCE_NAME
> from RPT_ASSET_SOURCE
> order by LIBRARY_ID ASC]]></xml-property>
> </oda-data-set>
> <oda-data-set
> extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet"
> name="LibCount" id="627">
> <structure name="cachedMetaData"/>
> <property name="dataSource">Data Source</property>
> <xml-property name="queryText"><![CDATA[SELECT * FROM T1 , T2
>
> select count(LIBRARY_ID) as LibraryCount from RPT_ASSET_SOURCE T1
>
> CROSS JOIN
> select view_name from all_views where view_name like
> 'RPT_ASSET_REQUEST_%' and view_name not like '%RPT_ASSET_REQUEST_ACTION_%'
> and view_name not like '%RPT_ASSET_REQUEST_ACTION_%' and view_name not
> like '%RPT_ASSET_REQUEST_APPROVAL%'
> and view_name not like '%RPT_ASSET_REQUEST_PROPERTY%'
>
> T2]]></xml-property>
> </oda-data-set>
> <oda-data-set
> extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet"
> name="final PCD Dataset" id="623">
> <property name="nullsOrdering">nulls lowest</property>
> <list-property name="computedColumns">
> <structure>
> <property name="name">NON_COMPLIANT</property>
> <expression
> name="expression">BirtMath.subtract(row["TOTAL_NO_ASSETS"],row["COMPLIANT"])</expression>
>
> <property name="dataType">decimal</property>
> </structure>
> </list-property>
> <list-property name="columnHints">
> <structure>
> <property
> name="columnName">SUBSTR(QUARTERS,4,4)</property>
> <text-property
> name="displayName">SUBSTR(QUARTERS,4,4)</text-property>
> <text-property
> name="heading">SUBSTR(QUARTERS,4,4)</text-property>
> </structure>
> <structure>
> <property
> name="columnName">SUBSTR(QUARTERS,1,2)</property>
> <text-property
> name="displayName">SUBSTR(QUARTERS,1,2)</text-property>
> <text-property
> name="heading">SUBSTR(QUARTERS,1,2)</text-property>
> </structure>
> <structure>
> <property name="columnName">QUARTERS</property>
> <text-property
> name="displayName">QUARTERS</text-property>
> <text-property name="heading">QUARTERS</text-property>
> </structure>
> <structure>
> <property name="columnName">COUNT</property>
> <text-property name="displayName">COUNT</text-property>
> <text-property name="heading">COUNT</text-property>
> </structure>
> <structure>
> <property
> name="columnName">QUARTER_START_DATE</property>
> <text-property
> name="displayName">QUARTER_START_DATE</text-property>
> <text-property
> name="heading">QUARTER_START_DATE</text-property>
> </structure>
> <structure>
> <property name="columnName">QUARTER</property>
> <text-property
> name="displayName">QUARTER</text-property>
> <text-property name="heading">QUARTER</text-property>
> </structure>
> <structure>
> <property name="columnName">TOTAL_NO_ASSETS</property>
> <text-property
> name="displayName">TOTAL_NO_ASSETS</text-property>
> <text-property
> name="heading">TOTAL_NO_ASSETS</text-property>
> </structure>
> <structure>
> <property name="columnName">COMPLIANT</property>
> <text-property
> name="displayName">COMPLIANT</text-property>
> <text-property name="heading">COMPLIANT</text-property>
> </structure>
> </list-property>
> <structure name="cachedMetaData">
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property
> name="name">SUBSTR(QUARTERS,4,4)</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property
> name="name">SUBSTR(QUARTERS,1,2)</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">3</property>
> <property name="name">QUARTERS</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">4</property>
> <property name="name">COUNT</property>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="position">5</property>
> <property name="name">QUARTER_START_DATE</property>
> <property name="dataType">date-time</property>
> </structure>
> <structure>
> <property name="position">6</property>
> <property name="name">QUARTER</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">7</property>
> <property name="name">TOTAL_NO_ASSETS</property>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="position">8</property>
> <property name="name">COMPLIANT</property>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="position">9</property>
> <property name="name">NON_COMPLIANT</property>
> <property name="dataType">decimal</property>
> </structure>
> </list-property>
> </structure>
> <method name="beforeOpen"><![CDATA[var id = new
> String(params["libraryID"]);
> var num = id.substr(id.indexOf(':',0)+1,id.length);
> query = new String(this.queryText);
> query = query.replace(/(RPT_[a-zA-Z_]+)(\d+)/g,"$1"+num);
> this.queryText = query;]]></method>
> <property name="dataSource">Data Source</property>
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">SUBSTR(QUARTERS,4,4)</property>
> <property
> name="nativeName">SUBSTR(QUARTERS,4,4)</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property name="name">SUBSTR(QUARTERS,1,2)</property>
> <property
> name="nativeName">SUBSTR(QUARTERS,1,2)</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">3</property>
> <property name="name">QUARTERS</property>
> <property name="nativeName">QUARTERS</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">4</property>
> <property name="name">COUNT</property>
> <property name="nativeName">COUNT</property>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="position">5</property>
> <property name="name">QUARTER_START_DATE</property>
> <property
> name="nativeName">QUARTER_START_DATE</property>
> <property name="dataType">date-time</property>
> </structure>
> <structure>
> <property name="position">6</property>
> <property name="name">QUARTER</property>
> <property name="nativeName">QUARTER</property>
> <property name="dataType">string</property>
> </structure>
> <structure>
> <property name="position">7</property>
> <property name="name">TOTAL_NO_ASSETS</property>
> <property name="nativeName">TOTAL_NO_ASSETS</property>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="position">8</property>
> <property name="name">COMPLIANT</property>
> <property name="nativeName">COMPLIANT</property>
> <property name="dataType">decimal</property>
> </structure>
> </list-property>
> <xml-property name="queryText"><![CDATA[select
> Substr(Quarters,4,4),Substr(Quarters,1,2), d.* from
> (SELECT CASE when to_char(b.dt,'MM') = 01 then 'Q1'
> when to_char(b.dt,'MM') = 04 then 'Q2'
> when to_char(b.dt,'MM') = 07 then 'Q3'
> when to_char(b.dt,'MM') = 10 then 'Q4'
> END
> || '-' || to_char(b.dt,'YYYY')as Quarters , nvl(a.cnt,0) as count,
> a.quarter_start_date,
> to_char(b.dt+93,'YYYY') AS QUARTER , nvl(a.cnt,0) AS "TOTAL_NO_ASSETS",
> NVL(C.CNT,0) AS COMPLIANT
> from ( SELECT TRUNC (T4.CREATED,'Q') AS quarter_start_date,--
> T4.ASSETCNT , T4.CREATED , COUNT (T4.ASSETCNT) AS cnt
> FROM ( SELECT COUNT( DISTINCT ( T3.ASSET_ID)) AS ASSETCNT, T3.APPR_STATE
> , T3.CREATED FROM ( SELECT DISTINCT (T1.ASSET_ID) AS ASSET_ID,
> T1.APPR_STATE , TO_DATE (TO_CHAR (T1.CREATED, 'MM/DD/YY'), 'MM/DD/YY')
> AS CREATED, T2.STATUS
> from RPT_ASSET_REQUEST_36 T1, (SELECT
> DISTINCT(ASSETID),STATUS,"ASSET-SOURCE-NAME" FROM RPT_CLASSIFIERS_36
> WHERE "ASSET-SOURCE-NAME"= 'BIRT_TESTING') T2
> WHERE T1.ASSET_ID (+) = T2.ASSETID AND T2.STATUS != 'PLANNING AND ANALYZE'
> AND T1.CREATED IS NOT NULL AND T1.APPR_STATE IS NOT NULL ) T3
> Group BY T3.APPR_STATE , T3.CREATED
> ) T4 WHERE T4.CREATED >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
> GROUP BY TRUNC (T4.CREATED,'Q')
> ) a,
> ( SELECT TRUNC (T4.CREATED,'Q') AS quarter_start_date,
> COUNT (T4.ASSETCNT) AS cnt
> FROM ( SELECT COUNT( DISTINCT ( T3.ASSET_ID)) AS ASSETCNT, T3.APPR_STATE
> , T3.CREATED FROM ( SELECT DISTINCT (T1.ASSET_ID) AS ASSET_ID,
> T1.APPR_STATE , TO_DATE (TO_CHAR (T1.CREATED, 'MM/DD/YY'), 'MM/DD/YY')
> AS CREATED, T2.STATUS
> from RPT_ASSET_REQUEST_36 T1, (SELECT
> DISTINCT(ASSETID),STATUS,"ASSET-SOURCE-NAME" FROM RPT_CLASSIFIERS_36
> WHERE "ASSET-SOURCE-NAME"= 'BIRT_TESTING') T2
> WHERE T1.APPR_STATE = 'Approved'
> AND T1.ASSET_ID = T2.ASSETID AND T2.STATUS != 'PLANNING AND ANALYZE'
> AND T1.CREATED IS NOT NULL AND T1.APPR_STATE IS NOT NULL ) T3
> Group BY T3.APPR_STATE , T3.CREATED
> ) T4 WHERE T4.CREATED >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
> GROUP BY TRUNC (T4.CREATED,'Q')
> ) c,
> ( SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9) as dt from dual UNION
> SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -6) as dt from dual UNION
> SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -3) as dt from dual UNION
> SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , 0) as dt from dual ) b
> where a.quarter_start_date (+) = b.dt AND C.quarter_start_date (+) = b.dt
> order by b.dt ) d
> order by Substr(Quarters,4,4) asc ,Substr(Quarters,1,2) asc
> ]]></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>QUARTER</design:name>
> <design:position>1</design:position>
> <design:nativeDataTypeCode>12</design:nativeDataTypeCode>
> <design:precision>17</design:precision>
> <design:scale>0</design:scale>
> <design:nullability>Nullable</design:nullability>
> <design:uiHints>
> <design:displayName>QUARTER</design:displayName>
> </design:uiHints>
> </design:attributes>
> <design:usageHints>
> <design:label>QUARTER</design:label>
> <design:formattingHints>
> <design:displaySize>17</design:displaySize>
> </design:formattingHints>
> </design:usageHints>
> </design:resultColumnDefinitions>
> <design:resultColumnDefinitions>
> <design:attributes>
> <design:name>COUNT</design:name>
> <design:position>2</design:position>
> <design:nativeDataTypeCode>2</design:nativeDataTypeCode>
> <design:precision>0</design:precision>
> <design:scale>0</design:scale>
> <design:nullability>Nullable</design:nullability>
> <design:uiHints>
> <design:displayName>COUNT</design:displayName>
> </design:uiHints>
> </design:attributes>
> <design:usageHints>
> <design:label>COUNT</design:label>
> <design:formattingHints>
> <design:displaySize>22</design:displaySize>
> </design:formattingHints>
> </design:usageHints>
> </design:resultColumnDefinitions>
> </design:resultSetColumns>
> <design:criteria/>
> </design:resultSetDefinitions>
> </design:ResultSets>
> </model:DesignValues>]]></xml-property>
> </oda-data-set>
> <oda-data-set
> extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet"
> name="PCD" id="651">
> <property name="nullsOrdering">nulls lowest</property>
> <list-property name="computedColumns">
> <structure>
> <property name="name">NON_COMPLIANT</property>
> <expression
> name="expression">BirtMath.subtract(row["TOTAL_NO_ASSETS"],row["COMPLIANT"])</expression>
>
> <property name="dataType">decimal</property>
> </structure>
> </list-property>
> <list-property name="columnHints">
> <structure>
> <property
> name="columnName">QUARTER_START_DATE</property>
> <text-property
> name="displayName">QUARTER_START_DATE</text-property>
> <text-property
> name="heading">QUARTER_START_DATE</text-property>
> </structure>
> <structure>
> <property name="columnName">TOTAL_NO_ASSETS</property>
> <text-property
> name="displayName">TOTAL_NO_ASSETS</text-property>
> <text-property
> name="heading">TOTAL_NO_ASSETS</text-property>
> </structure>
> <structure>
> <property name="columnName">COMPLIANT</property>
> <text-property
> name="displayName">COMPLIANT</text-property>
> <text-property name="heading">COMPLIANT</text-property>
> </structure>
> </list-property>
> <structure name="cachedMetaData">
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">QUARTER_START_DATE</property>
> <property name="dataType">date-time</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property name="name">TOTAL_NO_ASSETS</property>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="position">3</property>
> <property name="name">COMPLIANT</property>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="position">4</property>
> <property name="name">NON_COMPLIANT</property>
> <property name="dataType">decimal</property>
> </structure>
> </list-property>
> </structure>
> <method name="beforeOpen"><![CDATA[var id = new
> String(params["libraryID"]);
> var lib_count = new String(params["LibraryCount"]);
> for ( i=1 ; i < lib_count ; i++)
> var num = id.substr(id.indexOf(':',0)+1,id.length);
> query = new String(this.queryText);
> query = query.replace(/(RPT_[a-zA-Z_]+)(\d+)/g,"$1"+num);
> this.queryText = query;]]></method>
> <property name="dataSource">Data Source</property>
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">QUARTER_START_DATE</property>
> <property
> name="nativeName">QUARTER_START_DATE</property>
> <property name="dataType">date-time</property>
> </structure>
> <structure>
> <property name="position">2</property>
> <property name="name">TOTAL_NO_ASSETS</property>
> <property name="nativeName">TOTAL_NO_ASSETS</property>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="position">3</property>
> <property name="name">COMPLIANT</property>
> <property name="nativeName">COMPLIANT</property>
> <property name="dataType">decimal</property>
> </structure>
> </list-property>
> <xml-property name="queryText"><![CDATA[SELECT
> a.quarter_start_date,
> nvl(a.cnt,0) AS "TOTAL_NO_ASSETS", NVL(C.CNT,0) AS COMPLIANT
> from ( SELECT TRUNC (T4.CREATED,'Q') AS quarter_start_date, COUNT
> (T4.ASSETCNT) AS cnt
> FROM ( SELECT COUNT( DISTINCT ( T3.ASSET_ID)) AS ASSETCNT, T3.APPR_STATE
> , T3.CREATED FROM ( SELECT DISTINCT (T1.ASSET_ID) AS ASSET_ID,
> T1.APPR_STATE , TO_DATE (TO_CHAR (T1.CREATED, 'MM/DD/YY'), 'MM/DD/YY')
> AS CREATED, T2.STATUS
> from RPT_ASSET_REQUEST_36 T1, (SELECT
> DISTINCT(ASSETID),STATUS,"ASSET-SOURCE-NAME" FROM RPT_CLASSIFIERS_36
> WHERE "ASSET-SOURCE-NAME"= 'BIRT_TESTING') T2
> WHERE T1.ASSET_ID (+) = T2.ASSETID AND T2.STATUS != 'PLANNING AND ANALYZE'
> AND T1.CREATED IS NOT NULL AND T1.APPR_STATE IS NOT NULL ) T3
> Group BY T3.APPR_STATE , T3.CREATED
> ) T4 WHERE T4.CREATED >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
> GROUP BY TRUNC (T4.CREATED,'Q')
> ) a,
> ( SELECT TRUNC (T4.CREATED,'Q') AS quarter_start_date,
> COUNT (T4.ASSETCNT) AS cnt
> FROM ( SELECT COUNT( DISTINCT ( T3.ASSET_ID)) AS ASSETCNT, T3.APPR_STATE
> , T3.CREATED FROM ( SELECT DISTINCT (T1.ASSET_ID) AS ASSET_ID,
> T1.APPR_STATE , TO_DATE (TO_CHAR (T1.CREATED, 'MM/DD/YY'), 'MM/DD/YY')
> AS CREATED, T2.STATUS
> from RPT_ASSET_REQUEST_36 T1, (SELECT
> DISTINCT(ASSETID),STATUS,"ASSET-SOURCE-NAME" FROM RPT_CLASSIFIERS_36
> WHERE "ASSET-SOURCE-NAME"= 'BIRT_TESTING') T2
> WHERE T1.APPR_STATE = 'Approved'
> AND T1.ASSET_ID = T2.ASSETID AND T2.STATUS != 'PLANNING AND ANALYZE'
> AND T1.CREATED IS NOT NULL AND T1.APPR_STATE IS NOT NULL ) T3
> Group BY T3.APPR_STATE , T3.CREATED
> ) T4 WHERE T4.CREATED >= ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9 )
> GROUP BY TRUNC (T4.CREATED,'Q')
> ) c,
> ( SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -9) as dt from dual UNION
> SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -6) as dt from dual UNION
> SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , -3) as dt from dual UNION
> SELECT ADD_MONTHS ( TRUNC (SYSDATE, 'Q') , 0) as dt from dual ) b
> where a.quarter_start_date (+) = b.dt AND C.quarter_start_date (+) = b.dt
> order by b.dt ]]></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>QUARTER</design:name>
> <design:position>1</design:position>
> <design:nativeDataTypeCode>12</design:nativeDataTypeCode>
> <design:precision>17</design:precision>
> <design:scale>0</design:scale>
> <design:nullability>Nullable</design:nullability>
> <design:uiHints>
> <design:displayName>QUARTER</design:displayName>
> </design:uiHints>
> </design:attributes>
> <design:usageHints>
> <design:label>QUARTER</design:label>
> <design:formattingHints>
> <design:displaySize>17</design:displaySize>
> </design:formattingHints>
> </design:usageHints>
> </design:resultColumnDefinitions>
> <design:resultColumnDefinitions>
> <design:attributes>
> <design:name>COUNT</design:name>
> <design:position>2</design:position>
> <design:nativeDataTypeCode>2</design:nativeDataTypeCode>
> <design:precision>0</design:precision>
> <design:scale>0</design:scale>
> <design:nullability>Nullable</design:nullability>
> <design:uiHints>
> <design:displayName>COUNT</design:displayName>
> </design:uiHints>
> </design:attributes>
> <design:usageHints>
> <design:label>COUNT</design:label>
> <design:formattingHints>
> <design:displaySize>22</design:displaySize>
> </design:formattingHints>
> </design:usageHints>
> </design:resultColumnDefinitions>
> </design:resultSetColumns>
> <design:criteria/>
> </design:resultSetDefinitions>
> </design:ResultSets>
> </model:DesignValues>]]></xml-property>
> </oda-data-set>
> <oda-data-set
> extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet"
> name="ASSET_REQUEST" id="652">
> <list-property name="columnHints">
> <structure>
> <property name="columnName">VIEW_NAME</property>
> <text-property
> name="displayName">VIEW_NAME</text-property>
> <text-property name="heading">VIEW_NAME</text-property>
> </structure>
> </list-property>
> <structure name="cachedMetaData">
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">VIEW_NAME</property>
> <property name="dataType">string</property>
> </structure>
> </list-property>
> </structure>
> <property name="dataSource">Data Source</property>
> <list-property name="resultSet">
> <structure>
> <property name="position">1</property>
> <property name="name">VIEW_NAME</property>
> <property name="nativeName">VIEW_NAME</property>
> <property name="dataType">string</property>
> <property name="nativeDataType">12</property>
> </structure>
> </list-property>
> <xml-property name="queryText"><![CDATA[select view_name
> from all_views where view_name like 'RPT_ASSET_REQUEST_%' and view_name
> not like '%RPT_ASSET_REQUEST_ACTION_%'
> and view_name not like '%RPT_ASSET_REQUEST_ACTION_%' and view_name not
> like '%RPT_ASSET_REQUEST_APPROVAL%'
> and view_name not like '%RPT_ASSET_REQUEST_PROPERTY%'
> ]]></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>VIEW_NAME</design:name>
> <design:position>1</design:position>
> <design:nativeDataTypeCode>12</design:nativeDataTypeCode>
> <design:precision>30</design:precision>
> <design:scale>0</design:scale>
> <design:nullability>NotNullable</design:nullability>
> <design:uiHints>
> <design:displayName>VIEW_NAME</design:displayName>
> </design:uiHints>
> </design:attributes>
> <design:usageHints>
> <design:label>VIEW_NAME</design:label>
> <design:formattingHints>
> <design:displaySize>30</design:displaySize>
> </design:formattingHints>
> </design:usageHints>
> </design:resultColumnDefinitions>
> </design:resultSetColumns>
> <design:criteria/>
> </design:resultSetDefinitions>
> </design:ResultSets>
> </model:DesignValues>
> ]]></xml-property>
> </oda-data-set>
> </data-sets>
> <styles>
> <style name="report" id="4">
> <property name="fontFamily">sans-serif</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">
> <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>
> <table id="629">
> <property name="width">4.072916666666667in</property>
> <property name="dataSet">final PCD Dataset</property>
> <list-property name="boundDataColumns">
> <structure>
> <property name="name">TOTAL_NO_ASSETS</property>
> <text-property
> name="displayName">TOTAL_NO_ASSETS</text-property>
> <expression name="expression"
> type="javascript">dataSetRow["TOTAL_NO_ASSETS"]</expression>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="name">COMPLIANT</property>
> <text-property
> name="displayName">COMPLIANT</text-property>
> <expression name="expression"
> type="javascript">dataSetRow["COMPLIANT"]</expression>
> <property name="dataType">decimal</property>
> </structure>
> <structure>
> <property name="name">NON_COMPLIANT</property>
> <text-property
> name="displayName">NON_COMPLIANT</text-property>
> <expression name="expression"
> type="javascript">dataSetRow["NON_COMPLIANT"]</expression>
> <property name="dataType">decimal</property>
> </structure>
> </list-property>
> <column id="642">
> <property name="width">1.65625in</property>
> </column>
> <column id="643">
> <property name="width">1.0729166666666667in</property>
> </column>
> <column id="644">
> <property name="width">1.34375in</property>
> </column>
> <header>
> <row id="630">
> <cell id="631">
> <label id="645">
> <text-property
> name="text">TOTAL_NO_ASSETS</text-property>
> </label>
> </cell>
> <cell id="632">
> <label id="647">
> <text-property
> name="text">COMPLIANT</text-property>
> </label>
> </cell>
> <cell id="633">
> <label id="649">
> <text-property
> name="text">NON_COMPLIANT</text-property>
> </label>
> </cell>
> </row>
> </header>
> <detail>
> <row id="634">
> <cell id="635">
> <data id="646">
> <property name="textAlign">center</property>
> <property name="whiteSpace">nowrap</property>
> <property
> name="resultSetColumn">TOTAL_NO_ASSETS</property>
> </data>
> </cell>
> <cell id="636">
> <data id="648">
> <property name="textAlign">center</property>
> <property name="whiteSpace">nowrap</property>
> <property
> name="resultSetColumn">COMPLIANT</property>
> </data>
> </cell>
> <cell id="637">
> <data id="650">
> <property name="textAlign">center</property>
> <property name="whiteSpace">nowrap</property>
> <property
> name="resultSetColumn">NON_COMPLIANT</property>
> </data>
> </cell>
> </row>
> </detail>
> <footer>
> <row id="638">
> <cell id="639"/>
> <cell id="640"/>
> <cell id="641"/>
> </row>
> </footer>
> </table>
> </body>
> </report>

Report message to a moderator

Re: Multiple values for dynamic parameter [message #898072 is a reply to message #896034] Wed, 25 July 2012 17:59 Go to previous messageGo to next message
A R is currently offline A RFriend
Messages: 104
Registered: April 2012
Senior Member
Jason can u help me to understand a bit further.

To add all in default parameter means the complete query ....

Thanks
AR

Report message to a moderator

Re: Multiple values for dynamic parameter [message #898615 is a reply to message #898072] Thu, 26 July 2012 21:38 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member
What I meant is if you know all the ids you could look for a parameter
value all and then just add them manually in the beforeOpen. I wish you
could reproduce this issue with the sample db and I might be able to
offer some more help.

Jason

On 7/25/2012 1:59 PM, A R wrote:
> Jason can u help me to understand a bit further.
>
> To add all in default parameter means the complete query ....
>
> Thanks
> AR

Report message to a moderator

Previous Topic:Placing library in RAP environment
Next Topic:Cannot run report properly while using aggregations
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 23:03:22 GMT 2024

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

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

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly