Error when running large report to batch

When one of my users runs a larger report (500k+ lines) to batch, they receive the following error after about 2 hours:

Excel Rendering Extension: Number of rows exceeds the maximum possible in a worksheet of this version. Rows requested: 552994. Maximum rows: 65536.

Not sure why this is happening at this point.  My SysAdmin can run the same report and batch it to the exact same location (C:\Temp on the AOS machine) and it works fine.

UPDATE:
I forgot to post the solution here!  Turns out that my user's client was automatically adding a .xls extension to the excel file name for the batch.  Once we changed that to .xlsx, everything worked peachy!

Reverse a Customer reimbursement

In AX 2012, the Customer reimbursement functionality was introduced.  Recently, my customer had done a reimbursement by accident and wanted to reverse it.  Here is one way I found to do that.

Customer setup

To use this functionality you must create a vendor and tie that vendor to the customer. Open the Customer Details and go to the Miscellaneous details fast tab and set your vendor.

Reimbursement

For this sample, I am going to make a payment, using a payment journal, against a customer for $100 which will give them a balance of -100.00 (A credit in essence). If you look at the Customer transactions, you can now see the credt of 100.00 with a balanace of -100.00.  This customer is now eligible for a reimbursement.

Accounts receivable -> Periodic -> Reimbursement

**** NOTE ****

You MUST, MUST, MUST put a customer in the query.  If you leave it blank, it will pick up all customers that have a < 0 balance!

If you have successfully created a reimbursement, you will then receive the following infolog:

Now, if you open up your Customer Transactions, you will see the original payment AND the new Reimbursement.  These are automatically settled and you now have a balance of 0.00

Next, open up the Vendor transactions for the related vendor.  You will now see the same Reimbursement on the vendor side with a balance of -100.00.

From here, you can create a Vendor payment proposal in your Vendor payment journal and cut a check for this vendor.  In our case, we want to undo this reimbursement.

One of the problems here is that we can't 'Reverse' this reimbursement transaction like we would normally do with other Vendor and Customer transactions.

This means we need to do a General Journal and manually fix it.

The first line is a Credit of $100.00 to the customer which is the same thing we did for the customer payment.  The offset account will be the bank account originally used.

The second line is a Debit of $100.00 to the vendor with the same offset account. At this point you can also settle the vendor Reimbursement transaction to this Vendor journal line by using Functions -> Settlement. Post.

If you didn't settle in the Journal, then go to Vendor open transactions and settle the 2 $100 transactions against each other.

Now, take a look at our Customer transactions.  We have the extra $100 from the Journal that return us to a $-100.00 balance. This could now be applied to a customer invoice.

Also, look at your Vendor Transactions.  You are now settled back to 0.00.

SSRS Data or calculated fields with type 'Void' are not supported

I keep running into this error with SSRS reports.  First it was on a custom report which eventually went away.  Now, it is on the PSAProjInvoice report.

I have seen issues that show a similar error that says 'Record' instead of 'Void'.  That would usually indicate that a method is returning an entire 'Record' which is not allowed.

I have noticed that this may very well have something to do with the PSAProjInvoiceDP class.

Still looking into this...

UPDATE:

This is now on another custom report again.  Still no solution.  Leaning towards the DP class but can't be sure.

FINAL UPDATE:

This appears to have been a problem with my local install of VS 2010.  Not exactly sure what was causing it, however, now that I have cleaned my hard drive and re-installed, all appears to be working great!

FINAL UPDATE (For reals)

I don't think this is an issue of my local install of VS 2010.  The SSRS Repor dataset used a field list in it's 'Select' rather than a 'Select *'.  I changed it to a 'Select *' and everything is now working perfectly.

LOL UPDATE:
This issue is far from over.  I moved everything to one layer (CUS) which has made getting around this easier.  I delete my CUS layer, refresh dataset on the report, then restore CUS layer (TFS).  That allows me to make changes.  However, it will come back the next time I work on the report.  No idea what is causing this other than my custom fields.

SQL Error on insert_recordset

Today, I found myself working on the LedgerTransListDate report.  Basically, it's a list of voucher transactions grouped by date.

My customer needed two fields added from the GeneralJournalEntry table; createdBy and createdDateTime.

In looking at the code I found I need to add some assignments in the 'while (queryRun.next())' section AND in the insert_recordset section.

When I tested it, I recieved the following SQL error:

SQL error description: [Microsoft][SQL Server Native Client 10.0][SQL Server]The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

This didn't make any sense because I made sure to add my 2 fields on the field list AND on the select statement (in this case the 'join' of GeneralJournalEntry to GeneralJournalAccountEntry)

Included in the info log from AX, I found that the insert field list contained 3 custom fields instead of the 2 I had specified:

...,MYFIELD_CREATEDBY,MYFIELD_CREATEDDATETIME,MYFIELD_CREATEDDATETIMETZID,...

The last one was added somewhere, I'm just not sure where yet...

Stay tuned...

Update:
This is a 'flaw' (possibly) with using insert_recordset.  You can't use UTCDateTime fields with this functionality.

SSRS and Array Fields

I recently had a requirement to show amounts throughout different months of the year.  Originally, I only was showing 3 months, however, the requirement changed and all 12 months were then required.

This led me to try using and EDT with the Array Elements set up. Very easy on the AX side, however, I wasn't sure how SSRS would handle it.  I added the new array field to my report table and refreshed my dataset in SSRS and POOF!  It worked great.

SSRS sees the array numbering as 0-11 rather than 1-12 like AX.

Pleasantly surprised!

Although, I have noticed that no values are coming through to the report.

Also, to clarify, when using code I think ax uses[0-11], however, the EDT in the AOT appears to use 1-12.

SSRS is acting funny... It doesn't put MyArrayField[1] into the corresponding datasat SSRS Field MyArrayField1

https://community.dynamics.com/ax/f/33/t/177552

DIXF timeout issues

DIXF - Interesting find

I spent the past 2 weeks doing data migration and here are some interesting errors I found and how to fix them:

Several issues can be seen with large amounts of records (more than 200k - 300k) and medium amounts of records (around 200k) with a lot of data.
Here is the error:

System.ServiceModel.CommunicationException: An error occurred while receiving the HTTP response to http://myServer:7000/DMFService/DMFServiceHelper.svc. This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down). See server logs for more details. ---> System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host 
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) 
--- End of inner exception stack trace --- 
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) 
at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size) 
at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead) 
--- End of inner exception stack trace --- 
at System.Net.HttpWebRequest.GetResponse() 
at System.ServiceModel.Channels.HttpChannelFactory`1.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout) 
--- End of inner exception stack trace --- 

Server stack trace: 
at System.ServiceModel.Channels.HttpChannelUtilities.ProcessGetResponseWebException(WebException webException, HttpWebRequest request, HttpAbortReason abortReason) 
at System.ServiceModel.Channels.HttpChannelFactory`1.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout) 
at System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout) 
at System.ServiceModel.Channels.ClientReliableChannelBinder`1.RequestClientReliableChannelBinder`1.OnRequest(TRequestChannel channel, Message message, TimeSpan timeout, MaskingMode maskingMode) 
at System.ServiceModel.Channels.ClientReliableChannelBinder`1.Request(Message message, TimeSpan timeout, MaskingMode maskingMode) 
at System.ServiceModel.Security.SecuritySessionClientSettings`1.SecurityRequestSessionChannel.Request(Message message, TimeSpan timeout) 
at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) 
at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) 
at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message) 

Exception rethrown at [0]: 
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) 
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) 
at Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy.DMFServiceReference.ServiceContract.ShowPreview(DMFEntity entity) 
at Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy.DmfEntityProxy.DoWork[T](Func`1 work)


We solve this by adding 'recieveTimeout' and 'sendTimeout' into the C:\Program Files\Microsoft Dynamics AX\60\DataImportExportFramework/Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.exe.config

<bindings>

      <wsHttpBinding>

        <binding name="DMFService_WsHttpBinding" receiveTimeout="01:00:00"

sendTimeout="01:00:00"

          maxBufferPoolSize="2147483647" maxReceivedMessageSize="2147483647">

          <readerQuotas maxDepth="2147483647" maxStringContentLength="2147483647"

            maxArrayLength="2147483647" maxBytesPerRead="2147483647"             maxNameTableCharCount="2147483647" />

        </binding>

      </wsHttpBinding>

    </bindings>

Check if user has access set up on a menu item

Recently I was working on a project where I needed to check if the user had security access to a Menu Item.  The menu item wasn't being used, but it had the security access that I wanted to check.

Very simply, I was able to use this:

Global::hasMenuItemAccess(...);

Worked great!