Complex Syntax for Month and year calculation.

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)

Complex Syntax for Month and year calculation.

Author

Topic

Praz
Starting Member

9 Posts

Posted - 2012-12-13 : 14:17:29

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-13 : 14:52:13

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?

Praz
Starting Member

9 Posts

Posted - 2012-12-13 : 15:27:07

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?

Praz
Starting Member

9 Posts

Posted - 2012-12-13 : 15:28:36

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 03:24:29

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources