Cancel the Save Event in Dodeca

This is the third of three posts on using relational data with Dodeca, specifically on the Data Block Range that can be used with your SQL ranges in Dodeca.  It is another example of how Dodeca makes it easy for you to customize your Reporting, Planning and Analysis application to deliver the exact solution for you.

In the first post on the SQL DATA BLOCK range, we examined how it works with your SQL data range and how it can be utilized in your Dodeca Views.  In the second post, we added more functionality to perform validations on your SQL data.  In that example, we only provided feedback to the user.  We indicated that there was an issue with the data the user was trying to save.  However, we did not prevent the save action from occurring if the user went ahead and hit Save.  If the user did hit save with these Validation issues still applicable, they would still receive an error on the View.

In this post, we will go one step further and actually stop the Save process, and also provide a message as to why the event was cancelled.  Sometimes you really need to hit users over the head to get the message across.

So how will we do that?  One of the many reasons Dodeca is the best tool on the planet for your Planning and Reporting systems is the amount of control developers have with what happens in a Dodeca View.  The key to this control is what the Dodeca architect team refers to as the “event driven extensibility” of Dodeca.  This event driven extensibility is defined in Dodeca using what are called Workbook Scripts.

Do not let the term Script throw you though.  We do not actually write scripts filled with code.  That work has already been done by the Dodeca team.  They have already done all the heavy lifting.  We just need to define what we need to do, and then configure the workbook script to do it.

So here is what is happening in our example from the previous blog post.

  1. The user is attempting to perform an Insert.  Specifically, the user is attempting to Save a Data Set range, which will do the insert of the data to a relational table.
  2. We know the table is defined such that it does not allow null fields.  All the fields must have data in order to save the new row of data.
  3. Thus far, we have put a notification on the View to let the user know that all fields should be there before saving.
  4. If the user attempts to save anyway, the user will get an error.  We want to stop this error.

Here is our mission:

  1. If the user attempts to save while there are validation errors, we will stop the “save” process to avoid the error.
  2. We will present a message box to let the user know that the data cannot be saved, and why.

So here is where the Workbook scripts (WBS) enter the picture.  We will use a workbook script to accomplish our mission defined above.

Before we get into the WBS however, let’s create an easy way to indicate that there are Validation issues.  Recall from the previous post how we set a formula in column G to evaluate to TRUE if there is an issue.  We know where we need to check.  Let’s do two additional things on our template.  We will create a named range for the column G rows we need to check, and then use another cell to count the number of TRUE’s.

We will name the range in column G CheckFields:

Named range “CheckFields”. Where to check for problems

And add a formula to cell G2 to count the number of TRUE’s in the range.  And we will name this field ErrorCount:

Count the number of issues

Now we have an easy way to know if there are issues.  If the value in the ErrorCount cell is > 0.

Back to the Workbook Script.

Remember how I said the Dodeca team has already done all the heavy lifting?  Well, there are many things that can happen in a Dodeca view.  These are called Events (hence, event-driven extensibility), and Dodeca has already defined over 100 of these events for us to use.  Events are captured, and then Procedures are tied to the event to do get things done.  What event do you think we want to capture here?  That’s right, it is the Save event.  Specifically, it is the Save Data Set Range event.  And we want to capture the event *before* it happens.  So we will look for a Before Date Set Range Save event.

Let’s open the Workbook Script editor for this View.

The Workbook Script editor in Dodeca

There are four sections: Definition, Properties, Event Links and Procedures.  We are not concerned with the Definition or Properties sections today.

I can right click on “On Event…”, select New, and be presented with a list of the available events to use.  Scrolling down I find “BeforeDataSetRangeSave”.  Bingo.  That’s the one we want.

The event we want to capture

I can select that, then hit tab twice to automatically create a procedure called OnBeforeDataSetRangeSave.  The procedure name is automatically generated, but you could create one with another name for this if you want.

Now that I have the procedure, I need to specify the Methods within the Procedure.  The methods are the vehicles for getting things done.  We want to Cancel the Save event, so scrolling down I find the CancelEvent method:

Select the Method within the Procedure

Now to configure this event.  Here are the properties:

The CancelEvent method Properties

We only want this method to be in effect if there are validation errors.  And we know if there are validation errors if our ErrorCount field value is > 0.  So in the MethodCondition property we enter

=@ValueNumber(ErrorCount)>0

Also set the CancelProcedure property to TRUE

Setting the method properties

This method also provides a Property to enter the name of a procedure to run if the event is cancelled.  We can use this to display a message box informing the user of the cancelled event.  We will create a Procedure called ShowCancelMessage, and we can utilize the ShowMessageBox method there.  Let’s enter the message as:

“The Save operation has been cancelled due to Validation errors on the data. Please check the Validation messages and correct the data before saving.”

The ShowMessageBox method properties

The last bit of cleanup we need to do is reset the View status.  There is method for that, called, you guessed it, SetViewStatus.   The View status gets set to working when the Save button is clicked.  We want to reset this back to Ready.

Set the View Status back to Ready

That’s it.  One event, two procedures, and three methods used to get everything done.

Now when the user attempts to save date before it is ready to go, the Save will be cancelled, and the User will get a message as to why.  Let’s see it in action.  I have opened the View and entered some data.  It is incomplete however.  I try to save anyway:

The View showing Message box

I hit the okay button, and we are back to the View.  No errors.  Voila!

 

Validating SQL Data Before an Insert in Dodeca

This is the second of three posts on relational data in Dodeca.  The first post discussed the Data Block range and how it can be used to add functionality to your SQL Views in Dodeca.

This post will extend that idea by discussing how you can use Excel formulas in the Data Block range to test input data before saving data on a SQL View.

As in the last post, I will not go into detail on setting up the Data Set range, or the basics of setting up your SQL Views.  Please see the last post for links on those topics.

We are going to have a look at a SQL View in Dodeca of a simple employee table that allows the inserting of additional rows.  Aside from the ID which is auto-generated, there are only three fields in the table: LastName, FirstName and Position.

We would like to ensure all the fields are there before sending to the relational database.  Checking for blank fields is a rather simple validation, but the method discussed could be used for other validations as well, such as proper data types, valid codes, a proper numerical range, correct dates, etc.

Here is a look at the columns and data in the table called HR.

HR table columns and data

There are only four fields, and, as stated above, the ID column is auto generated on insert.  The important thing to know about this table is that all fields are required.  Nulls are not allowed in any field, so all fields must be entered in order to properly save the data.

We have a Dodeca View called “HR Update” to view this data, and also allows users to add to this data, i.e., perform an Insert to the table.  The DataSet range will allow Adds, but not updates or deletes.  Our SQL Passthrough dataset will have a Select Statement and an Insert Statement only, not a delete or update statement.

The Insert statement of the SQL Passthrough Dataset

Here is the View in Dodeca:

The HR Update View in Dodeca

Suppose there is a new entry to add to the table, Leslie Knope.  But the individual entering the data does not know what Position to enter so it is left blank.  When attempting to save the data, we get an error.  Oops.

HR Update View with error on Save

Clicking on the details button of the error notice we see:

Error message on Save attempt.

Hmmm.  This is the information returned from the relational database, and it is not exactly helpful.  I’m sure most users could figure out that all fields need to be entered in this simple example.  But if there were more advanced validations needed, wouldn’t it be better to somehow verify the data before sending it to the server?  Perhaps we could enter a message on the View before sending to indicate that something is missing or wrong.

We can do this by adding some Excel formulas since they are valid in a Dodeca View, and then include the formula cell in the Data Block range.  Recall when I wrote that Dodeca brings together the best of Essbase and relational data?  Well, add Excel to that mix as well.  It brings together the best of Essbase, relational data, AND Excel.

We will enter a formula in the View to check if any of the three required fields are blank, but we will only check this if one field has been entered.  If any of the three fields has been entered, check the other two for blanks.  We are checking each field individually using OR statements.  The formula we need can condense down to this:

=AND(OR(ISBLANK(C6),ISBLANK(D6),ISBLANK(E6)),OR(NOT(ISBLANK(C6)),NOT(ISBLANK(D6)),NOT(ISBLANK(E6))))

The formula looks a bit long-winded, but basically checks any of the three fields for blanks if any of the three fields has data.  If this resolves to TRUE then we have an issue.

For now, we are just going to inform the user if there is a potential issue with saving the data.  We will do this by presenting a Validation message on the View.  In the next blog post, we will actually stop the SQL save process if there are any Validation errors.  We will do this by adding a bit of workbook scripting.

Here is the current Data.Range:

The Data.Range named range in the View

It is three rows long to include the SQL Column names, and to include a blank line for inserts.

Now we will add the validation formula above to the template.  We will add it to cell G4.

Validation check field in the View

Let’s also add a formula to display a message if this field resolves to TRUE.  The formula will be entered in H4 and looks like this:

=IF(G4,”Please enter all fields before saving”,””)

Yes, we could have combined the two formula fields, but let’s just keep them separate for now.  We will also format cell H4 as bold and red.

Field to display message if there is a Validation issue

Now we name the range on the View as DataBlock.Range.

The DataBlock.Range defined range in View

We will hide column G in the View template since it does not need to be seen in the View.  And, as I highlighted in the last post, we include this range in the Data Table definition of the View.

The Data Table definition in the View

All set.  Now let’s run the View and try to add a new row of data.  We will only enter the first two fields.

Validation message displayed

The message gets displayed after the first field is entered.  It is there to notify the user of an issue.  It will only go away after all the fields are entered:

All fields entered, no validation message needed

And because the range follows all the fields, the validation message will appear on any and all lines that are missing fields:

Message appears only where appropriate

All set.  Our user now gets a helpful message before saving.  But the user might still go ahead and hit save, resulting in the same error message we saw earlier.  In the next blog post, we will add a bit of workbook scripting to stop the save event, adding even more control to the process and eliminating errors on the View.

 

Using a SQL Data Block Range in Dodeca

One of the many reasons why Dodeca is the best tool on the planet for working with Essbase is how easily it integrates with your relational data.  It allows you to bring together the domains of both Essbase and relational data to create the exact system you need.

Often when people hear the words “Essbase” and “relational data” together they think of drill-through.  And yes, Dodeca makes it extremely easy to drill through to the relational source of your Essbase data.  But that is just tip of the iceberg.  Dodeca also provides an easy interface for WRITING back to your relational data systems.  This creates many more possibilities for your Planning, Reporting and Analysis systems that use Essbase.

There is some great information on the web describing the use of Relational data within Dodeca.  There are the Applied OLAP videos on their YouTube channel.  There are also some great blog posts by Tim Tow, Jason Jones, and others on this topic.

Here are some of great sources of SQL in Dodeca information:

Applied OLAP YouTube videos on Relational data in Dodeca

Jason Jones’ Primer on Relational Data Views in Dodeca

Tim Tow’s Fundamentals of SQL Writeback

Relational Drill-Through in Dodeca by Cameron Lackpour

Jason’s Relational Data Input in Dodeca

Jason’s Post on Dynamic Relational Grouping in Dodeca

Please note:  The post by Jason Jones on Relational Data Input does an excellent job of highlighting the game-changing nature of the ability to combine Essbase with relational data.  If this is an interest to you, and you haven’t read it already, I would urge you to have a look.

Into these waters I wade.  This blog will attempt to add to this fine collection of information with three posts on using SQL in Dodeca.  The first is a quick post on the SQL Data Block range.  The second will be a post on extending the functionality of the Data Block Range to validate SQL data before doing an insert.  The third will take a look using some Workbook scripting to actually cancel an SQL insert event if there are any validation issues.

Also note, I will not discuss the basics of setting up a SQL View in Dodeca.  Those basics are well covered in the information links above.

The SQL Data Block Range

The DataBlockSheetRangeName property

What exactly is a SQL Data Block Range?

There is a property in the DataTable Range Editor called DataBlockSheetRangeName.   In the editor, in the help section, the property is described as:

The name of an optional sheet range that contains the data range and additional columns that contain formulas that refer to cells within the data range.  The DataBlockSheetRangeName must be the name of a sheet-level defined range.

The data block range is used to define formulas within cells on a data row that refer to cells within the data range on the same row.  When the range is built and when rows are added, inserted, or deleted, the columns within the data block are maintained along with the data range. 

Simply put, this range defines formulas that can tag along with each row of data in the SQL data range.

For example, if you are retrieving monthly data in your SQL data range, you could sum the months into quarter and year values using formulas in the data block range.  If you brought back 3 rows of data or 3000 rows, the data block would follow along with the data range and calculate values for as many rows of data exist.

The data block range is an extension of the data range.  It contains the data range, plus additional columns.  See figures below.  The first is the sheet defined Data.Range and the second is the defined DataBlock.Range.  These are defined on the View Template in the View Template Designer.

The Data.Range defined on the View template
The DataBlock.Range defined on the View Template

You can see that the DataBlock.Range actually contains the Data.Range.

The SQL Passthrough Dataset for this View retrieves data for the first three months of the year.  These values will be entered in columns B, C, and D, as defined in the data range.  Column E is part of the data block and contains a formula to sum the months.

Formula defined within the DataBlock range

When the View is opened, we see that the data is retrieved, and the formula is there to sum the months for all the rows that are returned.

The View with SQL data returned, and with the DataBlock range to sum the data.

That’s it.  Using a Data Block range with your relational data in Dodeca comes in very handy.  In the next post, we will discuss using the Data Block range to do something a bit more sophisticated that adding columns together.

 

 

 

Hide or Show Zero Rows in Dodeca (without Suppressing)

The “suppress missing” and “suppress zero rows” ability of Essbase is quite useful for many cases of handling information from your Essbase application.  But let’s look at a case where you may want to have the option of easily hiding or unhiding these rows, as opposed to getting rid of them altogether, and how easy it is to do with Dodeca.

We will use a simple budgeting view, and a simple Chart of Accounts to demonstrate.

Leslie Knope-Wyatt has a simple Dodeca View to manage the budget for the year at her company.  This View is aptly titled Budget by Acct. The budget information is loaded at the beginning of the year for the appropriate chart of account members, and then updated throughout the year.

This is an example of what the Budget data looks like across all accounts for the year.  The list scrolls across multiple pages.

Figure 1. All rows in Budget View

This is a rather simple Chart of Accounts.  There are only about 45 or so level zero accounts in the Chart.  But the budget is only loaded to about half of those.  Since all the accounts are listed, the user is presented with more information than she needs.  It would be much easier on the user to present a streamlined View, of only the rows with data listed.

Simple, you say.  Turn on suppression of zero and missing rows in the View options and we are done.

Figure 2. View options for suppressing zero and missing rows

Once those options are set to true, only rows with data will appear in the View.

Figure 3. View with zero and missing rows suppressed.

Not so fast!  The accounts with zero or missing data are now gone, never to be seen again in the View.  But what if Leslie now needs to use one of those accounts during the year to correctly do the budget?  What if there is a new account that has been added during the year that needs to have budget?

Obviously, suppressing the rows in this case is not a viable solution.

A better option would be for the user to have the ability to hide or unhide zero rows at her discretion.  Then she would have a streamlined view for the most common updates, but also be able to get to and plan for any chart of account member that exists.

With Dodeca this is easy to accomplish with a simple formula on the sheet, and a bit of workbook scripting.

What we can do is tell Dodeca to have a look at the data after it has been returned from Essbase and hide the row if it contains all zeroes.  So, by default, the View will be streamlined to only show the rows with data.  If the user needs to use an account that currently has zero values, she can simply use a button on the toolbar to unhide the rows.

We will accomplish this with the following steps:

  1. Add a formula to the worksheet template to determine if a row contains all zeroes.
  2. Hide the zero rows by default using a Workbook Script (WBS) procedure.
  3. Create another Procedure in the Workbook Script to unhide the zero rows.
  4. Add a button to the toolbar to call the Unhide procedure.

Step 1:  Add a formula to determine the rows to hide.

We want a cell in each row to resolve to TRUE if it should be hidden, so we will enter a formula to check all the columns in each row.  Should we simply sum the values to check if they add to zero?  That would be a simple solution but it would not work if there were offsetting values, say 500 in June and -500 in July.  These would sum to zero but this would be a viable row the user wants to see.  We could check each cell (If B9=0, and C9 =0, and D9=0, etc) but that would be a cumbersome formula.

A better solution is the check the Min and Max of the range with a simple AND statement:

=AND(MIN(B9:M9)=0,MAX(B9:M9)=0)

This will resolve to TRUE when all the values in the range are zero.  Let’s enter this formula in Column O on our View template.  Once we have entered the  formula and copied it down for each row, lets select this range and give it a name:  Hide.Range

Figure 4. The Hide.Range range on the View Template

We will hide this column in the when setting up the View Template so it does not appear when the View is opened.

Step 2:  Use a workbook script method to hide the rows with zeroes.

There is a method just for such an occasion called “SetHidden”.  We can set it to hide the rows where the value of the formula we entered in step 1 is TRUE.  We can use this method the AfterBuild event in Dodeca.  The workbook script (wbs) looks like this:

Figure 5. The SetHidden WBS method.

Let’s have a look at this method.

  1. Set Overload to Rows.  This method has a number of overloads depending on what you want to hide: Columns, Rows, Worksheets, etc.  We will set the overload to Rows.
  2. Address.  We are going to use the range name we created in previously called Hide.Range to determine the hide condition.
  3. CellByCell.  We will check each row, so we need to go CellByCell in our range.
  4. CellCondition.  We will use the Dodeca wbs function ActiveCell for the cell condition.  This will either be TRUE or FALSE on the View.  If it is TRUE, the condition will be met and we will hide the row.
  5. Hidden.  Yes, we want this to be TRUE so that the row will be hidden.

So far so good.  We now have a View that will open presenting only the Accounts with data.  This will give the user a much more streamlined look at the data.  She will no longer need to scroll through the list of accounts; they can all be seen in one page.  See figure below.

Figure 6. The View with hidden rows.

The sheet headings are left on the View to highlight a couple of points:

1.        Column O is hidden.  This contains our formula to check for zero rows.

2.       The row numbers now indicate that a number of rows are hidden.  We see the same data that is present in Figure 3 above, but we know that more rows exist in this version, they are just hidden.

Here is what the View looks like with all rows and columns displayed:

Figure 7. All rows and columns unhidden.

We are almost done.  We now need a way to unhide these rows at the user’s request.  But instead of just unhiding rows, Dodeca gives us an easy way to “toggle” the rows with zeroes, so the user can hide or unhide with one button.  We will use the same method we used previously.

Step 3:  Add a procedure to toggle the hidden rows.

This will be easy.  Add a new procedure in the workbook script called ToggleHiddenRows.  Then add one method to this procedure, the same one used previously, SetHidden.  See below.

Figure 8. The toggle procedure and method to hide/unhide

The overload for this procedure will be ToggleRows.  The following line of help information describes the ToggleRows overload.

Overload: ToggleRows (Set Hidden of the specified row(s) to the opposite of the current setting.)

We still need to enter the Address and the CellCondition for this to work properly.  With these set, the method will focus the hiding/unhiding on the rows where the formula resolves to False.  Try setting the CellCondition to TRUE or FALSE to get an understanding of the difference in how the method works with these settings.

Step 4:  Add a button to the View toolbar to toggle zero rows.

Once we have the new procedure in place, we can add a button to the toolbar to call it.  We do not need to describe that process here as there is another blog post for that.  For more information on how to add a button to the toolbar, please see this post on the topic.

The button that is added to the toolbar will have the caption “Toggle Zero Rows” for brevity, with the understanding that the user knows that in this case, toggle refers to hiding and unhiding the zero rows.

Once the button has been added to the toolbar, our user can now easily toggle to unhide or hide the zero rows:

Figure 9. The View with the Toggle button added

Our user can now easily manage the button for the zero rows with a simple push of a button.

Figure 10. The View with rows unhidden.

All done!

If our user now enters and sends data on any of these zero rows, that row will now always appear when the View is opened.