Hello,
I have a matrix report displaying data from a data cube, via analysis
services. It contains column groupings for Months, Quarters and Year as well
as row groups for Team, User and Region. This allows the user to drill down
from a top level "All Teams" total for the whole year, down to a full view of
a region for a particular month.
To achieve this, the "Sum" function is used to aggregate the figures. But,
we wish to also use a running total of Sales by region using Brought Forward
and Carried Forward balances. This needs to be aggregated by column (region
to user to team), but not by row. (Otherwise the Jan, Feb and March totals
are added together for example for Q1, which is incorrect).
Is there a way to specify the axis for the Sum function? Or another way to
create the report that still allows the drill-down/up functionality?
Many Thanks.
Ben Mann.Although I don't understand the business part of your question... Matrix
allows aggregations to be done on rows OR columns... It looks like you
wanted some aggregation on both axes... Matrixes can do this...
Also you can use any of the aggregate functions that RS supports, take a
look at runningvalue... that might be what you are lookin for...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ben Mann" <Ben Mann@.discussions.microsoft.com> wrote in message
news:37B6B96A-FF6F-4D63-A820-B6E588C13233@.microsoft.com...
> Hello,
> I have a matrix report displaying data from a data cube, via analysis
> services. It contains column groupings for Months, Quarters and Year as
well
> as row groups for Team, User and Region. This allows the user to drill
down
> from a top level "All Teams" total for the whole year, down to a full view
of
> a region for a particular month.
> To achieve this, the "Sum" function is used to aggregate the figures. But,
> we wish to also use a running total of Sales by region using Brought
Forward
> and Carried Forward balances. This needs to be aggregated by column
(region
> to user to team), but not by row. (Otherwise the Jan, Feb and March totals
> are added together for example for Q1, which is incorrect).
> Is there a way to specify the axis for the Sum function? Or another way to
> create the report that still allows the drill-down/up functionality?
> Many Thanks.
> Ben Mann.
No comments:
Post a Comment