Filtering drop down list based on another drop down in SSRS
Reports
Senario:
Filter Customer Account’s based on Customer Group.
Steps:
1. Create a Query CustReportDemo
Add CustTable as Datasource to Query.
Select fields in the properties option Dynamics == Yes.
2. Now we have to move to classes, as
per the requirement we have to create 2 classes.
3. DataProvider Class, Contract Class,
UI Builder class.
4.
So,
Here is what we need to do.
First create a contract class as (I am hoping)
we already know. Here I am using an example of Customer which will be filtered
by Customer group. The code of class declaration will be:
[
DataContractAttribute
]
public class custReportContract
{
CustGroupId custGroup;
AccountNum cust;
}
Now creating 2 new methods for getting and setting these
parameters. 1 for cust group
[
DataMemberAttribute('CustGroup'),
SysOperationLabelAttribute(literalstr("@SYS11904"))
]
public CustGroupId parmCustGroup(CustGroupId _custGroup= custGroup)
{
custGroup = _custGroup;
return custGroup;
}
And the other one for customer
[
DataMemberAttribute('Cust'),
SysOperationLabelAttribute(literalstr("@SYS313797"))
]
public AccountNum parmCust(AccountNum _cust= cust)
{
cust = _cust;
return cust;
}
Your screen will look like this:
Nothing new in it. We all know it (I guess).
Moving forward and now we are creating another class for UI builder.
Create this new class and on the class
declaration method, type the following code
public class custReportUIBuilder extends SysOperationAutomaticUIBuilder
{
}
your screen will look like this
Now we have to create 2 dialog boxes. 1 for customer group and
customer each. To do this type the following code.
DialogField dialogCustGroup;
DialogField dialogCust;
We also have to define our contract class here, so declare the
contract class
custReportContract contract;
so, the class declaration method will look like this:
UI Builder Class:
public class custReportUIBuilder extends SysOperationAutomaticUIBuilder
{
DialogField dialogCustGroup;
DialogField dialogCust;
custReportContract contract;
}
Now, we have to draw the dialog boxes. For this reason, we will
create another method of build and type the following code In it.
public void build()
{
Dialog dialogLocal
= this.dialog();
custReportContract contract =
this.dataContractObject();
dialogLocal.addGroup("Customer");
this.addDialogField(methodStr(custReportContract,parmCustGroup), contract);
this.addDialogField(methodStr(custReportContract,parmCust), contract);
}
Now that we created the build method, let’s move into filling
the cust group drop down with the table CustGroup. We’ll create another method
for lookup and type the following code in it.
public void lookupCustGroup(FormStringControl _control)
{
Query query = new Query();
SysTableLookup sysTablelookup;
sysTablelookup
=SysTableLookup::newParameters(tableNum(CustGroup),_control);
sysTablelookup.addLookupfield(fieldNum(CustGroup,CustGroup));
sysTablelookup.addLookupfield(fieldnum(CustGroup,Name));
query.addDataSource(tableNum(CustGroup));
sysTablelookup.parmQuery(query);
sysTablelookup.performFormLookup();
}
We need to create another method so that when user selects any
record from cust group drop down, the customer drop down will be filtered with
that value, for this reason we will select the modified event of cust group and
then type the code:
public boolean custGroupModified(FormStringControl _control)
{
dialogCustGroup.value(_control.valueStr());
dialogCust.value('');
return true
}
Ok now we will create the lookup method for customer. Here is
the code
public void lookupCust(FormStringControl _control)
{
Query query = new Query();
SysTableLookup sysTablelookup;
sysTablelookup
=SysTableLookup::newParameters(tableNum(CustTable),_control);
sysTablelookup.addLookupfield(fieldNum(CustTable,AccountNum));
sysTablelookup.addLookupfield(fieldnum(CustTable,Party));
query.addDataSource(tableNum(CustTable));
query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable,
CustGroup)).value(dialogCustGroup.value());
sysTablelookup.parmQuery(query);
sysTablelookup.performFormLookup();
}
Note that I filtered the cust query with the value selected in
cust group drop down
query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable,
CustGroup)).value(dialogCustGroup.value());
we have almost completed our code for lookups and their filters.
Now we will bind our dialog boxes with the contract class params and also
override the modified method of cust group with the method we
just wrote. To do this we will create another method for postBuild. Here
we go
public void postBuild()
{
super();
// From binding info, get the dialog field for racecode
attribute and add button
dialogCustGroup =
this.bindInfo().getDialogField(
this.dataContractObject(),
methodStr(custReportContract,parmCustGroup));
if (dialogCustGroup)
{
dialogCustGroup.lookupButton(2);
}
// register override method for lookup cust Group
dialogCustGroup.registerOverrideMethod(methodStr(FormStringControl, lookup),methodStr(custReportUIBuilder,
lookupCustGroup), this);
// register override method for modified
dialogCustGroup.registerOverrideMethod(methodStr(FormStringControl, modified),methodStr(custReportUIBuilder,
custGroupModified), this);
//binding info for customer drop down
dialogCust =
this.bindInfo().getDialogField(
this.dataContractObject(),
methodStr(custReportContract,parmCust));
// register override method for lookup customer
dialogCust.registerOverrideMethod(methodStr(FormStringControl, lookup),methodStr(custReportUIBuilder,
lookupCust), this);
if (dialogCust)
{
dialogCust.lookupButton(2);
}
}
We may have to create some other methods as well in order to
functioning your code properly. They are getFromDialog, initializeFields and
postRun. Create three new methods each for getFromDialog, initializeFields and
postRun and copy the following code to them
GetFromDialog:
public void getFromDialog()
{
contract =
this.dataContractObject();
super();
}
InitializeFields:
public void initializeFields()
{
contract =
this.dataContractObject();
}
PostRun
public void postRun()
{
super();
}
We are done. But wait one last thing, open your contract class
and in the class declaration method, reference your UI builder class just below
the DataContractAttribute. Your code will become:
Contract Class
ClassDeclaration
[
DataContractAttribute,
SysOperationContractProcessingAttribute(classStr(custReportUIBuilder))
]
public class custReportContract
{
CustGroupId custGroup;
AccountNum cust;
}
We are now done. In this post we have tried to filter our
single value drop down based on another drop down. In the next post we will try
to repeat the same process for multi value drop down list.
Thank you all
Here is the screen shot of what we just did
DataProvider
Class:
To fetch information from TmpTable and print data in to
report, we have to update in ProcessReport Method.
ClassDeclaration:
[
SRSReportQueryAttribute(queryStr(CustReport)),
SRSReportParameterAttribute(classStr(custReportContract))
]
public class custreportDP extends SRSReportDataProviderBase
{
CustreportTmp
custreportTmp;
}
CustReportTmp
[ SRSReportDataSetAttribute(tableStr(CustreportTmp))
]
public CustreportTmp
custreportTmp()
{
select * from custreportTmp;
return custreportTmp;
}
ProcessReport
public void processReport()
{
QueryRun qr;
QueryBuildRange qbr;
Query query;
CustTable
custTable;
CustGroup
custGroup;
// Get a reference to the contract for this
report. The relevant contract class is
// defined by the
SRSReportParameterAttribute used in the class declaration.
custReportContract
contract =
this.parmDataContract() as
custReportContract;
boolean addLine;
query = this.parmQuery();
if(contract.parmCustGroup()
&& contract.parmCust())
{
query.dataSourceNo(1).clearRanges();
qbr = query.dataSourceNo(1).addRange(fieldNum(CustTable,custGroup));
qbr.value(contract.parmCustGroup());
qbr = query.dataSourceNo(1).addRange(fieldNum(CustTable,AccountNum));
qbr.value(contract.parmCust());
qr = new QueryRun(query);
while(qr.next())
{
CustTable = qr.get(tableNum(CustTable));
custreportTmp.clear();
custreportTmp.AccountNum =
CustTable.AccountNum;
custreportTmp.CustGroup =
CustTable.CustGroup;
custreportTmp.insert();
}
}
else if(contract.parmCustGroup())
{
qbr = query.dataSourceNo(1).addRange(fieldNum(CustTable,custGroup));
qbr.value(contract.parmCustGroup());
qr = new QueryRun(query);
while(qr.next())
{
CustTable = qr.get(tableNum(CustTable));
custreportTmp.clear();
custreportTmp.AccountNum =
CustTable.AccountNum;
custreportTmp.CustGroup =
CustTable.CustGroup;
custreportTmp.insert();
}
}
else if(contract.parmCust())
{
qbr = query.dataSourceNo(1).addRange(fieldNum(CustTable,AccountNum));
qbr.value(contract.parmCust());
qr = new QueryRun(query);
while(qr.next())
{
CustTable = qr.get(tableNum(CustTable));
custreportTmp.clear();
custreportTmp.AccountNum =
CustTable.AccountNum;
custreportTmp.CustGroup =
CustTable.CustGroup;
custreportTmp.insert();
}
}
else if (!contract.parmCust() &&
!contract.parmCustGroup())
{
qr = new QueryRun(query);
while(qr.next())
{
CustTable = qr.get(tableNum(CustTable));
custreportTmp.clear();
custreportTmp.AccountNum =
CustTable.AccountNum;
custreportTmp.CustGroup =
CustTable.CustGroup;
custreportTmp.insert();
}
}
}
OutPut:
Senario1:
Selecting CustomerGroup In lookup as 10 , Customers should
get filter based on the custgroup.
After selecting Customer Account as 1101. Final report is
Senario 2:
Selecting Only Customer Group as “10”
Senario3:
Selecting only Customer Account as “1101”
Senario 4:
Customer Account & Customer Group is Null.
