Sharepoint Lists & Reporting Services

 

I needed a way to generate a report from list items in SharePoint. In Reporting Services, there is no way to select a SP List as a data source --- unless you create an extension.
Well, I found some great code to start with here à http://www.teuntostring.net/blog/2005/09/reporting-over-sharepoint-lists-with.html
I was able to modify this code and get it to work.
Some modifications from what the web site tells you:
The configuration files you will need to edit are as follows:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\RSReportDesigner.config
The assembly seems to want to be in 4 locations (2 for design and 2 for runtime); I understand the 2 for design as it needs one for the IDE setup of the data source and one for the Preview (probably), but anyway once the DLL lived in these locations, life was great:
C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies\
I can see this coming in handy across our MOSS 2007 projects….

RDA Corp - Business Intelligence and SQL Server

88 Million Copies, What can you say?

– There are 10,000 customers in the Customer Technical Preview (CTP) program for PerformancePoint Server, its new business intelligence offering.

88 Million Copies, What can you say?

If the purchase rate based on these customers is 5%, Microsoft is looking at $15-$30 million dollars worth of license sales for PerformancePoint.  Something tells me it will be much higher... now that Proclarity is in the mix for "free"

Microsoft won a patent battle in the spring against Timeline.  This may affect them in the future though, if Timeline decides to appeal.  Plus they're still going after Proclarity, now owned by Microsoft.

More info:

Federal Court Grants Partial Summary Judgment in Favor of Microsoft in Timeline Suit

06/4/2007

Timeline Inc. announced that the United States District Court for the Western District of Washington granted a partial summary judgment in Timeline Inc. vs. ProClarity Corporation. The import of the court's decision is that Timeline's Patent License Agreement (Patent Agreement) with Microsoft Corporation cannot be terminated, and that Timeline's August 2006 termination of the license based on Microsoft's breach of it was therefore ineffective. The court held that Timeline's remedy for any alleged breaches would be to seek damages. Consequently, the court dismissed claims of patent infringement against Microsoft. The federal court also then declined to exercise jurisdiction over state law claims between Timeline and Microsoft, including Timeline's claims for breach of contract, and dismissed them without prejudice to refilling of the claims in state court. This allows Timeline to pursue its claims of material breaches of the Patent Agreement in Washington State courts and to seek damages for breach rather than damages for patent infringement. Timeline had also claimed damages against Microsoft for breach of a separate agreement, under which Timeline wrote the Microsoft Small Business Financial Manager (SBFM Agreement). This cause of action was also dismissed without prejudice to refilling in state court by the federal court's ruling. The ruling does not affect Timeline's claims in the same suit for patent infringement against ProClarity Corporation, which is now a subsidiary of Microsoft Corporation. Those claims are set for trial in October 2007.

Artful Blog

 Useful tip...

One simple way to connect to Terminal Server/Services is to run following from the command line:

mstsc -v:MyServer /F -console

Alteratively, you can install Windows Server 2003 Administration Pack and use Remote Desktops (note s at the end - this is different from Remote Desktop (no s) from Windows XP).

Artful Blog

The Microsoft PerformancePoint Team Blog

 

The PerformancePoint Planning & Architecture Guide is now complete and live!

The PerformancePoint Planning & Architecture Guide is now complete and live!   Updates have also been make to the Deployment Guide and are now reflected out on TechNet.  The next refresh of the documentation will be in November.  http://technet.microsoft.com/en-us/library/bb794633.aspx

To recap, the roadmap to the PerformancePoint documentation is as follows:

• On the base install:  All context-sensitive help and base product documentation including conceptual information and procedures. Also contains links to Office Online, TechNet, and MSDN.

• On Office Online:  Links to README and other useful information
  • http://office.microsoft.com/en-us/performancepoint/FX102380591033.aspx
• On TechNet: For the IT pro - Deployment Guide, Operations Guide, Whitepapers, and Planning & Architecture Guide
  • http://technet.microsoft.com/office/performancepoint/default.aspx  or http://technet.microsoft.com/en-us/library/bb794633.aspx
• On MSDN: For the Developer - Planning Server, Business rules development guide and Monitoring SDK
  • http://msdn2.microsoft.com/en-us/office/bb660518.aspx

The Microsoft PerformancePoint Team Blog

Staging data into PerformancePoint Planning

 The documentation is intentionally vague on this subject.  We have implemented staging from our relational data sources (Accounting, Timesheet, Project Tracking) using SQL Server Integration Services. (SSIS)

One simple way for loading fact data is to create an SSIS Package with a Data Flow Task.  The DFT contains an OLEDB Source pointed to your data store & OLEDB Destination pointed to the fact label table within PerformancePoint's staging database for your application.  The procedure bsp_DI_CreateLabelTableForMeasureGroup is executed using a SQL Script Task after the DFT.

Here is a posting from the newsgroups that may help more. 

Hi Kurinji,
The ETL process to the Staging DB is such a custom process that
detailed steps can't really be given.  What you will have to do is to
create your dimensions, hierarchies, and models in the Business
Modeler, and then synchronize them all to the Staging Area.  Pay
special mind to the built-in dimensions, because they may have
properties you have to define, such as Account and the AccountType.
At any rate, what you'll need to do is load the following columns for
each dimension:
MemberId = NULL (This will be changed when it gets loaded to App)
Label = Your choice (This must be a unique identifier.  Be careful,
however, as the Label is used as an end-user identifier as well.
Make sure that it's both user-friendly and unique.)
Name = Your choice (Supposed to be the friendly name, so make it that
way)
Description = Your choice
SequenceNumber = NULL (PPS will take care of this)
SourceMemberId = The key of the source record (Use this to refer back
to your source table.  Usually you map the primary key to this.)
OwnerID = 0 (Tells PPS Admin owns it)
CreateDatetime = getdate()
ChangeDatetime = getdate() (Both of these are fairly
self-explanatory.)
LoadingControlID = NULL (PPS will take care of this)
BizSystemFlag = 200 (Needs to be 200 so PPS knows to add it. 300 is
update, and 400 is delete. 100 is that it's already in the database,
0 is do nothing with it and 900+ is an error code.)
BizSystemErrorDetails = NULL (PPS will flip this to the column names
where an error occurred if on did in fact occur.)
You may have a property you need to update if you've added any
dimension properties or you're using a built-in dimension.  If you're
using a built-in dimension, what you'll need to do is make a
cross-reference to the  corresponding AG_ table for its MemberId.
For example, if you wanted an Account to be an asset, for the
AccountTypeMemberId column in D_Account in Staging, you'd use (SELECT
MemberId FROM AG_AccountType WHERE Label = 'Asset').
Side note: All dimension tables are labeled D_, while measure tables
are labeled MG_.  Hierarchy tables are labeled H_.  Check the
documentation for specific details of these tables.
Also note that you should make heavy use of SSIS and the PPSCmd.exe
utility in your ETL process.  This way, you'll be able to make a
repeatable and scheduled process to do the ETL.  For PPSCmd.exe
documentation, look in the Business Modeler documentation.
As for MeasureGroup tables, you'll need to use the MemberIds of your
Dimension tables, so make sure that you load your Dimension tables
into the App DB BEFORE staging the fact data.
Your process in SSIS should look roughly like:
Sync Dimensions->Sync Models->Stage Dimensions->Load Dimensions->Stage
Hierarchies->Load Dimensions->Stage Fact->Load Fact->Deploy Model
This way, you'll have all the right IDs available when you need them.
Note that loading a dimension also loads all of its hierarchies.
Hopefully this is helpful!
Cheers,
Eric
Kurinji wrote:
> Hi all,
> Am new to PPS planning area. I would like to know the steps to
> load the staging database from the relational database(SQL Server)
> which i already have. In the documentaion, i read its a manual
> process to load the staging DB, but nowhere am able to see that
> manual process steps. Can somebody help me in setting up the staging
> db and loading it with the Relational database.
> Thx

Microsoft BI - Get the new resource kit

 

Explore the products and capabilities of Microsoft BI in the newly updated BI Resource Kit – now featuring the latest information on Microsoft Office PerformancePoint Server 2007. Discover the rich business potential of Microsoft BI through hands-on labs and demos, whitepapers, Web seminars and a comprehensive collection of technical resources when you request your complimentary BI Resource Kit. Learn how your organization can benefit from this complete, integrated BI solution from Microsoft that now includes the comprehensive performance management capabilities of PerformancePoint Server 2007. Information about the 2007 Microsoft Office System and SQL Server 2005 is also included. Quantities are limited, so register to get your BI Resource Kit now!

Microsoft BI

Charts And Graphs: Modern Solutions | Developer's Toolbox | Smashing Magazine

Everything you wanted to know about web-enabled charts and graphs for data visualization. 

Charts are supposed to visualize data in order to give a more profound understanding of the nature of a given problem or recent developments. Whatever type of data presentation you prefer (pie charts, bubble charts, bar graphs, network diagrams etc.), you can create charts in graphic editors manually or use special desktop-software instead. In both cases you have a major problem: once you’d like to update an old chart, or create a new one, you have to run the application and create new images over and over again. That’s not flexible. That’s also not usable — e.g. if you’d like to update your chart live.

Charts And Graphs: Modern Solutions | Developer's Toolbox | Smashing Magazine

Dan on eScience & Technical Computing @ Microsoft - Web Data Add-in for Excel 2007

 

The Excel 2007 Web Data Add-In makes it easy to use a Web page as a data source in Excel. The add-in plugs into Excel 2007 seamlessly, its entry point located on the Data Tab under the From Web option. The system extracts data by learning from a user’s selection of data they wish to capture into Excel. The more selections, the more the system is trained. An example scenario: You wish to import and track data from MSN's weather page. Visit the site using the tool, enter Data Capture mode, and select a row or two of data from the table. Then click Select Similar, and the system will find similar data based on your previous selections. You then can click Import and leverage Excel's rich data-editing capabilities, including the Refresh command, which will revisit the Web page and extract potentially new, updated data.

Dan on eScience & Technical Computing @ Microsoft

Sharepoint Integration & Navigation Links

I just noticed that the new protocol for accessing PerformancePoint doesn't work in a Sharepoint Navigation URL (fails validation)

The PerformancePoint Server protocol handler enables PerformancePoint Server users to open planning assignments through hyperlinks. This protocol handler is automatically installed on every client computer where the PerformancePoint Add-in for Excel is installed.
The PerformancePoint Server protocol handler is automatically registered when PerformancePoint Add-in for Excel is installed and appropriate registry keys are created.

PerformancePoint:http://server:46788&application=myapp&assignment=4119

One question I have is why didn't they just link to an Excel Worksheet with some parameters, or a web page that exports a worksheet that fires the PerformancePoint add-in, instead of adding the complexities of a protocol handler?

Here's more info on the protocol handler:

http://technet.microsoft.com/en-us/library/bb660547.aspx

Export XML data - Excel - Microsoft Office Online

Some solutions may require exporting XML feeds from Excel spreadsheets to feed SQL databases.  One alternative is to use linked servers, however this may not be available in some environments.

Here is some info on mapping XML to Excel 2007.

After you have imported XML data, mapped the data to cells in your worksheet, and made changes to the data, you often want to export or save the data to an XML file. There are two ways to export XML data from a Microsoft Office Excel 2007 worksheet:

Export XML data - Excel - Microsoft Office Online

Pranav ... Blogging : Command-line, the old beast ..

Command line switches for Office apps. 

Here is a small list about the command line switches, although it might not be a complete list, but i'll update my blog after collecting the remaining pieces.

Office 2007 : http://office.microsoft.com/en-us/word/HP101640101033.aspx

Office 2000 -2003 : http://support.microsoft.com/kb/210565

/a & /safe switch: http://support.microsoft.com/default.aspx?scid=KB;EN-US;813589

Excel: http://office.microsoft.com/en-us/excel/HA101580301033.aspx

Visio: http://support.microsoft.com/kb/314392

Powerpoint: http://office.microsoft.com/en-us/powerpoint/HA101538891033.aspx

Playlist files .. (this is something that I never knew about, even it's existence!): http://support.microsoft.com/kb/176678

Source: Pranav ... Blogging : Command-line, the old beast ..

Bill Morein's Weblog : Visio Active Directory Topology Diagrammer

 

One of the most common requests I get from IT users of Visio is for a way to draw out an Active Directory topology map. Visio has Active Directory shapes, but until now it didn't have the ability to actually go out and create a diagram from AD. We've put up a free download that does this -- the Microsoft Visio Active Directory Topology Diagrammer is now available. This runs as a separate application and is installed in the Start menu. Here is a screenshot of the UI:

Source: Bill Morein's Weblog : Visio Active Directory Topology Diagrammer

How do I export data with an outbound rule?

 

How do I export data with an outbound rule?

Outbound rule sets make it possible for you publish Planning Server data to specified external data stores called Data Destinations. Modelers can create sets of customized outbound rules to fit a particular business scenario.

This procedure provides guidelines that can help a modeler develop outbound rules and rule sets.

What you can do when you finish

When you create and run an outbound rule set, your Planning Server data is available to an extended business user community. Your data can be used by applications other than the Planning Server tools. For example, at the end of the budget cycle, a customer might want extract new budget numbers and update an enterprise plan. Alternatively, a customer might want to create a data catalog or snapshot of results from Planning Business Modeler.

How do I export data with an outbound rule?

PerformancePoint Server Developers Site

 

PerformancePoint Monitoring SDK

Use the PerformancePoint Monitoring SDK to help customize and integrate your own dashboard object extensions such as custom reports and data sources, wizards, and user interface extensions. The 2007 release of the SDK includes overview topics, code examples, and updated references for programming with PerformancePoint Monitoring Server.

PerformancePoint Planning Business Rules Development Guide
Get information about how to use PerformancePoint Planning to develop business rules that perform complex planning, budgeting, and forecasting, or financial operations such as consolidation. Develop custom rules in the PerformancePoint Expression Language (PEL), SQL, or MDX, or take advantage of the many templates that you can use for business rules.

PerformancePoint Server

The Microsoft PerformancePoint Team Blog : Launching ProClarity Professional from a PAS View within a PerformancePoint Dashboard

 

Here is an example URL with the "uiConfig" option set:

http://MyPasServer/pas/en/src/proclarity.asp?uiConfig=tb;ht;&book={6253ECA7-E784-403F-94F0-438B9722D219}&page={E220649B-2344-409E-835C-1ABF6939A06F}&folder=root&LibID={93E72F1B-806C-49AC-9BDC-256BFFA23D51}

The Microsoft PerformancePoint Team Blog : Launching ProClarity Professional from a PAS View within a PerformancePoint Dashboard

Bill Baer : Deploying ProClarity Viewer 6.3 for SharePoint

 

Deploying ProClarity Viewer 6.3 for SharePoint

I was recently asked to deploy the ProClarity Viewer 6.3 for SharePoint Web Part - after downloading the package I realized that it was not offered in a deployable package, but rather a compressed archive of the .dwp, assembly, and resource files.  The challenge became offering a user experience similar to the previous ProClarity Viewer version so I decided to package the Web Part into a reusable solution.  For those interested or looking to create a deployable Web Part package I've documented the steps below:

Bill Baer : Deploying ProClarity Viewer 6.3 for SharePoint

UNPIVOT

For certain source systems like AccPac, you may need to convert your column-based list of periods or months into a row-based list that is friendlier for an OLAP data model with a time dimension.

The SQL 2000 solution would be to use a case statement or possibly a custom pivot function, or maybe some creative use of the isnull function.

Here is an example query for this in SQL 2005: 

SELECT
[ACCTID]
,[FSCSYR]
,[FSCSDSG]
,[FSCSCURN]
,[CURNTYPE]
,[AUDTDATE]
,[AUDTTIME]
,[AUDTUSER]
,[AUDTORG]
,[SWRVL]
,[CODERVL]
,[SCURNDEC]
,[OPENBAL]
,[PERIOD], Balance
FROM
(
SELECT [ACCTID]
,[FSCSYR]
,[FSCSDSG]
,[FSCSCURN]
,[CURNTYPE]
,[AUDTDATE]
,[AUDTTIME]
,[AUDTUSER]
,[AUDTORG]
,[SWRVL]
,[CODERVL]
,[SCURNDEC]
,[OPENBAL]
,[NETPERD1] [Jan]
,[NETPERD2] [Feb]
,[NETPERD3] [Mar]
,[NETPERD4] [Apr]
,[NETPERD5] [May]
,[NETPERD6] [Jun]
,[NETPERD7] [Jul]
,[NETPERD8] [Aug]
,[NETPERD9] [Sep]
,[NETPERD10] [Oct]
,[NETPERD11] [Nov]
,[NETPERD12] [Dec]
,[NETPERD13]
,[NETPERD14]
,[NETPERD15]
,[ACTIVITYSW]
FROM [GLAFS]
WHERE activitysw = 1 ) Balance

UNPIVOT (
Balance
FOR [Period]
IN ( [Jan], [Feb], [Mar], [Apr],
[May], [Jun], [Jul], [Aug],
[Sep], [Oct], [Nov], [Dec])
) AS unpvt
WHERE BALANCE <> 0

Upgrading from CTP4 to RTM PerformancePoint 2007

A good posting from the newsgroups on upgrading PerformancePoint to RTM.  The question was "Do you have to uninstall CTP4 to install RTM?"

  Yes, that is the upgrade path for the "Planning Server".  Note that you do *NOT* want to drop the Databases after you uninstall, as you will be re-using them.  When you run through the RTM Configuration Manager you would choose a "Distributed" installation and Un-Check the Database Install option, this will allow you to "Attach" to the DB's... if the option is selected PPS will attempt to create the DB's for you, which is not what you want, and, since yours already exist, would fail.

  Installing RTM will "upgrade" the Planning Server's System and Service Databases automatically for you, if you have any Applications, you will need to upgrade those after the fact using the PPSCMD, (which is installed with the Business Modeler), "Upgrade" option, (you can type PPSCMD help Upgrade" on the command line for more info... by default, PPSCMD is installed here: "%ProgramFiles%\Microsoft Office PerformancePoint Server\3.0\BizModeler".

  The entire process should be in the Deployment or Operations guide, (which is up on the TechNet and Office Online PPS sites).  Note that after this has been done, you "may" need to go in and re-grant your SI Account some permissions... it depends on how your present system is setup.  The reason I say you "may" need to is because a "Distributed" install is considered by PPS to be a Production Environment installation, and as such certain permissions are not granted to the SI Account during Configuration of a Distributed Environment for security purposes... much the same way that after installing SQL Server 2005 your Remote Connections are not enabled by default. :-)  The Documentation should discuss this further. :-)

loose wire blog: A Directory of Visualizing Tools

My favourite visualization is the Treemap, which is useful in many applications.  (including freeing up space on your harddrive.) 

A treemap “is a bunch of squares, arranged to form a mosaic. The size and color of each block mean something”. It’s probably easier to show it than to explain it:

(from RoomforMilk, see below)

Source: loose wire blog: A Directory of Visualizing Tools

Saveen Reddy's blog - Automating Visio 2007

 I hope this is a misprint otherwise I'm using an older version of Visio.

Saveen has some good links on colour and automation of Visio.

Automating Visio 2007 with AutoVisio - now on CodePlex

As I've been exploring automating Visio 2008 through C#, I've been placing all that code in a library called AutoVisio. It's not well-commented and is built on the beta 2 version of Visual Studio 2008, but I thought now would be a good time to let others use it.

Link to project site on codeplex: http://www.codeplex.com/autovisio

Source: Saveen Reddy's blog

Visio Guy » Blog Archive » Visio 2007 Developer Resources #1

More info on Visio from Visio Guy's blog.   The release is out so this is a bit stale now...

Well the buzz about Visio 2007 is increasing daily, and all the hard-core Visio fans are already working on solutions for the next version of Visio. If you’re itching to jump on the bandwagon, I’ve collected a set of links to point you towards as many Visio 2007 developer-related links as possible!

Enjoy!

The Beta

Download Visio 2007 Beta 2
You won’t get very far in your Visio 2007 development without an installation of the latest beta!

Books

New Book: Visualizing Information with Microsoft® Visio 2007
David J. Parker over at bVisual has a new book coming out soon that will help you to harness the power of the data-integration and visualization features coming in the next version of Visio.

Articles

What’s New for Developers in Visio 2007 (Part 1 of 2)
What’s New for Developers in Visio 2007 (Part 2 of 2)
A great breakdown of the new ShapeSheet and Automation features for those of us who roll our own Visio, as well as info on how to use the new data features in Visio 2007.

Designing Shapes for Use with Themes in Visio 2007
Expanding on the color schemes feature available in earlier versions of Visio, Theming in Visio 2007 offers a sophisticated way to customize the colors and styles of your Visio diagrams. The shapes that come with Visio 2007 rely heavily on Theming, and the results are quite impressive. This article describes how you can design your own shapes to function with Theming and introduces several new ShapeSheet functions that help you get the job done.

Integrating Visio 2007 and Access 2007
An article from Visimation for MSDN which describes how to generate a Visio diagram from data stored in an Access database. Lots of code samples in C#.

Integrating Visio 2007 and Excel 2007
Another article from Visimation for MSDN which describes how to link data in Visio to Excel using the Visio Data Selector and generate a BOM (Bill of Materials) Code samples in C#.

Building Custom Data Graphics for Visio 2007
A nice 8-or-so-page article on developing your own data-widgets and getting them to work with Visio 2007’s cool data-integration features!

Web Logs & Portals

Visio 2007 on MSDN
A good place to start. At the top, you’ve got on-line SDK Documentation and Technical articles. Once you start clicking, you’ll probably never escape!

Visio Developer Portal on MSDN
Part of Microsoft Office Developer Center this site contains articles, whitepapers, code samples, downloads and links to other developer resources, including a list of web logs from Visio’s own staff!

Visio Insights Weblog
This web log is from the folks on the Visio team and contains some very deep posts on developing for Visio. Although it’s not just Visio-2007-specific, it’s nonetheless choc-full of information that’s vital to improving the quality of your Visio solutions.

Bill Morein’s Weblog
An MS Visio team member, Bill’s blog is all about Visio and Data Visualization, and many of his articles pertain to Visio 2007.

Eric Rockey’s Weblog
Another MS Visio team member, the title of Eric’s blog: “What’s new in Visio 12″ should be a grand give-away.

Downloads

Visio 2007 SDK (Software Development Kit) Beta 2
The latest version of the Visio SDK offers a great help file, a Code Librarian database full of Visio-related snippets, several VB.NET, C#, VB6 and C++ automation examples, the Visio 2003 Viewer, and .NET templates that help you to quickly create applications and COM Add-ins for Visio.

Office 2007 Developer Map Poster
Cool Office 2007 Developer’s poster in PDF format (thanks to Bill Morein’s weblog)

Related Posts:

• Data Graphics Resources
• Integrating Visio 2007 with Microsoft Technologies
• Book Review: Visualizing Information with Microsoft Office Visio 2007
• Visio 2007 Demo Videos
• Visio & AutoCAD

Source: Visio Guy » Blog Archive » Visio 2007 Developer Resources #1

Visualizing Information with Microsoft Visio 2007

One of the components to your company's PerformancePoint solution could be data-driven Visio 2007 diagrams.

This book should help with that, or at least get you certified in Visio 2007 Application Development (Exam 70-545)

http://www.visualizinginformation.com/

Link to Visualizing Information - Home