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)
 Complex Syntax for Month and year calculation.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Praz
Starting Member

9 Posts

Posted - 12/13/2012 :  14:17:29  Show Profile  Reply with Quote
hi,

My requirement is the column header should be displayed based on the conditions mentioned below,

When the input parameter is Dec 2012 then it should show Nov 2012 in column A and Nov 2011 in column B.

The challenge i am facing is when the input parameter is Jan 2012 then the column A value should be Dec 2010 and column B value should be Dec 2011!

Any help is appreciated!!

Thanks
praz

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/13/2012 :  14:52:13  Show Profile  Reply with Quote
quote:
When the input parameter is Dec 2012 then it should show Nov 2012 in column A and Nov 2011 in column B.

The challenge i am facing is when the input parameter is Jan 2012 then the column A value should be Dec 2010 and column B value should be Dec 2011!
If you said Dec 2011 and Dec 2010 in A and B, respectively that makes sense. That not being the case, I am not able to discern any pattern from the two examples you posted. Is there a pattern or rule that can be used to determine values for A and B if the month and year are given?
Go to Top of Page

Praz
Starting Member

9 Posts

Posted - 12/13/2012 :  15:27:07  Show Profile  Reply with Quote
Ok.. Let me explain the situation in brief,

I am supposed to run a report and with input parameters as Date and Region,

The logic which is in place is when i select a month say Dec 2012 then the result set displays 2 things one is Monthly value and the other is yearly value,
Ideal Scenario:

Monthly value is the one month prior to the entered value(ie Nov 2012) and yearly value is Jan - Nov 2012. Along with this i should also display previous years monthly and yearly value i.e Nov 2011(Monthly) and Jan-Nov 2011.

But when the case is Jan 2012 the logic changes,The prior month will be Dec 2011(Monthly value) and Jan- Dec 2011(Yearly value). Also the previous years monthly and yearly value needs to be displayed which will be Dec 2010(Month) and Jan-Dec 2010 (Yearly).

This logic is in place in the query part but i need to apply the same logic in SSRS report column header.

I hope i was able to give you some thoughts which you needed.

Let me know if you need any clarifications.

Regards
Praz


quote:
Originally posted by sunitabeck

quote:
When the input parameter is Dec 2012 then it should show Nov 2012 in column A and Nov 2011 in column B.

The challenge i am facing is when the input parameter is Jan 2012 then the column A value should be Dec 2010 and column B value should be Dec 2011!
If you said Dec 2011 and Dec 2010 in A and B, respectively that makes sense. That not being the case, I am not able to discern any pattern from the two examples you posted. Is there a pattern or rule that can be used to determine values for A and B if the month and year are given?

Go to Top of Page

Praz
Starting Member

9 Posts

Posted - 12/13/2012 :  15:28:36  Show Profile  Reply with Quote
Additionally i am using the below mentioned expression now,

=MONTHNAME(MONTH(Dateadd("m",-1,Parameters!SYDATE.Value))) + " " + CStr(YEAR(Dateadd("m",0,Parameters!SYDATE.Value))-1) + "$"

Regards
Praz

quote:
Originally posted by sunitabeck

quote:
When the input parameter is Dec 2012 then it should show Nov 2012 in column A and Nov 2011 in column B.

The challenge i am facing is when the input parameter is Jan 2012 then the column A value should be Dec 2010 and column B value should be Dec 2011!
If you said Dec 2011 and Dec 2010 in A and B, respectively that makes sense. That not being the case, I am not able to discern any pattern from the two examples you posted. Is there a pattern or rule that can be used to determine values for A and B if the month and year are given?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/14/2012 :  03:24:29  Show Profile  Reply with Quote
i think what you need to do is to apply logic to bring them as rows and then in report use matrix container to pivot them as columns

ie the result set will be like

Catgerory Value Othercolumns....
Monthly Nov 2012 ......
PrevYrMonthly Nov 2011 .....
...

then add category as column group in matrix to get the values onto columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.08 seconds. Powered By: Snitz Forums 2000