Future of ADP's - Pivot Tables

[Reese Watt]

Several weeks ago, we had a good discussion about the future of ADP's. For
the most part, it seemed like most of the features of ADP's could be
duplicated with other products.

But does anybody know if there is a good solution for Access Pivot Tables?

I'm sure somebody will mention the new PIVOT command in SQL 2005. But I've
researched that and it has some serious drawbacks. For one, you have to
know in advance what columns you want to pick. But also, it will only show
a two-dimensional grid. It doesn't give the nifty mult-dimensional expand
and collapse functions that you have in Access.

Those Pivot Tables, and specifically the ability to drill-down and collapse
the tables, offer very real value to our current application. As far as I
can tell, there is no way to duplicate that, even using Visual Studio.

Am I right about that?

 

[Sylvain Lafontaine]

Analysis services for SQL-Server are based on OLAP. Search Google for «
SQL-Server OLAP » or « SQL-Server Analysis Service ». The capabilities
provided by the pivot table in Access are only the tip of the iceberg in
analysis services. If you want to be serious about analysis services, the
OLAP services of SQL-Server can provide you with some real stuff.

You are right on the fact that these services can only be poorly integrated
into an ADP project: in the past, the Pivot Table component from the Office
Web Components was able to connect to some OLAP queries on SQL-Server 2000
and it was possible to integrate it in an ADP project but to my (limited)
knowledge, the OWC have been dropped from Office 2007.

Like the Pivot/Transform statement in Access, the Pivot command in
SQL-Server 2005 is only a quick patch and you are usually in a much better
situation to make your own transformation by using a temporary table than to
rely on these commands.

I'm not a specialist about OLAP so I cannot tell you anything more about the
topic of their integration with ADP or with Visual Studio.

 

[Reese Watt]

Sylvain,

Thanks for the reply.

I've read various documentation about the OLAP queries, but have never done
anythign with them. I may need to learn.