Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 20, 2003

Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis - Page 4

By William Pearson

Create the PivotTable View

Now that we have put a query in place to act as a data source, we are ready to set about the creation of a sample report. In our present case, we will create a straightforward PivotTable view to act as our report, keeping in mind, as we noted before, that any number of more elegant, robust, and enterprise-oriented solutions might be more appropriate in your own production environment.

Remaining within the Queries section of the Database window, let's take the following steps to begin construction of our new PivotTable view.

1.  Single-click the new Warehouse Cube Activity to select it

2.  Click Open on the Database window toolbar.

The query results set appears within the main window, as shown in Illustration 14.

Click for larger image

Illustration 14: The Database Window

3.  Select View --> PivotTable View from the main menu, as shown in Illustration 15.


Illustration 15: Select View --> PivotTable View

After a few seconds, a blank PivotTable appears, with a floating PivotTable Field List appearing in the foreground, as shown in Illustration 16. Note the appearance of the two additional fields that are based upon the StartTime field.


Illustration 16: A Blank PivotTable View Appears

As I suggested in the DatabaseJournal article Create a PivotTable View in Access, the blank PivotTable offers an excellent "conceptual" view of the basic makeup of a PivotTable. With a PivotTable we can create views of our data that strongly resemble, but far out-power, a cross-tab query. The PivotTable allows us to define the data values that we wish to occupy multiple rows, columns, pages (or "layers") and summaries.

We can see above that the center of the table contains numeric data ("measures"), while the rows and columns present (often hierarchical) dimensional data. We will see a simple illustration of how these areas of the conceptual "map" can be populated with our cube usage analysis data in the steps that follow.

Let's define our view to effectively present the data set returned from our new query.

4.  If the PivotTable Field List does not appear, initialize it clicking the Field List button, shown in Illustration 17, on the toolbar.


Illustration 17: The PivotTable Field List Button

5.  In the Field List, click MSOLAP_Cube to highlight it.

6.  Select Filter Area in the selector box, to the right of the Add to button (at the bottom of the Field List), as shown in Illustration 18.


Illustration 18: The PivotTable Field List with our Selection



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date