Last 10 days sales! - SQL Server Forums

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 2008 Forums

Analysis Server and Reporting Services (2008)

Last 10 days sales!

Author

Topic

sqlrich
Starting Member

6 Posts

Posted - 2010-02-24 : 03:23:26

Hi all,
i got requirement to create a report which show last 10 days sales of the month!

and report paramaters are year and month!

my datasource is ssas 2008 data cube!

how do i do this?

thanks in advance!

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-24 : 03:27:48

Here it is

select * from table_name where month(date_column)=@month_value and year(date_column)=@year_value
and day(date_column)>=20

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 04:14:07

Better index usage

select * from table_name
where
date_column>=dateadd(year,@year_value-1900,dateadd(month,@month_value-1,-20)) and
date_column<dateadd(year,@year_value-1900,dateadd(month,@month_value-1,0))

Madhivanan

Failing to plan is Planning to fail

sqlrich
Starting Member

6 Posts

Posted - 2010-02-24 : 04:41:56

thank you replying senthil & madhi...unfortunately this not what i want...im creating report using cube as a data source, so i need mdx method for this!

sorry! for not mensioning this above. sorry!

maeenul
Starting Member

20 Posts

Posted - 2010-03-09 : 00:22:58

Hello, I am not sure how your Time Dimension and Hierarchy is built, but you can use a mdx similar to this:

WITH MEMBER [MEASURES].[TEMP]
AS
1
select {
tail(
filter(
descendants([Period].[Hierarchy].[Year].&[2009], [Period].[Hierarchy].[Day]),
ancestor([Period].[Hierarchy].currentmember,[Period].[Hierarchy].[Month]) is [Period].[Hierarchy].[Month].&[11]&[2009]
),
7)} on rows,
{[MEASURES].[TEMP] } on columns
from [Test]

You can see that in this query you have the year and month member used in one place. So you can just replace it with your desired input.

You can see the following link to get detail idea about this query.

http://www.programmingsolution.net/sqlserver2005/sqlserver-solutions/display-article.php?article-id=30

-----------------------
maeenul
http://www.programmingsolution.net/sqlserver2005/sqlserver-solutions/
http://sqlservertipsntricks.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources