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

mwilliams

I think I'm missing something. What value is passed from the first dataSet that tells you what "datecolumn" to put in the second query? Is this something that changes for each row of the first dataSet? Or is this a value that you just need to grab once, from a report parameter to determine which "datecolumn" field you need to use? Let me know.