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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 subtract (current month) -(previous month) in matr
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

friend.vasu
Starting Member

India
5 Posts

Posted - 04/26/2013 :  07:13:24  Show Profile  Reply with Quote
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

India
52326 Posts

Posted - 04/26/2013 :  07:32:05  Show Profile  Reply with Quote
whats your backend query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

friend.vasu
Starting Member

India
5 Posts

Posted - 04/26/2013 :  07:33:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/26/2013 :  07:41:59  Show Profile  Reply with Quote
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
Go to Top of Page

friend.vasu
Starting Member

India
5 Posts

Posted - 04/26/2013 :  07:45:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/26/2013 :  07:59:12  Show Profile  Reply with Quote
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
Go to Top of Page

friend.vasu
Starting Member

India
5 Posts

Posted - 04/26/2013 :  08:02:13  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/26/2013 :  08:26:24  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/26/2013 :  08:29:14  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/26/2013 :  08:30:45  Show Profile  Reply with Quote
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
Go to Top of Page

friend.vasu
Starting Member

India
5 Posts

Posted - 04/26/2013 :  08:44:28  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 04/29/2013 :  01:02:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000