Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Archived » BIRT » Nested Table Name field value from Parent(Dynamic Table Name from Parent Table field value)
Nested Table Name field value from Parent [message #821631] Thu, 15 March 2012 16:15 Go to next message
Peter Brown is currently offline Peter BrownFriend
Messages: 7
Registered: March 2012
Junior Member
Hello,

[Eclipse 3.7.1
linux 64bit
BIRT Framework 3.7.2 v20120213]

Newbie to BIRT (coming from iReport).

I have been scouring the examples and web for a solution to the following:

Goal:
Primary Data Set:
select Abbr FROM Company_Master;

Secondary Data Set:
select Field1, Field2, Field3 FROM Abbr_Accounts, Abbr_Tran

Tried:
Secondary Data Set using Parameters:
manuf (String, Default "ABC", Linked to Report Param - None)
Secondary Data Set:
select Field1, Field2, Field3 FROM ?_Accounts

Master table row in layout:
Expression builder:
abbr = dataSetRow["Abbr"];
dataSetRow["Abbr"]

Secondary Table Data Set Parameter bindings
manuf (String, abbr)

Error: mysql...right syntax to use near ''ABC'_Accounts....'

[I don't want to have to enter multiple params for Company Abbr as this could mean many params with same value so I hope the below string holder will work...]

Alter SQL:
If I try a string holder in the Data Set Query ':abbr' (keeping parameter manuf) and (clicking on nested data set) and then Designer Script -> beforeOpen

this.queryText = this.queryText.replaceAll(':abbr', params["manuf"])

Error: Report parameter manuf does not exist.

this.queryText = this.queryText.replaceAll(':abbr', abbr);

Error: 'Cannot set the string value (AFS) to parameter 1.' and 'SQL error #1:Parameter index out of range (1 > number of parameters, which is 0).'

Remove manuf parameter from secondary data set.

this.queryText = this.queryText.replaceAll(':abbr', abbr);

Error: mysql...right syntax to use near ''ABC'_Accounts....'

Two different tries based on what I could google and I still can't get rid of the ''.

Any help much appreciated.

Thank you,

Report message to a moderator

Re: Nested Table Name field value from Parent [message #821746 is a reply to message #821631] Thu, 15 March 2012 19:24 Go to previous messageGo to next message
Peter Brown is currently offline Peter BrownFriend
Messages: 7
Registered: March 2012
Junior Member
ID10T Error....

string holder in Data Set Query should be :abbr and not ':abbr' if using this.queryText = this.queryText.replaceAll(':abbr', params["manuf"]).

as is should have used this.queryText.replaceAll('\':abbr\'', params["manuf"]).

Report message to a moderator

Re: Nested Table Name field value from Parent [message #821803 is a reply to message #821746] Thu, 15 March 2012 21:00 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member
So you have it working?

Jason

On 3/15/2012 3:24 PM, Peter Brown wrote:
> ID10T Error....
>
> string holder in Data Set Query should be :abbr and not ':abbr' if using
> this.queryText = this.queryText.replaceAll(':abbr', params["manuf"]).
>
> as is should have used this.queryText.replaceAll('\':abbr\'',
> params["manuf"]).

Report message to a moderator

Re: Nested Table Name field value from Parent [message #822381 is a reply to message #821803] Fri, 16 March 2012 14:43 Go to previous messageGo to next message
Peter Brown is currently offline Peter BrownFriend
Messages: 7
Registered: March 2012
Junior Member
Yes.

Summary:
1. Primary query provides a field value e.g. Abbr.
2. Put a string holder in your subquery e.g. select * from ':abbr'_master or (:abbr);
3. Create a table and insert the Abbr from primary query Data Set.
4. Doubleclick on it and enter:
abbr = dataSetRow["Abbr"];
dataSetRow["Abbr"];
5. Insert the sub Data Set into a new detail band of the primary Data set table (no params, no Property Binding).
6. Highlight the subquery Data Set and click on Script in the main report designer window.
7. Script select -> beforeOpen. (Default)
8. Enter this.queryText = this.queryText.replaceAll('\':abbr\'', abbr) or (':abbr', abbr);

The subquery will then run for each primary query replacing the string holder with the primary query's Abbr field value.

[You can also use this for report parameters replaceAll(':abbr', params["all_co"]) when the params "all_co" looks like 'ABC','BCD','CDE']

P.S. I create a sub query report and hard code the query to get the relevant columns. I then copy that across to the main report and insert into the layout. Once that is done I manipulate the query so it is dynamic, as you lose the field columns. Using the ':abbr' versus :abbr will mean you will not get error messages as BIRT tries to parse the query.

Hope that clarifies the steps I used to get it to work, Jason.

Report message to a moderator

Re: Nested Table Name field value from Parent [message #822392 is a reply to message #822381] Fri, 16 March 2012 14:57 Go to previous messageGo to next message
Peter Brown is currently offline Peter BrownFriend
Messages: 7
Registered: March 2012
Junior Member
sorry - you will get error messages depending on where the string holder is... Will not stop the query from running though.

Report message to a moderator

Re: Nested Table Name field value from Parent [message #822541 is a reply to message #822392] Fri, 16 March 2012 21:42 Go to previous messageGo to next message
Peter Brown is currently offline Peter BrownFriend
Messages: 7
Registered: March 2012
Junior Member
P.S. If you have to go back to the Data Set to edit you can change the abbr to a hard coded value e.g. 'ABC' (this.queryText = this.queryText.replaceAll('\':abbr\'', abbr) and you won't get any errors while you work in the Data Set. Then change it back to run preview.

Report message to a moderator

Re: Nested Table Name field value from Parent [message #824409 is a reply to message #822541] Mon, 19 March 2012 16:28 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member
Thanks for the update.

Jason

On 3/16/2012 5:42 PM, Peter Brown wrote:
> P.S. If you have to go back to the Data Set to edit you can change the
> abbr to a hard coded value e.g. 'ABC' (this.queryText =
> this.queryText.replaceAll('\':abbr\'', abbr) and you won't get any
> errors while you work in the Data Set. Then change it back to run preview.

Report message to a moderator

Previous Topic:report issue
Next Topic:Running Count Distinct
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 22:52:33 GMT 2024

Powered by FUDForum. Page generated in 0.04214 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly