iSite

Microsoft SharePoint Foundation 2010 and Microsoft SharePoint 2010’s lists and libraries provide the very useful option of reporting from the list data within Microsoft Excel 2007 PivotTables. If you want to store information that you would like to analyse and present in a graphical way and/ or to have the ability to refresh the data while it changes; using Microsoft Excel 2007 PivotTables should be your preferred option.

In this article, I will provide you with a simple guide how to use PivotTables and SharePoint 2010. I will use a list with sample quotes. I will create a report which presents ‘quote amount’ based on quote status and ‘bill to US state’.

First, let’s navigate to the list storing the data.

To use Excel PivotTable you will need to change the view to Datasheet. In SharePoint 2010 you can change a view by clicking on List Tools -> List and then Datasheet View.

Please note that while you are in Datasheet View the following options are active:

• New Row
• Show Task Pane
• Show Totals
• Refresh Data

In previous versions of SharePoint, these options were available from the actions menu. In 2010, they are more accessible. If you click Show Task Pane, a familiar side menu will appear on right hand side.

From that menu choose Create Excel PivotTable Report. Microsoft Excel will be launched and you will be prompted to enable data connection.

Once you enable the connection, Excel PivotTable options are displayed in Excel

 
Now we can quickly build the report.

In this example, we would like to know what are the total amounts quoted by ‘US state’, showing also the ‘quote status’.
I can quickly build such a report by dragging the proper fields to the appropriate report areas.

As a result, I have a table with a summary of quote values grouped by ‘bill to US state’ and by ‘quote total amount’. Now I would also like to present this in a chart. To achieve this we can click on the PivotChart button located on the Excel ribbon.

Le’s choose a column based chart and click OK. We can then format the chart.

We can exclude values using Categories and Series filters. I will exclude blanks value and Active, Closed, Drafts as I am interested only in quotes that were Won with US billing address. I will also Add Data Labels to have exact quotes totals.

From our chart we can clearly see that majority of my Won quotes are for NM (New Mexico). Once designed, the report can be saved to a local drive or published to Excel Services, with the latter being the preferred option. This report provides an option to refresh data, so if there is any change it is just one click for you to update the chart.

Using SharePoint lists for storing data can be very effective way to share information. It empowers business users to analyse data and graphically report from that data. This can be a time and money saver as creating similar reports via SQL reporting services would require significantly more time. It is also worth mentioning that data in SharePoint list can originate in business applications like CRM or any ERP or back end system, and these can be easily integrated into SharePoint.

Good luck!
Tomas

omanowski

Microsoft have introduced SharePoint 2010. It is high time for us to explore that product! Over the coming weeks we will evaluate and discuss the features and highlight any differences with previous versions.

In this article, I will compare the Import Spreadsheet option from SharePoint 2007, described in a previous blog, with the same option from SharePoint 2010. You can find information on how to prepare the Excel workbook in my previous blog article.

Since the SharePoint 2010 navigation has changed, lets spend some time investigating where to click to access Create page.

You can access Create page going either Site Actions and then More Options…

Or

Clicking All Site Content and then Create.

Both actions will lead us to Create page.

On this page, you can either:
• scroll down to find Import Spreadsheet option or
• click on ‘I’ key to go directly to Import spreadsheet or
• use Filter By option clicking on List item.

When Import Spreadsheet option is highlighted, then you can choose the Create button.

Now you are transferred to a more familiar import page. You need to name the new list and choose the Excel workbook to be imported.

You need to decide what range to import and then click on activated Import button.

However, unfortunately I got an error: “Method ‘Post’ of object ‘IOWSPostData’ failed.”.

This problem is PC specific and you may not experience it during your import process.

In my case, it is most likely caused by previously having Office 2003 installed, uninstalling it and then installing Office 2007. Not having updated code within Exptoows.xla file is to be blamed for the issue. You can get Microsoft hotfix using this link: http://support.microsoft.com/kb/956510
After successfully importing the Excel workbook, you will be presented with new list.

My list has the following columns.

Now you can modify the view to use Totals. To navigate to View settings you need to click on List Tools -> List -> Modify View.

You will navigate to Edit View page. You may note that there are some additional options, not present in the 2007 version. I will go straight to Totals option, which does not seem to have changed.
There are familiar functions allowing us to calculate:

• Count - Counts the number of arguments that contain data.
• Average - Returns the average (arithmetic mean) of the arguments in the column.
• Maximum - Returns the largest value in a set of values in the column.
• Minimum - Returns the smallest value in a set of values in the column.
• Sum - Adds all the numbers in the column.
• Std Deviation - Calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
• Variance - Estimates variance based on the column values.

As always if you have questions then just get in touch with myself or one of the team.

Good luck!

Tomas

The problem

We come across this scenario quite often - someone works with data in Microsoft Excel and would like to share this with other people. They would also like to retain control over editing the data, and do not want to distribute multiple files.

A suggested solution

One of the answers to this problem is to use SharePoint’s ability to create a list directly from an Excel spreadsheet. This option provides a quick and simple way to share data with other people, without losing the single version of data. There are advantages also with the author holding the ability to restrict editing rights and also offering reporting capabilities. Importing a spreadsheet is available both in SharePoint Services 3.0 and Microsoft Office SharePoint Server 2007 & 2010.

Before starting an import it is very important to make sure that your data in Excel is properly formatted, that is - all your columns have the proper format, e.g. currency, text or number. SharePoint will assign the format based on the column data type.  Taking a few minutes to check this, will save you time later when analysing the data, e.g. If SharePoint assigns a text column, you will not be able to use Totals on these values.

Note that you need User permissions to create lists (Manage List permission) to be able to complete the importing task.

How to achieve it - Importing Excel data into a SharePoint list

Navigate to your SharePoint site and the click on Site Actions and then Create.

Then on the Create page choose Import Spreadsheet option.

Then you need to name your list, you may also provide a description, than choose your Excel file using File location field.

After clicking Import, you will be presented with your Excel workbook and option to choose the Range you would like to import. There are three options: Table Range, Range of Cells, Named Range.
You may use Table Range if you want to import whole table or Range of Cells if only some columns and rows should be used. If you have a Named Ranges in your workbook you may also choose one of these.

The screens bellow show how to chose a Range of Cells.

After that you click Import button and SharePoint will create a list based on your Excel data.

When you go into List Settings, you will be able to check what the columns formats are. If you import Currency columns but then you see that they are formatted as Single line of text on SharePoint then you may need to import you workbook again. Otherwise you will not be able to use Totals function on your imported list.

If you have any questions on this article please get in touch with the iSite SharePoint team.

Good luck!

Tomasz

Installing Microsoft Dynamics CRM 4.0 on Windows Server 2008 requires some preparation work to be completed before Microsoft CRM 4.0 setup can be run.

The following roles and components are required:
Application Server role with the .Net Framework 3.5.1 service.
File Services role with File Server; Windows Server 2003 File Services; Indexing Service
Web Server role with the following role services:

• Web Server
• Application Development
• Health and Diagnostics
• Security
• Performance
• Management Tools

       (see screenshot below for full details)

The roles are essential to a successful installation.

Furthermore, at this point if the domain user account for CRM services is used to install, there may be an issue and the installation may fail with the following error:

‘Action Microsoft.Crm.Setup.Server.ConfigureAspNetAccountAction failed. System.ComponentModel.Win32Exception: The group name could not be found.’

To prevent this issue you would need to install Microsoft CRM 4.0 using the Network Service account for the security account of the service that will be installed. Then after Microsoft CRM 4.0 is successfully installed, we can manually complete the following actions to change the Network Service account to a domain user account. The following actions need to be completed:

First

Using Information Services (IIS) Manager change the user account used as Identity for CRM application pool to your domain user account.

The advanced settings are as follows:

After the account changes are confirmed, we need to restart IIS, whcih can be done from Internet Information Services (IIS) Manager. In the right top corner, if you click on your server name, there is a Restart option.

Second

Add domain account to PrivUserGroup and SQLAccessGroup Active Directory Groups created during Microsoft CRM 4.0 installation.

You will find these groups in Active Directory Organizational Unit created during pre-installation preparation work.

Third

On the CRM server change the permissions on the Trace folder located here C:\Program Files\Microsoft Dynamics CRM\.

You need to grant your service account user FULL CONTROL permissions to Trace directory and subdirectories.

Fourth

Add the service account to the local groups on your CRM server. Use Server Manager to access Local Users and Groups.

A common issue facing SharePoint users is the inability to efficiently report on data stored within SharePoint lists. Standard reporting capabilities of MS Excel and MS Access embedded in SharePoint are often not sufficient for enterprise reporting purposes.

The first instinct of any IT professional would be to use MS Reporting Services to build a reporting solution. However, when you connect to the SharePoint content database and try to query it you will quickly realise that it is not as easy as one would suspect.  It is time consuming and extremely difficult to make sense of the SharePoint database schema and query it effectively with SQL.

iSite have a solution!

We have created a Microsoft SQL Reporting Services data processing extension that makes it possible to retrieve data from SharePoint lists using standard SQL queries. Once installed, our Reporting Services Data Processing Extension will provide a new data source that can be used as a part of Reporting Services.

After installing the extension, you will be able to use this custom data source type, which is dedicated to SharePoint.

Install the iSite extension

After connecting to the appropriate SharePoint database for a particular site, you can start using the standard SQL Server Business Intelligence Development Studio interface, which will have two additional function buttons.

 The first button executes the query.

Second enables you to verify the SharePoint lists and their availability and makes query preparation so much easier.

 The pop up window that appears (see below) will display the available SharePoint lists. On the right hand side, when you select a list, you will see the fields for this list displayed.

When you click on OK, the query will be copied into the query pane (see image below).

�

So lets look at building a simple report using the SharePoint Reporting Extension.

Let assume that we have 2 lists, Account Revenue and Time. Account Revenue has a lookup into the Time list.
We will base our report on the following query:

select * from [Account Revenue] ar inner join [Time] t on t.id = ar.Time

In the query window, run the query and the results will display (see below).

And in this simple, quick and effective way we were able to create a report from SharePoint database.

The final report is shown below.

I would be happy to send you on the extension described in this blog.

Please get in touch with me at tomasz.romanowski@isite-solutions.c0m

Tomas

Firstly apologies for the delay in completing the blog article on iSite values!

Two things happened this past week that helped to crystallise my thoughts…

1. We had the IVI summer session in University of Maynooth where the consortium launched version 1.0 of our IT-CMF – a really important milestone in the evolution of this vital framework for managing IT.

2. I had the feeling of ‘reaching the summit’ of the turmoil that has been just, well everywhere this past twelve – eighteen months.

These two events were not related but helped me to regain the sense that we have everything within ourselves (iSite) that we need to succeed.

Let me present our two fundamental values.

Value No 1 -  iSite is built on the founding principle of win-win.

We can thank Stephen Covey for articulating this and he describes is as an abundance mentality (check out his book seven habits of highly effective people).  He reasons that there is enough out there for us all, and that we can win by sharing.

This means, quite simply, you go to work each day believing that you will give something (your time, skills and effort) and you will get something back (your salary, some satisfaction from solving a problem, creating something new, or meeting people).

If we can take this approach within our own team, with our customers and with our partners then … (we’re hoping!)… that every single day we are building something of value, and adhering to the principle of win-win. The fact is though, win-win means you have to be tough as well as emphatic. It means you have to understand what you are bringing to the party and be able to negotiate around that.

I also particularly like the fact there is an alternative to win-win – No Deal.

If for whatever reason, the circumstances of the engagement is blocking a win-win outcome then it may be more sensible to respectfully back away and accept a no deal.

Value No 2  - Share the Vision

This is where we get our tag line, our slogan… “a shared vision”. Okay well let’s be honest! It started as a neat fit with the name iSite but it has evolved  over the past four years into something we believe very strongly in.

Nigel Risner has even improved on this, and he says…

• Have a dream
• Share the vision
• Play with whoever shows up

These simple statements are so powerful!

Nigel believes that everyone has the necessary ‘stuff’ inside them to be successful… what’s often missing is the self awareness, or the self confidence, or the self belief to make it happen.

So if we want to try and practice value no 2, then let’s look at each one in turn…

1. Have a dream

This must be something which motivates you to get up in the morning. It can be a big hairy audacious goal, or just something small to get started.

2. Share the vision

This is vital. You have to be able to explain to someone else what the idea is, some thoughts on how to make it real. In sharing the vision, it brings other people in, and you can leverage the power of their creativity, their management skills, their sales ability.

3. Play with whoever shows up

When we were kids, you probably remember heading out in the morning into the street or yard or field and you would ‘play with whoever showed up’.  And that’s life! We can waste a huge amount of time looking for the best possible team, the star players…

When things get complex or fraught with danger, value no 2 really brings us back to basics and helps us realise where we are and why we are here.
To bring this back to iSite, We try to always apply this principle when dealing with our customers.

Sharing the vision really means getting an in-depth understanding of what the customer is trying to achieve. It means understanding not just what they want to do, but also the obstacles that are in their way, the constraints they have in front of them. It means we have to dig deep to empathise and communicate our understanding in a way they will recognise and comprehend.

I hope these offer some insight into the iSite way of doing business. These core values will not change as we build our company, but hopefully we can add others and learn from major success stories like Johnson and Johnson.

Dave

Taking up where we left off on how we are shaping the values of our company, let me recount a recent revelation which has served to reassure that we are on the right path.

In the past few weeks we completed a successful project with big Pharma company Johnson and Johnson. In chatting to one of their senior people in the post-project evaluation, we found out about their credo…

Johnson and Johnson credo

and wow, what a powerful statement this is! Placing shareholder value last in line, JnJ trust their main focus on people who use their products, their employees and business partners. The fact that JnJ are around for almost 120 years tells its own story.

Taken from the JnJ website…

Robert Wood Johnson, former chairman from 1932 to 1963 and a member of the Company’s founding family, crafted Our Credo himself in 1943, just before Johnson & Johnson became a publicly traded company. This was long before anyone ever heard the term “corporate social responsibility.” Our Credo is more than just a moral compass. We believe it’s a recipe for business success. The fact that Johnson & Johnson is one of only a handful of companies that have flourished through more than a century of change is proof of that.

So it proves that putting customers first can be hugely successful and very powerful.

The iSite challenge is not to pay lip service to the values we have at the core of our business.

More on this in my next post when I will describe our values and how we see them shaping the next five years in our business.

A recent article in the Harvard Business Review by Roger Martin, lent us some fascinating insights into the rapidly changing world of capitalism.

His main idea is that the harder a CEO is pushed to increase shareholder value, the more the CEO will be tempted to make moves that hurt shareholder value.

These words jumped off the page!

The author was fundamentally challenging the longstanding corporate aim of maximising shareholder value. He argued that it was impossible to continually increase the return to shareholders.

“Most executives figure out that Shareholder value creation and destruction are cyclical. They can push it up in short bursts, but in due course share prices will fall again”.

He also was able to show the numbers behind his argument, taking several typical large corporations and highlighting these trends over the longer term.

Incidentally, one of the central aims of the iSite strategy is to maximise shareholder value(!!), mainly by measuring CFROI (Cash flow return on investment).  We take the view that business propositions have a much shorter lifespan these days so the average life of an ‘asset’ is 3 years.

In researching this a little more, we find that long held views on this are changing. Jack Welch, the former General Electric chief who is credited with coining the phrase in 1981, said last year that the emphasis he placed on shareholder value was misplaced.

“On the face of it, shareholder value is the dumbest idea in the world,”
“Shareholder value is a result, not a strategy…your main constituencies are your employees, your customers and your products.”

Alan Greenspan has also recanted:

 “I made a mistake in presuming that the self-interests of organisations, specifically banks and others, were such as that they were best capable of protecting their own shareholders and their equity in the firms”.

So what is the alternative?

According to Roger Martin in his article:

“Companies should seek to maximise customer satisfaction while ensuring shareholders earn an acceptable risk-adjusted return on their equity”.

With this strategy the CEO can focus on building the real business.

Customers become the top priority; with the focus on improving products, services and operations. This replaces a relentless drive to increease profits above all else.

Underpinning this idea is the mathematical certainty that there can only be one goal. Linear programming is a technique for optimising a given variable subject to other constraints.

Taking the example - Roger explains that you cannot maximise both the value of outputs in a factory and the quality of products shipped. You have to pick one main objective function and treat the others as constraints. 

So the recommended approach is to make Customer Value your main goal, subject to creating a minimum shareholder value.

As CEO of iSite, this has really touched a nerve.  I have always believed that the “customer is king” and that by building a loyal customer base, a company would be successful in the long term. As we look at the larger multinationals, you have to question if some of the actions they take on a daily basis are on behalf of shareholders?

We will discuss these ideas in future posts, and I will share some insights as we adopt this strategy at iSite.

- Dave

 “I would like to use SharePoint to send alerts and notifications”.

We have received this request numerous times from our customer support teams.

So to help you out, let’s take a step by step approach to configuring our SharePoint server to support alerts and notifications.

As a starting point, the server name is teowebdev001 and we have all SharePoint services installed on that one server. We also have the SMTP service installed.

Step 1

The first action it to setup Outgoing E-Mail Settings. For the  as Outbound SMTP server, we will use the local server, teowebdev001, and the email address. It is recommended to use a dedicated email address for notification, preferably one accessible by the SharePoint administrator.

 
 Figure 1 - Set the outgoing email settings.
 

Step 2

Next we check if SMTP service is running, Open IIS and click on SMTP…

Step 3

We will use these logs to check if the updates were successful. We enable logging by Right Clicking on SMTP and choosing Properties, the general tab will be highlighted and select the tick box Enable Logging

Step 4

Now we need to configure SPF (Sender Policy Framework) with the ‘pass’ option for the SharePoint external URL or IP (depending on your SPF configuration options).
SPF is required to indicate that SharePoint emails are not forged. SPF is located on the email server.

Note - If the SharePoint outgoing email is from the same domain as the users emails there will be ‘Received-SPF: pass’ information in an email details.

 Figure 4 - Configure SPF 

Step 5 

We can then check the user email settings on SharePoint -  we need to make sure that the user has an email address configured.

Complete!

Now you should be able to receive alerts and other SharePoint notifications. If there are problems or investigation is required SMTP logs, which are located here C:WINDOWSsystem32LogFilesSMTPSVC1, are great source of information.

Note - If you don’t receive emails even though you have all the settings right, and you have tested them, then we would recommend to add a user email address also to the SIP Address field.

Good Luck!

Tomasz

The following brief article documents a workaround to an issue installing SharePoint 2010.
SharePoint 2010 installation error cause by .Net Framework 3.5 SP1 issue (KB971831)

SharePoint Foundation 2010 (Windows SharePoint Services 2010 Beta) is available now for download from both MSDN and from Microsoft public website:

http://www.microsoft.com/downloads/details.aspx?FamilyID=906c9f5a-6505-4eba-bf24-95e423ac1703&displaylang=en

Once you have successfully run the SharePoint Product and Technologies Preparation Tool it present a screen which looks like this…

You will get into the following error when trying to run SharePoint setup file…

When you search for KB971831 you will find that Microsoft does not expose the required file and you are asked to contact Microsoft Support

http://support.microsoft.com/kb/971831

So to fix this issue, use any of the following links to quickly get hold of the file and be able to finalise your installation:

• http://code.msdn.microsoft.com/KB971831/Release/ProjectReleases.aspx?ReleaseId=3235
• http://connect.microsoft.com/VisualStudio/Downloads/DownloadDetails.aspx?DownloadID=21953
• http://code.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=KB971831&DownloadId=7285

Feel free to contact me if you have any queries tomasz.romanowski@isite-solutions.com