How to show last three quarter's based on user selected date

[dhanu]

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

[Lynn]

Is your source relational or dimensional?

[dhanu]

Its a relational  model.

[Lynn]

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:

Code Select Expand


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:

Code Select Expand


case
when [YourDataDate]
      between _first_of_month(_add_months(?EndDate?,-5))
      and _last_of_month(_add_months(?EndDate?,-3)
then [YourMeasure]
else 0
end


[dhanu]

Thanks Lynn.
But the measures are not returning the correct value.

[Lynn]

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.

[blom0344]

my superpower-mind-reading capabilities

I knew it..     ;)