Web Intelligence – Extracting a date from a string

This article discusses a solution to a problem encountered by one of our clients.

In this particular case, our client has a string field which is populated with some free form text; a comment. When this data is pulled into the data warehouse, the date the comment was entered is appended to the comment.

The client wanted to create a Web Intelligence report that would bring back comments made over the past N number of days.

A number of issues meant this was not a simple request.

First – The comment creation date is not held seperately within the data warehouse and we have no quick means of changing the data warehouse to do so.
Second – The date has been appended to the comment as one long string, so the date format has been lost.
Third – Finally, the user wanted some flexibility to the report to select the last N number of days.

The solution:

The solution to the problem required several steps.

Step 1
The first step was to create an object to return the current date less a number of days specified by the user. We created a formula which prompted the user for a value (the number of days), and we subtracted that value from the current date. As we wanted to keep the user input, we used a prompt:

convert(datetime,convert( varchar,dateadd(dd,-@Prompt(‘Go back N days’,’N’,,MONO,FREE),getdate()),102))

When the user selects this object in the query panel, we get two values. The first is the result of the prompt ‘Go back N days’. This will be used in the report later. The second is a date value which is the current date less the number of days specified by the user. This will also be used in the report.

Step 2
The second step was to isolate the date field from the comment. Luckily the date is held in a constant format (Mmm dd yyyy) and is always at the end of the comment. The date part of the string is always eleven characters long.

Using the ‘Right’ function, we created a variable as follows:


This tells Web Intelligence to read eleven positions back from the end of the string ‘Retail_User_Comment’.

This was stored in a variable called v_Comment_Date. We will use this later.

Step 3
The next step was a bit more tricky. The date field is held in a character string format. We wanted this in a date format. To do this the component parts were broken down into day, month and year.




The items were then concatenated together, seperated by a forward slash using the Concatenation function.

Finally, the whole string was converted into a date field in the format we required dd/MM/yyyy. The resulting formula is as follows:


This variable was named v_Comment_Date2.

Step 4:

We now have the date field isolated from the character string. Now we want to see rows which have been updated in the last N days. If you remember, we prompted the user for a number of days. This is held in the ‘Go back N days’ prompt.

Using the ‘DaysBetween’ function, we can create the following formula:

=If(DaysBetween([v_Comment_Date2];[Go back N days])

If the days between the comment date and the ‘Go back N days’ date is less than the value entered by the user, we return a 1 otherwise we return a 0.

We save this formula as a measure called v_Comment_Filter. It is important that this is saved as a measure otherwise we would not be able to filter on this value.

Step 5:

The final step is to filter the v_Comment_Filter object to show records with a 1 value, i.e. those records with a comment date less than the numberof days entered by the user at the prompt.

When the user now refreshed the report, he or she is presented with a prompt for number of days to go back, and is presented with a report with comments updated N days ago.

I hope you found this useful.


2 Responses to “Web Intelligence – Extracting a date from a string”

  1. goulart Says:

    Passing a value for use within a report
    Using the function UserResponse you can pass a value for use within the report.
    In my company we are implementing a balance scorecard, and most of the measures used comes out of the legacy systems which are mapped into Webi Universes.
    The challenge was to put the goals into the reports so we could calculate the results.
    But, as the goals changes in a monthly or quarterly basis, to hardcode the goals into the reports would be a burden.
    So we came out with a simple solution that could help webi users in many other situations.
    We create a query prompt that returns the values of the desired goals. Responding to the prompt with something like” xxx;90.30;70.50” would give us two values : 90.30 and 70.50. The initial xxx stands as a comparison string that would be greater than any value existent that may exist for the dimension to be tested against.
    To clarify, I will give the example using the efashion universe.
    Create a query filter that goes like [Store Name] less than Goal were Goal is the name of a prompt. This filter will always evaluate true for the prompt value “xxx;90.30;70.50”, since all stores names begins with “e-fashion” which are lesser than “xxx”
    When you run the query, answer the prompt with the desired values. Remembering to start the prompt with xxx, and separating the values with some special character (In my case ;).
    Inside the report, create two sets of variables : One to receive the full value entered for the prompt, and the second the variables that would receive the needed values.
    FullText = UserResponse(“Goal”) and value1= SubStr(FullText;5;5), value2=(FullText;11;5), use FormatNumber to convert the result of value1 and value2.

    Note that this can be used to enter dates values too. Just use FormatDate instead.
    Hope it’s not very confusing, but I thing that I gave you the big picture. If there’s any doubts, please feel free to contact me.

    Rogerio Goulart

    • weldblog Says:


      Thank you for thaking the time to post this comment.

      Hopefully your solution/method will help others too.

      Thanks for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: