Please help with Year to Date MDX

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

Please help with Year to Date MDX

Author

Topic

Joozh
Posting Yak Master

145 Posts

Posted - 2004-06-08 : 02:17:22

Hi,

I have 2 dimensions Time and Product....

If I stay at the Year level (say 2004), I get the Year To Date Sales for the selected Product(s)... However when I "zoom into" the Time dimension and select, say, Quarter 2, I get the Sales only for Quarter 2 of the selected Product(s). This is fine but at the same time I also want to see the Year To Date Sales of the selected Products. I hope my post is clear? Let me add a little example to clarify this further and will really appreciate your help:

Suppose I have selected ALL products and have selected Quarter 2 of 2004. I want the following results (Note it's really the YTD column that I am after):

Total Sales YTD Sales
----------- ----------
$ 50,000 $ 125,000

Again, I'll appreciate your help in writing the MDX for this.

Thanks & Regards.

-J

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-08 : 02:35:53

Can you post your table design? (ie the CREATE TABLE statements)

some sample data would also help.

Tim

Joozh
Posting Yak Master

145 Posts

Posted - 2004-06-08 : 04:01:24

Hi,

Sorry... I should have posted the DDLs earlier...

CREATE TABLE [ProductSales] (
[SaleYear] [smallint] ,
[SaleQuarter] [smallint] ,
[SaleMonth] [smallint] ,
[SoldProductName] [nvarchar] (20) ,
[SaleAmount] DECIMAL (12,2)
) ON [PRIMARY]
GO

CREATE TABLE [Time] (
[Year] [smallint] ,
[Quarter] [smallint] ,
[Month] [smallint] ,
) ON [PRIMARY]
GO

Would this be sufficient?

Thanks & Regards.

-J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources