Accessing the Query Text for a Birt Dataset

Welcome to the third post in my Birt tutorial series !

Today I will go over how we can access the Query Text for a Dataset - complete with any parameters that have been inserted. We can then add this Query Text to display when an optional Debug parameter is being used so that we may debug the Query remotely , which can be handy when the Report file is located on a remote server and can be a serious timesaver.

Once again will use the CLASSICMODELS sample Dataset from the Birt docmentation for my example, and have included a working example file below.

Steps

1. In the AfterOpen script for your DataSet add the below code:

    
   try {
       importPackage(Packages.java.lang);
   	var query = this.queryText;
       
       // Replace the ? mark becaseu Birt throws an error when trying to split on this symbol
   	var temp = query.replace("?","-!!-");
   	
   	temp= temp.split("-!!-");
   	
   	var query_text ="";
   	
   	for(i = 0 ;  i < temp.length ; i++ ){
   		query_text += temp[i] + this.getInputParameters().values().toArray()[i];
   	}
       // Set Our Gloal Variable which we will display later. 
   	reportContext.setGlobalVariable("querytext", query_text);
   
   }
   catch(err) {
     var msg = "/*Error Getting Parameters , Base version included instead */" + this.queryText;
     reportContext.setGlobalVariable("querytext", msg);
   }
   
   

2. Create a Data element named "debug_text" and set the value to the below javascript expression.

    reportContext.getGlobalVariable("querytext");
   
   

   This will show the Query Text.
3. We can go a step further here and set the visibility settings so that it will only show if the report is accessed when in "Debug Mode" ( by passing debug=true via the query string ). Add a parameter of type boolean and set the default to false
4. Click on the debug_text element we just created and open the visibility options in the the property editor.
   
5. Check the Hide element box and add the below Javascript in the Detail expression.

    if(params["debug"].value != true){
   	true
   }
   
   

6. Save & Preview
7. The results will appear as below when Debug = true , otherwise it will be completely hidden !
   



You can download the report file I created here