Highlights of SQL View

Highlights of SQL View

SQLView.pngSharePoint® does not currently provide an easy way to work with popular reporting systems since all SharePoint list data is in the same database table. Field names are generic, making it difficult to retrieve information. SQL View is especially useful when users have the need to build and run ad-hoc reports. A user can take standard SharePoint list data (such as announcements, tasks, events, etc.), automatically create a SQL statement, and turn it into a SQL view or embed it into any ad-hoc report.

Create a Microsoft Access Report for a SharePoint Task List

Create a Microsoft Access Report for a SharePoint Task List

hw04012.gifThis topic covers how to create a Microsoft Access report for a SharePoint Task List. In this example, our sample task list resides in a sub-site based on a standard Team Site template, as shown here.

Create a SQL query statement from SQL View and use that query in a Microsoft Access report, running from your desktop. Go to the page where SQL View is installed, generate the SQL query statement by completing the following:

Step Action Result
1. In the Select Site drop-down list, choose the site where the Task list was created. STEPS.jpg
2. Select your task list in the Select List drop-down menu.
3. Enter a name in the View Name text box. To create a view in the SharePoint Content database, this view name must be unique. If a view with a similar name already exists, then the web part identifies it and view is not created. Note that the existing views are not deleted or dropped.
4. Select the Validate Lookup Fields check box so the query will perform the joined query to get the name of the assigned user.
5. Select Fields you want returned in the query.
6. Leave the Generate View in Database check box clear.
7. Click the Generate button. hw04016.gif
Copy the SQL statement result to the windows clipboard starting from the SELECT statement.
8. Open Microsoft Access and select Project using existing data… from the wizard menu. The first thing you have to do is set the name of the project. Then the next screen asks for the connection information. hw04018.gif
9. In this example, we will set the connection to use Windows Authentication, the name of the SharePoint server, and the name of the SharePoint content database. Make sure to test the connection to the database before you click OK. hw04020.gif
10. After a few moments, the wizard finishes and you can see the tables and views within the SharePoint content database. hw04022.gif
11. Navigate to the Report tab in the control panel on the left. You can use a wizard to build the report or just create the report in Design View by clicking on Create Report in Design View. The report and its properties and events sheets are displayed. Click on the Data tab and enter the SQL query statement (saved in step 7) in the Record Source property. hw04024.gif
12. Once the SQL statement is inserted, a box displays with all the fields in the Select statement so that it can be dragged onto the report.

Complementary Products for SQL View

Complementary Products for SQL View

sharepointsolutions.pngYou will find many of our products complement each other quite nicely saving you the time and trouble to write custom code. With Bamboo at the heart of your SharePoint investment, you gain access to a huge catalog of enhancements, components, and accessories that add the critical functionality your business requires.

The same components can be easily used in future applications and they all come from a single, trusted vendor, ensuring an easy purchase process and support you can count on. That’s the Bamboo Way!