Management Reporter Size Limitation

Over the past couple of weeks, I have been working with a client on a series of odd Management Reporter issues.  The first issue they reported happened when they were working with trees.  The user would add a branch to a tree in MR, and then enter a series of dimension codes for that branch.  Randomly, it seemed, the dimensions code would not save on some branches of the tree.  Odd.  The tree branch/unit would be there, but the dimension field would be blank.

So, we spent some time one afternoon trying to figure out some sort of common thread with the disappearing dimensions.  It seemed like different combinations of dimensions would cause the issue.  After talking with Microsoft, it was determined that the issue is a 512 character limitation on the dimension field in the tree.  So, when you add multiple dimensions to a tree branch/unit, it may add up to 512 pretty quickly since MR includes the dimension name for each dimension you include.

I know that this may be working as designed, but this seems like something that many users would encounter over time-- particularly those with large/long account strings.

As a workaround, I suggested that the client used dimension sets on the branches of the tree instead.  And this worked great, except that there also appears to be a size limitation on the dimension set as well.  So if the user tries to add too many dimension combinations (apparently also variable based on the dimension name and other characters included in the string) it also causes a series of errors.  In this client's case, they would get one of the following errors:

• Object is currently in use elsewhere
• Column ‘ParentAccountSetID, LinkIndex’ is constrained to be unique.  Value ‘8e1f4448-f5c1-461a-b898-f7c7cc572bf2,23’ is already present

In both cases, it would force Management Reporter to close and the building block would then be checked out to the user.  The second error message above is possibly a quality report (per Microsoft) and is being analyzed, it does appear to be caused by some sort of limitation on the size of dimension set.

I know that both of these issues may seem to minor for a lot of clients.  But as I have recently been working on MR reports for a Dynamics AX client with a large account string and long chart of accounts, I do worry that we might start to hit up against these sort of issues more frequently.  The workarounds that seem to be available for now include:

• Keeping dimension sets small
• Using dimension sets instead of including large lists of dimensions on a tree
• Using ranges and/or wildcards wherever possible to decrease the number of characters used
• Use shorter names for your segments, so that they take up less space when repeated in a dimension list

Will keep you posted as I hear back on the status of both of these items.  And, yes, I still love Management Reporter :)

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Helpful Microsoft Forecaster Scripts

I know I have been quite absent from the blogosphere lately, but I hope that Steve has been keeping everyone entertained (or at least keeping you in deep thought over complex technical issues).  I wish I could say that I was on a boat in the middle of the ocean with not a care in the world, or sitting on a beach somewhere with a frosty beverage in hand.  But, alas, that is not the case.  Sadly, my father passed away last week after an 8-month battle with pancreatic cancer.  Cancer sucks.

But, on to something helpful regarding Forecaster.  When working with a large chart of accounts, it can be helpful to use SQL scripts to compare your line sets with your segment setup, or check to see if every department has a line set assigned as an override.  Here are a few scripts I have used to help with these sorts of reviews.

--Identify the assignable segment values (Setup>>Segments) that do not have an override line set defined (Setup-Budgets-Input Set-Overrides tab), does not include assignable segments defined as summary departments.  Note that depending on the position of your assignable segment in your segment definition, you may need to modify this to use a different M_SEG table (e.g., M_SEG2).


select M_SEG1.ID, M_SEG1.Label from M_SEG1 where M_SEG1.ID not in (Select Z_INP_SEG2.SEG1 from Z_INP_SEG2) and M_SEG1.LN=-1


--Identify accounts in line sets (Build-Lines) that are not set up in the master (Setup-Segments).  This is helpful when you have created line sets by copying and pasting, rather than using the wizard and/or inserting from segments. Note that depending on the position of your main/reversable segment in your segment definition, you may need to modify this to use a different M_SEG table.

select Z_LIN.LINE, Z_LIN.CODE from Z_LIN where Z_LIN.CODE not in (select M_SEG0.ID from M_SEG0)

--Here is the opposite of the script above, this one identifies accounts that are in the account master (Setup-Segments) but doesn't exist in a line set (Build-Lines) to ensure you caught everything.  Same note about the M_SEG table applies here as well.

select M_SEG0.ID, M_SEG0.LABEL from M_SEG0 where M_SEG0.ID not in (select Z_LIN.CODE from Z_LIN)

I am looking forward to getting back in to the swing of things here on the blog, and I hope everyone is enjoying their holiday season!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Decisions Fall 2011 On Demand Presentations Now Available

In case you missed the excellent MS Dynamics World Decisions Fall 2011 virtual conference, all of the presentations are now available for replay on demand:

http://decisions.msdynamicsworld.com/

There are 12 Dynamics GP presentations available, plus presentations for AX, NAV, and CRM!


Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

GP Developer Tip: List All Parameters for a SQL Server Stored Procedure

Occasionally I work on a Dynamics GP integration where I have to call a Dynamics GP stored procedure from a .NET application.  As always, there are several ways to do this, but I typically use one of two approaches.

In situations where the procedure returns one or more values in OUTPUT parameters, I usually like to call the stored procedure directly in a procedure (rather than rely on a separate call to a data access class).  In this case, I use the very, very cool DeriveParameters method:

gpCommand = sqlConn.CreateCommand();
gpCommand.CommandType = CommandType.StoredProcedure;
gpCommand.CommandText = "zDP_PM10200SI";

//Get all of the parameters from the stored procedureSystem.Data.SqlClient.SqlCommandBuilder.DeriveParameters(gpCommand);

When you call DeriveParameters, .NET interrogates the stored procedure and builds a list of all of the parameter objects for you.  This saves ALOT of typing, and ensures that all of the parameters are properly declared.

On the other hand, when I don't need to get return values from a stored procedure, I use my data access class, which requires an array of SqlParameter objects that includes the parameter name and data type.  In this case, I have to assemble the array and assign the SqlParameters.

SqlParameter[] sqlParameters = new SqlParameter[29];
sqlParameters[0] = new SqlParameter("@EMPLOYID", System.Data.SqlDbType.VarChar, 15);
sqlParameters[1] = new SqlParameter("@ID_Accrual_Code", System.Data.SqlDbType.VarChar, 11);
sqlParameters[2] = new SqlParameter("@ID_Accrual_Code_2", System.Data.SqlDbType.VarChar, 11);

In this case I need the parameter name and data type.  Some stored procedures are simple and only have a few parameters.  But there are a few that have dozens of parameters, which makes this type of coding a tedious chore.

So make this process easier, I wanted to get a list of all of the stored procedure parameters.

It turns out that this is pretty easy--just use sp_help:

sp_help zDP_PM10200SI

This provides a very nice parameter listing, with data types, that can be copied and pasted into Excel or text editor, and then easily converted into code.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com