Dynamics GP, Microsoft Office, Sharepoint, Uncategorized

Deploying Business Intelligence to the end user with PowerPivot

Retrieve data using queries in Microsoft SQL Server®2005 and 2008, Microsoft SQL Server Reporting Services, Microsoft FRX, SmartList Builder software & Microsoft Excel®2010 with PowerPivot software

Deploy PowerPivot in SharePoint 2010 to give staff access to data

With PowerPivot you can create browser based Business Intelligence pages in SharePoint by uploading Excel workbooks with the PowerPivot add in using SQL queries and views to pull real time data from multiple sources including SQL Databases, Excel Workbooks and Microsoft Dynamics GP, Forecaster, Encore and your Association Management System.

Background Information

§ I love collecting code and re-using it as much as possible to save time and money and leverage SharePoint.

§ I gather views by searching the internet sites like “GPUG’s Share My Code” site, SharePoint Users groups and CodePlex.

§ I belong to several users groups including GPUG, TheSUG, AUG and FEDSPUG where I get valuable information on SharePoint tips and tricks.

§ What is a PivotTable?

§ A PivotTable report is an interactive table that automatically extracts, organizes, and summarizes data.

§ PivotTables allow you to summarize and analyze large amounts of data in excel.

§ PowerPivot allows you to connect to Tables, Views or write a query to pull data into a list from multiple sources

§ What is required to install PowerPivot for Excel?

§ Microsoft Office 2010 and the add on PowerPivot for Excel

§ What is required to deploy Excel PowerPivot reports to SharePoint?

§ SharePoint 2010 SP1, Windows®2008 R2, Microsoft SQL Server 2005 and 2008, Office 2010, PowerPivot and Power View software

§ Download Powerpivot from the Microsoft site

§ http://www.microsoft.com/en-us/bi/powerpivot.aspx

§ It is an Add in that self installs when you open Excel.

§ Open Excel

§ Create a new Blank Workbook


§ Row Fields are fields from the source data that are assigned to a row layout in a PivotTable.

§ Column Fields are fields from the source data that are assigned to a column layout.

§ Report Filters are fields from the source data that act as filters in a PivotTable report

§ Items are the subcategories of a row, column, or report filter.

§ Values Fields are fields from the source data that contain values to be summarized.

§ The Data Area is the range of cells in a PivotTable report that contains summarized data.




§ Components of PowerPivot for SharePoint

Client and server components integrate with Excel and Excel Services in a SharePoint farm.

On a SharePoint farm, Analysis Services runs on an application server where it is paired with related services that handle requests for PowerPivot data.

§ PowerPivot client and server components


PowerPivot Web service runs on a web application server.

It redirects requests from the web application to a PowerPivot System Service instance in the farm.

An Analysis Service server instance in SharePoint integrated mode completes the deployment. It loads, queries, and unloads data.

It also processes data if the workbook is configured for PowerPivot data refresh.



Membership, Microsoft Office, Sharepoint

New App for the Ipad or Iphone for SharePoint access from Harmon.ie

I tested a New App for the Ipad or Iphone for SharePoint access from Harmon.ie that is now available in iTunes under harmon.ie: SharePoint Mobile Client. It is for the iPad, iPhone, iPod Touch and it is under the category: Business.

I was looking for an easy way to access files in SharePoint from the Ipad and found the SharePoint mobile client, Harmon.ie. This app works with SharePoint Windows authentication or forms based authentication so I did not have to do anything to our environment or the Ipad to use the app other than enter the URL and then choose the authentication mode.  I was able to access our intranet through Windows authentication and then our website using a test member record with forms based authentication.

Security is an important factor in a corporate environment and Harmon.ie does not store documents locally on the mobile device which is a big plus for me with the staff using more personal devices on our corporate intranet.

We have seen a rise in members using the Ipad to access our website which requires a new way of thinking on how members consume information that we are provide through SharePoint. External users (association members) can download the free lite version  to view files uploaded for them in our committee collaboration Form-based Authentication site.  This product will make it easy for any of our members that currently have iPhones and iPads to access reports and files.

I was able to access my SharePoint sites from my IPad quickly without looking at instructions or documentation.  The interface is clean and easy to understand.

The bar across the bottom allows access to the sites, files, updates, previous locations (History) and people.

harmonie picksharmonie

You can check-in, check-out and update documents. It also prompts user save changes to SharePoint.  You can also mark a document and library location as favorites using “breadcrumbs” and view a list of recent visited locations from the bar at the bottom.

I was able to open document types including Excel, Word, PowerPoint and Adobe documents using quick view or quick look.  I was also surprised that I was able to open documents using the SkyDrive app on my IPad  and download using SkyDrive or drop box.

I uploaded pictures and documents from my iPad to SharePoint just by clicking on forward arrow in drop box and the harmonie option came up and allowed me to save files. I saved  e-mail attachments to SharePoint from an open email without any issues.

I am going to be setting up my sites for my members so I am looking forward to the ability to view colleagues’ profiles and contact them via e-mail or phone. This will be a nice addition to our member directory by giving members and staff the ability to find people in SharePoint by entering department, skills, expertise and other keywords.

The other nice feature is that you can see profiles of the colleague who last modified a document and get updates when documents are modified so you can easily contact the person who modified the document with one click.

The Harmon.ie app allows you to scroll through all the SharePoint content on the iPad  and quickly access any documents that you have permission in the document library. The interface for the documents allows you to quickly see all the information about a file including the modification date and user,  version, size and file type. Once you click on the file you can use the send button to email a link or the document or you can just copy the link to use in another document.  You can open the document to view with the Harmon.ie quick viewers or use one of your preinstalled iPad apps that works with the different file types. The files can also be checked out if you want to prevent other users from changing the file while you are working on it.

I have been using the Harmon.ie add in for Outlook which has allowed me to add meta data quickly to multiple files from outlook so I am looking forward to using this app on the iPad for the times that I am out of the office and need to quickly get to a file on the intranet or workspace.

I would definitely recommend downloading this app and trying it out.

Dynamics GP, Sharepoint

Microsoft Dynamics GP Business Analyzer–New reporting tool

The Microsoft Convergence conference has come to an end so it is time to go back to work and implement the new products that were demonstrated  in the sessions. The new Microsoft Dynamics GP Business Analyzer is the first product that I am going to install from the GP 2010 installation disk.  This will allow my end user to run their reports from the desktop without having to log into GP.  This will save time for the users who just want to run a quick report.

It requires SQL 2008, Dynamics GP 2010 R2 and Reporting services 2008 or later. You can pull from a GP database that is on SQL 2005 but the reporting services must be running on SQL 2008 or later. Lync turns on extra features but is not required for the installation.  If you want the map functionality then you need SQL 2008 R2.

To install Business Analyzer you get the installation Disk for GP2010R2 and look under additional products.  Select GP Business Analyzer.  You need to enter where your reporting services server is so if you don’t know you need to go to the Reporting services configuration manager and look under the web service URL – this is your reporting services server location.

After Business Analyzer is installed you go to Start – Programs – Business Analyzer and the configuration window opens. You select the role and it will add default reports.

The top window is the primary report area where your report is displayed. You can use any reports written in SQL reporting services.  you can resize the report areas, use charts, pull from multiple companies, upload photos or images.

The actions are on the left hand side where they are buttons for information, view and drill back.

This looks like it could provide a simple platform to deploy reports.  I am going to try to install on a terminal server and see if staff can log in and run reports remotely.

Should be fun to play with since you can run any reports in it – It can connect to any datasource so it is not just tied to GP.


Windows Phone 7 at the WPC2010 in DC

Today I finally got to see a prototype of the new Windows phone 7 and a demo of some of the new features. 
Demo showed Four hubs. There is a Start screen that you can customize.  There is a  people hub that integrates w Facebook and linkedin.
You can have multiple exchange accounts on windows 7 phone. You can act on multiple emails at the same time – flagging and then filter flags and priority in views.
Shows calendar conflicts and allows you to accept or modify can show multiple calendars.
Attachment PowerPoint opens in landscape in slideshow. You can see slides and modify them.
One note can sync with sky drive automatically. You can sync desktop one note to sky drive.
Documents that you save to windows 7 phone and can be edited. Excel, PowerPoint and word. Can also sync to Sharepoint workspace mobile
Windows phone 7 can open sharepoint docs from a link in an email or through the sharepoint mobile workspace. It Shows a view of metadata for the file with a table of contents so you can see what is in the file without downloading it. You can go in edit the document and see comments others made and create your own comments that save back to the document in sharepoint.


I can see this as a very useful tool for staff with the ability to access and modify documents.


Microsoft SharePoint Resources 2010 and 2007

Here are some great SharePoint resources that were posted on the Microsoft SharePoint page in Facebook
1. Check out the Office 2010 & SharePoint 2010 Launch Keynote and on-demand sessions, and the SharePoint 2010 Product Site on Microsoft.com

2. Download the SharePoint 2010 Overview Evaluation Guide, the SharePoint 2010 Professional Developer Evaluation Guide and the SharePoint 2010 IT Professionals Evaluation Guide to quickly ramp up on platform updates and additions.

3. Check out the learning plans for IT Professionals, Developers and End Users and get started on your SharePoint 2010 learning plan.

4. Download the pre-configured Hyper-V SharePoint 2010 Evaluation and Demo Virtual Machine (~1.8GB) along with the SharePoint 2010 Walkthrough Guide for a chance to get hands on with SharePoint 2010 with zero set up and configuration.

5. Download 180 day trial versions of;  SharePoint Server 2010, FAST Search Server 2010 for SharePoint, Office Professional Plus 2010, Windows Server 2008 R2, SQL Server 2008 R2 to install and evaluate SharePoint 2010 yourself, and while you’re downloading stuff here’s some links to the full versions of; SharePoint Foundation 2010, Search Server Express 2010, SharePoint Designer 2010

6. Watch the Getting Started with SharePoint 2010 for IT Professionals video series and the Getting Started with SharePoint 2010 for Professional Developers video series to get bite-sized video overviews of some of new and updated features.

7. Do the self paced IT Professional hosted virtual hands-on-labs and Developer hosted virtual hands-on-labs to get hands-on with SharePoint 2010 through a series of scripted tutorials, and if you want more, then download and install 10 getting started Developer Hands-on-labs.

8. Discover what’s new for end users with Getting started with SharePoint 2010 guides and Take SharePoint Server 2010 training at your desk.

9. Visit the TechNet and MSDN SharePoint 2010 sites for a wealth of technical reference documentation and prescriptive guidance through our categorized resource centers, such as the Installation and Deployment Resource Center to learn how to plan, install and configure SharePoint 2010 and the Upgrade and Migration Resource Center for the latest guidance and best practices for upgrading to SharePoint 2010.

10. Got questions?  Search for an answer, or ask the community in the official Microsoft SharePoint 2010 Forums.

 Already a SharePoint 2007 expert?

Check out the free advanced training content for IT Professionals and Developers looking to upgrade their skills from SharePoint Server 2007 to SharePoint 2010.

We’ve got 36 modules of self paced IT Professional Advanced Training and Developer Advanced Training that includes audio and video recordings.  We’ll also be adding 24 hosted virtual hands-on-labs in the very near future to accompany the training content – All for FREE and on demand.

Want to get certified on SharePoint 2010?

Take a look at the Microsoft Learning SharePoint 2010 Training Portal for information and expected availability dates for SharePoint 2010 Certification, Classroom training, E-Learning, MS Press books and Learning Snacks.


Database Mirroring with Sharepoint – links

SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007
– Part 1 (Introduction, Overview, and basics

SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007 –
Part 2 (Configuration)
Planning Redundancy and Scaling the SharePoint Environment