Power Query Access to Manage 2000 Data

Power Query is a common import tool used by both Power BI and Excel 2016+.

From either Power BI or Excel, Get Data dialogs allow you to import data from many different sources.  Among the options is Web. Get Data Web automatically detects JSON formatted results from REST endpoints and starts a Power Query editor session.

From the Power Query editor session a click on the Convert To Table button starts building the M language script which will transform the JSON data package into tables suitable for driving Power BI or Excel.  From there clicking on column expand icons and selecting columns continues to build the transform script.

To see the full script select the Advanced Editor.

This script will automatically be applied anytime the data is refreshed from the source.

Data relations and formats can be refined in Power BI Desktop.

Report visualization can then be assembled by simple drag and drop selection.

In order to plug Power Query directly into Manage 2000 a new JSON Service named GetData and a new PWS function named JSON.PACKAGE.MAINT have been added to release 8.1. JSON.PACKAGE.MAINT defines named selections of data. The specification for the selection is in the form a UniQuery SELECT statement with &var& replacement variables using the MSO replacement engine.  So relative time frames can be defined like 'Process_Date GE "&T-7&"'.

The JSON packages can be picked up at the /MT/JSONServices endpoint using the GetData service.

So if you are looking for a way to directly plug Manage 2000 data into Power BI or Excel check out the new JSON packages. (dev patch 40126 for 8.1 sp2 and 8.1 sp3)

Embedding Power BI Reports in Manage 2000 News Feeds

Power BI provides shareable visualizations mashed up from multiple data sources.  The data might come from Excel or SQL or ODBC.  For instance Manage 2000 data might be synced through Novo Motion Outbound to SQL server. From there Power BI might refresh reports and dashboards.

Users can consume the report through the Power BI interface.  But it is also very easy to embed Power BI reports in Manage 2000 news articles for display in the Manage 2000 function NewsReader or other RSS feed readers.

From the Power BI Service browser interface:

Select a report

Select File

Select Embed

Copy the iframe content

Paste it into a STATIC article Summary (HTML) prompt

(Recent updates to Power BI have made this a little more round about;

 After copying the iframe snippet into the Summary (HTML) prompt -

   copy the link to the app.powerbi.com website from the Secure embed code dialog to the clipboard

   use the Edit button on the right to bring the Summary (HTML) text up in the editor

   select the string inside the quotes on the right side of the "src" attribute

   paste in the app.powerbi.com url string

   save the contents back to the Summary (HTML) prompt - 8/19/2019)

(Even more recent updates corrected this hiccup.)

Copy the link to the app.powerbi.com website

Paste it into the Drill Back Document prompt
(Clicking on the news article link will send the user to the drill back URL, in this case the report in the Power BI Service. If related detail such as the original Manage 2000 data is available from another URL use that instead.)

Attach the article to a news feed and view from the Newsreader function:

Selecting a row in the airport table refocuses the map to display that specific information. The interactive features of the report remain active when viewed in a Manage 2000 newsfeed.

Manage 2000 8.1 service pack 3 - sneak peek

Development cutoff is fast approaching for service pack 3 of Manage 2000 8.1. We will be cleaning up the service pack material throughout February in preparation for an early March release.

Once again a number of projects are being released along with bug fixes in this development rich service pack.  Here are my top ten favorite new features:

1. Collapsible Panel Navigation in over 40 web functions provides better use of screen territory and improved mobile experience.
2. MSO support in web functions provides improved web function tailoring capabilities. MSO displays and web functions may be configured to be available without customizing using the same MSO techniques used in PWS workflow configuration.
3. Local ‘Portal’ menus are now scoped to browser tab. This allows multiple browser tabs to display different portals or the same portal with different keys without the local menus interfering with each other.
4. ProductConfigurator supports alternate front-end screens from CTO.ENTRY. This allows ProductConfigurator to use a simplified version of the screens used in CTO.ENTRY.  Sales Reps may then do preliminary configurations in ProductConfigurator and then back end configuration specialists can reconfigure the generated item in CTO.ENTRY.
5. Summary Notes Option added to attachment notes menu on web pages.  This feature matches the PWS notes summary display available in PWS.
6. Global Menu: Home and Logoff buttons are easily accessible no matter how menus are configured.
7. WEB.SETTINGS.INQ - Displays the web preference hierarchy settings for one or more contacts, so if your web settings hierarchy has grown into a ball of spaghetti you can get it sorted out.
8. Customer Administrator role allows delegation of add, change, or deactivation of other contacts. CMADM role users may use UserPreference | ContactMaint to maintain other users from their company.
9. Visual Studio 2017 compatibility updates to website vbproj files.
10. Visual Studio Wizard Updates.
1. Collapsible Panel generation in BTO Inquiry and BTO Posting wizards.
2. BTO Posting wizard support for templated columns with roiTextBox controls bound to metadata for improved data entry features.

So keep an eye open for an upcoming official announcement and after you load up this new service pack find out more about these and other new features in the FEATURE.SUMMARY document.