Repeat Query, Macro?

[Dextrious]

Hello,

I'm trying to simplify multiple almost identical queries to one, more flexible query....but I'm not seeing it..

DWH setup:
Each dim/fact line has a begin and end date.
> If a change occures the current line gets an end date and a new line is created with current date as begin date.


What I want to report is the situation on a certain date.
More specifically on the end of last month.
So I have a [Date] data item = something like endofmonth(addmonth(getdate(),-1)) (Syntax not correct here, but you get the idea)
And a filter [Date] between begindate and enddate.

This gives me the situation on the end of last month like I want it.
I also want to know this for 2 months ago, and 3 and..
What I currently do is copy the query 6 times and change the -1 to -2, -3, -4 etc and then union the queries.

Is there a way to change this so I have only one query that gets repeated for -1 to -6 for example.
Can I replace the -1 with a variable that goes from -1 to -6?

[CognosPaul]

Do you have a time dimension?

[Dextrious]

In the DWH, yes

[CognosPaul]

With a good time dimension, this boils down to a complex join problem. To what extent can you modify the time dim? Can you add month end flag? (The month start flag is, of course, day of month = 1).


So you have two fields, begin date and end date, and you want to retrieve all lines that occur at the month end. Is it possible for a line to span multiple months?

I'd recommend creating a roleplaying time dim for this purpose. The join should be something like:

[Namespace].[Time Dim].[Date] between [Namespace].[Fact].[StartDate] and [Namespace].[Fact].[EndDate]

And you should add an inline filter to the time dim that limits it to last day of month.  Alternatively, if you have a lastDateOfMonth field, you can use determinants to group the dim correctly.

Once the join is defined correctly, you can then just pull in month from the new time dim, and it will automatically group the data the way you want. The filter in your query becomes a simple [Report Month] between _add_months(?selected month?,-6) and ?selected month?

[Dextrious]

Exactly what I needed. Much appreciated, thx!