Chandru's SharePoint / MCMS Blog ! : BDC : How to Create Filters in ADF manually

BDC : How to Create Filters in ADF manually

When an ADF is generated using BDC Editor tool, by default no filters will be added to any method instance. There is an option available in BDC editor tool to create filter. For guys who have tried to create filters using BDC Editor tool know how much painful it is. I know that there are other tools available in market using which this can be achieved easily but remember that nothing comes for free !

Let’s remember that nothing is as great as “Programming Ourselves” and that is what exactly we are going to do. Once you know what changes need to be done and why, then it’s just a matter of time to change.

So, first create ADF using the BDC Editor tool and save it. Open the XML file and locate the method where you like to add filters.

1. Change the Query adding where clause in it. Remember, the field in the where clause is going to be the filter.

Select "EmployeeKey","FirstName" from DimEmployee where([EmployeeKey] LIKE @ EmployeeKey)

2. Add Filter Descriptors and parameters in it. Here we are assuming it’s a Comparison type – you can also use WildCharacter.

</FilterDescriptors>

</DefaultValues>

</TypeDescriptor>

</Parameter>

3. Make sure that MethodInstanceName specified in the default value matches with the method instance name specified in <MethodInstances>

Published Sunday, December 07, 2008 8:47 PM by chandrasekarn

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

Sunday, December 07, 2008 6:07 PM by Chandru's SharePoint / MCMS Blog !

# BDC Error Messages

· BDC Tool Design Surface Error Message : “Could not process Table ‘xyz’. Make sure you have SELECT rights

Friday, January 23, 2009 10:46 AM by Filipe de Campos Cavalcante

# re: BDC : How to Create Filters in ADF manually

Hello there!

Thanks for sharing this information, but unfotunatelly i can't do filters works on my project.

look what MS BDC Definition Editor has generated:

<Property Name="RdbCommandText" Type="System.String">select

car.Atendimento as "Atendimento"

,car.CD_CARGA as "ID Carga"

,car.DS_Carga as "Descrição Carga"

,car.RS_Carga as "Carga"

,un.Acronym

,un.Description as "Descrição"

,un.Type as "Tipo de Unidade"

,un.[Fator to Metric] as "Fator para Métrica"

,clas.DS_Genero as "Gênero"

,tipo.DS_Tipo as "Tipo de Carga"

from Cargas car

inner join Units un

on un.CD_UNIT = car.CD_UNIT

inner join [Classe de Carga] clas

on clas.CD_Genero = car.CD_GENERO

inner join [Tipos de Cargas] tipo

on tipo.CD_Tipo = clas.CD_TIPO

WHERE

(car.Atendimento = @Atendimento or @Atendimento is null)</Property>

</Properties>

</FilterDescriptors>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

<Property Name="ShowInPicker" Type="System.Boolean">false</Property>

</Properties>

</TypeDescriptor>

</Parameter>

</Parameters>

</MethodInstances>

</Method>

But sharepoint always give me the error "An error occurred while retrieving data from LBH_Instance. Administrators, see the server log for more information." . I don't know what i can do to make this works... If i remove the filter and "where" statement form query my BDC works just fine... but i need this filter. And when i test it on BDC Definition Editor the error is "has a value in field "TypeDescription" that is invalid"

Do u have any other ideas to help me?!

Thx anyway! =D

Cya!

Monday, January 26, 2009 4:53 PM by chandrasekarn

# re: BDC : How to Create Filters in ADF manually

Hey Filipe....I see that you have used BDC Editor tool to genereate this ADF.

My blog here says how to add filters manually. In your ADF i see "FilterDescriptors". Here is what i would suggest you.

Don't try to add filters using the BDC tool. If you do that then you are going to land in trouble. Just create ADF "Without filters" and then using any XML editor, try the steps that i said before to add the filters and it should work. Let me know how if you have any problem !

-Chandrasekar

Monday, March 02, 2009 9:54 AM by Deepti

# re: BDC : How to Create Filters in ADF manually

Hi I tried adding filters using the method posted above.

Here is my xml :

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

<Property Name="rdbconnection Data Source" Type="System.String">01HW125397\BFSTEG</Property>

<Property Name="rdbconnection Initial Catalog" Type="System.String">BFS Accounts</Property>

<Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">SqlServer</Property>

<Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">PassThrough</Property>

</Properties>

</LobSystemInstance>

</LobSystemInstances>

</Identifiers>

<Property Name="RdbCommandText" Type="System.String">Select "ProductID","Name","ProductNumber","Color","Size","Class","Style" from Product where ProductID=@ProductID</Property>

</Properties>

</FilterDescriptors>

<TypeDescriptor TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

IdentifierName="ProductID"

Name="ProductID"

AssociatedFilter ="ProductID">

<DefaultValue MethodInstanceName="Find_Product_Instance"

Type="System.Int32">

</DefaultValue>

</DefaultValues>

</TypeDescriptor>

</Parameter>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

</MethodInstances>

</Method>

<Property Name="RdbCommandText" Type="System.String">Select "ProductID" from Product where ProductID=@ProductID</Property>

</Properties>

</FilterDescriptors>

<TypeDescriptor TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

IdentifierName="ProductID"

Name="ProductID"

AssociatedFilter ="ProductID">

<DefaultValue MethodInstanceName="FindAll_Product_Instance"

Type="System.Int32">

</DefaultValue>

</DefaultValues>

</TypeDescriptor>

</Parameter>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

</MethodInstances>

</Method>

</Methods>

</Entity>

</Entities>

</LobSystem>

But whenI import the application to BDC in Central admin , it says no filters configured for the entity. also I am unable to use it in the BDC Data List Webpart.

Monday, March 02, 2009 10:18 AM by chandrasekarn

# re: BDC : How to Create Filters in ADF manually

I don't think so the problem is with the "Filter" but with the "MethodInstance" Type.

I belive you have generated this ADF using BDC Editor tool. By default, you will get two method instance, Specific Finder and IdEnumerator. As you are using BDC Data List Webpart, it will be looking for methodinstance of type "Finder". IdEnumerator is generally used for BDC search.

You have

</MethodInstances>

So Change it to "Finder" Type and check

</MethodInstances>

Tuesday, March 03, 2009 1:38 AM by Deepti

# re: BDC : How to Create Filters in ADF manually

Thanks :)

The solution worked.

Wednesday, April 01, 2009 8:07 AM by Yannis

# re: BDC : How to Create Filters in ADF manually

Hello Chandrasekar,

I'm creating my filters manually based on your solution which works fine.

I have a couple of issues:

1. I'm not able to get results when using more than one filters with data type Integer.

2. I'm not able to get results when I combine different data types (for example date & string).

3. I'm not able to get results when I have more than five filters in a ADF.

I'm not sure if it's my fault or these are BDC constraints. Do you have any ideas?

Thank you in advance for your time & effort.

Yannis

Here is my xml :

<?xml version="1.0"?>

<LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.XSD" Type="Database" Version="1.0.0.0"

Name="SIS_DBLOBSystem_Assignments_Integers" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

</Properties>

<Property Name="AuthenticationMode"

Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">RevertToSelf</Property>

<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>

<Property Name="RdbConnection Integrated Security" Type="System.String">false</Property>

</Properties>

</LobSystemInstance>

</LobSystemInstances>

</Identifiers>

<Property Name="RdbCommandText" Type="System.String">Select

[purchaseorder_key],[consultant_key],[fteorder],[poItem],[poSAPCreationDate],[poStartDate],[poEndDate],[poMaterialNumber],[quantity],[rate],

[rateUnit],[guard],[travel],[overtime],[purRequestDate],[functionalLead],[functionalLeadName],[hierarchicalMgr],[hierarchicalMgrName],[funct

ion],[role],[experienceLevel],[gbu],[group],[project],[locationOfMission],[costcenter],[fincoCostcenter],[fincoCostcenterName],[fincoProject

],[fincoProjectName],[Itec],[esnCodeHQ],[siemensLocalNr],[wbsElement],[startMissionDate],[endMissionDate],[endMissionReason],[activityType],

[poStoppedPrematureOn],[description] From dbo.[TBL_RfR_Assignments_test] where ([consultant_key] LIKE @ConsultantKey) and ([quantity] LIKE

@Quantity)</Property>

</Properties>

</FilterDescriptors>

</DefaultValues>

</TypeDescriptor>

</Parameter>

</DefaultValues>

</TypeDescriptor>

</Parameter>

<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[TBL_RfR_Assignments_test]DataReader">

<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" Name="dbo.[TBL_RfR_Assignments_test]DataRecord">

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstance Name="dbo.[TBL_RfR_Assignments_test]Finder" Type="Finder" ReturnParameterName="dbo.[TBL_RfR_Assignments_test]"

ReturnTypeDescriptorName="dbo.[TBL_RfR_Assignments_test]DataReader" ReturnTypeDescriptorLevel="0" />

</MethodInstances>

</Method>

<Property Name="RdbCommandText" Type="System.String">Select

[purchaseorder_key],[consultant_key],[fteorder],[poItem],[poSAPCreationDate],[poStartDate],[poEndDate],[poMaterialNumber],[quantity],[rate],

[rateUnit],[guard],[travel],[overtime],[purRequestDate],[functionalLead],[functionalLeadName],[hierarchicalMgr],[hierarchicalMgrName],[funct

ion],[role],[experienceLevel],[gbu],[group],[project],[locationOfMission],[costcenter],[fincoCostcenter],[fincoCostcenterName],[fincoProject

],[fincoProjectName],[Itec],[esnCodeHQ],[siemensLocalNr],[wbsElement],[startMissionDate],[endMissionDate],[endMissionReason],[activityType],

[poStoppedPrematureOn],[description] From dbo.[TBL_RfR_Assignments_test] Where (purchaseorder_key=@purchaseorder_key) AND

(poItem=@poItem)</Property>

</Properties>

</Parameter>

</Parameter>

<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[TBL_RfR_Assignments_test]DataReader">

<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" Name="dbo.[TBL_RfR_Assignments_test]DataRecord">

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstance Name="dbo.[TBL_RfR_Assignments_test]SpecificFinder" Type="SpecificFinder"

ReturnParameterName="dbo.[TBL_RfR_Assignments_test]" ReturnTypeDescriptorName="dbo.[TBL_RfR_Assignments_test]DataReader"

ReturnTypeDescriptorLevel="0" />

</MethodInstances>

</Method>

<Property Name="RdbCommandText" Type="System.String">Select [purchaseorder_key],[poItem] from

dbo.[TBL_RfR_Assignments_test]</Property>

</Properties>

<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="dbo.[TBL_RfR_Assignments_test]IDs">

<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089" Name="dbo.[TBL_RfR_Assignments_test]">

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstance Name="dbo.[TBL_RfR_Assignments_test]EnumeratorInstance" Type="IdEnumerator"

ReturnParameterName="dbo.[TBL_RfR_Assignments_test]IDs" />

</MethodInstances>

</Method>

</Methods>

</Entity>

</Entities>

</LobSystem>

Friday, April 03, 2009 12:17 PM by Yannis

# re: BDC : How to Create Filters in ADF manually

Please ignore my previous post. My issues where caused by wrong use of 'AND & OR Operators' as well as incorrect 'DefaultValues'.

Kind regards,

Yannis

Tuesday, June 02, 2009 11:07 AM by Chandru's SharePoint / MCMS Blog !

# BDC Error Messages

· BDC Tool Design Surface Error Message : “Could not process Table ‘xyz’. Make sure you have SELECT rights

Title (required)
Name required
Your URL
Comments (required)

Enter Code Here: Required
Remember Me?

Chandru's SharePoint / MCMS Blog !

This Blog

Syndication

Search

Tags

Recent Posts

Archives

BDC : How to Create Filters in ADF manually

Comment Notification

Comments

# BDC Error Messages

# re: BDC : How to Create Filters in ADF manually

# re: BDC : How to Create Filters in ADF manually

# re: BDC : How to Create Filters in ADF manually

# re: BDC : How to Create Filters in ADF manually

# re: BDC : How to Create Filters in ADF manually

# re: BDC : How to Create Filters in ADF manually

# re: BDC : How to Create Filters in ADF manually

# BDC Error Messages

Leave a Comment