Posted by Troy on August 7, 2010 under Blogging, Business, Business Intelligence, IT, Microsoft, Performance Management, Social Networking | 
Well I am not sure how much sense it makes for a Blog to have a Facebook Page but I though I would try it out. I am always looking for new topics or issues to write about especially in the area of Business Intelligence or even day to day question on how to use Microsoft Office and SharePoint to enhance business activities. Feel free to post ideas or comments.
Troy Scott – Blog
Promote Your Page Too
Posted by Troy on July 19, 2010 under Business, Business Intelligence, Microsoft, Performance Management, SharePoint, SQL Server |
If you have been following along in the last few posts, I have created an External Content Type called BcsSalesForecast with the following operations:
- Read List
- Read Item
- Update
Then an External List was created called Sales Forecast 2010. Up to now the main focus of the discussion has been around creating the External Content Type and the store procedures which are used as its data source. However, if you currently view the list below its presentation as a lot to be desired. First off, its sorted by ID and there are over 700 records with 30 per page which means that it could take awhile to find the record you are looking for.
In order to make the list more “useable” it is possible to modify the view. Before the view is modified a minor update is required to the data. The Sales Forecast 2010 list is a weekly forecast application for 2010 and provides the previous years as a starting point (the data is actually from Adventure Works for 2008). Ideally it would be nice to sort the data by week but I did not include a sort key as part of the original design. But this can fixed by running the following UPDATE statement against the dbo.WeeklySalesForecast table:
update dbo.WeeklySalesForecast set WeekNumber = case when CONVERT( INT,SUBSTRING(WeekNumber,5,3)) < 10 then 'Week 0' + SUBSTRING(WeekNumber,6,1) else WeekNumber end
This will add a leading 0 to all weeks that are less then week 10 which will ensure the sorting by Week will work. Now you can navigate to the Sales Forecast 2010 list and click on the List tab:
Next click Modify View but do not click on Modify in SharePoint Designer (Advanced). For this scenario SharePoint 2010 Designer is not required. The changes to the list can be performed in the browser. Change the First Sort Column from ID to WeekNumber:
Next set the First Group By Column to SalesRegion and the Second Group By Column to ProductCategory:
Change the number of items to display per page from 30 to 300 :
Hide the SalesRegion and ProductCategory since these are redundant because they are being used as Groups. Secondly the ID field can be hidden because it is just required as the unique identifier for the row:
(Note: the position of the columns may be different than what you see when you “Modify” the view)
Click OK to save the changes to the view and now look at the List:
As you can see, a few simple modifications to the list can make a significant difference in terms of the lists usability.
Troy
Posted by Troy on July 17, 2010 under Business, Business Intelligence, IT, Microsoft, Performance Management, SharePoint, SQL Server |
* Please note that the following post is based on SharePoint 2010 Foundation Server and not SharePoint 2010 Server.
In a previous post , I created an External Content Type with 2 operations and then created an External List based on the External Content Type. In short this means that you are now able to create a SharePoint List based on some external data (e.g. database table or store procedure in this example). But currently the external data is read only because we only created the following operations for the External Content Type:
Given that the example was based on Sales Forecast data at the very least we need to give the user the ability to update the data. In order to do this a new operation needs to be added and a new stored procedure is required to support the operation. Before we add the the Update Operation to the BcsSalesForecast External Content Type we need to create a store procedure which will be used for this operation called dbo.BcsSalesForecastUpdate:
CREATE procedure [dbo].[BcsSalesForecastUpdate] @ID nvarchar(512), @ForecastAmount int as set nocount on update dbo.WeeklySalesForecast set ForecastAmount = @ForecastAmount from dbo.WeeklySalesForecast where EnglishMonthName + '-' + WeekNumber + '-' + SalesTerritoryRegion + '-' + Category = @ID
Now that the store procedure is created, SharePoint Designer can be used to add the new operation to the BcsSalesForecast External Content Type. Navigate to the BcsSalesForecast External Content Type and click “Edit External Content Type” on the Ribbon:
Now click on the “Operations Design View” Menu item on the Ribbon:
Right-click on the Routine folder and select Refresh. The dbo.BcsSalesForecastUpdate stored procedure should appear. Right-click on the stored procedure and select “New Update Operation”.
Make sure the @ID is select as the “Map to Identifier”:
select * from dbo.WeeklySalesForecast where ForecastAmount is not null
The BcsSalesForecastReadItem and the BcsSalesForecastReadList have to be updated too. The following fields must be set to “Read Only” or the new Edit Form will not work:
- MonthName
- WeekNumber
- SalesGroup
- SalesRegion
- ProductCategory
- LastYearAmount
Below is an example of setting a field to read only. Remember this must be performed on both the Read Item and Read List operations:
Click Finish and the new operation has been created. The solution is not finished yet. Remember, we also have an External List called “Sales Forecast”. If you view the Sales Forecast list and right-click on a row you will notice that there is a new menu item called “Edit Item” and the “Edit Item” button in the ribbon is no longer grayed out. Try to edit the row. You should receive the following error:
The reason for this is that an Edit Form does not exist for the list. To work around this we can simply create a new External List and call it “Sales Forecast 2010” and the SharePoint designer will create the necessary forms depending on what operations have been defined for the External Content Type:
Select the list in SharePoint Designer and click the “Preview in Browser” button on the Ribbon to try out the new “Edit” functionality. Right-click on any row on the list and select Edit item. The following form will pop-up:
Click Save and the the SharePoint List and the External Data will be updated. Use Management Studio and execute the following query against the dbo.WeeklySalesForecast:
select * from dbo.WeeklySalesForecast where ForecastAmount is not null
Now this example may not be practical especially if one person was responsible for updating all of the forecast data. But this example shows how easy it is to create a simple CRUD (Create, Read, Update, Delete) based application based on External data with little or no code. This example only implemented the Read and Update operations but the Create and Delete operations would be very easy to implement and would require 2 more stored procedures (dbo.BcsSalesForecastCreate, dbo.BcsSalesForecastDelete)
Troy
