Now that Project's ListViewWebPart has been customized into an XSLT Data View, I want to go ahead and go back to SharePoint Designer. I am going to do some more customization in this site to create a real Data View Dashboard using external SQL data. To do so, I am going to open up this page again in SharePoint Designer, and then I notice immediately that I see an icon over here telling me this page has been unghosted. What I can choose to do is right-click, and return this page to the site definition by clicking Reset to Site Definition or I can continue along the lines of my customizations.
I want to go ahead and continue with my customizations. To do so, I want to insert a new DataViewWebPart above this Projects list that contains information about my Resellers. To do so, I want to create a new data source to my SQL database back end. To add a new data source to this web site, I am going to click on the Task Panes menu, choose the Data Source Library, and then once in the Data Source Library, I am going to add a new SQL data source. I can see SharePoint Lists are in here automatically, SharePoint document Libraries are in here automatically but I want to add a Database Connection. So I expand the Database Connections section and click Connect to a database. From the Data Source Properties dialog, I can Configure a Database Connection. The database for the purposes of this lesson is just localhost, and the User name is sa, and I will type in my Password, and then click Next.
I get a warning that The selected authentication options saves the username and password as clear text in the data connection. What this means is that anyone who has author permissions to this web site will be able to open up the data source connection and see my username and password. If that is a problem, you should use single sign on authentication which is available on Mask. I am going to click OK because I trust my authors, and that will iterate through the available databases, and show me a list of databases on this server. I am going to choose AdventureWorksDW which will then show me a list of the tables available inside of this data source, and I am going to choose the Resellers table, and then click Finish. I want to rename this so I will give it a Name of Resellers and then click OK.
Now I can see that database in my Database Connections and when I click on it, I can choose to Show Data. What this does is round trips through the server and does a SQL query against that database back end which returns all the data as an XML file. That XML file is then iterated and recreate a table over here that shows all of the fields inside of the table, inside of that SQL database. So here I can see that the fields available inside of this table are ResellerKey, GeographyKey, etcetera. I have two options here for inserting a view. I can either drag straight from the Data Source Library or I can select individual fields here and then insert them as a Data View. I am going to select individual fields and then use the button to insert these selected fields but in order to do so; I first want to make room on my page.
So I am going to click Back on to this DataFormWebPart. After I click on the DataViewWebPart, I can hit the Left Arrow twice to get my cursor outside of this WebPart zone, and then when I hit Enter, it will insert a little space above the zone, and I can type characters in here. For example, Reseller, and then I am going to hit Enter to add some more space here. Notice that when I selected that DataFormWebPart, that the Data Source Details changed to show me the properties of the Projects List. I have to set it back to showing me the properties of that SQL data source I created. To do so, I can simply click on the Data Source Library, choose Resellers, and Show Data again, and here we have the fields loaded up again inside of the Data Source Details Task pane.
I am going to maximize this Task pane so I can see all of the fields inside of my table. This initial view is going to contain the name of my Reseller, their address, and their phone number. So the first field that I want to select is the ResellerName so I will scroll down to that, and then their address which is AddressLine1, I can Ctrl+Click and select that field, and then their Phone number, and I will Ctrl+Click and select that field. Now what's interesting about this is that the order I select the field actually does matter. They look like they are selected so that when I insert a view, it will insert Phone first, then ResellerName, then AddressLine1 but since I have selected ResellerName first, it's going to insert that field first, and then AddressLine1, and then the Phone number.
I will go ahead and click on Insert Selected Fields as, and choose a Multiple Item View, and we can see ResellerName, AddressLine1, and Phone number are inserted. I don't like the name AddressLine1, so I am just going to click in to here, type a space character, and take out the word Line1. I am going to save my work and then finally, what I want to do is change the look and feel of this DataFormWebPart using some of our styles that are included out of the box. What I want to have happen is the ResellerName should show up as slightly larger than the Address and the Phone number and it should be bold. So I am going to look through the galleries of styles that SharePoint Designer ships and choose one that approximates that look and feel.
To do so, I click on Data View, Change Layout, and then click on the HTML view styles. I see the first one is just a Basic layout which I have currently, but the next one appears to be similar to the style that I want. It has bold, large title, and some repeating data underneath that. So I am going to choose that style and click OK, and sure enough that's pretty close to what I want. I am going to go ahead and save this page, and now I want to change the paging of this Data View so that instead of seeing these ten companies, I am only going to see one.
One thing that I would like to call out here is that you can see inside of the Data Source Details Task pane, there are actually 701 Reseller companies inside of my Data Source table but only ten were inserted. That's because whenever there are more than 25 items in a data source, that data source is automatically paged back down to ten in order to save on performance.
For this lesson, I am going to change the paging down to 1. To do so, I click on Data View Menu again, choose Paging, and I can see that it is currently set to 10; I am going to set that to 1, and click OK. Now I want to save the page and preview in the browser. We can see that Data View show up, the Reseller text that I typed, as well as the name of the company in bold, the blue line, and the Address, and the Phone number.
Transcription by:
Scribe4you Transcription Services