Adventures in Groovy – Part 29: Troubleshooting Data Movement With GridBuilder

One of the challenges working with grids is validating the results.  As with an Essbase calculation, Smart View reports are developed to validate results.  The same happens when calculations, or data movement, is executed in Groovy via grids.  When the results in Smart View aren’t accurate, where do you go?

Make It Simple

By now you have probably used the grid iterator to iterate through cells to validate data, write values to the log, and check to see if the cells have been edited.  The same can be done with GridBuilders.  All aspects of the grid can be logged.  If done correctly, this can be copied directly from the log and pasted into excel to accomplish 2 things.  One, you have a report in Excel.  Two, you have a Smart View ready ad-hoc report that can be refreshed.

Send The Grid To The Log

Grids can be different so this may be a start for you to construct this validation.  This example has 2 column headers.  The rest should be very close and likely completely reusable.  To break this down, we have a source grid we are pulling data from to create a grid to send to another plan type.  Basically, this loops through the members in the POV and replicates rows for the number of columns in the grid.  This is repatative, but it will provide a retrievable Smart View.

// Loop through the POV to create column headers for each
povmbrs*.essbaseMbrName.each{ POV ->
  // Add a blank column for the row members
  print ','
  // Loop through the columns and repeat the POV member for each of the columns
  colmbrs[0]*.essbaseMbrName.size().times{
    print POV + ','
  } 
  // Print a line return for the next POV member
  println ''
}
//Print a blank column and then each of the column headers for both headers
println ',' + colmbrs[0]*.essbaseMbrName.join(',')
println ',' + colmbrs[1]*.essbaseMbrName.join(',')

At this point, the log will show the column headers.  The following is created while looking through the source grid and produces the row header and the respective data for each of the column headers.

...{
  sValues.add(it.crossDimCell(cMonth.toString(),cCurrency.toString()).data) 
  addcells << it.crossDimCell(cMonth.toString(),cCurrency.toString()).data
}
// After the variables are created with the numeric data to be used when creating the rows, the row is created
finGrid.addRow([acctMap.get(it.getMemberName('Account'))],addcells)
// Print to the log exactly what is being used to create the grid
println "${it.getMemberName('Account')}" + "," + sValues.join(",")

At this point, the entire Smart View is created in the log and can be copied and pasted to Excel.  The log will look something like this.

When pasted into a text editor that doesn’t wrap, it looks a little more palatable!

Create An Excel Report / Smart View Ad-hoc

At this point, you are ready to get this into Excel.  Select the part of the log related to the validation and copy it to Excel. I normally wrap the Groovy code above in a few println statements so it is easier to identify what is related to this validation effort and what isn’t.

println '*************** BEGIN VALIDATION ***************'
// CODE ABOVE
println '*************** END VALIDATION ***************'

If it doesn’t parse by comma automatically, go to the Data ribbon and select the option to convert the selection to Text to Columns and select comma.  This will parse it to what is required.  This may not need to be done depending on a few things, which won’t be discussed here.  The result of the example above looks like this.

Finishing

Now there is an easy viewable report of what is being used to create the grid.  If this data is incorrect, move backwards in the process to the source grid and fix it.  This should provide all the information to do that.  Is the POV correct?  For me, this is normally the issue – I am pulling the wrong POV.  Once the source grid POV is changed, go through the process again and you should see better results.