SQL Server Forums
Profile | Register | 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
 New Topic  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
52309 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
52309 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
52309 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
52309 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
52309 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
52309 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
52309 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  
 New 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