Display PivotTable from SQL data in WSS 3

Posted on 2009-12-21
Last Modified: 2012-05-08
I've installed the Office Web Components (client and server) for WSS 2 on my WSS 3.0 SP1 server (following Microsoft instructions). The install went okay, and I believe the components are working.

I'm now trying to use the PivotTable Web Part to display data from an SQL database (server located on same machine, but I don't think that makes any difference).

I've tried a couple of methods: 1. creating a working .ODC file and storing it in a document library, then liking the Web Part to this. 2. Clicking the 'Connect to external data source' link and creating a new connection using OLEDB for SQL Server.

Both methods return the same error: 'There was an error while retrieving data from the data source."

I'm not even sure if what I want to do is possible in WSS 3  -- I'm really after confirmation whether this should work or whether to stop trying.
Question by:matbeard
    LVL 42

    Assisted Solution

    i'm not aware of how to do this the way you're going about it... with wss 3.0.  i thought pivot table web part required Excel Services, which is in moss 2007, but not wss 3.0

    the method i'm aware of for displaying external data in wss 3.0 is using Data View web part (DVWP)
    LVL 1

    Author Comment

    Well, strictly speaking, the Office Web Componments were for WSS 2, but Microsoft provides a method for extracting them and manually installing on WSS 3.

    I've done this, and all the required Web Parts are displayed, they just don't seam to work -- I can't imagine MS would explain how to install them on WSS3 if they wouldn't work.

    I can use the DVWP to display data from extrenal sources, but it really was the PivitTable functions I was after. Maybe I'll have to upgrade to MOSS for the Excel Services?
    LVL 1

    Accepted Solution

    Actually - I've got it working now. It was my own stupid fault. The .ODC file had carriage returns in the SQL query. These were being lost when used with the Web Part, making the query invalid.

    Once I spotted that, removed them and added appropriate spaces, it started working!

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    The Scenario: Let’s say you have a quote worksheet in Excel that you use to work up sales figures and such for your clients. You utilize SharePoint to manage and keep track of these documents. You would like values from your worksheet to populate Sh…
    We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now