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