Home » Archived » BIRT » Dynamic Query "from"
| Dynamic Query "from" [message #180097] |
Wed, 26 July 2006 04:06  |
Eclipse User |
|
|
|
Originally posted by: jeromevergereau.hotmail.com
Hello,
Is it possible to change dynamically the following part with BIRT in a
query : "from table1 t,table2 p" ???
For example I wanted to change the name of a table in my dataSet like I
would do for a parameter, so I tried using "property Binding" but it
didn't work.
for example at one time I would have the following query :
select t.toto, p.bob
from personne t, pays_french p
where ....
and at another time I would have this one :
select t.toto, p.bob
from personne t, pays_USA p
where ....
I use report design 2.1.0
thx.
|
|
|
| Re: Dynamic Query "from" [message #180204 is a reply to message #180097] |
Wed, 26 July 2006 10:42  |
| Eclipse User |
|
|
|
At runtime, you can replace the entire query. You will want to write
your query originally in the .rptdesign file to end in FROM Then, you
will add in the rest at runtime. Here is an example: (To help you notice
the important parts, I will add an extra line above and below the portions
you will need to edit to get your query to be changed at runtime. In this
example, I am changing the WHERE clause at runtime. However, using the
same approach, you will be able to change your query from the FROM clause
on down.
Items of note below:
1.) The parameter WHERE_CLAUSE is set in my Java class before I reach
here.
2.) The <method name="beforeOpen"> is where the substitution happens.
If you
examine it, you will see that it gets the existing queryText and
then appends the additions to the WHERE clause. Finally, this replaces the
original queryText with the new version (with the where appended).
3.) Next, at the very end of the query, you see that I have only the
WHERE clause. I know my program will add that in so I want to
prepare
my query to accept that addition.
4.) Finally, for the sake of completeness, I have added the Java
methods that actually set everything up.
<?xml version="1.0" encoding="UTF-8"?>
<!-- Written by Eclipse BIRT 2.0 -->
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.0.1 Build
<20060222-1204></property>
<property name="units">in</property>
<text-property name="displayName">Worksheet Report</text-property>
<parameters>
<scalar-parameter name="WHERE_CLAUSE" id="7">
<property name="valueType">static</property>
<property name="dataType">string</property>
<property name="allowNull">true</property>
<property name="defaultValue">1 = 0</property>
<property name="controlType">text-box</property>
<property name="format">Unformatted</property>
</scalar-parameter>
</parameters>
<data-sources>
<oda-data-source
extensionID="org.eclipse.birt.report.data.oda.jdbc" name="Data Source"
id="4">
<property
name="odaDriverClass">com.microsoft.sqlserver.jdbc.SQLServerDriver </property>
<property
name="odaURL">jdbc:sqlserver://localhost:1433;databaseName=FREYA_DB</property>
<property name="odaUser">USERNAME</property>
<encrypted-property
name="odaPassword">bWlkZ2FyZDE=</encrypted-property>
</oda-data-source>
</data-sources>
<data-sets>
<oda-data-set
extensionID="org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet "
name="Claim Data Set" id="6">
<property name="dataSource">Data Source</property>
<method name="beforeOpen"><![CDATA[this.queryText =
this.queryText + " " + params["WHERE_CLAUSE"];]]></method>
<property name="queryText">
SELECT
(
CASE
WHEN CLAIM1.SUBMITTED_CLAIM_IID IS NULL
THEN CLAIM1.CLAIM_IID
ELSE CLAIM1.SUBMITTED_CLAIM_IID
END
) AS 'ClaimDS CLAIM_IID',
CLAIM1.CLAIM_IID AS 'Orig CLAIM_IID',
CLAIM_LINE.CLAIM_LINE_IID AS 'ClaimDS CLAIM_LINE_IID',
CLAIM1.CLAIM_EID AS 'Claim Id',
CLAIM1.ACCOUNT_EID AS 'Account Id',
ACCOUNT.NAME AS 'Account',
CLAIM1.PLAN_EID AS 'Plan Id',
INSURANCE_PLAN.DESCRIPTION AS 'Plan',
PATIENT.MEDICAL_RECORD_NUMBER AS 'Patient Id',
PATIENT.DATE_OF_BIRTH AS 'Patient DOB',
PATIENT.GENDER AS 'Gender',
CLAIM1.SYSTEM_ENTRY_DATE AS 'Import Date',
CLAIM1.BATCH_EID AS 'Batch',
ENTERPRISE.NAME AS 'Enterprise',
CLAIM1.CONTEXT AS 'Environment',
CLAIM1.LAST_ANALYSIS_DATE AS 'Analysis Date',
CLAIM1.CLAIM_STATUS AS 'Claim Status',
CLAIM_LINE.CLAIM_LINE_EID AS 'Line Id',
CLAIM_LINE.SERVICE_START_DATE AS 'Begin DOS',
CLAIM_LINE.SERVICE_END_DATE AS 'End DOS',
CLAIM_LINE.SUBMITTED_PROCEDURE_CODE AS 'Sub Proc Code',
CLAIM_LINE.ADJUSTED_PROCEDURE_CODE AS 'Adj Proc Code',
PROCEDURE_CODE.DESCRIPTION AS 'Proc Code Desc',
(
CASE
WHEN
(
SELECT
COUNT(PROCEDURE_CODE)
FROM
PROCEDURE_CODE
WHERE
PROCEDURE_CODE = CLAIM_LINE.SUBMITTED_PROCEDURE_CODE
AND
CATEGORY = 'CP'
) = 0
THEN 0
ELSE 1
END
) AS 'AMA Sub Proc Code',
(
CASE
WHEN
(
SELECT
COUNT(PROCEDURE_CODE)
FROM
PROCEDURE_CODE
WHERE
PROCEDURE_CODE = CLAIM_LINE.ADJUSTED_PROCEDURE_CODE
AND
CATEGORY = 'CP'
) = 0
THEN 0
ELSE 1
END
) AS 'AMA Adj Proc Code',
CLAIM1.IS_MODIFIED AS 'Modified From Claim',
CLAIM_LINE.SERVICE_UNITS AS 'Units',
CLAIM_LINE.TYPE_OF_SERVICE_CODE AS 'TOS',
CLAIM_LINE.PLACE_OF_SERVICE_CODE AS 'POS',
CLAIM_LINE.REVENUE_CODE AS 'Revenue Code',
PRACTITIONER.PRACTITIONER_EID AS 'Provider Id',
PRACTITIONER.FIRST_NAME AS 'Provider FName',
PRACTITIONER.LAST_NAME AS 'Provider LName',
CLAIM_LINE.CHARGED_AMOUNT AS 'Sub Charge',
CLAIM_LINE.ADJUSTED_AMOUNT AS 'Adj Charge',
CLAIM_LINE.RVU AS 'RVU',
CLAIM_LINE.DISPOSITION AS 'Disposition',
CLAIM_LINE.IS_MODIFIED AS 'Modified From Claim_Line',
CLAIM_LINE.REFERENCE_LINES AS 'Ref Line ID',
CLAIM_LINE.MODEL_LINE AS 'Model Line ID',
CLAIM_LINE_MODIFIER.MODIFIER_CODE AS 'Modifier Code',
CLAIM_LINE_MODIFIER.ADJUSTED_MODIFIER_CODE AS 'Adj Modifier Code',
CLAIM_LINE_MODIFIER.MODIFIER_ORDER AS 'Modifier Order',
MODIFIER_CODE.DESCRIPTION AS 'Modifier Description',
CLAIM_LINE_DIAGNOSIS.DIAGNOSIS_CODE AS 'Diagnosis Code',
CLAIM_LINE_DIAGNOSIS.DIAGNOSIS_ORDER AS 'Diagnosis Order',
DIAGNOSIS_CODE.DESCRIPTION AS 'Diagnosis Description',
CLAIM_LINE_EDIT.EDIT_MNEMONIC AS 'Edit Mnemonic',
CLAIM_LINE_EDIT.EDIT_MESSAGE AS 'Edit Description',
CLAIM_LINE_EDIT.ACTION AS 'Flag Status',
CLAIM_LINE.SEQUENCE_NUMBER AS 'Claim Line Seq',
CLAIM_LINE.PRIOR_AUTHORIZATION_CODE AS 'Pre-Auth',
CLAIM_LINE.RULESET_NAME AS 'Ruleset',
CLAIM_LINE.STATUS AS 'Status',
(
SELECT
SUM(CLAIM_LINE.CHARGED_AMOUNT)
FROM
CLAIM_LINE
WHERE
CLAIM_IID = CLAIM1.CLAIM_IID
AND
CLAIM1.IS_MODIFIED = 'N'
GROUP BY
CLAIM_IID
) AS 'Total Sub Charge',
(
SELECT
SUM(CLAIM_LINE.ADJUSTED_AMOUNT)
FROM
CLAIM_LINE
WHERE
CLAIM_IID = CLAIM1.CLAIM_IID
AND
CLAIM1.IS_MODIFIED = 'N'
GROUP BY
CLAIM_IID
) AS 'Total Adj Charge',
(
CASE
WHEN
(
(
SELECT
COUNT(DISTINCT CLAIM_IID)
FROM
CLAIM
WHERE
CLAIM_IID = CLAIM1.CLAIM_IID
OR
SUBMITTED_CLAIM_IID = CLAIM1.CLAIM_IID
) > 1
AND
CLAIM1.IS_MODIFIED = 'N'
)
THEN 0
ELSE 1
END
) SHOW_RESULTS
FROM
CLAIM CLAIM1
INNER JOIN
CLAIM_LINE
ON CLAIM1.CLAIM_IID = CLAIM_LINE.CLAIM_IID
INNER JOIN
ENTERPRISE
ON CLAIM1.ENTERPRISE_IID = ENTERPRISE.ENTERPRISE_IID
LEFT OUTER JOIN
PROCEDURE_CODE
ON CLAIM_LINE.ADJUSTED_PROCEDURE_CODE = PROCEDURE_CODE.PROCEDURE_CODE
LEFT OUTER JOIN
CLAIM_LINE_MODIFIER
ON CLAIM_LINE.CLAIM_LINE_IID = CLAIM_LINE_MODIFIER.CLAIM_LINE_IID
LEFT OUTER JOIN
MODIFIER_CODE
ON CLAIM_LINE_MODIFIER.MODIFIER_CODE = MODIFIER_CODE.MODIFIER_CODE
LEFT OUTER JOIN
CLAIM_LINE_DIAGNOSIS
ON CLAIM_LINE.CLAIM_LINE_IID = CLAIM_LINE_DIAGNOSIS.CLAIM_LINE_IID
LEFT OUTER JOIN
DIAGNOSIS_CODE
ON CLAIM_LINE_DIAGNOSIS.DIAGNOSIS_CODE = DIAGNOSIS_CODE.DIAGNOSIS_CODE
LEFT OUTER JOIN
INSURANCE_PLAN
ON CLAIM1.PLAN_EID = INSURANCE_PLAN.INSURANCE_PLAN_EID
LEFT OUTER JOIN
ACCOUNT
ON CLAIM1.ACCOUNT_EID = ACCOUNT.ACCOUNT_EID
LEFT OUTER JOIN
CLAIM_LINE_EDIT
ON CLAIM_LINE.CLAIM_LINE_IID = CLAIM_LINE_EDIT.CLAIM_LINE_IID
LEFT OUTER JOIN
PATIENT
ON CLAIM1.PATIENT_IID = PATIENT.PATIENT_IID
LEFT OUTER JOIN
PRACTITIONER
ON CLAIM_LINE.PRACTITIONER_IID = PRACTITIONER.PRACTITIONER_IID
WHERE
</property>
</oda-data-set>
private void getHTMLReport(IMarkupWriter pWriter, Object[] pParameters)
{
ReportEngine engine = ReportEngineFactory.getEngine();
// Run reports, etc.
try
{
HashMap<String, Object> parameters = new HashMap<String, Object>();
setOutgoingParameters( parameters, pParameters );
IReportRunnable design = getReportSpecification( engine,
(String)pParameters[ 0 ] );
// Create task to run the report and render the report
IRunAndRenderTask task = engine.createRunAndRenderTask(design);
// Set Render context to handle url and image locataions
HTMLRenderContext renderContext = new HTMLRenderContext();
// renderContext.setImageDirectory("image");
HashMap<String, Object> contextMap = new HashMap<String, Object>();
contextMap.put(EngineConstants.APPCONTEXT_HTML_RENDER_CONTEX T,
renderContext);
task.setAppContext(contextMap);
task.setParameterValues(parameters);
populateDatabaseConnectionParameters( design );
// create output stream
ByteArrayOutputStream out = new ByteArrayOutputStream(2048);
// Set rendering options - such as file or stream output,
// output format, whether it is embeddable, etc
HTMLRenderOption options = new HTMLRenderOption();
options.setOutputStream(out);
options.setOutputFormat("html");
task.setRenderOption(options);
// UTF-8
task.run();
pWriter.printRaw( out.toString( "UTF-8" ) );
//printReport(pWriter, engine, report);
}
catch (Exception e1)
{
e1.printStackTrace();
}
// Shut down the engine.
if (engine != null)
{
// engine.destroy();
}
}
private IReportRunnable getReportSpecification( ReportEngine pEngine,
String pFileName ) throws Exception
{
InputStream stream =
PeReportRenderer.class.getClassLoader().getResourceAsStream( pFileName );
IReportRunnable design = pEngine.openReportDesign( stream );
return design;
}
/** This is for the Worksheet_Report.rptdesign */
public void populateParameters1( HashMap<String, Object> pOut, Object[]
pIn )
{
StringBuffer where = new StringBuffer();
where.append( " \n CLAIM1.DISCRIMINATOR = 'PRO' " );
where.append( " \n AND CLAIM1.CLAIM_STATUS = '" + Identifiable.ACTIVE
+ "' " );
where.append( populateStartAndEndClaimIID( pOut, pIn[ 1 ],
pIn[ 2 ] ) );
/** The following logic is necessary in case we arrived here via the
Claim Edit Screen, which only
* supplies parameters for parms 0, 1, 2 */
if( pIn.length > 3 )
{
where.append( populateStartAndEndSystemEntryDate( pOut, pIn[ 3
], pIn[ 4 ] ) );
where.append( populateStartAndEndServiceStartDate( pOut, pIn[ 5
], pIn[ 6 ] ) );
where.append( populateStartAndEndBatchEID( pOut, pIn[ 7
], pIn[ 8 ] ) );
where.append( populateStartAndEndPractitionerEID( pOut, pIn[ 9
], pIn[ 10 ] ) );
where.append( populateStartAndEndMedicalRecordNumber( pOut, pIn[ 11
], pIn[ 12 ] ) );
where.append( populateFlags( pOut, pIn[ 17
], pIn[ 18 ], pIn[ 19 ], pIn[ 20 ] ) );
where.append( populateEnvironment( pOut, pIn[ 21
], pIn[ 22 ] ) );
where.append( populateAnalysisType( pOut, pIn[ 26
] ) );
where.append( populateAccountsList( pOut, pIn ) );
where.append( populatePlansList( pOut, pIn ) );
where.append( populateFlagsList( pOut, pIn ) );
}
pOut.put( "WHERE_CLAUSE", where.toString() );
}
|
|
|
| Re: Dynamic Query "from" [message #180361 is a reply to message #180204] |
Thu, 27 July 2006 00:36 |
| Eclipse User |
|
|
|
Originally posted by: jeromevergereau.hotmail.com
Thx Tyrone Hed ^_^
I will try this.
Actually I use "scripted DataSet" to manage my query but I have to
redefine a java class to make the data connection etc etc.
Now I'm gone try your example.
Thx again!!!
Merci :)
|
|
|
Goto Forum:
Current Time: Fri Apr 25 21:36:32 EDT 2025
Powered by FUDForum. Page generated in 0.03468 seconds |
Home