Using the SQL Query Wizard.
This article refers to functionality released in BDC Meta Man 4.0.0.7 and is only available in the professional version.
In previous version of BDC Meta Man the Query Wizard allowed you to build up queries in a similar fasion to the query designer in Microsoft Access. The component we wrote to do this however had a number of bugs in it and wouldn’t allow some basic functionality such as grouping of columns.
The people who want to be able to add custom queries are skilled at creating them, so instead of adding a query designer that would take a while to configure anyway – we’ve changed it so there is a simple textbox to paste your query into and then configure it to be used within the BDC.
The query we want execute is going to be run against our AdventureWorks2000 database:
select sum(subtotal) as total, AccountNumber from Customer INNER JOIN
SalesOrderHeader ON Customer.CustomerID = SalesOrderHeader.CustomerID group by AccountNumber
Order By total
This will get the total value of all the orders for each customer. We want this to be displayed in a BDC List Web Part. Generally we would recommend creating this as a View in SQL Server and then creating your Entity from the View, however something this isn’t possible and so allowing you to enter ad hoc SQL queries into BDC Meta Man makes it even more powerful.
Here is how to configure it:
1, Open BDC Meta Man and connect to your MS SQL Server database.
2, In the data source explorer select the database you wish to create an Entity against.
3, Right click on the design surface and chouse ‘Create blank entity’ from the menu item that appears. Give the Entity a name of ‘SumTotals’.
4, Right click on the blank Entity and choose ‘Edit Entity’ from the drop down menu to open up the Entity Management screen.
5, Click on the Methods tab, and then the Add button within the methods section and from the pop up select SQL String
6, The form to configure the SQL statement for our BDC method will now appear. Give the method a name such as GetCustomerTotals
7, Paste our SQL query into the main textbox, then click the Apply Query button
8, If this query had any parameters we’d need to configure these in the parameter section. As ours is a simple query however we can just click the Test Query button to ensure it runs OK.
You’ll get a MessageBox showing whether the query successfully executed or any errors with it. If you have success press the OK button on the MessageBox and then also on the SQL Query window to close it and show Entity Management screen again
9, In the Entity Management screen click the Save button to save the settings of our Entity. We get a warning pop up that the Entity doesn’t have any identifiers and so all method will be removed except the Finder – this is fine, so just click the OK button.
10, Now we can generate our application definition file. To set the filename and location go Configuration -> Settings on the main menu and use the browse button to set the file and path
11, Press the play button in the top left of BDC Meta Man to generate our XML file.
12, We now need to import this into SharePoint. Navigate to SharePoint 3.0 Central Administration and then your Shared Service Provider admin site. Follow the link to ‘Import application definition’ and upload your XML file.
When the file is uploaded you will be presented with a message that the Profile Page creation step has been skipped. This is just because we did not create a SpecificFinder method for our Entity in this example.
13, Navigate to your SharePoint site and add a BDC Data List Web Part, once you configure this web part to use the SumTotals entity you will see the customer numbers and sum totals for each displayed.
It would be nice to make this query more complicated to also bring back customer names, filter on a date range – all which is possible with BDC Meta Man.
We hope you found this walk through useful. If you have any questions please send them over to support@lightningtools.com
