Validating Data in MicroStrategy Transaction Services

Validating Data in MicroStrategy Transaction Services
Author: Calvin Rodda 25/04/2018

MicroStrategy Transaction Services can be used in conjunction with MicroStrategy Web and Mobile documents
to rapidly create some fantastic applications that allow you to write back to the database. We often get asked
if Transaction Services can perform data validation and, if so, how it can be done. There are a few ways to
validate user input, but some are more complicated than others and you probably came here to find the
simplest way to do it, and that is what we’ll be showing you. This is a quick guide on how to do simple
validations directly within the MicroStrategy Web document that you are developing. This validation on user
input provides a simple method without having to code error checking into the SQL procedures. The latter is
always an option, but the user will have to submit their details first and will then receive a generic error code
and will have to capture their details from scratch. With these two simple methods, an error message is
displayed directly after the incorrect input is received and the validation is performed before the data is
submitted. We will first discuss validation using Regular Expressions and then using Thresholds.

Validation Using Regular Expressions

A regular expression is a sequence or set of characters that defines a search pattern. A wide range of regular
expressions are available online, from ensuring text fields contain only alphabetical characters and checking
that names start with CAPS to validating South African ID numbers and cell numbers. Regular Expressions are
useful in MicroStrategy Development because they provide us with a very simple way to validate user input
without having to first submit the captured data.

Below are some examples of regular expressions that can be used in MicroStrategy. These examples and more
can be found on the Regular Expression Library at http://www.regexlib.com

 

  1. Validate South African phone numbers

  2. Validate South African phone numbers with country code
  3. Validate South African phone numbers with country code

These examples show the expressions used to perform the validation, as well as some samples of matches and non-matches for each expression. We can now look at how to use these regular expressions to validate transaction services in MicroStrategy. When configuring a transaction in MicroStrategy Web, click Configuration Properties and select Regular Expression as the Validation type.

  1. Validate South African phone number
    Expression: (^0[78][2347][0-9]{7})You can set a maximum and minimum length for the input field, because South African phone numbers are always 10 digits.
  2. Validate South African ID number
    Expression: (((\d{2}((0[13578]|1[02])(0[1-9]|[12]\d|3[01])|(0[13456789]|1[012])(0[1-9]|[12]\d|30)|02(0[1-9]|1\d|2[0-8])))|([02468][048]|[13579][26])0229))(( |-)(\d{4})( |-)(\d{3})|(\d{7}))You can set a maximum and minimum length for the input field, because South African ID numbers are always 13 digits.
  3. Validation successful
    If all the data that has been captured is of a valid format, no error messages will be displayed and the user can select Submit. A successful message can be displayed, saying Submitted!When the user enters any invalid data such as an incorrect ID number or phone number, the Transaction Service won’t allow the user to submit until all the data is valid. This is very useful and saves a lot of time as the user does not have to first submit the data to receive an error message and then recapture the data for all fields. The user can simply see the data is invalid for a specific field and can recapture data for that specific field.
  4. Invalid phone number
    If an invalid phone number is entered, the user will receive an error message and action will be required before any other changes can be made.
  5. Invalid phone number
    If an invalid ID number is entered, the user will receive an error message and action will be required before any other changes can be made.

Validation Using Thresholds

Another method of input validation is that of Thresholds. Thresholds are more appropriate for some data types such as date, and for Transaction Services that use tables. If you require an input in your Transaction Services document to be validated, such as validating that StartDate is always earlier than EndDate, then you can make use of Thresholds in MicroStrategy.

More details on Thresholds can be found on the MicroStrategy Community article https://community.microstrategy.com/s/article/KB426484-How-to-Set-Input-Data-Validation-in-Transaction-Service-Document-in-MicroStrategy-Web-10-5

  1. Right-click on your transaction grid and select Thresholds and then Choose EndDate, and apply a qualification such that EndDate is less than StartDate
  2. Double-click on your threshold, check Replace Data and type Invalid Date into the textbox. This sets the error message text that the user will see when the data is invalid.
  3. Run your document in presentation mode and you will notice that all EndDate values that are after their respective StartDate will display as Invalid Date

Thanks for reading and I hope you have enjoyed learning the simple methods of data validation in MicroStrategy. If you have any questions or problems, please don’t hesitate to contact us.

Your email address will not be published. Required fields are marked *

Our Partners

div#stuning-header .dfd-stuning-header-bg-container {background-image: url(http://expeditus.co.za/wp/wp-content/uploads/2018/02/Expeditus-Banner-Blog.jpg);background-size: cover;background-position: center center;background-attachment: scroll;background-repeat: no-repeat;}#stuning-header div.page-title-inner {min-height: 550px;}