Function in Jet Reports to Get List into a String

Jet ReportsWhile working on a Jet Report for a client recently, working with data from Microsoft Dynamics 365 Business Central, I had need to return the output of an NL Filter into another function which required a string rather than a list, which meant that I couldn’t use the NL Filter directly.

Fortunately, there is a What in the NP Jet function which allows you to exactly this.

My NL Filter was in cell D4 which I can reference in the Array field of the NP function; the third field to set is the Join String which is the character which will separate the joined items from the list; you need to place the double quotes around the join string yourself:

Jet Function Wizard with a What of Join, Array of $D4 and Join String of "|"

Continue reading “Function in Jet Reports to Get List into a String”

Create a Calculated Field in the Jet Reports Table Builder

Jet ReportsI’ve recently been creating some reports for a client using Jet Reports and data pulled from Microsoft Dynamics 365 Business Central. One of the requirements was to have a report which pulled out Intrastat related information in a specific format. As well as pulling out the standard information, they also wanted a calculation of the net weight for a sales order line, which mean linking from the Purch. Inv. Line (123) to the Item (27) table and multiplying the quantity by the net weight.

I initially tried to do this using a calculated field in the Excel PivotTable which was added on top of the data returned by Jet Reports, but quickly realised that the result wasn’t correct. This [prompted me to go back to the source data and see if I could perform the calculation using Jet Reports as it got the data rather than afterwards.

The report was being built using the Table Builder tool of Jet Reports; to add a calculated field in Table Builder, click the Add Formula button in the bottom left:

Table Builder with the Add Formula button ringed

Continue reading “Create a Calculated Field in the Jet Reports Table Builder”

Add Calculated Field to PivotTable in Microsoft Excel

Microsoft OfficeClients often think that I know more Excel than I do, because I know how to use some functions such as IF, CONCAT and more, but my knowledge of Excel is quite limited when it comes to charts, graphs and PivotTables. I was recently writing a report for a client using Jet Reports to pull Intrastat information from Microsoft Dynamics 365 Business Central (both Jet and Business Central are irrelevant as far as the detail fo this post are concerned).

One of the things I wanted to do was create a summary of the data pulled from Business Central, which meant using a standard Excel PivotTable, something with which I have almost no experience. I managed to get most of the PivotTable configured without issue, but I needed to perform a calculation within the PivotTable and it took me a while to work out.

This is done by adding a calculated field through the PivotTable Analyze tab on the action bar; click the Fields, Items & Sets » Calculated Field button:

Excel showing the Calculated Field button on the PivotTable Analyze tab

Continue reading “Add Calculated Field to PivotTable in Microsoft Excel”

Upcoming Microsoft Dynamics 365 Business Central Knowledge Hub Webinars (Mar-May 2024) from MADIC dynamics

MADIC dynamicsEvery month, on the third Tuesday, at MADIC dynamics we will be presenting a Knowledge Hub Webinar on Microsoft Dynamics 365 Business Central and related products; I will be presenting the first few webinars, but in future other consultants will be getting involved, so there will be a variety of presenters through time.

We aim to have the next three monthly Knowledge Hub Webinars scheduled and details of them available. The next three webinars, starting next month, are:

Upcoming Microsoft Dynamics 365 Business Central Knowledge Hub Webinars (Mar-May 2024) from MADIC dynamics

Extending Business Central with Power Automate

Tuesday, 19th March 2024 – 14:00-14:45

Learn how Business Central can be extended with Power Automate, from approvals to process automation to document distribution.

Register Here

Upcoming Microsoft Dynamics 365 Business Central Knowledge Hub Webinars (Mar-May 2024) from MADIC dynamics

Jet Reports & Business Central

Tuesday, 16th April 2024 – 14:00-14:45

Learn how Jet Reports can be used to increase your financial and operational reporting capabilities from Business Central.

Register Here

Upcoming Microsoft Dynamics 365 Business Central Knowledge Hub Webinars (Mar-May 2024) from MADIC dynamics

What’s New in Business Central 2024 Wave 1

Monday, 20th May 2024 – 14:00-14:45

Discover the upcoming new features in Microsoft Dynamics 365 Business Central 2024 Wave 1.

Register Here

MADIC dynamics Knowledge Hub Webinars: Reporting with Business Central

MADIC dynamicsIn our most recent webinar here at MADIC dynamics, we took a look at Reporting with Business Central.

If you want to catch up on this webinar, you can do so here.

  1. Introduction
  2. List Pages
  3. Standard Reports
  4. Financial Accounting
  5. Jet Reports
  6. Jet Analytics
  7. Power BI
  8. 3rd Party BI
  9. Conclusion

Introduction ^

In this webinar we took an introductory look at the reporting tools which come with Microsoft Dynamics 365 Business Central as standard, such as list pages, standard reports and financial reporting, as well as then taking a look at the commonly used third-party reporting tools which include Jet Reports and Power BI.

Continue reading “MADIC dynamics Knowledge Hub Webinars: Reporting with Business Central”

Upcoming Microsoft Dynamics 365 Business Central Knowledge Hub Webinars (Feb-Apr 2024) from MADIC dynamics

MADIC dynamicsEvery month, on the third Tuesday, at MADIC dynamics we will be presenting a Knowledge Hub Webinar on Microsoft Dynamics 365 Business Central and related products; I will be presenting the first few webinars, but in future other consultants will be getting involved, so there will be a variety of presenters through time.

We aim to have the next three monthly Knowledge Hub Webinars scheduled and details of them available; the webinars are open to everyone, not just customers of MADIC dynamics. The next three webinars, starting this month, are:

No february-2024 events found.

Upcoming Microsoft Dynamics 365 Business Central Knowledge Hub Webinars (Feb-Apr 2024) from MADIC dynamics

Extending Business Central with Power Automate

Tuesday, 19th March 2024 – 14:00-14:45

Learn how Business Central can be extended with Power Automate, from approvals to process automation to document distribution.

Register Here

Upcoming Microsoft Dynamics 365 Business Central Knowledge Hub Webinars (Feb-Apr 2024) from MADIC dynamics

Jet Reports & Business Central

Tuesday, 16th April 2024 – 14:00-14:45

Learn how Jet Reports can be used to increase your financial and operational reporting capabilities from Business Central.

Register Here

New Book on Jet Reports with Microsoft Dynamics 365 Business Central Available

Microsoft Dynamics 365 Business CentralI have a new book available now called Jet Reports with Microsoft Dynamics 365 Business Central; this book is aimed at end-users or consultants looking to use Jet Reports with Microsoft Dynamics 365 Business Central for financial and/or operational reporting.

The book covers the basics of installing and configuring Jet Reports as well as how to design and build reports using the Excel Add-in, before moving on to step-by-step guides on creating six different types of report and closing with a look at the report building tools, Jet Hub and report distribution.

Jet Reports with Microsoft Dynamics 365 Business Central by Ian Grieve

The book is available as an eBook direct from my azurecurve Publishing site (always with a discount of $5, but this month with an additional discount of 15% using voucher code JulyWithBCJuly) or from Amazon:

The book is structured into these chapters:

  • Chapter 1, Introduction to Jet Reports, introduces Jet Reports and explains what type of reporting for which it can be used.
  • Chapter 2, Implementing Jet Reports, covers the installation of Jet Reports including the Jet Service Tier and Jet Hub.
  • Chapter 3, Configuring Jet Reports, shows how to configure Jet Reports once it has been installed.
  • Chapter 4, Introducing the Excel Add-in, introduces the Excel Add-in and the features available.
  • Chapter 5, Report Structure, shows how reports can be structured to make maintaining them easier.
  • Chapter 6, Using the functions in the Excel Add-in, shows how each of the four Jet Functions can be used.
  • Chapter 7, Creating and using an Options page, covers the creation and use of an options page in reports.
  • Chapter 8, Create a Summary Trial Balance, steps through the creation of a Summary Trial Balance report.
  • Chapter 9, Create a Detailed Trial Balance, shows how to make the Summary TB from the previous chapter into a Detailed report.
  • Chapter 10, Create a Balance Sheet, covers the creation of a formatted Balance Sheet.
  • Chapter 11, Create an Income Statement, covers the creation of an Income Statement and how to make it an intercompany/consolidated version.
  • Chapter 12, Create a Summary Payables Aged Trial Balance, shows how to create a Summary Payables Trial Balance report.
  • Chapter 13, Create a Sales Invoice by Customer Report, covers the creation of an operational report on sales invoices.
  • Chapter 14, Report Creation Tools, introduces the tools which can be used to make creating reports easier.
  • Chapter 15, Using Jet Hub, shows how users can access Jet Reports via web access and without a local install of Jet Reports.
  • Chapter 16, Report Distribution, shows how users can schedule or generate reports into Microsoft Word.

If you buy the ebook from me directly, the coupon code JetWithBCJuly will get you 15% off the price of this book (offer available until the end of July 2023).

New book on Jet Reports with Microsoft Dynamics 365 Business Central available

Microsoft Dynamics 365 Business CentralI have a new book available now called Jet Reports with Microsoft Dynamics 365 Business Central; this book is aimed at end-users or consultants looking to use Jet Reports with Microsoft Dynamics 365 Business Central for financial and/or operational reporting.

The book covers the basics of installing and configuring Jet Reports as well as how to design and build reports using the Excel Add-in, before moving on to step-by-step guides on creating six different types of report and closing with a look at the report building tools, Jet Hub and report distribution.

Jet Reports with Microsoft Dynamics 365 Business Central by Ian Grieve

The book is available as an eBook direct from my azurecurve Publishing site or from Amazon (for $5 more):

The book is structured into these chapters:

  • Chapter 1, Introduction to Jet Reports, introduces Jet Reports and explains what type of reporting for which it can be used.
  • Chapter 2, Implementing Jet Reports, covers the installation of Jet Reports including the Jet Service Tier and Jet Hub.
  • Chapter 3, Configuring Jet Reports, shows how to configure Jet Reports once it has been installed.
  • Chapter 4, Introducing the Excel Add-in, introduces the Excel Add-in and the features available.
  • Chapter 5, Report Structure, shows how reports can be structured to make maintaining them easier.
  • Chapter 6, Using the functions in the Excel Add-in, shows how each of the four Jet Functions can be used.
  • Chapter 7, Creating and using an Options page, covers the creation and use of an options page in reports.
  • Chapter 8, Create a Summary Trial Balance, steps through the creation of a Summary Trial Balance report.
  • Chapter 9, Create a Detailed Trial Balance, shows how to make the Summary TB from the previous chapter into a Detailed report.
  • Chapter 10, Create a Balance Sheet, covers the creation of a formatted Balance Sheet.
  • Chapter 11, Create an Income Statement, covers the creation of an Income Statement and how to make it an intercompany/consolidated version.
  • Chapter 12, Create a Summary Payables Aged Trial Balance, shows how to create a Summary Payables Trial Balance report.
  • Chapter 13, Create a Sales Invoice by Customer Report, covers the creation of an operational report on sales invoices.
  • Chapter 14, Report Creation Tools, introduces the tools which can be used to make creating reports easier.
  • Chapter 15, Using Jet Hub, shows how users can access Jet Reports via web access and without a local install of Jet Reports.
  • Chapter 16, Report Distribution, shows how users can schedule or generate reports into Microsoft Word.

If you buy the ebook from me directly, the coupon code JetWithBCLaunch will get you 20% off the price of this book (offer available until the end of June 2023).

Urgent Resolution to a Permissions Issue With Jet Reports and Jet Analytics When Used With Microsoft Dynamics 365 Business Central Cloud Data Connector

Microsoft Dynamics 365 Business Centralinsightsoftware have issued an urgent notification of a permissions issue with Jet Reports and Jet Analytics when used with Microsoft Dynamics 365 Business Central cloud data connector. An error is produced when the user tries to use either of the Jet products.

The issue is that Microsoft have made a change today to deprecate a permission on the Jet app registrations called Azure AD Graph. The previous app was published as Jet Global Data Technologies; a new one called insightsoftware has been created with the correct API permissions.

To pick up the new permissions and resolve the issue, all users need to do is log out and back in (Excel » Jet » Settings » Data Source Settings » Authentication » Log Out » Log In), accepting the permission request when prompted.

Jet Analytics users will be automatically prompted to log back in and they will not get an error, however, they will also need to accept the new permissions request.

If the users receive a message saying that admin approval is needed, the Azure Active Directory Administrator needs to provide Administrator consent for the new Jet Reports App in order for users to sign in against it.

If needed the Azure Active Directory Administrator consent is needed, this is done by logging into Business Central via Jet Reports by going to Settings » Data Source Settings » Authentication » Log in; they will get an option to consent to the application being used in the tenant.

Manually Enable Jet Reports on a Terminal Server

Jet ReportsI’ve done some work with a client recently who has been a long time user of Jet Reports; they’ve recently merged with another company and needed some of the Jet Reports amended and deployed for the new companies which were added to Dynamics GP. The infrastructure and implementation decisions used by Jet Reports were made well before my involvement and, in some cases, they go against the recommendations of the vendor.

There are two key issues which we encountered rolling Jet Reports out to the new companies. The client does not use the Jet Service Tier which allows you to roll out settings to users from a central service and they have Jet Reports available to users as a published app rather than shared desktop.

The former item means a setup of connections on each client and the latter means this is not a supported configuration ( as mentioned in this article on the Jet Reports knowledge base Jet Reports can only be supported on a shared desktop).

This is something which needs to be discussed with the client, as they should really be operating using a supported method. However, the short term issue is how to get Jet reports available to users.

Usually you’d do this by using the Enable Jet Add-in app on the Windows start menu or launching Jet using the Jet Reports app. In this case, as a published app, the end-user does not have access to the Enable Jet Add-in app and the IT department was resistant to publishing the Jet Reports app.

Instead we were able to get Jet Reports enabled in Excel by adding the add-in manually to Excel.

We did this by launching Excel from the Start menu and selecting (File » options). In the Options window select the Add-ins tab and click the GoManage Excel Add-ins:

Excel Options window

Continue reading “Manually Enable Jet Reports on a Terminal Server”