Eclipse Community Forums: BIRT » BIRT v4.6 - Create a Dynamic Dataset Query Based on Parameter Inputs ?

Home » Archived » BIRT » BIRT v4.6 - Create a Dynamic Dataset Query Based on Parameter Inputs ?

BIRT v4.6 - Create a Dynamic Dataset Query Based on Parameter Inputs ? [message #1747371]

Sun, 13 November 2016 23:03

Eclipse User

My dataset query is like:

Select time.year, time.quarter, time.month, dim1.country, dim2.bank, fact.measure1
from fact
left join time on (fact.fkey=time.pkey)
left join dim1 on (fact.fkey1=dim1.pkey1)
left join dim2 on (fact.fkey2=dim2.pkey2)

I have created two computed columns:

comp_col1
if (params["param_time"] == "Year") {row["year"];}
else if (params["param_time"] == "Quarter") {row["quarter"];}
else if (params["param_time"] == "Month") {row["month"];}
else{0}

comp_col2
if (params["param_dim"] == "Country") {row["country"];}
else if (params["param_dim"] == "Bank") {row["bank"];}
else{0}

The two report parameters, param_time and param_dim, have the following static values:
param_time = Year, Quarter, Month
param_dim = Country and Bank (Country and Bank has many values)

In the crosstab, I have dragged [u]comp_col1 into the row area and comp_col2 into the column area and added measure1 in detail area.

When I run the report, I get to choose the Time and Dimension parameters and I get the desired result.

My requirement: Now, I need the dataset query to be built dynamically based on the user input values for the two paramaters i.e. I need a code in the beforeOpen window of my dataset, with following functional logic:

SELECT comp_col1, comp_col2, sum(fact.measure1)
FROM fact
WHERE comp_col1 = userinputvalue for param_time
AND comp_col2 = userinputvalue for param_dim
GROUP BY comp_col1, comp_col2

Looking forward to some advice and suggestions. My sample report atttached.

Best Regards

Attachment: DynamicCrosstabQuery_wip.rptdesign
(Size: 28.40KB, Downloaded 327 times)

Re: BIRT v4.6 - Create a Dynamic Dataset Query Based on Parameter Inputs ? [message #1747441 is a reply to message #1747371]

Mon, 14 November 2016 12:51

Eclipse User

You can either substitute a '?' for the parameters and add a data set parameter linked to the report parameter or you can move the Where Clause and Group By to the beforeOpen method of the data set. (See images)
index.php/fa/27577/0/

Attachment: dsPram.png
(Size: 32.83KB, Downloaded 2888 times)
Attachment: ScriptPram.png
(Size: 18.60KB, Downloaded 2872 times)

Re: BIRT v4.6 - Create a Dynamic Dataset Query Based on Parameter Inputs ? [message #1747591 is a reply to message #1747441]

Wed, 16 November 2016 05:35

Eclipse User

Jerry Sheets wrote on Mon, 14 November 2016 23:21

You can either substitute a '?' for the parameters and add a data set parameter linked to the report parameter or you can move the Where Clause and Group By to the beforeOpen method of the data set. (See images)

Thanks for the reply Jerry.

I have done it the beforeOpen way. I am building my dataset query dynamically by adding the Select, From and Group By items into the beforeOpen script.

index.php/fa/27598/0/

But I am getting the foll. error:

Caused by: java.sql.SQLException: [ActuateDD][SQLServer JDBC Driver][SQLServer]The multi-part identifier "s.stat_cod_pk" could not be bound.

I have checked the script for spaces and typos. All seem correct but still the above error. Also, the same reportdesign but with different dimensions using CLASSISMODELS is working fine!

My report design attached.

Best Rgards

Attachment: Dynamic Crosstab_wip_2.rptdesign
(Size: 22.08KB, Downloaded 209 times)
Attachment: Dynamic Crosstab_wip_2.png
(Size: 95.79KB, Downloaded 2921 times)

Re: BIRT v4.6 - Create a Dynamic Dataset Query Based on Parameter Inputs ? [message #1748080 is a reply to message #1747591]

Fri, 18 November 2016 06:00

Eclipse User

Devpriyo Bhattacharya wrote on Wed, 16 November 2016 16:05

Jerry Sheets wrote on Mon, 14 November 2016 23:21
You can either substitute a '?' for the parameters and add a data set parameter linked to the report parameter or you can move the Where Clause and Group By to the beforeOpen method of the data set. (See images)

Thanks for the reply Jerry.

I have done it the beforeOpen way. I am building my dataset query dynamically by adding the Select, From and Group By items into the beforeOpen script.

But I am getting the foll. error:

Caused by: java.sql.SQLException: [ActuateDD][SQLServer JDBC Driver][SQLServer]The multi-part identifier "s.stat_cod_pk" could not be bound.

I have checked the script for spaces and typos. All seem correct but still the above error. Also, the same reportdesign but with different dimensions using CLASSISMODELS is working fine!

My report design attached.

Best Rgards

Problem Solved!! Smile

In the beforeOpen script, for the params[Dimension], I just placed the From clause this.queryText.replace expression at the top, followed by the Select clause expression and then finally the Group By clause expression.

It's working now.

Best Regards

[Updated on: Fri, 18 November 2016 06:03] by Moderator

Previous Topic:	Help!Unsupported hive2 protocol(birt+hive2+tomcat)
Next Topic:	Unable to add com.microsoft.sqlserver.jdbc.SQLServerDriver drivers in new DataSource

Goto Forum:

-=] Back to Top [=-

Current Time: Sun Jul 13 20:32:20 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter