How to handle DIV/0 errors in Web Intelligence

A short and simple post about handling DIV/0 errors.

To handle DIV/0 errors, use the If() and IsError() functions.

IsError() will return a true or false value depending on the item being evaluated. Wrap this with an If() statement and you can evaluate the formula as normal is if it is ok, or return a message to the user if there is an error:

=If(IsError(15/0);”Error in formula”;15/0)

How to remove a specified number of characters from a string

A question was recently posted on the excellent Business Objects forum about how to remove a variable number of characters from the front of a string up to a specified character.

In this example the data was CKI=ICD9!250.02 and the user required all the values after the ! returned, i.e. 250.02. They also went on to state that the number of characters after the 250.02 may vary in length.

Interesting. What immediately sprang to mind was the MID() function available in Excel. However, this is not available in Web Intelligence.

However, Web Intelligence does provide us with the Right(), Length() and Pos() functions. Used together in a formula, we can get the desired result.

The first step is to determine the length of the character string. We are told it could be a variable length, so lets determine its length:

=Length([Object])

In this case we get 15.

Next we determine the position of the exclamation mark using Pos():

=Pos([Object];”!”)

This gives us 9

Finally, we use the Right() function. This takes two values; an input string and the number of characters to retrieve from the right of the string.

The second part is built up using the Length and the Pos calculations we made earlier.

=(Length([Object])-Pos([Object];”!”))

This gives us a value of 6. We should therefore count six characters from the left of the character string.

Putting this all together we create this formula:

=Right([Object];(Length([Object])-Pos([Object];”!”)))

…giving us the desired result of 250.02

Alternate row shading in Webi/Deski

One of our Business Objects Blog readers recently asked a question about alternate row coloring in Web Intelligence.

“Hi. I have a question on alternating row colors in Webi. I went to the properties and set 2 for the frequency in the “Alternate Row/Column colors” and have the specified color. Do you know why it’s showing as one color and not alternating the colors with white in between? Is there any other settings I need to change.
I’d appreciate your opinions. Thanks, Sam.”

I’m not sure about everyone else, but I have found that selecting a colour from the drop down list doesn’t appear to work for me. Instead I determine the RGB codes and entered them in the Color row. The Frequency of the alternate row is set to 2. This produces the desired alternate row effect.

alternate row shading - webi properties

Before this feature was available, I implemented alternate row shading via the use of a variable and the alerter feature. I’ll describe it here, both for Web Intelligence and Desktop Intelligence.

Web Intelligence alternate row shading

Through the use of the Odd() and LineNumber() functions, we can create a statement that can be used by an alerter to create our alternate row shading.

The steps to create this are as follows:

In the Formula Editor, create a new statement as follows:

=Odd(LineNumber())

alternate row shading - odd linenumber function

(You may need to temporarily add a new column to your table to do this. We can delete this later.)

The Odd function requires that you pass in a numeric value. It will determine if that value is odd. If it is, it will return a 1. If it is not, it will return a 0.

The LineNumber function simply returns the line number for each row in a table.

Put the statement into a variable, e.g. v_AlternateRowShading_Calc using the Create Variable icon.

alternate row shading - create variable

Highlight the column/s you wish to apply alternate row shading and click the Alerter icon.

alternate row shading - alerter icon

Create a new alerter and give it a meaningful name, e.g. a_AlternateRowShading.

In the cell ‘Filtered object or cell’, use the button to find your newly created variable. Chose ‘Select an object or variable’ from the drop down list.

alternate row shading - alerter editor parameters

Click OK.

Ensure the operator is set to ‘Equal to’

In the Operand(s) cell, type 1.

Then click the ‘Format’ button to set the format of the cell. In this case I’ve changed the background color to pink and the font color to black.

alternate row shading - alerter display parameters

Click OK, and OK again. The table should now have alternate row shading.

You can now safely remove the column containing the v_AlternateRowShading variable.

alternate row shading - report output

Desktop Intelligence alternate row shading

A similar process to the Web Intelligence method.

Create a new variable using the Variable Editor.

In the Definition tab, give it a meaningful name such as v_LineNumberFormat.

alternate row shading - deski variable editor 2

In the Formula tab, enter =Odd(LineNumber())

alternate row shading - deski variable editor

Highlight the column/s you wish to apply alternate row shading and click the Alerter icon.

Create a new alerter and in the Definition tab, give it a meaningful name, e.g. a_AlternateRowShading.

alternate row shading - deski alerter boxIn the Conditions tab, select v_LineNumberFormat from the drop down list of variables to compare.

alternate row shading - deski alerter box 2

In the cell ‘Filtered object or cell’, use the button to find your newly created variable. Chose ‘Select an object or variable’ from the drop down list.

Ensure Operator1 is set to ‘=’

In the Value 1 cell, type 1.

Then click the ‘Result’ drop down button, and select Format. Set the format of the cell. In this case I’ve changed the background color to pink and the font color to black.

Click OK, Apply and OK again. The table should now have alternate row shading.

alternate row shading - deski report output

Sam – I hope this helps to answer your question. Thanks for submitting a question to the BOBJBLOG!

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.

Handling formula errors in Web Intelligence

Don’t you just hate it when you see a report with DIV/0 errors?

Thankfully, Web Intelligence provides us with a function to handle these types of errors, allowing us to provide a more meaningful message.

Lets look at IsError

IsError() is a function which will return a boolean value.

A 1 is returned is the formula being evaluated is in error. A 0 if there is no error.

This function is particularly useful when embedded into an IF statement:

Lets assume x = 6 and y = 0

IF (IsError(x/y);”The y object is zero – a DIV/0 error has occured”;(x/y))

In this example, the statement x/y is being evaluated within the IsError function:

6/0

As you can’t divide by zero, a DIV/0 error has occured and a 1 is returned by the IsError statement.

The IF statement then presents one of two outcomes depending on this boolean value.

In the case of an error, the user is presented with some meaningful text:

“The y object is zero – a DIV/0 error has occured”

If we change the y value to 3, the IsError function returns 0 – no error, and the second part of the IF statement is evaluated: x/y – which gives us a value of 2.

It is good practice to include error handling into your reports, particularly when you are performing any type of division task. Wrapping an If IsError check around your formula ensures that these type of errors are handled in the correct manner and should help the user identify how the problem can be fixed.

How to convert a string prompt into a date in WebIntelligence

 There are occasions where we need to prompt the user for a date. This can be done using the prompt functionality in the query panel.

We can display the value entered by the user at the prompt using the UserResponse function.

The UserResponse function is made up of two parts:

string UserResponse(string data_provider;string prompt)

The first part is the Data Provider that contains the prompt that you want to use. This is an optional field and is only required if your report contains multiple data providers. If you are only using one data provider, ignore this first part.

The second part is the EXACT text of your prompt string.

This needs to be a perfect match to your prompt. A way to ensure this is to copy and past the prompt string before you refresh the report.

When refreshed, the function will return the prompt value.

Note that the value returned from the UserResponse function is a string. Despite the value being shown as a date, the value is stored as a string. This means we are not able to perform date calculations on this field as it stands. The error message that will be shown is:

 

To overcome this, we expand the formula slightly and nest the UserResponse function inside a ToDate function.

The ToDate function is made up of two parts:

date ToDate(string input_string;string date_format)

The first part is the input string (in our case the UserResponse formula), and the second part is the format of the input. Note that the format has to match the input exactly.

We end up with the following formula:

 =ToDate(UserResponse(“Enter Date:”);”dd/mm/yyyy”)

At this stage, the user prompted date is converted to a date format and can be used in date calculations.

Bonus:

If you subsequently want to display the user prompted date in a specific date format, we would nest the formula further and include a FormatDate function:

string FormatDate(date date_to_format;string date_format)

This function takes two values. The first is the date you want to modify and the second part is the format string you want the date to appear in.

For example, if the user has entered the value 14/01/2012 at the prompt and we want to display this as Saturday 14 January 2012, we would create the following formula:

=FormatDate(ToDate(“Enter Date:”);”dd/mm/yyyy”);”Dddd dd Mmmm yyyy”)

In the above example, note that the output of the FormatDate function is a string, not a date. This is important to remember. If you need to perform calculations on a date, make sure it is in a date format (using ToDate).

Once you have finished your date calculations, you can then format the output in any way you like using FormatDate.

For a list of available date formats, take a look at this post. https://bobjblog.wordpress.com/2012/03/05/web-intelligence-date-formats

Note: If you receive #ERROR messages in your calculations, ensure that you are not using a date and time string. The examples above are based on dates in the format mm/dd/yyyy and not mm/dd/yyyy hh:mm:ss

Web Intelligence Date Formats

A simple post to highlight the different date formats that are available in Web Intelligence.

How to display the previous working day in Web Intelligence

Through the use of the DayName function, we can display the day name of the week for a particular date.

Today is Monday 13th February 2012.

What would be the result of creating the following formula in WebIntelligence?

=DayName(CurrentDate())

The answer is Monday. Today is Monday 13th February 2012.

Now, if I wanted to display the previous working day, I couldn’t use the RelativeDate function on it’s own. Remember we are looking for previous working day and not previous day.

In order to do this, we need to check the current day and determine if the previous day is a weekend. If it is, we need to adjust the output accordingly.

We can develop this logic within WebIntelligence using a nested IF statement.

Let’s work through an example:

Create a new WebIntelligence report. If you have a CurrentDate object in your universe, we can use that. If you don’t, then bring back any object (preferably one that doesn’t bring back many rows – we don’t actually want to use that value, but we do want to end up with a table).

If you do not have a CurrentDate object, create the following formula in WebIntelligence:

=CurrentDate()

Note that the format of the date value returned depends on how the universe object has been set up or how your WebIntelligence setup is configured. In some cases it may be formatted as dd-Mmm-yyy or dd/mm/yyyy. If you want to use a specific format and you are using the =CurrentDate() formula in WebIntelligence, you can format the date using:

=FormatDate(CurrentDate();”dd-Mmm-yyyy”)

For the purposes of this article however, we will be using the default format.

Add a second column to your table. We will call this column ‘Current Day Name’ and we will use the DayName function to return the day of the week:

=DayName(CurrentDate())

Now to get the previous working date.

We need to check the value (Day Name) of the current date. If it is a Tuesday, Wednesday, Thursday, Friday or Saturday, we will be fine using the RelativeDate function, as the previous day to the above listed days is a working day. (I’m ignoring Bank Holidays, Festive Days, etc. That’s for a future topic!)

However, if we find that the current Day Name is a Sunday or a Monday, then using RelativeDate is going to retrieve a weekend day. We need to handle these two scenarios using a nested IF statement.

The statement is built up as follows:

If the current day name is a Monday, then subtract three days from the current date
If the current day name is a Sunday, then subtract two days from the current date

The formula is made up as follows:

=If(DayName(CurrentDate())=”Monday”;RelativeDate(CurrentDate();-3);If(DayName(CurrentDate())=”Sunday”;RelativeDate(CurrentDate();-2);RelativeDate(CurrentDate();-1)))

Lets break down this formula.

An IF expression has three components:

If(boolean_expr;value if true;value if false)

The boolean_expr part is the statement that we test.

In our case, we want to check if the CurrentDate() is a Monday.

If it is true that it is a Monday, we want to use the RelativeDate function to subtract 3 days from the CurrentDate().

If it is false, i.e. the current date is not a Monday, we need to perform a further check. This requires a second IF statement to perform this second check.

The second check asks if the CurrentDate() is a Sunday.

If it is true that it is a Sunday, we want to use the RelativeDate function to subtract 2 days from the CurrentDate().

If it is false, i.e. the current date is not a Sunday, and we know its not a Monday as we checked for that previously, then we know that the day is a Tuesday, Wednesday, Thursday, Friday or Saturday and hence we can safely use RelativeDate with a value of -1 to give us the previous working day.

The formula above displays the result in a date format. If we wanted to display the result as a Day Name only, we can tweak the formula a little to incorporate the DayName() function:

=If(DayName(CurrentDate())=”Monday”;DayName(RelativeDate(CurrentDate();-3));If(DayName(CurrentDate())=”Sunday”;DayName(RelativeDate(CurrentDate();-2));DayName(RelativeDate(CurrentDate();-1))))

So using this formula we should end up with the following result:

Current Day Previous Working Day

Monday         Friday
Tuesday        Monday
Wednesday  Tuesday
Thursday     Wednesday
Friday           Thursday
Saturday      Friday
Sunday         Friday

How to add or subtract a number of days from a date field in Web Intelligence

A report developer may be required to display a date field in a report which is made up of an existing date field plus/minus x number of days. A good example of this may be a report developed for the credit control function where you have issued an invoice on 30 day terms. The credit control team may want to identify those invoices which have exceeded those 30 days terms.

WebIntelligence provides a function to allow us to do this:

RelativeDate()

This function requires two values.

The first is the date field you wish to add/subtract from. The second value is the number of days you want to add or subtract. These two values are separated by a semi colon in WebIntelligence.

Note that the second value should be a numeric value and should not be in single or double quotes.

If you want to subtract a number of days, ensure that the value you enter is a negative number (proceeded by a minus sign).

Lets work through a couple of examples:

Adding seven days to a date field

Create your report and pull in a date field.

In this case I am using an object name called ‘Date Field’. Your date object may have a different name.

Add a new column to the right of the Date Field column, and build the following formula:

=RelativeDate([Date Field];)

*Replace the <number of days to add> with your value

=RelativeDate([Date Field];7)

This adds seven days to the value held in the Date Field

Subtracting seven days from a date field

Create your report and pull in a date field.

Add a new column to the right of the Date Field column, and build the following formula:

=RelativeDate([Date Field];-)

*Replace the <number of days to subtract> with your value. Remember that this should be a negative value.

=RelativeDate([Date Field];-7)

This subtracts seven days to the value held in the Date Field.

If you have access to Universe Designer, or can request a new object to be added to your reporting universe, we can take this to another level by making that value a dynamic field. This means that the user will be prompted for the number of days to add or subtract from the Date Field.

What if I want to prompt the user for a value to use in this formula?

This is possible. However, in order to do this, you need to add a new object to your Business Objects universe.

Create a new dummy object in the Business Objects universe which holds a User Response prompt.

Give it a meaningful name such as User_Prompted_Days.

@Prompt(‘Enter number of days to add/subtract:’)

Save and export the universe.

Rebuild your report and pull your new object User_Prompted_Days into your query alongside your date object.

Refresh the report.

You should now be presented with the following prompt:

“Enter number of days to add/subtract:”

Enter a value. This value will get stored in the object called User_Prompted_Days.

We can now use this object to build up our new date column.

Add a new column, and populate this column with the following formula:

=RelativeDate([Date Field];ToNumber([User_Prompted_Days]))

We use ToNumber in this formula as the prompt value will be stored as a character string.

You should end up with a new date based on the value held in the Date Field column plus/minus the days entered by the user at the prompt.

This approach has a benefit in that the value entered by the user can be applied to any number of date fields you bring back in your report.

If you find that you only need to apply the user response to a particular date field, consider creating an object with the prompt built in:

The object code to subtract x days from the current date in SQL Server is:

convert(datetime,convert( varchar,dateadd(dd,-@Prompt(‘Number of days to subtract from current date:’,’N’,,MONO,FREE),getdate()),102))

The object code to add x days to the current date in SQL Server is:

convert(datetime,convert( varchar,dateadd(dd,@Prompt(‘Number of days to subtract from current date:’,’N’,,MONO,FREE),getdate()),102))

In case you missed it, the key difference in the two formulas is the minus sign before the @Prompt.

Hopefully this will have helped you understand how to add or remove days from a date field in Web Intelligence.

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!