How to insert th into a date field

Is it possible to enter th/nd/rd/st into the date field on a report?

This was the question recently asked of me by a client.

Unfortunately there isn’t a specific date format you can use to give you this information. However, with the use of a number of functions, you can build up a formula to achieve the desired result.

Lets break this down into small steps:

Step 1:

Let say we start with a date field with the following value: 09/Jul/2013 (dd/Mmm/yyyy).

The day part of this field will determine the text to append the day part of the date. There are four potential apend options depending on the day selected:

Day Number – Text To Append
1 – st
2 – nd
3 – rd
4 – th
5 – th
6 – th
7 – th
8 – th
9 – th
10 – th
11 – th
12 – th
13 – th
14 – th
15 – th
16 – th
17 – th
18 – th
19 – th
20 – th
21 – st
22 – nd
23 – rd
24 -th
25 – th
26 – th
27 – th
28 – th
29 – th
30 – th
31 – st

 As we want to query the day portion of the date, we need to use the Substr() function. This is used to bring back the first two characters of the date from a start position of one. But note: As our input value is a date field, we need to convert the date to a string. We do this using the FormatDate() function.

We end up with the following formula:

=Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2)

which returns 09

Step 2:

Now that we have the day portion, we want to identify which value to append. There are four possible values. Days 1 and 31 will have ‘st’ appended. Day 2 will have ‘nd’ appended. Day 3 will have ‘rd’ appended. And every other value will have ‘st’ appended.

We can create this formula by building on the previous statement and using the If() function. In our example we have to use a nested If() statement to evaluate three conditions. If the test doesn’t match any of the three conditions, it will default to a fourth option:

=If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“1″;”01″;”21″;”31″);”st”;If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“2″;”02″;”22″);”nd”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“3″;”03″;”23″);”rd”;”th”)))

You will notice that as well as checking for any days prefixed with a zero, I am also checking for the same value without a preceeding zero.

Running the above formula for 09/Jul/2013 will return the result: th as it is the 9th today.

Step 3:

We then break out the other components of the date field to extract the month and year parts. We will use these formulas shortly:

=Substr(FormatDate(CurrentDate();”dd/Mmm/yyyy”);4;3)

This returns Jul.

Step 4:

=Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);7;4)

This returns 2013.

We now have all the components we need to generate a date string which incorporates the th/nd/rd/st text.

Step 5:

Here we put it all together using several Concatenation() statements:

=Concatenation(Concatenation(Concatenation(Concatenation(Concatenation(Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2);
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“1″;”01″;”21″;”31″);”st”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“2″;”02″;”22″);”nd”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“3″;”03″;”23″);”rd”;”th”))));
” “);Substr(FormatDate(CurrentDate();”dd/Mmm/yyyy”);4;3));” “);
Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);7;4))

You will notice that I have included spaces in between the individual date components. Without this all the data will be bunched up and won’t read nicely.

We end up with the following result:

09th Jul 2013

Bonus:

The icing on the cake is the removal of the leading zero from the day part of the date. We can acheive this by performing a check on the date field using an If() statement. If the day part of teh field starts with a zero, we start our substr() statement at position 2 and return 1 character. Otherwise we start in position 1 and return 2 characters:

=Concatenation(Concatenation(Concatenation(Concatenation(Concatenation(If(Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;1)=”0″;Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);2;1);Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2));
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“1″;”01″;”21″;”31″);”st”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“2″;”02″;”22″);”nd”;
If (Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);1;2) InList (“3″;”03″;”23″);”rd”;”th”))));
” “);Substr(FormatDate(CurrentDate();”dd/Mmm/yyyy”);4;3));” “);
Substr(FormatDate(CurrentDate();”dd/mm/yyyy”);7;4))

This gives us the following result:

9th July 2013

Bear in mind that adding the th/nd/rd/st text means this field is now a string an not a date. The effect of this is that you won’t be able to use any Date specific functions on this new field. You can overcome this by performing any date calculations prior to appending the text.

So while not the most elegant of solutions, it is perfectly possible to achieve. Does anyone know a more elegant solution?

Practical Business Objects Developer Tips

I’ve been meaning to write up a good practice guide for Business Objects developers for some time now.

I have finally managed to find some time to put together a list of tips I’ve used over the years. I hope you find the following list of tips useful in some small way.

If, after reading this article, you come away with something new, then I’ll be very happy.

I’m going to keep this post going for a while, adding to it as and when I can. But more importantly, I’d love for you to share your developer tips. I know there is a wide community of Business Objects experts out there, so please help develop this list so we can all benefit from our shared experiences.

If you’d like to share a tip or two, please add a comment to the post. I’ll then add it to the main list and give you a name check!

Universe Designer Application

• Use business terminology for all universe objects – always. The universe is the semantic layer between the business and IT.
• Wherever possible, and certainly for measures and calculated objects, add a description to the object.
• Ensure your object is formatted correctly. Pay particular attention to dates and numeric fields.
• Create filtered prompts wherever possible. Anything which can help the user get their data more efficiently helps. Remember to allow the selection of all data.
• Ensure your List of Values (LOV) is sorted. Don’t assume it will be.
• Don’t create a LOV on a measure.
• If you have to create a LOV on a dimension with many data items, consider grouping those items.
• Group your universe objects into a logical order. Create something that the business will understand and be familiar with.
• Create different time periods – users like to compare current year to date to last year to date, quarter to date, month to date, etc.
• Contexts – these can be incredibly useful. But they can become terribly confusing to the end user. If you really have to put in a context, do so, but make sure it has a meaningful name and description. If your user is presented with a context prompt box, it should be absolutely clear to them which context to choose. If it isn’t, you need to revisit your context or approach. The purpose of the tool is to help the end user get their results. Remember that.
• For fields based on free form input text, consider trimming the field. Profiling your data beforehand should help you identify these fields.

Reporter Application

The Query Panel

• Only use objects that you are going to use in your report.
• Put the objects in the order that you would like them sorted on the report if possible.
• Move your measures to the end.
• Use pre-defined (universe) filters if available.
• Avoid hard coding values wherever possible – use prompts instead.
• If you are not familiar with your data set, restrict the number of rows returned by your original query. If you are happy with the result set, you can remove this restriction. The last thing you need is to run the mother of all queries.
• Make prompts meaningful. If you are prompting for a date, include an example of the expected format. If a user can enter a ‘*’ to select all values, say so.
• Get the server to do the work rather than the report. Not all end user PC’s are high spec.
• If you are going to use multiple queries, give each query a meaningful name.

Report Design

• Create a report header tab/page – this should list the report title, parameters used, descriptions, values entered at the prompts, last refreshed date.
• Ensure the report opens on this header page. This should result in a faster opening report.
• Keep the report look and feel the same throughout – table positions, headers, fonts and colours.
• For tables that span across several pages, repeat the header on every new page.
• Dates – who is your target audience? US and UK data formats differ. If you do not know who your audience is, or if it is a worldwide audience, specify the format of the dates in the header page of the report or the column heading.
• Decimal places – how many decimal places do you need to report on? How many decimal places are used in the calculation?
• When working with large numbers with several digits, consider dividing by a thousand or a million. Ensure the report states that you are reporting in that way.
• Include error handling in any calculations performed on the fly. Pay special attention to DIV/0 errors. Use the IsError function. This post may help.
• For all new formulas you create, consider putting them into a variable to allow reuse and easy maintenance. Prefix the variable name with v_ to make them easily identifiable.
• Include a cell at the bottom of your report to show the last refreshed date.
• Build in a very visible partial refreshed alert. All too often that yellow triangle is overlooked by users. This post may help.
• Number your report pages. ‘Page n of n’ works well.
• Give the report a meaningful name. Include a description. Keywords are helpful too for searching.
• Consider a numbering scheme for your reports. E.g. first digit represents dept/function, second digit represents area, etc. E.g. 104 – Sales Report London, 105 Sales Report New York, 204 – Finance Report London. This will allow rapid identification of a report series.
• Use autofit width or wrap text for longer fields. If you only want to display the first n characters of a field in a report, create a formula to do so (e.g. Left(,20))
• Be consistent with your fonts.
• Use a sensible font like Arial. Some of the more fancy fonts do not print too well, especially if a smaller size font is used.
• Consider how your users will use the report. If they tend to print reports, and they do not have a colour printer, avoid colour on your report. Colours on report don’t lend themselves well to output on a black and white printer. If you must use shading, consider greyscale. If you users only view reports on screen, colours can help identify sections of data.
• Use alerters. These are a great tool for bringing data to the users attention. Given them a meaningful name and description.
• Optimise your report for speed – reduce or eliminate unused formulas, alerters and formatting. Keep it simple.
• Consider the use of section breaks to break up large tables. These can be easily searched in the side panel.
• Before publishing or releasing a report to production, do a print preview. Does the report print out as you’d expect? Do you need to save the report in landscape. Also save to Excel. Are any fields appearing incorrectly formatted?
• Big reports – Do your users really need that 100 page report? Really? If all they are after is a data dump, a more efficient way to deliver this is to write a piece of SQL code and save the result as a CSV file. But always ask why they want a data dump in the first place. What they do with the data? BOBJ is a powerful tool. Show them how to do what they want to do. Use it.
• Save your work regularly. You never know when the power will go, or an application will crash. If you have spent the last couple of hours working on a report, it’s not going to hurt to hit that save button. I normally save every significant change as a new version. Once I have a report ready for production, I’ll delete those versions.
• Images such as a corporate logo can be inserted into reports. But keep the images small. Use a file format that compresses the image to a sensible size. Don’t even think about inserted a large 1mb image on every page. This post may help.
• Make use of multiple tabs in the report. However, avoid hard coding date values in these tabs. You will have to maintain these later.
• If you have a particularly complex report, add a tab that provides the detail behind the report. This will aid future developers.
• If you have one set of users that simply love to play with data, and others who want a formatted report, consider adding a Data Dump tab at the end of your report. This should be a simple table with zero formatting.
• Prior to release to UAT/Production, delete any unused variables and formulas.
• If running multiple queries, ensure the relevant dimensions are linked.
• Purge your report before release to production. The data may be obsolete by the time the user retrieves the report and it will be bigger than it needs to be.
• For corporate reports, consider developing a standard report template
• If your company reports in several currencies, ensure the measures on your report are clear as to what currency is being used.
• Avoid falling into the trap of creating several versions of essentially the same report. Remove hard coding and make the report as generic as possible. Use prompts. If a user wants a new field, can it be added to an existing report or do you need to create a duplicate with the new field?
• If you use graphs, keep them simple. Avoid 3D graphs. Read a good book on Data Visualisation to understand how to deliver data in a visual format. This post may help.
• If you have multiple tabs in a section, ensure that they are positioned relative to each other. Same applies to floating cells. Ensure they are relative to something so that they don’t overlap with other objects on your report.
• Percentages should be calculated within the report to ensure it is in context.
• If you create a grouped variable, you need to check that all possible values for that dimension are grouped. If not, the next time you run that report, you may find data that is not sitting in a group. This could lead to misleading results.

Business Objects: Count v CountAll functions

Business Objects: Count v CountAll

I’m going to quickly run through the difference between the Count and CountAll function. Note that the CountAll function is only available in Full Client. Web Intelligence provides the Count function with a set of parameters, which I will discuss later.

Let’s take a simple example data set:

Two columns, one called Name and the other called Function.

Lets create eight rows of tab delimited data in a text file as follows:

Note that the seventh row does not have a persons name. Let us assume this is a position that needs to be filled. The eighth row has a name and no function.

If we load this data into BusinessObjects, we get the following result:

What result do you expect if you perform a count on the name column? Remember, you have loaded 8 rows.

If you guessed 6, you are correct.

To prove this, select the n icon from the toolbar or right click on the column and select Calculations/Count, an additional row is added to the table with the value 6. If you take a look at the formula behind the value 6, you will see it is =Count(<Name>).

Now if you perform a CountAll on the name column, what value do you expect to see?

If you guessed 7, you are wrong. Why? Because CountAll counts all rows, including duplicate values and empty rows.

To prove this, right click again on the column and select Calculations/CountAll. An additional row is added to the table with the value 8.

We can repeat this exercise on the Function column. Remember, we loaded 8 rows. What will be returned if you run a Count on the Function column?

If you guessed 7, you are wrong. Count will return a count of DISTINCT rows. So while you have loaded 8 rows, two of those contain Sales. Hence the Count will return one count of Sales. Also note it will not count empty rows.

To prove this, select the n icon from the toolbar or right click on the column and select Calculations/Count, an additional row is added to the table with the value 6. If you take a look at the formula behind the value 6, you will see it is =Count(<Function>).

So what value will be returned if we perform a CountAll?

If you guessed 8, you are correct. CountAll will return a sum of all rows, including duplciates an empty rows.

Web Intelligence: Count

If you are using Web Intelligence, you will not have a CountAll function. Instead you have a Count function two parameters:

Count([object]; [INCLUDEMEPTY]; [DISTINCT|ALL])

Using our example data set, what will Count([Name]) return?

The answer is 6. Count([Name]) will return a distinct list of Names.

What do you think will be returned by Count([Name];[INCLUDEEMPTY])?

If you guessed 8, you are wrong. If you guess none, well done.

Why? Okay, I slightly misled you. The syntax of the formula is incorrect. If you are including a parameter, do not use the square brackets for the parameter. The correct formula is: Count([Name];INCLUDEEMPTY).

This will bring back 7 rows. Six distinct rows plus an empty row.

So how many rows are counted using Count([Name];INCLUDEEMPTY;DISTINCT)?

The answer is 7. You bring back all the distinct rows (there are six – John is duplicated), and one empty row.

So what will Count([Name];INCLUDEEMPTY;ALL) bring back?

The answer is 8. All rows, including duplicates and empty rows.

There are valid scenarios where you may want to use the CountAll or Count([Name];ALL) function. It is important to be clear on where these functions should be used, as used incorrectly, they could bring back incorrect results.

Did you find this type of article useful? Are there any other functions that you would like me to walk through? If so, please let me know via a comment. Thanks for reading!