| BIRT v4.6 - Create a Dynamic Dataset Query Based on Parameter Inputs ? [message #1747371] |
Mon, 14 November 2016 04:03  |
 Devpriyo Bhattacharya
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 #1748080 is a reply to message #1747591] |
Fri, 18 November 2016 11:00  |
Devpriyo Bhattacharya
Messages: 12
Registered: October 2016 |
Junior Member |
|
|
Devpriyo Bhattacharya wrote on Wed, 16 November 2016 16:05Jerry Sheets wrote on Mon, 14 November 2016 23:21You 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!! 
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 |
|
|
Powered by
FUDForum. Page generated in 0.01489 seconds
Search
Help
Register
Login
Home
