Time Dimensions or Prompts

[coledog]

coledog
Full Member


Forum Citizenship: 0
Online

Posts: 5





  Time Dimensions or Prompts
Ã,« on: 20 Sep 2006 03:55:25 PM Ã,»   

--------------------------------------------------------------------------------
I have this request for a report. The user prompts for a date.  ( Can be Any date ) and Just one prompt.

and the report returns

Current date ( User entered ).... Week to date .... Month to date ..... and Year to date (all base and the date the user selected from the prompt)

Now I get it to work with 4 prompts for day week month Year.   any Ideas on how to go about this.
can this be done.  if you need more detail let me know. 

Report to moderator    64.242.30.138 



Blue
Global Moderator
Community Leader


Forum Citizenship: 7
[Applaud this member's contribution] [Warn others about this member]
Offline

Posts: 137

The Earth is one country ...





  Re: Time Dimensions or Prompts
Ã,« Reply #1 on: 20 Sep 2006 04:20:37 PM Ã,»   

--------------------------------------------------------------------------------
Sure, in the database.

Create a relative time table.

Use multiple queries and merge them in your report.

Create a view that pre-processes the data for the report.

Create the RS report from a cube data source.

Several options; pick one.

Report to moderator    Logged 



coledog
Full Member


Forum Citizenship: 0
Online

Posts: 5





  Re: Time Dimensions or Prompts
Ã,« Reply #2 on: Today at 08:55:39 AM Ã,»   

--------------------------------------------------------------------------------
I created a time_Dimension table in SQl  ( Alot Like the one in the sample databases ) . now what I am trying to do Is have one prompt in the report. ( a date Prompt ) when they Pick the date in a cross tab report . it returns  this

                                         selected-date    |     WTD   |     mtd    |     YTD    ( based on Selected Date )


brand  | amount
brand  | amount


now for the columns I did Query  example

select date

[Dimension View].[All dates].[TIME_DIMENSION].[Day] = ?day? ( and this returns correctly)

MTD

[Dimension View].[All dates].[TIME_DIMENSION].[Day]>= _first_of_month( ?Day?) and [Dimension View].[All dates].[TIME_DIMENSION].[Day]<= ?Day? ( when I run this I get errors )

and so on and on I cant get past this

Any help is need I am at a stand still  i need an answer soon   !!!!!! Help !!!!!!  I can give a phone number if we need to talk about this ....

PS the time dimension in sql is in mm/dd/yyyy format and in reportnet it comes in as yyyymmdd

again Help