nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Pulling datarow from outer dataset

TheShadraq

In one of my reports there is a section that goes 3 dataSets & Tables Deep.

mainDataSet --> taskDataSet --> workDataSet

In my sql query in workDataSet, I need specific rows from the other DS. For mainDataSet I used row[0]["dataFromMain"] and it pulls the correct data. For taskDataSet I've hit a wall. I've tried multiple things and haven't gotten anything to work correctly.

I know my query is fine, because if I hard-code in specific task #'s everything runs as it should, with correct data.

my latest attempt used row._outer["dataFromTask"]. This did not work.

essentially:

Select data,data1,data2 from tables where maindata = row[0]["dataFromMain"] and taskdata = ???["dataFromTask"]

Any help you have would be most welcome.

Find more posts tagged with

Comments

mwilliams

What is your version? Any way you can recreate this issue with the sample database?

TheShadraq

BIRT 2.3.2 
 
It's not an issue so much as it is a how to. How do I put into my dataSet where clause parameters from both data sets?

mwilliams

The row._outer should work. Can you attach your report?

TheShadraq

Mike, attached is my file. It's pretty large so here is what you're looking at:

mwilliams

The issue with using row._outer is probably because you're trying to use it directly in the dataSet script. Try setting up a dataSet parameter, in the dataSet editor, then going to the binding tab of the inner table, and clicking on the dataSet parameter binding tab to set it up.

TheShadraq

So for the outer DS I added an output parameter:

Name: theTaskID
Data Type: String
Direction: Output

In the fetch I put: outputParams["theTaskID"] = taskDetailsDataSet.getString("taskid");

For the inner DS I added an input parameter:
Name: theTaskID
Data Type: string
Direction: Input
Default Value: outputParams["theTaskID"]

Inside my inner DS Query I have:
var theTaskID = inputParams["theTaskID"];

Now it runs without error. However, it isn't looping. Such as, there are 9 tasks and it's only passing in the first one and iterating it throughout the loop (see attached)

mwilliams

Don't use the output parameter. Use the dataSet parameter binding button, in the binding tab of the inner table to set up the passing of the row._outer.

TheShadraq

Ok, so using the above I have done the following: 
 
Removed Param and Fetch reference from outer DS 
 
Inner DS: Edit Data Set --> Parameters --> Edit 
Name: theTaskID 
Data Type: String 
Direction: Input: 
Default Value: <empty> 
* This gave me an error, but I saved anyway. It was the only way Dataset Parameter Binding option showed something to edit. 
 
Inner DS: Dataset Parameter Binding --> Edit --> Selected appropriate value (ended up as row["taskid"]) --> Ok 
 
Inner DS Query: removed var theTaskID = inputParams["theTaskID"]; and let it just stand as is. 
 
Same result as before with the previous screenshot. Did I miss something?

mwilliams

Doesn't look like it. Was just having you try another option. I'll have to set something up to run a similar situation with a scripted dataSet. I'll post back with what I find.

TheShadraq

Ok. Thanks.

mwilliams

This may be overly simplified, but it returns the correct results using an inputParams and the outer table's value passed through, using dataSet parameter binding.

TheShadraq

Ah, yes. That did it. Looks like I had my params muddled up just a bit. Thanks for all of your help. I learned quite a bit.

Thanks,
Shadraq

mwilliams

Great! Always glad to help!