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; } } }

Reg dynamic parameter

sairag

For one specific requirement I need to come up a query as follows for my dataset.

select * FROM ASSET_REQUEST_29 UNION ALL
select * FROM ASSET_REQUEST_31 UNION ALL
select * FROM ASSET_REQUEST_32 UNION ALL
select * FROM ASSET_REQUEST_33 UNION ALL
select * FROM ASSET_REQUEST_34 UNION ALL
select * FROM ASSET_REQUEST_36

Problem here is there is possibility of that ifin future new version of the "ASSET_REQUEST" is created like "ASSET_REQUEST_37, ASSET_REQUEST_38" then my report won't be able to capture the informaiton from the same. I need to modify the report.

Please suggest me , how can I be able to achieve the approach where I can dynamically over come this issue.

Let me know if I need to provide any details further.

Thanks
AR

Find more posts tagged with

Comments

mwilliams

Could you use parameters to pass the range of asset_request tables you'll want to use and build the query in your dataSet's beforeOpen script?

sairag

Williams can you please elaborate ur answer.

So far I am not able to achieve the solution. Now I am trying to achieve: if I have tables a result of one dataset( ASSET_REQUEST_37, ASSET_REQUEST_38, ASSET_REQUEST_45, ASSET_REQUEST_52 etc ) how can I select the data from all the tables in a single dataset ?

Please let me know if any inputs need from me.

Thanks
Sairag

mwilliams

How do you know what Asset_Request tables you'll need in the report? Is this something that is or could be selected or entered in a parameter? If so, you could do something like this: 
 
Parameter Selection Screen 
 
Select Assets: 
*37 
*38 
39 
40 
41 
42 
43 
44 
*45 
 
The ones with * are selected 
 
Then, in the beforeOpen of the dataSet you could build your query with something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
query = "select * from ASSET_REQUEST_" + params["myParam"][0];
i=1;

while (i < params["myParam"].length){
query = query + " UNION ALL select * from ASSET_REQUEST_" + params["myParam"][i];
i++;
}

this.queryText = query;

</pre>
 
I didn't test this, but it'd be something like this. 
 
If the asset tables aren't selected as a parameter, how do you know which ones you need to use? Let me know.

sairag

Williams for your question : How do you know what Asset_Request tables you'll need in the report?

Ans: I am hitting query on all_tables/all_views to identify the tables that will help to achieve my requirement.

Using this approach I am able to get the list of tables in one dataset. Problem here is how to union all the tables in the list is the show stopper

Please help me with your suggestions.

Thanks
AR

sairag

Williams can you please check the following thread for the same issue.

http://www.eclipse.org/forums/index.php/m/897643/#msg_897643

Thanks
AR

sairag

Hi Williams, I am sorry. Now only I am getting slowy the above solution won't help me to achieve the requirement. I am trying this option to achieve the issue :

http://www.eclipse.org/forums/index.php/m/895607/#msg_895607

Please find the attahed qry for the dataset, that gives a static solution. Please let me know if any concerns.

Thanks
AR

mwilliams

<blockquote class='ipsBlockquote' data-author="'sairag'" data-cid="107514" data-time="1343165035" data-date="24 July 2012 - 02:23 PM">
Williams for your question : How do you know what Asset_Request tables you'll need in the report? 
 
Ans: I am hitting query on all_tables/all_views to identify the tables that will help to achieve my requirement. 
 
Using this approach I am able to get the list of tables in one dataset. Problem here is how to union all the tables in the list is the show stopper

Please help me with your suggestions. 
 
Thanks 
AR </blockquote>
 
Ok, so you're returning one dataSet that has the list of tables that you want to use in your main query? Is this correct? Let me know.

sairag

Yes sir, this is what I am looking for.

I have attached the main query, for reference.

Please help me asap at ur convenience.

Thanks
AR

mwilliams

Ok. You should be able to do something like this: 
 
In your design, put a hidden text box at the top, bound to the dataSet with the list of tables you want to access. In the beforeOpen of that dataSet, put something like: 
 
myTables = new Array(); 
 
In the onFetch of that dataSet, put something like: 
 
myTables[myTables.length] = row["tableNameField"]; 
 
In the beforeClose, put: 
 
reportContext.setPersistentGlobalVariable("myTables",myTables); 
 
In the beforeOpen of your main dataSet, you should now be able to access this array of tables, like: 
 
myTables = reportContext.getPersistentGlobalVariable("myTables"); 
 
Now, you can step through your array and build your query, with more beforeOpen script: 
 
i=1; 
query = "select * from " + myTables[0]; 
while (i < myTables.length){ 
query = query + "UNION ALL select * from " + myTables; 
i++; 
} 
this.queryText = query; 
 
Or modify your existing queryText in a similar fashion.

sairag

Hi Williams - I am not able to proceed with the steps suggested. Attached the sample report design with the static solution. The cahrt is developed with the dataset which is static.

Please find the attachment.

Thanks
Amarnath

mwilliams

One quick question. What does row["VIEW_NAME"] actually contain? Just the number?

Like is it, ASSET_REQUEST_29 or just 29? Let me know.

sairag

VIEW_NAME consists the following table names:

RPT_ASSET_REQUEST_7
RPT_ASSET_REQUEST_52
RPT_ASSET_REQUEST_51
RPT_ASSET_REQUEST_50
RPT_ASSET_REQUEST_5
RPT_ASSET_REQUEST_49

Thanks
AR

mwilliams

Try this. Let me know if you get errors, since I can't test it.

sairag

Hi Williams - It seems we are close to the final result. I have slightly modified the script that you have provided. Problem is the code is not reading the list of tables. To verify I have modified code with default table value. It is working.

Plz find the attached modified code.

Thanks
AR

sairag

Hi Williams - Thanks for the response .

It seems we are close to the final result. I have slightly modified the script that you have provided. Problem is the code is not reading the list of tables. To verify I have modified code with default table value. It is working.

Plz find the attached modified code.

Thanks
AR

mwilliams

Are you getting an error when you run it how I sent it to you before? I just checked my script in a sample report, using the sample database and it works as expected. Let me know.

sairag

Williams - I am getting the following error : 
 
The following items have errors: 
 
 
Chart (id = 657): 
+ An exception occurred during processing. Please see the following message for details: 
Failed to prepare the query execution for the data set: PCD for HUB 
Cannot get the result set metadata. 
org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object. 
SQL error #1: ORA-00942: table or view does not exist 
 
; 
java.sql.SQLException: ORA-00942: table or view does not exist 
 
Thanks 
Amarnath

sairag

Williams - Please find the attached log for the same ....

mwilliams

Can you show me a screenshot of the ASSET_REQUEST result set? It's saying the table or view doesn't exist, so there seems to be an issue with the values we're getting from the first dataSet.

sairag

Plz find attachment.

mwilliams

Looks like it didn't get attached!

sairag

I nam not able ot atttach the picture .

Please use the link :

http://www.eclipse.org/forums/index.php/m/900906/#msg_900906

mwilliams

Can you copy and paste what the report output is, if the query text shows up?

sairag

select

sairag

Bottom of the query , the following text is there:

The following items have errors:

Text (id = 658):
+ Can not load the report query: 658. Errors occurred when generating the report document for the report element with ID 658.

mwilliams

That looks correct to me. You might delete the text box I have bound to the second dataSet and replace it. The bindings don't show in the binding tab, because I don't have access to the data. This might be the issue.

sairag

Sorry my dear friend

Even i am not able to see any data for the second text box ...

mwilliams

Can you expand the '+' from the error in thread post #26, so I can see the details?

sairag

The following items have errors:

mwilliams

And you tried deleting the text box and re-adding one, in the same spot, bound to the second dataSet? I sent you an email asking if you have Google Talk or Facebook chat for easier discussion of this.