Multi-column primary key in cascading parameter group

Hi there, 
 
I need to build a report where an initial parameter drives the values of several other parameters. I found the following demo useful as a starting point: 
<a class='bbc_url' href='http://download.eclipse.org/birt/downloads/examples/reports/2.1/cascade/cascade.html'>http://download.eclipse.org/birt/downloads/examples/reports/2.1/cascade/cascade.html</a> 
 
Let's say we have a department dropdown list, and a role dropdown which shows the roles associated with this department. 
 
The query to get the departments is: 
select C, I, NAME FROM Departments 
 
The query to get the roles for the selected department would then be: 
select C, I, NAME FROM Roles where C_DEPT = ? AND I_DEPT = ? 
 
However, when I create a cascading parameter group, I can only select one column as the value from the department dropdown. 
 
Is there any way I could get round this problem? 
 
Many thanks, 
Cedric

Find more posts tagged with

Comments

JasonW

Cedric,

Can you not use a multi dataset cascade for this?
create the first dataset against departments and the second against roles
In your cascade group you levels would look like

1 - c deptdataset
2 - i deptdataset
3 - name rolesdataset

Your roles dataset would contain
select C, I, NAME FROM Roles where C_DEPT = ? AND I_DEPT = ?

and you would tie the two dataset parameters to report parameters
params["c"]
and
params[i"]

Jason

cypherdj

Hi Jason,

would that not create 2 parameter drop down lists, one for c_dept and one for i_dept though?

From the report parameter capture, I'd want:

rpt_dept : Department DDL (displays name, but value would be a pair [c_dept, i_dept], based on departments data set "select c, i, name from department")

rpt_role : Role multi-select DDL (based on rolesByDept data set "select c, i, name from role where c_dept = ? and i_dept = ?" -> here is where I struggle to link my data set to the single rpt_dept parameter to the query parameters qry_CDept, qry_IDept)

Does that make more sense? From the gui, you can only associate a single data set column to the rpt_dept parameter value, unless you would create a computed column which was my original idea to get round this limitation: value = row["C"] + "_" + row["I"] and then parse the selected value in the beforeOpen method of the rolesByDept data set:

var deptKey = params["rpt_dept"].value.split("_");
var cdept = deptKey[0];
var idept = deptKey[1];

this.params["qry_cdept"].value = cdept;
this.params["qry_idept"].value = idept;

but this does not seem to work. Any ideas what I'm doing wrong in this case?

Regards,
Cedric

JasonW

Cedric,

You are correct. Could you not just take the roles query and put it in like
select C, I, NAME FROM Roles

And in the beforeOpen of the roles query add this

var depKey = params["rpt_dept"].value.split("_");
var cdpt = deptKey[0];
var idptt = deptKey[1];

this.queryText = this.queryText + " where c_dept = " + cdpt + " and i_dept =" +idpt;

You may also want to add something like this for testing
importPackage( Packages.java.io );
out = new PrintWriter( new FileWriter( "c:/test/myquery.txt", true ) );
out.println( "cdpt = "+ cdpt + " idpt= " +idpt);
out.close();

Jason

cypherdj

Hi Jason,

yes, that sounds like it would fit better with what I'm trying to do. The other option would be to delve into the runtime and framework code and make the change to support multi-column value keys, but at this moment, I think the concatenating of values might just do the trick.

I'll try to give this a go in the coming days,
Thanks for your time, and I'll plug the log code in as well to monitor what's happening,
Regards,
Cedric

cypherdj

Hi there,

I found one way round this issue, by adding a computed column CLASS_ID = row["CLASS"] + "_" + row["ID"].

However, I'm now faced with a second problem:
- I have several parameters, dateTo, dateFrom, department, role and workTypes,
- workTypes is a multi-select,
- role and workTypes depend upon the value of department.

It would appear that cascading parameters do not allow multi-selects for starters so that's my 1st problem.

2ndly, when I create a cascading parameter group, with department, role and workTypes in this order, I must select a value from the role drop-down before the workTypes drop-down is updated. So there is currently no way to indicate which parameter drives the values of a subsequent parameter, apart from the order in which they are created, ie, department drives role, and role in turn drives workTypes.

I thought this might be overcome by introducing a parameter group, then attempt to drag and drop it into the cascading parameter group, but this doesn't seem to work.

Any suggestions on both problems would be greatly appreciated,
See attached report design,
Kind regards,
Cedric

JasonW

Cedric,

You should log a bugzilla entry for the multiselect at the eclipse.org/birt website.
As far as the second issue, can you try and the display name to do the indication?
eg, for role enter role(based on department).

Jason

cypherdj

Hi Jason,

sure, I'll log a bugzilla, might have been fixed by now, as I'm on BIRT framework 2.2.2 ;-)

not sure what you mean about the display name? Do you mean the label for the parameter?

JasonW

Cedric,

What i meant is that you can set a displaytext column for each level in the cascade. As a work around you could create a computed column in you data set that has the value that gives a brief description for the levels.

Jason

cypherdj

Looks like the multi-select issue has been raised already and is scheduled for release 2.3: 
 
<a class='bbc_url' href='https://bugs.eclipse.org/bugs/show_bug.cgi?id=208477'>https://bugs.eclipse.org/bugs/show_bug.cgi?id=208477</a> 
 
Regards, 
Cedric