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

Using Parameter to select the datasource

nt4boy

<div>So I've got my sqlite report pointing at a database on a remote server.</div>
<div>I have a list box parameter that is present when the report is run and drops down to make the initial selection.</div>
<div>Report then displays, all well and good.</div>
<div> </div>
<div>Problem I now have is that there are several identically structured databases in the same location, and the user wants to be able to choose which one to interrogate.</div>
<div> </div>
<div>So using this as guidance:-</div>
<div><a data-ipb='nomediaparse' href='http://wiki.eclipse.org/Change_Database_URL_at_runtime_based_on_Parameter_(BIRT)'>http://wiki.eclipse.org/Change_Database_URL_at_runtime_based_on_Parameter_(BIRT)</a></div>
<div> </div>
<div> </div>
<div>
<div>I created another parameter, also a list box, placed above the 1st one. This new one is poplated with choices to feed the :-</div>
<div> </div>
<div>datasource binding</div>
<div> </div>
<div>if ( params["Production"] == "Option1" ){</div>
<div> "jdbc:mysql://farawayhost/share/database1.db";</div>
<div> </div>
<div>}else if ( params["Production"] == "Option2" ){</div>
<div> "jdbc:mysql://farawayhost/share/database1.db"; </div>
<div> </div>
<div>}else{</div>
<div> "jdbc:mysql://localhost/qa";</div>
<div>}</div>
<div> </div>
<div> </div>
<div>The report runs, and I can make the database selection, but the second parameter list doesn't get updated. So far its not a method I can make work.</div>
<div> </div>
<div> </div>
<div>Can this be made to work please?</div>
</div>
<p> </p>

Find more posts tagged with

Comments

Clement Wong

<p>You are using SQLLite? Your JDBC URL has <strong>mysql</strong>, btw.</p>
<p> </p>
<p>To solve your requirements, you can create a Cascading Parameter Group. The first parameter will be tied to a Static Data Source/Set (if using commercial BIRT) or Scripted Data Source/Set (if using OS BIRT).</p>
<p> </p>
<p>Then, you can change the Database URL of the SQLLite via the Property Binding and set it to the first parameter's value. In my example, it's params["DBURL"].value. </p>
<p> </p>
<p>The DBURL is populated in the fetch of the scripted data set. You can optionally have another column to be your display value there.</p>
<p> </p>
<p>I tested this solution in both OS BIRT 4.5.0 and commercial BIRT iHub 3.1.1.</p>

nt4boy

<p>Clement,</p>
<p> </p>
<p>I've been away a while, and just got around to picking up the real world.</p>
<p> </p>
<p>This looks like a life saver, thank you very much.</p>

nt4boy

<p>Clement,</p>
<p> </p>
<p>After some playing around, I've decided that the listbox isn't the best approach for this report as the database names and location aren't locked in stone and I don't need another task maintaining the report.</p>
<p> </p>
<p>What instead I'd like to do is set the database in the report URL. That way I can have any number of desktop links and the users can edit and change their database names as they see fit.</p>
<p> </p>
<p>So currently I am running the report:-</p>
<p><a data-ipb='nomediaparse' href='http://server/birt/frameset?__report=report\MyBirtReport.rptdesign'>http://server/birt/frameset?__report=report\MyBirtReport.rptdesign</a></p>
<p> </p>
<p>I see in my mind something like:-</p>
<p><a data-ipb='nomediaparse' href='http://server/birt/frameset?__report=report\MyBirtReport.rptdesign&RPTFolder=folder1&dbname=database1.bd'>http://server/birt/frameset?__report=report\MyBirtReport.rptdesign&RPTFolder=folder1&dbname=database1.bd</a></p>
<p> </p>
<p> </p>
<p>Can I use what you've shown as the basis, or is a different technique required?</p>
<p> </p>
<p>Thanks</p>

nt4boy

<p>Clement,</p>
<p> </p>
<p>I have taken a good day to work through your cascading parameters option, and I would like to say that even with my choice of 8 databases in the fetch script on my scripted data source (which I didn't understand at all when I started), does work brilliantly and fast.</p>
<p> </p>
<p>Thank you for that guidance.</p>
<p> </p>
<p>But

for the future I would still like to be able to call the database URL from the report URL.</p>
<p> </p>
<p>Best Regards</p>

nt4boy

<p>Ah Clement,</p>
<p>I was so pleased with making the basic structure work but now deployed I've found what I think is a small hole in your original attached file sample.</p>
<p>Although the Second list box populates with fresh data in relation to the selected database and you can select an item from it, this selection is ignored in the report output. </p>
<p> </p>
<p>I have tried adding a parameter to the dataset and adding a matching "where name = ?" line in the query but I have run out of skill in getting it to function.</p>
<p> </p>
<p>Thank you in anticipation.</p>

Clement Wong

<p><em>I see in my mind something like:-</em></p>
<p><em><a class="bbc_url" href="http://server/birt/frameset?__report=report\MyBirtReport.rptdesign&RPTFolder=folder1&dbname=database1.bd" title="External link">http://server/birt/f...me=database1.bd</a></em></p>
<p> </p>
<p>If you want a parameter with a database name, and don't mind that the user can change it, then why don't you define a new parameter, say "dbName".</p>
<p> </p>
<p>Then, in the Data Source for your SQL Lite, you can...</p>
<p>1. Edit Data Source</p>
<p>2. Click on Property Binding</p>
<p>3. For JDBC Driver URL, click on the "fx" icon to change the expression to similar to the following:<br>
</p>
<pre class="_prettyXprint">
if (params["dbName"].value == "something")
   "jdbc:sqlite:C:/temp/test1.db";
else if (params["dbName"].value == "somethingelse")
    "jdbc:sqlite:C:/temp/test2.db";
else
    "jdbc:sqlite:C:/temp/test3.db";
</pre>

nt4boy

<p>Clement,</p>
<p>I'm broken.... passing parameters from the URL is one step too far for me after all so I have returned to the first plan.</p>
<p> </p>
<p>I have the front end of this report using your scripted datasource template apparently working perfectly.</p>
<p>I can choose the database, and the data select List box repopulates with data from that database. All so exactly as required.</p>
<p> </p>
<p>So why does the Report Output ignore the dataselect listbox selection and gives me a report as if the choice was * ie the whole database? </p>
<p> </p>
<p>I cannot work it out. </p>

Clement Wong

<p>I'm not sure I quite understand the latest issue you are facing. The sample design runs in my environment as expected. If I choose the first db, the results from the first db are returned. If I choose the second db, the results form the second db are returned.</p>
<p> </p>
<p>Maybe screenshots would help, or if you are able to replicate with the sample design?</p>

nt4boy

<p>Clement,<br>
Thanks for your patience.</p>
<p> </p>
<p>This contains a series of screenshots that show my dilema.</p>
<p> </p>
<p>

Clement Wong

<p>The screenshots are helpful, but I'm not sure how your report design is structured. </p>
<p> </p>
<p>Please PM with and send me the location of your design (Google Drive, OpenText Core, Dropbox, etc...) so it won't be posted publicly. I will look into this further after you send me the .RPTDESIGN.</p>

nt4boy

<p>Clement,</p>
<p> </p>
<p>It works now.....?!</p>
<p>I put it away last night and today demonstrated what was in my attachment to a young colleague with a bright mind and he studied it for a few minutes and said I think you need to point the parameter at a 'where field = ?' statement in the data query.</p>
<p>I said, yes but it doesn't work. I've tried it dozens of times, look I'll put it back and show you. Uncommented two lines in the query that have the where statement, linked the parameter created and pressed ok.</p>
<p>Ran it one more time and it worked exactly as required. I have no explanation as to why I failed before when its such a simple thing, but anyway all is well in my world again. Works on the jboss server as well</p>
<p> </p>
<p>Thank you for your offer to look more deeply, but I am now back on an even keel.</p>