Dynamic Excel pivot report powered by Analysis Services 2005?

[StevenK]

I'm looking at writing a MS Excel pivot table report driven by a SQL Server 2005 database - using SQL Server 2005 Analysis Services.

I have a CUSTOMER table and an INVOICE table which holds details of the invoice transactions for the customers held in CUSTOMER.

Ideally what I want to be able to do is the resultant report to have a format like:

Customer Name / 0-10 Days / 11-20 Days / 21-30 Days / Total
CUSTOMER 1 / 100 / 200 / 300 / 600
CUSTOMER 2 / 200 / 400 / 600 / 1200
CUSTOMER 3 / 300 / 600 / 900 / 1800
... with additional columns for 31-40, 41-50, 51-60, 61-70, 71-80, 81-90 Days - this being just a simple representation above.

With this being a pivot table report I ideally want to be able to drill down on these totals to view the invoice transactions that make up these totals.

Given the fact that the 'aging' column (i.e. 0-10 Days / 11-20 Days) is driven by the date of the INVOICE record reliant on the current system date - how can this be achieved?

Is it possible to have dynamic data-cubes to be able to work something like this?
Either driven by the current system date (i.e. GetDate()) or by a user-entered parameter (which again would be the current date)?

Or will I need to run this report in another way?
I.e. via a stored procedure - which loads an additional table - which the pivot table (and/or datacube) is reading from?

Any advice would be appreciated.
Thanks in advance,
Steve

 

[SkipVought]

Steve,

In your query, you must have a column that calculates the days, like [Some date]-SYSDATE.

Then, return that field to the column area. You might get a message that says you have more data than can fit on the sheet -- thats OK.

After the PT is on the sheet, right-click the DAY field - Group and Outline - GROUP and select the DAYS that you want to group by; 10 in your case it seems.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[xlbo]

...or use SQL to group the days into buckets

GetDate is available to SQL so you could do your bucket calcs there - judicious use of INT / MOD would probably get you most of the way there...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question