Accessing a value from a dataset and passing it as a parameter for another dataset

easothomas

Hi

In Birt, I have a requirement like this.

I have two tables which has student name, teacher's name, subject name and marks.
I am querying this table to get all these details. While showing each student's marks across each subject and its teacher's name, I need to show his attendance also. So I am passing some values from the first dataset to the second dataset and get the values.

the query for the first data set is something like this

select studentname, subjectname, teacher name, SUM(marks), marks_duration from studenttable group by studentname, subjectname,teachername,marks_duration

In the second dataset, I am getting the studentname from the first dataset and then firing the query to get his attendance details

select datetable.semestercolumn, sum(days) from attendancetable where studentname=? group by semestercolumn

I have set the studentname to be passed dynamically when birt runs and gets the value for the second dataset.

So if the first dataset returns 10 rows, the second dataset query will be fired 10 times, one for each student name.

But the problem here is that I need to change the datecolumn (that is semestercolumn mentioned earlier) also dynamically. That is if the aggregation is done for one year, then the second dataset query should be

select datetable.<datecolumn>, sum(days) from attendancetable where studentname=? group by <datecolumn>

So <datecolumn> should be semestercolumn if I query for semester, yearcolumn if I query for year, monthcolumn if I query for month.

I can put ? in the edit Dataset window. But I don't think I can change the column name dynamically via the Property Binding editor.

Can you guys help me.

See the attached image for clarity.

thanks,
Thomas

Tubal

If your second table is inside of your first one, you will have access to the data that is in the first table.

In your second dataset, set it up to use two parameters:

select datetable.<datecolumn>, sum(days) from attendancetable where studentname=? group by ?

Leave the first parameter as is (linked to a report parameter), and when you set up the second parameter, then where it says "Linked to Report Parameter" leave that as none. That will tell it to look for the parameter data somewhere else.

Then in your second table's data bindings, you can assign this 2nd parameter to a value from the first table.

See the image.

The only thing I'm not sure of is if you can group based on a parameter. This would be an SQL issue, not a BIRT issue. If you can't, you'd have to edit your dataset in the beforeOpen script to add the grouping.

Hopefully this will get you started.