Create a time prompt

[Former Member]

Hello experts,

I'm quite struggling with a date-time problem at Universe (IDT 4.1 SP7)&BO Webinar (14.1.7).

Let's start with customer request:

He needs a Webi report where he can filter by prompt -date and time in separately

For example, if he filters:

Date between 01/01/2016-30/02/2016

Time between 11:00-15:00

He wants to see all data between these dates and time 11:00-15:00

The problem -

In universe -Data Foundation I'm getting from EVENTS table a date-time filed (SQL server) - for example "04:26:47 24/07/2017"

So, on the Data Foundation level (in the EVENTS table) I created 2 "calculation columns"

Date filed - "toDate(@catalog ('RC_Control')."Control.dbo"."EVENTS"."EVENT_TIME_UTC")

Time filed - "toTime(@catalog ('RC_Control')."Control.dbo"."EVENTS"."EVENT_TIME_UTC")

In the Business Layer I created 2 dimentions (date&time)that brings these calculated columns.

Their DataType -Date

On the date filed I'm getting just the date-as I need.

On the Time dimention, on the "Advanced" tab- "Edit display format"-I costumed the format as customer need - "hh:mm"

When I create a query I'm getting the "he:mm" as I need.

Now on BO Webi I created the 2 prompts and here are my problems:

Problem no. 1 : "Time" prompt - I'm getting date only (!!!!). It doesn't recognize the costume I made on the IDT.

I need it to be filtered by "hh:mm" only

Problem no.2 : when I display chosen date filter (by - userResponse("psEnter Value for date (start):") I'm still getting the time (00:00:00)

I have tried to use function FormatDAte &toDate but it didn't word, I've got an error.

*when I put the time/date filed on a grid- I'm getting the filed as I need (for example date - 27/01/207 time - 18:45)

I've been searching for a solution for a couple of days ..can't find anything.

Hope you'll have an idea that could solve my problems...thanks.

Rob

[jyothirmayee_s]

Hi,

Does the problem exists even after publishing UNX with the changes ?.

Thanks,

Jothi

[CdnConnection]

Rob,

I ended up creating a detailed Time related Derived table to help me resolve my issue and connected it to the EVENT table. See screenshot below. This resolved ALL my time related Prompts and report related issues.

Regards,

Ajay

[Former Member]

Hey Ajay,

Thanks for the help!

Can you make a screenshot of you SQL code (the expression) please?

I need just the HH:MM please.

[Former Member]

Hi Rob

For Problem 1 have you tried defining the time object using SqlServer's Cast function?

Try CAST(@catalog ('RC_Control')."Control.dbo"."EVENTS"."EVENT_TIME_UTC" as Time)

For Problem 2:

Two options:

1. Left(userResponse("psEnter Value for date (start):");10) will give you the date part only for display as a string, or

2. Formatdate(toDate(userResponse("psEnter Value for date (start):");"dd/MM/yyyy HH:mm:ss");"dd/MM/yyyy") converts the string returned by the UserResponse() function into a date and them formats the output to as a string to only display the date part.

[Former Member]

Charles,

Thanks for the quick feedback.

Problem 1 I prefer to solve on Universe\webi level.

Problem 2 - your 2 ways worked!!!

Thank you 🙂