Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » 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] Mon, 14 November 2016 04:03 Go to next message
Devpriyo Bhattacharya is currently offline Devpriyo BhattacharyaFriend
Messages: 12
Registered: October 2016
Junior Member
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
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 17:51 Go to previous messageGo to next message
Jerry Sheets is currently offline Jerry SheetsFriend
Messages: 70
Registered: July 2009
Location: Salt Lake City, UT
Member
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/
index.php/fa/27578/0/
  • Attachment: dsPram.png
    (Size: 32.83KB, Downloaded 761 times)
  • Attachment: ScriptPram.png
    (Size: 18.60KB, Downloaded 734 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 10:35 Go to previous messageGo to next message
Devpriyo Bhattacharya is currently offline Devpriyo BhattacharyaFriend
Messages: 12
Registered: October 2016
Junior Member
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
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 11:00 Go to previous message
Devpriyo Bhattacharya is currently offline Devpriyo BhattacharyaFriend
Messages: 12
Registered: October 2016
Junior Member
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 11:03]

Report message to a 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:
  


Current Time: Sun Sep 15 05:28:19 GMT 2019

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

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

Back to the top