I have a new Question

[coledog]

We did this report or a set of reports. that have current day same, day last year, wtd, wtd last year, mtd, mtd last year, ytd, ytd last year and last 28 days. All dates are driven off of a date prompt. Plus the were 8 measures. and we had to do the time for every measure. This was alot of work and alot of queries , joins, and unions. but it can be done and it works. with one problem leap year and I think it a problem for everyone.

Now I see or I know how to do the measure as a dimension in framework manager ( and this would save us alot of work ).

But the current day same, day last year, wtd, wtd last year, mtd, mtd last year, ytd, ytd last year and last 28 days is the problem. Since the date prompt is in the report and not in framework manager.

Can I use a date prompt from a report and pass it thru to framework manger and use that to build the time dimension I need it this report ? I know this is trying to use this tool as transformer.

Well if you have any thought on this let me know or am I barking up the wrong tree

[MDXpressor]

I suppose there is little chance of an upgrade to C8?  Relative Time is best handled via cubes, and can be extracted with a fair amount of ease using MDX.

Otherwise, does your time dimension have data items such as
[Year]
[Month Number]  -> in the case of December this result would simply be 12
[Week Number] -> 0 through 52


Moving through time on these items as opposed to counting days should be able to alleviate some of the problems with changing number of days in a month.

[coledog]

Thank you for your reply!

This is what we are doing.

Create a report with one date prompt from that one date prompt we have to create date selected, same day last year, wtd, same wtd last year, month to day, mtd last year, year to day, year to date last year, and last 28 days. ( we did create this report by creating alot of queries.  and I know the layout of a time dimension table. )  but when I join the table to transaction file I get just one day of data coming back now our database is sql2000  and it something like this

one transaction file joined to chart of account  m to 1

property file joined to transactions 1 to m

department file joined to chart of account 1 to 1

Perhist joined to transaction file  1 to M

now we have a time dimension table  what is the join I would say it should join to transaction table on dates in each table  1 to M ( time dimension to transactions

now the report prompts for selection date and this is where I get lost?

Like I said I did get the report done but it was alot of hard work and there must be a better way.

Now I can do alot of this work in sql but my boss wants to do it in the tool. We have a support dept and they have trouble dealing with the sql.

What is the best way of doing something like this and how do I get this time dimension table thing worked out.

and the C8 is no problem.

Ps transaction down to day level and the time dimension table is also down to day level also

any help I can get

[coledog]

One more thing I think this is more a frame work managet thing too

[MDXpressor]

What you are attempting to do is a matter of very simple MDX calculations if you could deliver this data in a Powercube to C8.Ã,  Trying to get it out of a Relational Mart is going to be a very trying experience, especially the WTD calculations.Ã,  So, my first recomendation is to upgrade to C8.Ã,  Short of that:

The join you describe between the Time Dimension and the Transaction Table seems correct.
_add_years([Transaction_Date],-1) can give you the same day from a year ago
an if...then...else statement can deliver you the WTD data you are looking for:Ã,  if (_week_of_year(?Date Prompt?)=_week_of_year([Transaction_Date])) then ([Measure]) else (0)
Do the same If...Then...Else statement except substitute the _add_years calculation for the [Transaction_Date].

Again I say, upgrading to C8 and delivering this data in a Powercube will not only make the calculations easier, it will speed the response of the query.

Hope this helps...

[coledog]

I am going to give it a try and will let you know