When you’re developing some tests with NBi it’s not uncommon that you’ve some issues to compare decimal values and this is especially true if your tests involve an MDX query. Usually, you’ll meet a pitfall where NBi is comparing two rows and returns that the value “10.3” is different than “10.30” or even “10,30”. The following test simulate this issue:
<testSuite name="Compare decimal values" xmlns="http://NBi/TestSuite"> <settings> <default apply-to="everywhere"> <connectionString>Data Source=mhknbn2kdz.database.windows.net;Initial Catalog=AdventureWorks2012;User Id=sqlfamily;password=sqlf@m1ly</connectionString> </default> </settings> <test name="Keys on decimal value" uid="0001"> <system-under-test> <execution> <query> select cast(10.3 as decimal(3,1)) </query> </execution> </system-under-test> <assert> <equalTo> <resultSet> <row> <cell>10.30</cell> </row> </resultSet> </equalTo> </assert> </test> </testSuite>
After the execution of this test, you’ll receive a report of failure similar to the screenshot at your right. If you analyze this report you’ll understand that the representation of the numerical value “10.3” has not been handled the same way in the system-under-test and in the assert. In the system-under-test, NBi receives the information from Sql Server that the value returned is a decimal (so a numeric) and NBi will handle this value as a numeric. It means that when displaying this value, NBi will use your regional settings and, in my case, it will use a comma as decimal separator. The display will be “10,3” as highlighted in blue on the screenshot. On the other hand, for the assertion, NBi is taking the assumption that the value “10.30” is a textual value. So the representation in the report will not be modified and the value “10.30” will be displayed (highlighted in red). When NBi compares the content of two cells, it always use the type specified in the assertion. So in this case, NBi compares effectively two textual values “10,3” and “10.30”. Following this logic, the two rows are effectively different.
The good news is that you can easily tune this behavior. In fact if you’re vigilant, you’ve probably seen that NBi is warning you about the comparison as textual values in the failure’s report. In the header of the tables, NBi explains the considered type for the comparison. In this sample, NBi explains that it’s currently using a type “Text” (circled in red).
To change this, you’ll need to overwrite the default configuration of NBi. By default, NBi considers that all the columns of a result-set are textual values except the last one which is numeric. But in the case of result-set that only one column, NBi considers this column as textual. To change this, you need to express a column definition in your assertion. This definition is highlighted in the code below at the line 3.
<assert> <equalTo> <column index="0" role="value" type="numeric"/> <resultSet> <row> <cell>10.30</cell> </row> </resultSet> </equalTo> </assert>
Putting in place this configuration of the result-set, we’ve defined that the column is now a “numeric” and it’s role is “value” and not anymore a “key”. NBi will now compare two numeric values “10.3” and “10.30”, which are identical, and return a success.