Advertisements

Gaining the Competitive Advantage with BI

See how a robust business intelligence solution can help you leverage technology in order to gain new visibility on your business that will enable profitable, data-driven decisions on a daily basis.

Video-1

 

Video-2

 
Advertisements

Financial Statements and Data Warehousing (Jet Reports)

Learn how a data warehouse makes creating financial statements from multiple systems fast and easy by storing all of your data in a single place that is optimized for end user reporting.

Video-1

Video-2

Leveraging Business Intelligence for Ad Hoc Analysis

Leveraging Business Intelligence for Ad Hoc Analysis

Video-1

 

Video-2

 

BI Advantage (“Reporting” and “Cubes”) Jet Reports

“Reporting” and “Cubes”: The Best of Both Worlds
Video-1

Video-2

BI Advantage (Reporting to Business Intelligence) Jet Reports

Graduating from Reporting to Business Intelligence

Video-1

Video-2

BI Advantage (The 5 Minute Dashboard ) Jet Reports

The 5 Minute Dashboard – It’s Easier than You Think

Video-1

Video-2

BI Advantage (Web Based Dashboards ) Jet Reports

Web Based Dashboards Provide Insight from Anywhere

Video-1

Video-2

Using GL Function in Jet Reports

Syntax: =GL (What,Arg1,Arg2,Arg3,..,Arg22)

Purpose: Returns the budget, balance, net change, quantity, debits or credits of the G/L Account of a given company based on filters.

Dynamics NAV Parameter Description
 What NAV: Determines what the GL Function returns. Options are Balance, Budget, Quantity, Credits or Debits Note that the options available for the What argument depend on the Where argument.
 Account NAV: G/L Account Number, Filter or Range. If you specify a single, totaling account, you will get totals. If you specify multiple accounts or a range of accounts, totaling accounts will not be included in the returned number even if the other account(s) have nothing to do with the specified totaling account(s). If the Where argument is “Rows”, “Columns”, or “Sheets”, then the What options are “Accounts” which will give a list of account numbers, “Categories” which will give a list of account category numbers, or “SegX” where X is a segment number and which gives a list of that specific account segment.
StartDate NAV: Specifies the starting date of transactions to include. If you are interested in the balance of an account on a given date, leave StartDate blank. If you are interested in the net change of an account, use Balance and specify both the StartDate and EndDate
 EndDate NAV: Specifies the ending date of transactions to include. Specifying a start period and an end period will give you the net change between the first day of the start period and the last day of the end period. Specifying a start period with no end period will give you the net change between that start date and the present. Specifying no start period will give you the balance/budget as of the end period. Specifying no start period or end period will give you the present balance/budget.
 View NAV: The G/L Analysis View to use. Leave this blank to use balances from the G/L directly. Analysis Views are available in Navision version 3 and later. This field should be blank if you are using objects from an earlier version of Navision.
 Dim1 NAV: Filter for the first dimension of the analysis view. If View is blank, this is the filter for Global Dimension 1. Dimension totaling is handled the same way as Account totaling. In Navision versions before 3.0, Dim1 is used as the Department filter.
 Dim2 NAV: Filter for the second dimension of the analysis view. If View is blank, this is the filter for Global Dimension 2. In versions before 3, this is the Project filter.
 Dim3 NAV: Filter for the third dimension of the analysis view.
 Dim4 NAV: Filter for the fourth dimension of the analysis view.
 BusinessUnit NAV: Filter for the business unit.
 Budget NAV: Budget filter. This is unused unless returning budgets.
 Company NAV: Company Name. This must be spelled the same as it appears in Navision, including case, spaces and punctuation. If this parameter is empty (“”), the default company in the Jet Reports Options/Data Sources Screen is used.
 Reserved NAV: Blank. For backwards compatibility, a Data Source Name as defined in Jet/Options can be used.
 Reserved GP: Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.
 Reserved GP: Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.
 Reserved   GP: Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.
 Reserved GP: Specifies the budget filter, blank for all budgets. Note that budgets are associated with a specific year in Great Plains so if your budget and fiscal year filters do not coincide you will get a 0 value.
 ExcludeClose NAV: “True” to exclude closing date transactions. Defaults to “False”.
 ShowQuery NAV: “True” to show the finhlink string that will be used for drilldown. Defaults to “False”.
 Reserved GP: Company name. If this parameter is blank, the default company is used.
 Data Source Data source name. If this parameter is blank, the default data source is used.

Reports based on the G/L are easy with the GL function.

=GL(What, Account, StartDate, EndDate, View, Dim1, Dim2, Dim3, Dim4, BusinessUnit, Company, Reserved, ExcludeClose, Reserved, Reserved, Reserved, Reserved, Reserved, Reserved, ShowQuery, Reserved, DataSource)

NAV Cronus Examples

To retrieve the balance of G/L account 44100, you would type the following.

=GL(“Balance”,”44100″)

If you wanted to know the net change of account 44100 between 1/1/2002 and 1/31/2002, you would type the following.

=GL(“Balance”,”44100″,”1/1/02″,”1/31/02″)

For G/L Balances with standard NAV:

  • you can filter on the two Global Dimensions for G/L Balances or
  • if using a NAV Analysis View, you can filter on up to 4 dimensions that are tied to that View=GL(“Balance”,”40100″,,,,”USA”,”COPPER”) 
  • Please note that some NAV verticals that allow more than 2 Global Dimensions may not be compatible with the GL function. 
  • For the balance of account “40100” with Global Dimension 1 of “USA” and Global Dim 2 of “COPPER”, you can use the following function.

Stay tuned for how to use GL Function in Jet Reports.

I will come up with more details on this in my upcoming posts.

Using NP Function in Jet Reports

Syntax: =NP (What, Arg1, Arg2,…,Arg22)

Purpose: Does various utility functions documented below.

Let’s see what options are available in below table:

What Description/Parameter
“Eval” Evaluate the formula in the Arg1 parameter. The formula must be enclosed in quotes and will be evaluated when the report refreshes.
“DateFilter” Calculates a date filter using the start date and end date specified in the Arg1 and Arg2 parameters.
“Union” Returns (in the form of a Jet-specific list) the Union of two arrays specified in the Arg1 and Arg2 parameters. Note that in versions of Jet Essentials 2015 and earlier, if NP(“Union”) is by itself in a cell, it will only return the first value from the array. For those versions, you must put it inside an NL(“Rows”) in order to correctly return all the data.
“Integers” Returns a string that can be used to generate integers using a Replicator, where Arg1 is the start number and Arg2 is the end number.
“Intersect” Returns (in the form of a Jet-specific list) the intersection of two arrays specified in the Arg1 and Arg2 parameters. Note that in versions of Jet Essentials 2015 and earlier, if NP(“Intersect”) is by itself in a cell, it will only return the first value from the array. For those versions, you must put it inside an NL(“Rows”) in order to correctly return all the data.
“Difference” Returns (in the form of a Jet-specific list) the difference of two arrays specified in the Arg1 and Arg2 parameters. Note that if NP(“Difference”) is by itself in a cell, it will only return the first value from the array. You must put it inside an NL(“Rows”) in order to correctly return all the data.
“Format” Formats an expression with a specific Excel formatting string.  Arg1 is the expression to format such as a date or cell reference, and Arg2 is the Excel formatting string such as “YYYY/MM/DD” for a date formatted with a 4-digit year then a 2-digit month and 2-digit day.
“Join” Joins the elements of the array specified in Arg1 together into a single string separated by the contents of Arg2.
“Split” In versions of Jet Essentials 2015 Update 1 and higher, this function splits the string in Arg1 into a Jet-specific list.
In earlier versions of Jet Essentials, this function splits the string in Arg1 into an array of values. The splitting is delimited by the contents of Arg2. Note that if NP(“Split”) is by itself in a cell, it will only return the first value from the array. You must put it inside an NL(“Rows”) in order to correctly return all the data.
“Codeunit” Evaluates and returns the value returned by the Dynamics NAV code unit function.
“Companies” Returns a list of the companies associated with a data source. Arg1 is a company filter such as A* to return all companies that start with the letter A. Leaving Arg1 blank will return all companies. Arg2 is the data source. Leaving Arg2 blank will return companies from the current data source. Note that you should reference the result of this function in the table argument of an NL replicator function to actually list them out in Excel.
“Dates” Returns a string that can be used to generate dates using a Replicator, where:
Arg1 is the start date
Arg2 is the end date.
Arg3 can be used to specify a period type of Day, Week, Month, Quarter, or Year.  Default is Day.
Arg4 can be set to “True” in order to return the end of each period.  Default is “False”.
“DataSources” Returns an array containing the current user’s Jet data sources.
“Formula” Evaluates the Excel formula contained within Arg1.
“Slicer” Returns an Excel Slicer in Arg1 that can be used as a filter in Jet functions when using a Cube data source.

EVAL

To increase performance, you can reduce cross-sheet references. The following NP evaluates the formula in cell of D5 from a worksheet called Options. =NP(“Eval”,”=Options!$D$5″)

This function is executed once on refreshing the report, rather than for every cell update. =NP(“Eval”,”=Today()”)

Performance can also be increased by not using volatile functions.

DATEFILTER

Results of using the NP(DateFilter) function, which can then be nested in other functions.
NP-1
INTEGERS

This NP(Integers) function will create rows with the numbers 1 through 10. =NL(“Rows”,NP(“Integers”,1,10))

JOIN

The following NP(Join) joins the strings from an array and creates the result “100|200|300|400” for potential use in another function. =NP(“Join”,{“100″,”200″,”300″,”400″},”|”)

SPLIT

The following NP(Split) splits up the string “this|is|an|array” and creates the array {this, is, an, array}. =NP(“Split”, “this|is|an|array”, “|”)

COMPANIES

The following NP(Companies) function lists all the companies for the current data source in rows. =NL(“Rows”,NP(“Companies”))

DATES

The use of NP(Dates) to create a set of column headers for a report. (Dates can also be placed in reverse order by putting the later date in first)
NP-2
DATASOURCES

This NP(DataSources) function will return a list of the data sources in use on the machine it is run on. =NL(“Rows”,NP(“Datasources”))

FORMULA

Used in conjunction with the NL(Table) function to define a calculated column in the table definition. For example: To determine available credit for a customer; if cell E6 contains the credit limit, and cell F6 contains the open credit, then =NP(“Formula”,”=E6-F6″) would be put in the field list of the NL(Table) definition

SLICER

The Slicer function works in conjunction with pivot tables and dashboards to provide information for filters when refreshing reports.
NP-3
Array Calculations

Arrays are lists of data values. You can obtain a string representing such a list from Jet using “Filter” as the What parameter in an NL function. The values in arrays returned by Jet are guaranteed to be unique. The resulting array might be a list of Customers or a list of Invoice Document numbers or any other list of data that match a set of filters. The array calculation operations of the NP function allow you to find different combinations of two arrays.

An example of when you would need an array calculation is listing the invoice document numbers where either the Type on an Invoice Line is “Item” for all item numbers, or the Type is “G/L Account” and the account number is 300. Both the Item numbers and the G/L Account numbers are stored in the same “No.” field, so there is no single set of filters that will create this list of document numbers.

The array operations available in the NP function are “Difference”, “Union” and “Intersect”. The difference between two arrays consists of all of the elements that are in the first array but are not in the second. The union of two arrays consists of a single copy of all of the elements in both arrays with any duplicates eliminated. The intersection of two arrays is the set of elements that are common to both arrays. An example of the results of the array operations are listed in the table below.

Array 1 {100, 200, 300, 400, 500} Array 2 {400, 500, 900, 1000, 2000}
Difference {100, 200, 300}
Union {100, 200, 300, 400, 500, 900, 1000, 2000}
Intersect {400, 500}

=NL(“Rows”, NP(“Union”, NL(“Filter”,”Customer”,”No.”,”Name”,”A*”), NL(“Filter”,”Customer”,”No.”,”Name”,”B*”)))

=NL(“Rows”,”Customer”,”No.”,”Name”,”A*|B*”)

The following formula creates a list down rows of the document numbers of all invoices where either the Type field is “Item”, or it is “G/L Account” and the No. field is 2000.

=NL(“Rows”, NP(“Union”, NL(“Filter”,”Sales Invoice Line”,”Document No.”,”Type”,”Item”), NL(“Filter”,”Sales Invoice Line”,”Document No.”,”Type”,”G/L Account”,”No.”,”2000″)))

You should be cautious using arrays because they are often not the easiest or fastest way to solve a problem. Example 1 is a good example of a query that does not require arrays, and will run much slower if you use them. Also remember that, with Jet Essentials 2015 and earlier, if NP(“Union”), NP(“Intersect”), or NP(“Difference”) are by themselves in a cell they will only return the first value from the array. You must put them inside NL(“Rows”) as in the examples above in order to correctly return all the data.

There are two more array operations that behave a bit differently than those listed above: “Split” and “Join”. “Split” takes two text strings and splits the first string based on the second, resulting in an array. For instance, if you wanted to create a list of account numbers based on the string “1000+2000+3000”, the formula would look like the following.

=NP(“Split”,”1000+2000+3000″,”+”)

The result would be the array {“1000″,”2000″,”3000”}. Note that this must be put inside an NL(“Rows”) as in the Union examples above in order to return all the data.

In the opposite scenario, if you have an array but would like to create a text string by joining each element of that array separated by a given string, you would use the “Join” operation. Using the same array, you can create a string for a filter with array values separated by the “|” character with the following formula.

=NP(“Join”,{“1000″,”2000″,”3000″},”|”)

The result would be the text string “1000|2000|3000”, which is a valid filter that you could pass into an NL function.

For Join and Split, Arg1 of the NP function is the value you want to manipulate and Arg2 is the character by which you want to join or split the value. If you experiment with these operations, you will find that you have an amazing amount of flexibility, especially when you use them in conjunction with the other array calculation formulas listed above.

Please note that the results of an NP(“Join”) may be very large and thus putting it directly inside another function may cause problems with Excels 256 character formula limit as in the following formula.

=NL(“Rows”,NP(“Split”,NP(“Join”,{“some”,”array”,”here”},”|”),”|”))

It is recommended that in a situation like this the NP(“Join”) be placed in a separate cell as in the following.

B2: =NP(“Join”,{“some”,”array”,”here”},”|”)

B3: =NL(“Rows”,NP(“Split”,B2,”|”),”|”))

Stay tuned for usage of NP functions in Jet Reports.

I will come up with more details in my upcoming posts.

Previous Older Entries

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: