query reg Cascading Parameters and multiple parameters

Hello there
I have 2 datasets - one contains (for example )- ServerName, BusinessApplicationName and BusinessUnit. The second dataset contains ServerName and CPUUtilisation.

I have defined cascading parameters - Businessunit (top level) and ServerName (sub level) from the first dataset. The user need to select one top level parameter (BusinessUnit) and the sub-parameter is optional. If the user is not selecting any sub-parameter the result set should return for all serverNames under the selected Business Unit and the columns retunred are ServerName, BusinessApplicationName, BusinessUnit, CPUUtilisation.

I tried the example to pass multiple parameters using the IN clause - (Using IN/LIKE Clause in BIRT) from Fahad Zia's weblog. But I unable to expand that for my specific requirement.

Find more posts tagged with

Comments

mwilliams

Hi Saba,

What problems are you having? From looking at Fahad Zia's post, it looks like to follow what they did, you'll have to use and if/else statement that checks whether anything was input for your sub-parameter and adjust the query text accordingly.

Saba

Hello Michael
Thanks for your response. The script that I used to pass multiple values in the sub-parameter(NewParameter1) is below. On chosing a top level parameter the values of the sub-parameter changes..I am using a Cascading parameter option.
--

oldservername = reportContext.getParameterValue("NewParameter1");
newservernames="";
for (var counter=0;counter<oldservername.length;counter++)
{
newservernames += "'" + oldservername[counter] + "'" + ",";
}
newservernames=newservernames.substring(0,newservernames.length-1);
this.queryText += " AND CICS_REGION IN ("+newservernames+")";

My understanding on the above query, for whatever sub-parameters the user has selected, the above query is executed by adding the ' and ,. and the IN clause.

WHAT I dont understand ( am new to this world of BIRT and just under pressure to deliver reports) is how to modify this report in such a way that when the user selects a top level parameter and does not select a sub parameter the query executes for the CICS Region (here) for the top level parameter that I have selected. Please note that there are different CICS regions for different top level parameter that I select and the main table contains for all CICS region and is a huge table.

I hope I have clarified my question. Any help will be appreciated

Thanks
Saba

mwilliams

Saba,

Since you're modifying the query in script, you should be able to use an if/else statement to change the query to not include the last parameter line if the second parameter is null.

Saba

Does this mean that if the second parameter is null, it will not read all the parameter values in the sub - parameter?

Because the sub-parameter values change based on the top level parameter, I need a way to pass some or all the sub-parameter values to a query based on the selected top level parameter.

....What I am trying to achieve is if the second parameter is not selected, I should pass all the values from the second parameter to the query..It is akin to the user selecting all the values in the second parameter.
I still need to capture all the parameter values in the second parameter eventhough the user does not select any...

Well..Am I missing something here?

mwilliams

Saba,

If the second parameter is null and you send the query through with only the first parameter. All the values from the first parameter will automatically be brought into the report, which would bring in all "Servers" for that "BusinessUnit" selected. If the second parameter isn't null, you use the same logic from the Fahad Zia post and pass the second "IN" statement. I haven't tested this, so the idea may take some tweaking, but I'm pretty sure it would work this way.

Saba

Hello Michael
I continue to battle with my requirement.
A few clarifications from my end.
1. Dataset1 is from a csv file containing ServerName, BusinessApplicationName and BusinessUnit.
2. Dataset2 is from a DB2 database containing ServerName and CPUUtilisation. (It does not contain the BusinessApplicationName and BusinessUnit columns). (each day for 1650 servers and CPU stats for every 15 min )
3. Report Parameter - Cascaded - Business ApplicationName is top level and ServerName is the sub-parameter
4. On selecting a BusinessApplicationName, the corresponding server names will be passed as parameters to Dataset2 so that the resultset will be an efficient table for only those servers.

One question that will help me understand your comments
1. In a cascaded parameter scenario on selecting a BusinessApplicationName are all the corresponding ServerNames stored in an array and how do I retrieve it

I will keep trying !!!
Thanks for all your help
Saba

mwilliams

Saba,

Thanks for the clarification. One thing that would work would be doing a joint dataSet and doing filtering on that based on the cascading parameter values. Not sure if the processing time will be too much for you here. Worth a try. Can you attach your report design so that I can see what you're doing so far?

Saba

Hello Michael
Please find attached the design file . Please let me know if you have any thoughts/questions

Saba

Hi Michael
I have resolved the issue with a work around. I used a String text box to input the parameters within single quotes and used the following script in the before open method

var endOfQuery = "AND CICS_REGION IN (" + params["system"] +") ORDER BY CICS_REGION, DATE, TIME;";
this.queryText = this.queryText + endOfQuery;

This helped me read all the CICS_REGION associated to a high level parameter. my program got to read the parameters stored it in pipe -delimited text file.
Thanks
Saba

mwilliams

Saba,

Sorry I was unable to look at your report design in time to help. I'm glad to hear you have found a solution. Let us know if you run into any other problems.