Dimensions within Excel Pivot Table

Hi

I have a cube that has dimensions such as year, company, customer, statustext, employee etc.

In the Browse in Analysis Manager - all the dimensions look fine.

When I access the same cube from Excel after dragging and dropping the dimensions during analysis the dimensions in the Page section are not what is show when dragged to the row section.

For example - i have a display Customer as rows, years as columns. I drag the statustext next to customer and shows customer. The filter in the page section for statustext is correct. I have tried moving the dimension back to the Field List and re-adding, refreshing from the cube makes no difference. The only solution I have been able to come up with is rebuild the Pivot table - not what I want users to have to do.

Unfortunately, at teh moment we are limited to Excel for presentation.

Anyone ever seen this or have any suggestions?

Just remembered, I have experienced "Catastrophic Failure" - in Excel not sure if this is related and is server generated or local to Excel.

Steve

What version of SQL Server and what version of Excel are you using?

Also I did not quite understand the problem you are experiencing. Could you be more specific?

|||

Hi

SQL Server 2000 - Analysis Server 2000 SP3

I have a Pivot table from a cube, this pivot table has multiple dimensions.

For example - Account Manager, Status, Customer, Employee

If I display Sales by Customer (row) by Year (col) - that works.

If I then drag Status to the row - either to replace or in addition to customer - the display is actually Account Manager. The dimension Status is no longer in the Page Header but Account Manager is.

However, if I filter in the Page Header for a specific Status this shows correctly.

The only way I can fix it is to go into the Wizard - choose options - remove all dimensions, re-add them.

Thanks

|||

One possibility is that if your Excel file got corrupted (some mixup with dimensions), this would explain current behavior.

Can you re-produce the problem from scratch (blank spreadsheet)? If you can, how long does it take?

|||

It does happen from scratch but I cannot reproduce it intentionally. I did wonder if it may be because the structure of teh cube within Analysis Server changed. I hope this is not the case.

|||

I have seen a thread where somebody said he regularly experiences problems with pivot becoming corrupt when a datasource changes.

He said it's really common in Excel XP, but does not happen as often with Excel 2003. Since I don't know the Excel version you use, one possibility is to go with Excel 2003.

Also another solution is to always build pivots from scratch. You can even automate this by writing some VBA logic. Hope this helps!

Difficult query help

I have a table that stores billing rates for our employees by client.
Each employee can have a different billing rate for each client for a
specified period. Here are the columns in the table.

eid - Employee ID#
cid - Client ID#
startdt - start date of billing rate
enddt - end date of billing rate
brate - billing rate

I need to create a script that will verify that for a given eid, and cid
that either the startdt or enddt for one billing rate, the periods do
not overlap.

For example, I need to be able to detect overlaps such as this:

eid cid startdt enddt brate
001 001 1/1/2003 12/31/2003 $50
001 001 11/01/2003 04/01/2004 $75

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!I assume you've already got a unique constraint on (eid,cid,startdt) and a
check constraint to ensure that (startdt<enddt).

CREATE TABLE BillingRates (eid INTEGER, cid INTEGER, startdt DATETIME, enddt
DATETIME NOT NULL, CHECK (startdt<enddt), brate NUMERIC(10,2) NOT NULL,
PRIMARY KEY (cid,eid,startdt))

INSERT INTO BillingRates VALUES (001, 001, '20030101', '20031231', 50)
INSERT INTO BillingRates VALUES (001, 001, '20031101', '20040401', 75)

This query will find any overlaps:

SELECT B1.*
FROM BillingRates AS B1
JOIN BillingRates AS B2
ON B1.eid = B2.eid
AND B1.cid = B2.cid
AND B1.startdt <= B2.enddt
AND B1.enddt >= B2.startdt
AND B1.startdt <> B2.startdt

--
David Portas
SQL Server MVP
--|||Thanks, that's exactly what I needed.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!