I'm looking at writing a 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
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