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

complete dynamic report

birtprofi · 2012-05-02T04:14:21+00:00

Hello, I have to build a completely dynamic report. This is a really challenge for me. But if it done I think it would be something for DevShare maybe. Lets talk about my first problem. for example: Dataset1: (no problem, I handle this with property binding) I run the…

birtprofi

Hello, 
 
I have to build a completely dynamic report. This is a really challenge for me. But if it done I think it would be something for DevShare maybe. 
 
Lets talk about my first problem. 
 
for example: 
Dataset1: (no problem, I handle this with property binding) 
I run the report from an application and within the link I use a parameter called ID=4 
Then I have to run the first dataset query like: "Select NUMBERS from mytable where ID=4" 
 
Then I get this values: "(xx,xy,xz,zz)" (these are the names of colums of another table) 
This values I have to use in a new Data-Set Query like: 
 
Dataset2: (problem) 
Then I have to build a new SQL like this: 
"Select NAME, STREET, >> xx,xy,xz,zz << from ADRESS 
 
So I have to run Dataset1 and then build in the result of dataset1 into the query of Dataset2 
 
After this is solved I will come back with the next step. 
 
kind regards 
Rafael

Find more posts tagged with

Comments

SNX2012

You want to do this in just one call of the report?

birtprofi

if it?s possible yes of course

SNX2012

i think i should be possible with more then one call. if you write the results of the first querry to parameters and call the second run with the new parameters. but all in one call, i dont have an idear... iam sorry.

Tubal

One way to do it would be to connect to your db and run your first query in your report beforeFactory event, store the value returned to a global variable, and then in your dataset's beforeOpen event, replace the pertinent part of your sql statement with your global variable. 
 
So you wouldn't actually have a premade first dataset. You would do all this in script. 
 
I do something similar in the attached report. In this report, I call the database to get my column names and widths, store them in an array, and then dynamically create my table from this array. The beforeFactory event is where the database query takes place. The only difference is you would also need to fetch the parameter (ID=4) from your url before you created your dataset. You could do that with something like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>importPackage( Packages.javax.servlet.http );
var request = reportContext.getHttpServletRequest();
id=request.getParameter("__id");</pre>

birtprofi

Hi tubal,

thanks for your support. It looks really good.
I will make some tests and come back early next week.

best regards
rafael

birtprofi

so, I have made some test and have actually following question or problem. 
On initialize or before factory I run following script: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var odaDataSet = new OdaDataSetDesign( "Tmp Data Set" ); //making our new Data Set
odaDataSet.setDataSource( odaDataSource.getName( ) ); //setting our Data Source
odaDataSet.setExtensionID( "org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet" );
var OENR = " where OEPERM_NR = " + params["ID"];
var strSQL = "select OEPERM_SALDEN_SP from OEPERM" + OENR; //this is the query to our new data set
odaDataSet.setQueryText( strSQL );
de.defineDataSource( odaDataSource );
de.defineDataSet( odaDataSet );
queryDefinition = new QueryDefinition( );
queryDefinition.setDataSetName( odaDataSet.getName() );</pre>
 
as value I get only one field/row back with data like this: "[2,5,7,8,9,10]" 
 
Now I have to run a dataset with property binding with this data field. Do you have any Idea, how I could make this? Could I save the result of my tmp data set as variable, so that I could use this within the next query?

Tubal

You can store this data in a variable for later use. 
 
You don't say where you are putting this data you are getting, but lets assume it's stored in a variable called myData. You may need to do some string modification to your data before you pass it to your query, but that should be easy enough using javascript. 
 
You can put this final data into a global variable that's accessible in other areas of the report. 
 
Something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setGlobalVariable("myGlobalVariable", myData);</pre>
 
I'm not exactly sure what your query set up is, but if it was something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT myField
FROM myTable
WHERE myField2 IN (1,2,3,4,5);</pre>
 
Then you could edit your query to be something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT myField
FROM myTable
WHERE myField2 IN (000000);</pre>
 
And then in the beforeOpen script of the dataset you want to use this data in, you'd do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("000000",reportContext.getGlobalVariable("myGlobalVariable"));</pre>
 
This will replace any instance of '000000' with your variable.

birtprofi

Hi, 
 
thanks for your answer. I will try to explain my problem better. You wrote: 
<blockquote class='ipsBlockquote' >You don't say where you are putting this data you are getting</blockquote>
This is actually myproblem. Your sample report is my example. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var odaDataSource = new OdaDataSourceDesign( "Tmp Data Source" ); //this is our new data source
var strSQL = "select ABC from XYZ//this is the query to our new data set
</pre>
The result data from strSQL will be stored in Data Set "Tmp Data Source". Is this right? 
 
Accepted the result is "(DEF)". How could I store this result or the data content of "Tmp Data Source" into a variable? Is in your report the values stored in this variables below? 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var pq = de.prepare( queryDefinition );
var qr = pq.execute( null );
var ri = qr.getResultIterator( );
var rsmd = qr.getResultMetaData( );</pre>
 
Sorry, for my questions, but I try to understand your report and could take efforts and improve myself. 
 
best regards

Tubal

Yes, ri and rsmd is where the data is returned. 
 
 
ri basically holds how many rows you get from your query, and rsmd holds the data (rsmd = result set meta data) returned. 
 
So depending on how your data is being returned (if it's one row, or multiple rows) you would cycle through your result set using ri to put your data in whatever format you want. 
 
In my sample, I need to get 3 columns from each row, and my query generally returns multiple rows. So I create a 3 column array called 'columns', and store the 3rd, 5th, and 7th fields from my query into that array. 
 
You can see how I cycle through each row returned (ri) and pull the data out (ri.getValue(rsmd.getColumnName(3)) to get the 3rd column from that row, etc). So by the time I'm done, say my query returns 10 rows, i'll have an array with 10 rows and 3 columns. 
 
If your query only returns 1 row, and you only need 1 field, obviously you wouldn't need an array. You could just store this value into a string variable. 
 
Something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var myVariable = ri.getValue(rsmd.getColumn(1));</pre>
 
If you return multiple rows, you'd have to cycle through each row like I did, and build your string however you need. If you need comma delimited, you might do something like: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var myVariable;
while ( ri.next( ) )
{
myVariable = myVariable + "," + ri.getValue(rsmd.getColumnName(1));
}</pre>

birtprofi

@tubal: thanks for your excellent description. I will make some tests now and come back.
Thanks a lot.
best regards

birtprofi

Hi, ok I had time to make some test and now it works nearly perfect. 
But there is one Problem. So I get the correct Data with this script: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var Spalten = ri.getValue(rsmd.getColumnName(1));</pre>
the data of "Spalten" = [1,5,52,58] 
 
now I work with this script to make my new SQL (also in initialise or before factory script): 
<pre class='_prettyXprint _lang-auto _linenums:0'>var i, Wert, Newsql;
Wert = Spalten;
Wert = Wert.substring(1, Wert.length - 1).split(",");
Wert.length = 10;
for (i = 0; i < 10; i += 1) {
if (Wert[i]) {
Wert[i] = "SP" + Wert[i] + " as ZE" + (i + 1);
} else {
Wert[i] = "ZE999 as SP" + (i + 1);
}
}
Newsql = Wert.join();</pre>
 
If I run the script above outside BIRT I get this values: SP1 as ZE1,SP5 as ZE2,SP52 as ZE3,SP58 as ZE4,ZE999 as SP5,ZE999 as SP6,ZE999 as SP7,ZE999 as SP8,ZE999 as SP9,ZE999 as SP10 
 
but if I run this within birt I get a failure: 
<blockquote class='ipsBlockquote' >Cannot convert NaN to java.lang.Integer (/report/method[@name="initialize"]#38) (Element ID:1)</blockquote>
 
The #38 = 
<pre class='_prettyXprint _lang-auto _linenums:0'>Wert = Wert.substring(1, Wert.length - 1).split(",");</pre>
 
What can I do? 
best regards

Tubal

That error is saying that it's trying to use a null value as an integer. 
 
I'm not really good in javascript, but it might have something to do with: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>Wert.length = 10;</pre>
 
I wasn't aware this was a value you could set.

birtprofi

Hi,

no this is not the problem. I tested it also without lenght and is the same problem. I dont know why, how this script not works within birt.

Maybe Birt interprets the string as integer?

sharder

Tubal, first I want to say thanks for the great example. Secondly, I was wondering if you could help me with with your example. I am doing things slightly different. I am building the whole query in beforeFactory prior to replacing the dataset query. The initial query builds the dataset query by doing some logic on each row fetch, then I add a where clause. The issue that I am having is that it appears the query is running(progress bar), but there isn't any data shown. It shows the header and footer, but white space in between. Do you know what might be happening? 
 
<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="99896" data-time="1335972669" data-date="02 May 2012 - 08:31 AM">
One way to do it would be to connect to your db and run your first query in your report beforeFactory event, store the value returned to a global variable, and then in your dataset's beforeOpen event, replace the pertinent part of your sql statement with your global variable. 
 
So you wouldn't actually have a premade first dataset. You would do all this in script. 
 
I do something similar in the attached report. In this report, I call the database to get my column names and widths, store them in an array, and then dynamically create my table from this array. The beforeFactory event is where the database query takes place. The only difference is you would also need to fetch the parameter (ID=4) from your url before you created your dataset. You could do that with something like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>importPackage( Packages.javax.servlet.http );
var request = reportContext.getHttpServletRequest();
id=request.getParameter("__id");</pre></blockquote>

birtprofi

Hi sharder,

there are more possibilities.

-> Your query don?t return data. Try your sql query in a query tool direct on your database. Do yout get some data back?

-> Maybe the Coloum Names (Data Set Binding) of your "new" query is different.

rf

sharder

Hi birtprofi,

I figured it out, I had to create a dynamic detail row as well.

Thanks