PowerPivot for SharePoint - Part IV

Finally reaching the last post on PowerPivot for SharePoint, let's go ahead and have some fun with the PowerPivot site.

1. Let’s activate the features now, go to your SharePoint site, and turn on the feature in “Site Collection Features”!

2. Go to Create Site now, you will be seeing a new PowerPivot Site option!

3. Hurray, let's rock and roll with PowerPivot Gallery!

For more related posting, please refers,

PowerPivot for SharePoint - Part III

PowerPivot for SharePoint - Part II

PowerPivot for SharePoint Installation

PowerPivot for SharePoint - Part III

Yesterday, Part II, we really focus on how to setup the pre-requisites and ground work for PowerPivot for SharePoint.

Today, it about the configuration!

1. Before we start the PowerPivot Service Application's configuration, some simple configuration needed to be done on Excel Service Application first,

2. We need to set the trusted location (this is also applicable for Excel Services publishing)

2.1 Important #1 - In External Data, set Allow External data to “Trusted data connection libraries and embedded”. This setting is required for PowerPivot data access in a workbook.

2.2 Important #2 - Clear the “Warn on Data Refresh” checkbox to allow preview images of individual worksheets in PowerPivot Gallery. If you choose to keep the warning and workbook settings specify refresh on open, you might get a single preview image of the warning instead of the pages in your workbook. (You don’t want the PowerPivot Gallery, turns out showing the ugly warning page on Worksheet preview).

2.3 Important #3 – Increase the Workbook Size!

3. Next thing to do is to configure the Unattended Data Access Account, go to "Secure Store Service" application.

4. Create a New Secure Store Target Application (Just follow the screenshot, and remember the Application Name you specify)!

5. Set who can use this Secure Store Target Application Account!

6. Finally, set the credential (which account to use).

7. Finally, tell PowerPivot Service Application (The name you have given in Step 4), which Secure Store Application to use for Unattended Data Refresh Account.

8. Now you get the PowerPivot dashboard! Go to the “Configure service application settings”, and set the Unattended Data Refresh Account!

Guys! We are DONE with the configuration!

I promise the next post will be quick, how to activate the features, and create a PowerPivot site (with the sexy silverlight PowerPivot viewer) and we will start to rock-and-roll!

For more information about PowerPivot for SharePoint,

PowerPivot for SharePoint - Part III

PowerPivot for SharePoint - Part II

PowerPivot for SharePoint Installation

PowerPivot for SharePoint - Part II

I decided to resume blogging again, with the same old story that i left out six months back, on the installation of PowerPivot for SharePoint.

This time around, i am doing it on a serious end-to-end series of the installation and configuration, on an existing SharePoint Farm.

It is going to be lengthy, so i will be separating it to few posts.

Let's get started.

1. Installing PowerPivot for SQL , on existing Farm!

2. This is taking a while to complete. (My case, it took around 7 – 10 minutes to complete).

3. It is DONE! (With Success!)

4. Next thing after installation, make sure you deploy the “powerpivotwebapp.wsp” to all the sites that you want to enable PowerPivot integration!

5. You should have two (2) wsp installed, you only deploy “powerpointwebapp.wsp” to all web applications you want to enable PowerPivot!

6. You have to make sure these important services (Claims to Windows Token, Excel Calculation Services, Secure Store Service) are running as well (These services MUST be running)!

7. So, you have done with the preparation of PowerPivot for SharePoint, next is to “Create the Service Applications”!

8. I usually just accept the default settings :p

We are now done with the preparation, these are just the preliminary ground work required for the PowerPivot to work in SharePoint.

The next part we will talk about more detail configuration required on Excel Services Application, Secure Store Service Application, and PowerPivot Service Application required to empower your PowerPivot integration feature with SharePoint.

For more information about PowerPivot for SharePoint,

PowerPivot for SharePoint - Part III

PowerPivot for SharePoint - Part II

PowerPivot for SharePoint Installation

Linq with SharePoint 2010 - Part I

Today I have decided to start working on my web part that will be interacting with SharePoint 2010 through LINQ, after spending a few R&D through the web, I have started my development of Visual Web Part with SharePoint LINQ (Of course, using Visual Studio 2010!).

Before that, let me create a list named "Quote of the Week" which a few columns.

1. Start Visual Studio 2010 (Of course!)

2. Create a Visual Web Part Project, which will automatically generate the files required.

3. Now, i need to really get my hands on the SharePoint Linq experience, thus, i will be going to my web part folder, and run the SPMetal.exe command from your command prompt.

4. A class file will be generated, basically this class file will be wrapping all your lists definition, so that you can access STRONG TYPED list objects (Yeah!!!)

5. Add this class file to your project NOW!

6. Wait, i need a field, internal field for created date, but, it is not in my generated class. So, what i will do is to replicate a property (It will returned the field value based on the properties you set for the list!).

6. Now, i have my object models for SharePoint Linq done, let's put this into practical, note the following code snippet. This is the page load event that query SharePoint list, using Linq of course!

NOTE: This piece of code query the Quote of the Week list, search for quote items where Start Date <> and End Date > Today, and it will be sorted by the created date, in descending order.

7. Yes! My Quote of the Week web part is now DONE!

Easy? Now, let's rock-and-roll NOW!

Note : If you are changing the namespace and class name, you need to change the Safe Control registration, see the following URL,

http://www.andrewconnell.com/blog/archive/2010/07/01/renaming-web-parts-for-sharepoint-2010-with-visual-studio-2010.aspx

SharePoint 2010 Master Page

When it come to styling and changing the UI for SharePoint, you can't run away from Master Page, now, in 2010, you will still be changing and working with Master Page, from your SharePoint Designer 2010.

Few things to be aware of when working with SharePoint 2010 master page,

1. When you have a team site, please turn on your Publishing feature so you can then pick and choose the master page to apply to your site.

2. Simplest to start off with is really to start off with the Starter Master Page by Randy Drisgill, this master page really give you a quick and clean starts by removing all unnecessary html elements, which is quite irritating if you were to reposition the SharePoint controls.

3. In the Starter Master Page, change the CSS registration control to map to your CSS file (Of course, store it in your Style Library).

4. Start your IE8, use the Developer Tool to start picking up elements and change the CSS classes to your preference! You may refer the following URL for the list of CSS classes used in SharePoint,

• http://msdn.microsoft.com/en-us/library/ms438349.aspx - From MSDN, specifically on SharePoint Foundation.
• http://erikswenson.blogspot.com/2010/01/sharepoint-2010-base-css-classes.html - This is cool, it even give you a picture visualization on each of the class, well explained.

Have Fun with your Master Page, i will post one of my "test drive" in the later post!

Linq with Sharepoint

Doing a few round of testing on Linq and Sharepoint with my tech lead, and here is the outcome of our testing, the joining between two lists with simple Linq syntax was extremely straight forward, but, the result was just awesome!

The following is the code snippet,

If you are developing some custom applications which stored data in multiple Sharepoint lists, this piece of code is gonna save your days!

I will be posting the sample lists and results achievable in a later post.

Microsoft Innovation Day - 6 August 2010

For those who attended the Microsoft Innovation Day, Malaysia, @6 August 2010, if you need the slide that i have presented, feel free to download from my sky drive, or view online at the following link,

Reporting made easier in SharePoint, with Reporting Services and Excel / PowerPivot

Happy Reporting!