subtract (current month) -(previous month) in matr

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)

subtract (current month) -(previous month) in matr

Author

Topic

friend.vasu
Starting Member

5 Posts

Posted - 2013-04-26 : 07:13:24

i have one matrix with JAN to DEC . i want get (Current Month)- (Previous Month) after the month. columns?
can any body please please please help me. i am trying from morning on wards.

sreee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 07:32:05

whats your backend query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

friend.vasu
Starting Member

5 Posts

Posted - 2013-04-26 : 07:33:55

eventhough its SSAS MDX query. now i want to do in SSRS. Please help.

quote:
Originally posted by visakh16

whats your backend query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sreee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 07:41:59

oh...MDX? just makes it that more difficult

SSRS only way you can do is to make use of ReportItems collection and do like

ReportItems!CurrentValueTextbox.value - ReportItems!PreviousValuetestboxid.value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

friend.vasu
Starting Member

5 Posts

Posted - 2013-04-26 : 07:45:55

i can use reportitems. I have to use in columns and it contains JAN to DEC. now i want find difference between april and march only.

quote:
Originally posted by visakh16

oh...MDX? just makes it that more difficult

SSRS only way you can do is to make use of ReportItems collection and do like

ReportItems!CurrentValueTextbox.value - ReportItems!PreviousValuetestboxid.value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sreee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 07:59:12

you can or you cant? reportitems will have all the columns inside the container

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

friend.vasu
Starting Member

5 Posts

Posted - 2013-04-26 : 08:02:13

i cannot use reportitems. Becuase, current month value will change dynamically.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

[/quote]

sreee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 08:26:24

the only other way is to drop the result from MDX onto a table and then do previous month calculation. Then in your report use a sql query to take it from table which is populated by the MDX

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 08:29:14

In case you wondered how you could do that refer the below blog on the steps

http://www.bidn.com/blogs/guavaq/ssas/2721/ssis-2012-inserting-data-into-a-sql-server-table-from-an-mdx-query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 08:30:45

If you've control over source cube, you could also write custom calculations to do the calculation of previous month values using LAG function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

friend.vasu
Starting Member

5 Posts

Posted - 2013-04-26 : 08:44:28

Normally LAG function works for static data. but in My case month has to be change dynamically.

quote:
Originally posted by visakh16

If you've control over source cube, you could also write custom calculations to do the calculation of previous month values using LAG function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sreee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 01:02:57

quote:
Originally posted by friend.vasu

Normally LAG function works for static data. but in My case month has to be change dynamically.
quote:
Originally posted by visakh16

If you've control over source cube, you could also write custom calculations to do the calculation of previous month values using LAG function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sreee

hmm...do you mean you need to go different number of months back eachtime?

if your concren is months change then you could simply define your set as

SUM([Time].[Month].CURRENTMEMBER,[Measures].[YourMeasureField])-SUM([Time].[Month].CURRENTMEMBER.LAG(-1),[Measures].[YourMeasureField])

and create a memebr for above calculation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources