Saturday, July 02, 2005

SQL Server Report Pack for SPS


The Microsoft SQL Server Report Pack for Microsoft Office SharePoint Portal Server 2003 is a set of 8 Microsoft SQL Server 2000 Reporting Services reports that work with a sample database of information extracted from a SharePoint Portal Server environment. This database can be populated from your own SharePoint Portal Server environment using the downloadable Data Extraction Program (DEP). The DEP will read the SharePoint Portal Server data via the object model. You also can use the sample reports as templates for designing new reports. This Report Pack includes the following reports:

  • Storage Report
    Shows a listing of the virtual servers and the number of collections, sites, areas, lists, files and size. Also shows a size distribution and storage usage chart, and a top 20 sites based on size.

  • Storage Trend Report
    Shows four charts illustrating the virtual server storage trend, site collection growth trend, area growth trend and list growth trend.

  • Site Trend Report
    Shows hit counts for virtual servers, collections, areas and lists. Also shows the top 20 sites based on hits.

  • Comprehensive Site Collections Report
    Shows the list of site collections, who owns the collection, configurable characteristics about the owner and the date the collection was last accessed.

  • Detailed Site Collection Report
    Shows top 20 pages accessed (based on hit count) for this site collection.
  • Detailed Page Report
    Shows users who have access to the page, when they last accessed it, any referrer URL and number of hits. Also shows two charts illustrating user distribution and referrer distribution.

  • Best Bet Keyword
    Shows top 20, top 10, bottom 10, or bottom 20 keywords used for searching. Also shows which keywords have best bets.

  • Search Terms
    Shows top 20, top 10, bottom 10, or bottom 20 search terms used for searching. Also shows which search terms match a defined keyword.
Download




13 Comments:

  • Anonymous Jay said…

      Mark,

    Have you tried using the DEP? I have and each and every time I have tried to extract my existing information numerous times and each time I get the error message that says that "a string or binary would be truncated" and the statement ends.

    No documentation (well aside from the 9 page ReadMe), no help from MS, nothing in any of the SQL documentation. All very frustrating.

    However, it does work well with the sample database, not like I can use that though.   

  • Blogger Mark said…

      Check this - there is a KB article on the way :

    http://blogs.msdn.com/dinod/archive/2005/08/19/453724.aspx   

  • Anonymous Jay said…

      Thats awesome Mark THANKS!!!   

  • Blogger hawkeyes2005 said…

      Mark,

    I encountered the following warning and errors when using DEP:

    WARNING: Unable to access the virtual server "Default Web Site"...

    ...

    An exception has occurred:
    System.Data.SqlClient.SqlException
    Null Values Exist in tblTempIIS_toFactIIS. Data not loaded
    Error converting data type int to tinyint
    Null Values Exist in tplTempFileStorage_toFactStorage. Data not loaded
    Error converting data type int to tinyint.

    During installation & setup, I have renamed my Default Web Site to ePayment and also change the port# for Default Web Site to 81. Will this cause the above errors?

    Can you help?

    Thanks in advance.   

  • Anonymous Anonymous said…

      We just release a KB article they is related to the issue they might be encountering:

    http://support.microsoft.com/?kbid=906508

    Moreover if they changed the default web site to port 81, they need to make changes to the RPDataExtraction.exe.config on the XML element that points to the Default Portal.   

  • Anonymous toki said…

      Hi,

    I made modification which is described in "http://support.microsoft.com/?kbid=906508". However, an error still occurs when executing "RPDataExtraction.exe".
    I don't change the default web site.

    (RPDataExtraction.log)
    ...
    Data extraction failed at 9/5/2005 5:44:12 PM...
    Details:
    System.Data.SqlClient.SqlException
    Null Values Exist in tblTempIIS_toFactIIS. Data not loaded
    Error converting data type int to tinyint.
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
    Null Values Exist in tblTempFileStorage_toFactStorage. Data not loaded
    Error converting data type int to tinyint.
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
    Warning: Null value is eliminated by an aggregate or other SET operation.
    ....

    Could you give me some advice for my problem?

    Thanks in advance.   

  • Blogger Mark said…

      Check this : http://markharrison.co.uk/blog/2005/09/support-for-sql-server-report-pack-for.htm   

  • Anonymous toki said…

      I will contact the e-mail address.
    Thanks.   

  • Anonymous vulupe said…

      Did anybody actually got a response from that support e-mail address?
    If so, please post the solution so others (who are not so lucky) cen get over this bug.
    Thanks   

  • Anonymous Anonymous said…

      I also have the same problem, when I execute DEP i got the following error which is different than KB Article in Microsoft. Is there any solution to this??


    System.Data.SqlClient.SqlException: Null Values Exist in tblTempWSS_ToFactLoad. Data not loaded
    Error converting data type int to tinyint.
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
    Null Values Exist in tblTempIIS_toFactIIS. Data not loaded
    Error converting data type int to tinyint.
    Null Values Exist in tblTempFileStorage_toFactStorage. Data not loaded
    Error converting data type int to tinyint.
    Null Values Exist in tblTempSiteDiskUsage_toFactSiteUsage. Data not loaded
    Error converting data type int to tinyint.
    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.   

  • Anonymous Anonymous said…

      well following the documentation doesnt seem to work at least with wss/sps sp 1/2? IIS parsing seems to work fine though...



    C:\Program Files\Report Packs\SPS\Data Extraction>RPDataExtraction.exe
    Resolving...Microsoft.SharePoint.Portal.resources, Version=11.0.0.0, Culture=en-
    US, PublicKeyToken=71e9bce111e9429c
    Resolving...Microsoft.SharePoint.Portal.resources, Version=11.0.0.0, Culture=en,
    PublicKeyToken=71e9bce111e9429c
    Parsing IIS Log files...



    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\IIS\HOUSTON\W3SVC1 with Log Parser...finished



    Parsing WSS Log files...



    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\HOUSTON...
    An exception has occured:
    System.UnauthorizedAccessException
    Access to the path "C:\Program Files\Report Packs\SPS\Data Extraction\WSS\HOUSTON" is denied.







    -so i deviate from the documentation and just place the sts logs directly under the WSS root (rather than creating a SERVERNAME folder) - then this happens.....



    C:\Program Files\Report Packs\SPS\Data Extraction>RPDataExtraction.exe
    Resolving...Microsoft.SharePoint.Portal.resources, Version=11.0.0.0, Culture=en-
    US, PublicKeyToken=71e9bce111e9429c
    Resolving...Microsoft.SharePoint.Portal.resources, Version=11.0.0.0, Culture=en,
    PublicKeyToken=71e9bce111e9429c
    Parsing IIS Log files...



    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\IIS\HOUSTON\W3SVC1 with Log Parser...finished



    Parsing WSS Log files...



    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\00.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\01.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\02.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\03.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\04.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\07.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\09.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\10.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\12.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\13.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\14.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\15.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\16.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\19.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\22.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\23.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\24.log...finished
    Parsing C:\Program Files\Report Packs\SPS\Data Extraction\WSS\25.log...
    An exception has occured:
    System.Data.SqlClient.SqlException
    Error converting data type nvarchar to smalldatetime.



    C:\Program Files\Report Packs\SPS\Data Extraction>

    and i thought it was the answer to all my problems...   

  • Anonymous Anonymous said…

      We also had the same problem. The problem was in the database were we imported old log files by running the dep for the first time. I think the log files of IIS and WSS are combined together in the dbSPSReportingstaging database. Because we had only the IIS logging of weeks, and one litle WSS logfile (we just put on the logging in Sharepoint)
    I started the installation which deletes and recreate the databases. After that I ran the import of only newly logfiles again. This worked. I didn't get the error anymore.   

  • Anonymous Anonymous said…

      does this report pack actually work?

    only errors

    the wss files can't be processed...unauthorized acces; if I copy files from the subfolders, as someone said in an earlier post, it gives me the same error:

    "Error converting data type nvarchar to smalldatetime."   

Post a comment