AX 2009 Ad Hoc vs Self-Service reporting

What is ad hoc reporting? Click HERE. lol. Enjoy that!
'Ad Hoc' (or self-service) capabilities basically means allowing the users to manipulate or create something on the fly. Self service reporting and Ad Hoc reporting are one and the same.

Some people will argue that there are subtle differences between ad hoc and self service but that is not true. They can be used synonymously. Don't let the BI (Business Intelligence) jive trip you up.

It's pretty common to hear customers stressing the importance of Ad Hoc Reporting or the ability to create Ad Hoc Queries. It's one of the buzz words you'll hear throughout the projects. Why? The information needed for end users change from day to day as businesses grow and new requirements are created. Or requirements exist for information analysis that is not possible at the moment.

Can you blame users for wanting a robust way to get the information they need without the reliance on anyone else? I can't. Who wants to get on the phone with a guy from tech to explain that you want a field added/removed/moved from a report. Or that you need a new report made that doesn't exist and then wait at least 3 months before you ever get one only to find out it is filled with bugs (my reports never have those...).

A vital component of future ERPs will definitely be these abilities and the ease of users to get the information they need.

Microsoft has done a great job in its progression from AX 4.0 to AX 2009 (exporting to excel with a single click, SSRS Reporting, SSAS) to AX 2012 (can't give details yet but lets just say you better get used to SSRS).

I won't get into specifics of what AX's capabilities are but just wanted to make a post about how there are no differences between ad hoc and self service reporting. If you want to know more about ad hoc and BI, check out this site: Reporting & BI in AX: An Overview.

Hate the tag, not the coder. Marquee 4 life!

How to create a 'Do not tell me again' info box

In AX, there is an Ok/Cancel box that can pop up to the user but have the option to allow the user to not be shown that popup in the future. An example of this would be opening the User's Options within AX.
This feature could be used for something like providing valuable tips to the user when they first log into AX or a form/module.
This is easily accomplished using AX's Box function:
Box::okCancelOnceModal(_captionStr, _headerStr, _infoStr, _ownerStr)
This call does not return a DialogButton enum like the other OkCancel prompts. It will instead return a boolean.
//Sample Code
if (this.checkUserQueryCriteria)
{
     return Box::okCancelOnceModal('Checking query','@SYS70764', 'This could take a while...', 'Fun Times Example');
}
else
{
     return true;

}

_captionStr - The text that appears at the very top of the dialog box. This could be something like 'Did you know that...' in the example of providing useful tips to the users. Or informating them that a feature has changed in a new version of something.

_headerStr - The text that appears just to the right of the 'Info' bubble in the white section of the pop up window.

_infoStr - The text that appears just above the 'Do not show this again' text.

_ownerStr - this is the text that will appear in the Usage Data's 'More info...' field with a 'System name' field value of BoxinfoOnce.

USERFUL TIP: You can access the usage data by going to the Options form (Dynamics AX Menu -> Tools -> 'Options'), then clicked the 'Usage data' button. Under here, find the appropriate place where the call is located. In AX 2009, it is under the 'Jobs' tab with a 'System name' of 'BoxInfoOnce'. Yours will be the one with the 'More Info' you put in the string (in my code example it is 'Fun Times Example'). By highlighting the record, you can delete it so that it will prompt the user again.

/*-------------!UPDATE!-------------*/
I was recently asked 'Why would you use this?'. Fair question. There are many reasons but I'll cover two common ones.

Scenario 1: Let's assume that we are prompting a user with a query where they will enter criteria. They have the possibility of making the returned results considerably larger than they intended. We could do some kind of validation on the input (like check for a range) and if it meets some kind of criteria, prompt the user letting them know that the result set could be very large and would they like to continue. To some, this would be a great check they would like to consistently be reminded of. To others, it is one more click. Using this box would allow both parties to be happy.

Scenerio 2: You want to provide the user with helpful hints or information upon opening something. It may be a little 'Welcome' introduction or revolving tips that change over time that people may not want to see again. Its not for us to decide so we give the users the option to skip it.

Using UTCDateTime Variables in Select Statements

Sometimes, you need to be able to compare a date with a UTC Date Time variable. This is how you use it in a select statement. This statement looks for sales table records that were created on 12/7/2010.
The trick to this is that the function dateToBeginUTCDateTime() is not visible to the users through the normal right click method finding technique. You just have to remember what it is and know how to use it. I always forget the name so this is my personal reminder for the next time I inevitably forget.

SalesTable salesTable;
Date daxDate = str2date('12/7/2010', 2);
TimeZone daxTimeZone = DateTimeUtil::getUserPreferredTimeZone();
UTCDateTime daxDateTime = dateToBeginUTCDateTime(daxDate,daxTimeZone);
;
while select salesTable
     where salesTable.createdDateTime == daxDateTime
{
     info (strFmt("%1 - %2", salesTable.SalesId, salesTable.CustAccount));
}

or, you can replace the daxDateTime variable with a one-liner like this:
datetobeginUtcDateTime(str2date('12/7/2010',2),DateTimeUtil::getUserPreferredTimeZone())

Update AIF service with new field on a table

If you added a field to a base table that utilizes the AIF (aka new field on SalesTable), there are a few steps that are needed to successfully do this. This blog entry assumes that the webservice is already deployed and setup. We will use the Sales Order web service that comes out of the box.

NOTE: Make sure this procedure is done in the proper layer! AKA don't do it in the USR layer.

Step 1: Update the document service (Figure 1 below).


Figure 1 - Dynamics AX -> Tools -> Development Tools -> Application Object Framework -> 'Update Document Service'

Step 2: Select the service class name that will need to be updated (Figure 2 below). The class name will end with '…Service'. Any other class will give an error saying that the class is not a valid service class. Check the two supporting classes options in this feature.

Figure 2 – Updating the Sales Order webservice

Step 3: There may be a number of errors in one of the web services classes (mine was the sales table header class). There seemed to be a problem generating the macros used in the code. I just added them to the class dec of that class and moved on.

Step 4: Regenerate the webservice


Figure 3 – Regenerate the Sales Order webservice

Step 5: Enable the Action Data Policy fields for the endpoints that are currently using this webservice. Since the data policies are governed on an Endpoint by Endpoint level, this is where these new fields will need to be turned off/on. This can be accessed by going to

-Basic -> Setup -> Application Integration Framework

-Select appropriate endpoint id

-Click 'Action policies' button

-Select appropriate Action policy

-Click 'Data policies' button.

-Enable new fields

Once these steps are complete, the web service's schema will allow for additional fields to be changed to allow these new fields to be integrated into the application.

NOTE: The underlying class may need to be deleted in order for things like string size to properly update in the Schema.

Enjoy!

Change the text for when you hover over an AX field - ToolTip

Sometimes there is a need to change the text that is display when a user hovers over a field in an AX form. By default (OOTB), the information that displays is defaulted from the table relations. This is decided in the kernel. If you change the information in the title fields for that table, you will see the automatically generated tooltip information change.

The automatic information will follow the format '[Field Name]: [main tables's first title field], [main table's second title field]'. For example, hover over the item number for a sales order's sales lines. The tool tip should read 'Item number: [InventTable.ItemId], [InventTable.InventName]'. Mine reads 'Item number: 10001, Yellow Basket' but yours will be different. Hovering over the line's unit of measure (salesLine.SalesUnit) will yield 'Unit: [Unit.UnitId], [Unit.Txt]'. Mine reads 'Unit: Each, Each'.

Assuming you don't want to see this. There is an override on the form's field (actually at the field level) called 'toolTip'. Definition: Tooltip (also known as intotip) – a common graphical user interface element which is used in conjunction with a cursor, usually a mouse pointer. http://en.wikipedia.org/wiki/Tooltip

This method's super() returns a string that is formed by grabbing the table relation title fields discussed earlier. All that is needed is to replace the string that is returned from the super and there you have it! Using the strFmt('%1, %2...'), variable1, variable2, ...));, function, you can put any number of variables in here to display. I have not seen a limit... But too much would look dumb so I'd try to keep it to no more than 4.

Field in AX table is visible in AOT but not in Table browser or SQL

Question: What's going on if there is a field in an AX table that can be seen in the AOT under the table and in the intellisense, but not in the table browser or SQL tables?

Answer: This is probably due to licensing. The system you are on may not have the appropriate license to use those fields.

The following is an example for you to follow along with and confirm if needed. Lets use the 'standardPalletQuantity' field on the table InventTable:

• Navigate out to the table in the AOT. Find the field (e.g. standardPalletQuantity), and look at the properties for the ExtendedDataType/Base Enum.
  
• We will then navigate to the appropriate node for that EDT/Enum (e.g. WMSStandardPalletQuantity).
  
• Look to see if it has a value in the ConfigurationKey property. If it has one (e.g. WMSPallet), we will want to navigate out to that configuration key in the AOT (AOT\Data Dictionary\Configuration Keys).
  
• Look to see if the key has a value in the ParentKey property. If it does, we will want to navigate to that configuration key (e.g. WMSAdvanced). This key will be the one we want to look at if it has no value in the ParentKey property. If needed, keep going until you find the top most parent key.
  
• The label for the parent configuration key (ParentKey) is 'Warehouse Management II'.
  
• Go to Administration -> Setup -> System -> 'License information' -> 'Modules' tab.
  
• Once there, look for the label from the parent key in the 'Code description' field. If there is not a set of '*****' in the 'License code' field or the 'Status' is not 'Ok', that is the answer.

If all the license keys looked ok, I would do the exercise above one more time to make sure I didn't get off track. If still unsuccessful, open a ticket with Microsoft. You may need to re-import your license keys as something may have gone wrong in the original import.

Clear GroupBy on a query for a form

While working on a form, I recently needed to group by Customer account but then use the actual, non-grouped query in a different location. To remove a group by field on a query, find the form's datasource that has the group by and use the following line of code replacing 'FormDataSource' with the target datasource.

FormDataSource_ds.query().ClearGroupBy;