Hi
i have a crosstab
i need to show the report by quaterly
eg:
if user choose end date as 31 august 2012 and choose no of peroids as '4'.
has to Display 4 quarterly reporting periods (as of end of each quarter),
of end of August 2012, May 2012, February 2012, November 20121.
Please find the attached Requirement output.
Can anyone please help me on this.
Thanks in advance
Is your source relational or dimensional?
Its a relational model.
If there is a set number of possible periods allowed then you can create separate query items for each possible period and use a case statement in the expression of each to "bucket" your measures accordingly. A conditional style on the empty buckets could be used to suppress those beyond what the user selected.
So period1 query item might say something like this:
case
when [YourDataDate]
between _first_of_month(_add_months(?EndDate?,-2))
and ?EndDate?
then [YourMeasure]
else 0
end
while period2 query item might say this:
case
when [YourDataDate]
between _first_of_month(_add_months(?EndDate?,-5))
and _last_of_month(_add_months(?EndDate?,-3)
then [YourMeasure]
else 0
end
Thanks Lynn.
But the measures are not returning the correct value.
Ok, no problem. I will use my superpower-mind-reading capabilities to figure out what the problem is and get back to you ;)
In the meantime, you might want to post back with a list of some sample data along with the result you are seeing and what result you expect to see so that others who doe not have superpower-mind-reading capabilities can understand what "measures are not returning the correct value" actually means with some degree of specificity.
You might also consider creating a separate test query that simply returns the various date calculations for different selected period numbers to see if that is where the problem lies.
Quote from: Lynn on 22 May 2013 07:26:07 AM
my superpower-mind-reading capabilities
I knew it.. ;)