Multiple Date Parameters 1

[stuartgmilton]

Hey guys,

I have a report which our user would like to view for the following periods;

'Today', 'This Week', 'This Month' & 'Custom Period'

This report is going to end up being accessed via WEBI.

Can anyone give me any ideas?

Cheers,

Stuart

 

[blom0344]

My first thought is to add a calendertable to your universe and use this to base your prompts on.
Do you want prompts in all cases?

Ties Blom

 

[stuartgmilton]

I guess what I'd like, is an initial choice between day, week, month and custom. If they select custom then they would be further prompted for the start and end date.

No idea if this is possible.

Cheers, Stu

 

[hilfy]

I would create a filter in the universe using a prompt and a case statement. You'll also need to add some data dimensions to your universe to get this to work.

I add class called "Date" with a set of Date dimensions to all of our universes with things like Today, Yesterday, This Month, This Year, First Day of This Month, Last Month, Last Year etc. The "select" for these measures needs to use the date handling routines from the database. We use Oracle, so I use stuff like this:

Yesterday = sysdate
This Month = To_Number(To_Char(sysdate, 'MM'))
Last Month = decode(@Select(Dates\This Month), 1, 12, (@Select(Dates\This Month) - 1))

I know that the syntax in SQL Server is different, but I'm not sure exactly what it is.

You'll also want to create a measure for the prompt. Something like this:

@Prompt('Select Report Type', 'A',
('Today', 'This Week', 'This Month', 'Custom Period'), Mono, Constarined)


For the filter, you'll do something like this (here again, I'm not absolutely certain of the syntax for a Case statement in SQL Server, so I'm using Oracle...):

Case @Select(Class\Report Type Prompt)
when 'Today' then 
  @Select(Class\Date Measure) = @Select(Dates\Today)
when 'This Month' then 
  To_Number(To_char(@Select(Class\Date Measure), 'MM)) = @Select(Dates\This Month) and To_Number(To_Char(@Select(Class\Date Measure), 'yyyy')) = @Select(Dates\This Year)
when 'This Week' then
<...I'll leave this one for you to determine...>
Else
  @Select(Class\Date Measure) between @Prompt('Enter Start Date', 'D', 'Class\Date Measure', Mono, Free) and @Prompt('Enter End Date', 'D', 'Class\Date Measure', Mono, Free)
end

You'll have to tweak all of this for your specific situation, but hopefully it points you in the right direction.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[stuartgmilton]

Thank you so much Hilfy, obvously I've a bit of work to do to change to SQL but thats extremely helpful.

I'm gonna have a go at setting it up this morning.

Thanks,

Stuart

 

[blom0344]

Yesterday = sysdate
This Month = To_Number(To_Char(sysdate, 'MM'))
Last Month = decode(@Select(Dates\This Month), 1, 12, (@Select(Dates\This Month) - 1))

SQL server:

Yesterday = dateadd(dd,0, datediff(dd,1,getdate()))
[removes timepart from systemdate]
This month = month(getdate())
Last Month = CASE WHEN (@Select(Dates\This Month) = 1 THEN 12 ELSE (@Select(Dates\This Month)-1 END


Ties Blom

 

[blom0344]

Additional tip:

You may need to strip the time component from the date-fields. This depends on how your dates are stored in the database. In some cases the time component is registered (requires stripping), but in other cases only the date is stored with time as '00:00:00.000'

Ties Blom

 

[stuartgmilton]

Cheers Guys

 

[stuartgmilton]

OMG! Right, I've finally gotten back to this after a while off and am working through Hilfys post.

I already have a class called Date which contains all my date measures.

I tried to create a measure with the 'Select' text of;

@Prompt('Select Report Type', 'A',
('Today', 'This Week', 'This Month', 'Custom Period'), Mono, Constarined)

But this doesn't work!?

I am also confused about the filter, I though filters were done in the BO Designer not Universe?

Sorry if I'm being thick.

Stuart

 

[hilfy]

The prompt is not going to be a Measure, it's going to be a Filter. Filters are used in the Webi Designer, but complex filters can be set up in the Universe. Take a look at the sample code that I gave you for setting up the filter to get the basic format for how you need to do this.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[stuartgmilton]

Hey Hilfy,

in your post you mention;

You'll also want to create a measure for the prompt. Something like this:

@Prompt('Select Report Type', 'A',
('Today', 'This Week', 'This Month', 'Custom Period'), Mono, Constarined)

I have not created this yet, but it is required for the Filter/Condition.

Cheers,

Stuart