Skip to Content
avatar image
Former Member

Create a time prompt

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Aug 15, 2017 at 12:31 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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 ��

  • Aug 16, 2017 at 03:34 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.