Dynamic Parameters

Lesbea

Given that I have two parameters for my report
[BRANCH] representing a branch of the bank
[ACCOUNT] representing an account number of the bank

The data set has the [ACCOUNT] as a unique key along with [BRANCH] i.e. each account number belongs to only one branch.

I originally set up the report with a cascading group of parameters starting with [BRANCH] and then [ACCOUNT] so the report would always generate a result from the dynamic nature of the Cascading Parameter group.

Of course I had linked the parameters to the dataset with the 'WHERE Data.Branch = ? and Data.Account = ?'
conditions and linked the parameters to this.

There was a BUT from the users of teh report.

"BUT I also want to select all the accounts for one branch , I want to select all the accounts for the bank and I dont know what branch the account is for but I know the account number".

Given these scenarios yes one could easily solve the problem by creating a copy of the report and use various different parameter sets to achieve the result. However this report is all a drilldown report from another report and as such may be re-run whilst being in drilldown mode.

I moved the [BRANCH] and [ACCOUNT] paramters to normal parameters from cascading group to allow the use of an all default value of '%'. I also altered the WHERE statements to "WHERE data.branch like ?" etc.

This has partially worked except that the dynamic relationship between the parameters is now lost because I only get all the items on the [ACCOUNT] list for the parameter.

I would like to know that when the [BRANCH] parameter changes from '%' default to any of the selections available this then makes the dropdown list for the [ACCOUNT] to have still the default of '%' for all but only those accounts for the branch selected i.e. it acts similar to Cascade Group but allows all records to be selected.

Any ideas or methods of doing this would be greatly appreciated as a solution will be used for a more adventurous report where we have [YEAR] [MONTH] [DAY] [HOUR] as the parameters that need for obvious reasons to follow along the same lines of ALL or selection from the top [YEAR] down to the [HOUR]

Tubal

What I have done in the past is create a dataset just for the parameters, and then another dataset that's built from my parameters.<br />
<br />
If you have access to your queries, which it seems you do, then in your parameter dataset, you could do a union that would include an 'ALL' branch, and an 'ALL' account.<br />
<br />
For example, for your parameter dataset:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT branch, account
FROM myTable

UNION

SELECT 'All Branches', account
FROM myTable

UNION

SELECT branch, 'All Accounts'
FROM myTable

UNION

SELECT 'All Branches','All Accounts'
</pre>
<br />
That query should give you a list of parameters you could cascade, including a branch called 'All Branches' and an account called 'All Accounts' in each branch. If your account is an integer, you may have to cast it as text in your query for it to union with 'All Accounts'.<br />
<br />
Keep in mind that will basically double the # of accounts you actually have, so if you're talking millions of rows, it might be costly on your database.<br />
<br />
<br />
Once you get your parameters, you can modify your query on your data dataset in the dataset's beforeOpen script.<br />
<br />
So the dataset you use to build your report would start out with something like this, or whatever your dataset needs to be, without the parameters (we'll add those later in script):<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT *
FROM myTable
/**params**/</pre>
<br />
Then in the beforeOpen script, you would just replace the /**params**/ with your parameter. Something like this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>var branch;
var account;
if(params["myBranch"]=='All Branches'){
branch = '%';
} else {
branch = params["myBranch"];
}
if(params["myAccount"]=='All Accounts'{
account = '%';
} else {
account = params["myAccount"];
}
this.queryText = this.queryText.replace("/**params**/","WHERE branch = '" + branch + "' AND account = '" + account);</pre>
<br />
What this script does is find /**params**/ in your query, and replace it with the 'WHERE .....' statement we create from your parameters.<br />
<br />
I'm not sure if that's exactly what you're looking for, but it should get you pointed in the right direction.