DataFormWebPart in SharePoint designer linked to SQL not being able to filter

Question

Hello,
 

I am trying to make a DataFormWebPart in SPD. My webpart gets its data from an SQL table, I tried both through a shared procedure and through just selecting the table I needed.

I can enable the toolbar to do sorting and grouping as it should, but I've been trying to get the filter in the toolbar to work but this just doesn't want to show as it does when I get data from a sharepoint list.
The checkbox to enable filtering (where you can also check "grouping" and "sort" isn't there.

Is there a way to make this work?


Best regards,

Jeroen

Answer 1

Hi Jeroen,

 

There is a workaround to realize filtering function, specifically on the modification to correct the XPath expression.

1.       On the inserted linked source data view, click on the [>] to bring up the Common Data View Tasks pane. Click Filter.

2.       On the Filter Criteria dialog, configure the filter. Example: Employee LName Begins With 'A'.

Note: the filter should get successfully applied (albeit, incorrectly) and nothing should show in the data view.

3.       Go to code view, and search for 'A' (or whatever filter criteria you used). Change the following XPath on the following code (just an example, change your code according to this) snippet from:

 

<xsl:variable name="Rows"

select="/dsQueryResponse/JD_Edwards_Test_Data/Rows/Row[starts-with(normalize-space(../../../Rows/Row/@Employee_x0020_LName), 'A')]"/>

 

to:

 

<xsl:variable name="Rows"  select="/dsQueryResponse/JD_Edwards_Test_Data/Rows/Row[starts-with(normalize-space(@Employee_x0020_LName), 'A')]"/>

Note: the change occurs within the normalize-space function. The ../../../Rows/Row/ is erroneous as we are already at that level in the XML structure.
 

After make the change, the data view should now filter as expected.

 

Hope it helps! If I misunderstood your concern, feel free to correct me.

 

Best Regards,

Lionel

Answer 2

I tried the above in the primary list of a linked aggregate datasource and it works fine.  But is it possible to filter on field in the secondary list of the linked data source?  And if so, how?  thanks