Use hierarchies in PivotTables

Recall from my previous post Create a hierarchy in PowerPivot Data Model, We will continue from where we left our earlier post.

Now we have a Sports hierarchy and Locations hierarchy, we can add them to PivotTables or Power View, and quickly get results that include useful groupings of data.

Prior to creating hierarchies, you had to add individual fields to the PivotTable, and arrange those fields how you wanted them to be viewed.

In this post we will use the hierarchies created in the previous post to quickly refine our PivotTable.

Open the Excel workbook which we used in our previous post and Insert PivotTable as shown in below screen.

• Add field in the PivotTable Medal from Medal Table in the FILTERS area, and Count of Medal from Medal Table in the VALUES area. Your nearly empty PivotTable should look like the following screen.

• From the PivotTable Fields area, drag SDE from the Events table to the ROWS area. Then drag Locations from the Hosts table into the COLUMNS area. Just by dragging those two hierarchies, your PivotTable is populated with a data, all of which is arranged in the hierarchy we defined in the previous steps. Your screen should look like the following screen.

• You can expand any of those Sports in the PivotTable, which is the top level of the SDE hierarchy, and see information in the next level down in the hierarchy (discipline). If a lower level in the hierarchy exists for that discipline, you can expand the discipline to see its events. You can do the same for the Location hierarchy, the top level of which is Season, which shows up as Summer and Winter in the PivotTable.

By dragging those two hierarchies, you quickly created a PivotTable with interesting and structured data that you can drill into, filter, and arrange.

I will come up with more details and other options on this topic in my upcoming posts. Till then keep learning and practicing.

How to Use the Jet Report Scheduler

Overview

The Jet Scheduler is a powerful tool that allows users to schedule reports to be automatically run by the Windows Task Scheduler. The user can also control where the output file is saved to, if the report should be emailed once it has been generated, and the output format of the report.

Creating a New Scheduled Task

To set up a scheduled report, open the report in Excel, click the Jet ribbon, and click the Schedule button.



Click the New Task... button to schedule a new task.



The Scheduled Task window will now appear.

Reports Tab



The Reports tab contains general information about the scheduled task.

• Task Name: This represents the name of the task as it will appear in the Scheduled Task window to the user.


• Run All Reports in a Folder: This button will enable the user to schedule all Jet Reports in a full to be run


• Run a Single Report: This button will enable the user to schedule a single Jet Report to be run


• Input: This represents the folder and file name of the report that will be run


• Output: This represents the folder and file name of where the finished report will be saved

Schedule Tab



This tab will define the frequency of how often the report will be run as well as if the task is currently disabled and if the report should be run when the user is logged off.

The available options for the frequency are:

• Once: The report will only be run one time


• Daily: The report will be run every day (it is possible to set on the next tab the number of days to wait between runs)


• Weekly: The report will be run every week (it is possible to set on the next tab the days of the week for the report to be run on)


• Monthly: The report will be run every month (it is possible to set on the next tab the months for the report to run on and the days of the month for the report to be run on)


• When Idle: The report will run every time that the computer goes into idle mode


• At Startup: The report will be run each time that the computer is turned on


• At Logon: The report will be run each time that the user logs on to the computer

Frequency Tab



The name of this tab will change depending on the frequency specified on the Schedule tab.

In the screenshot above a Weekly frequency has been specified.

• Start Date and Time: This represents the first date that the report will run and the time that it will run for this and subsequent schedules


• Weeks between report runs: This specifies how many weeks the Scheduler will wait between report runs before running the report again


• Days to run report: This represents the days of the week that the report is scheduled to run on

Email Tab

The Email tab allows the user to define who the report will be sent to if emailing is desired.

• Send email: If this box is checked it will enable the report to be emailed to recipients


• Mail properties: This dropdown will allow the user to specify whether the report will be emailed using Outlook or a generic SMTP protocol. SMTP must be configured in the Jet Essentials Application Settings in order for it to be used.


• Message: This allows the user to specify a custom subject or body to be sent as part of the email


• Attach report to email: If this box is checked the report will be attached to the email. If the box is unchecked the report will not be attached. This can be used as a type of notification when used in conjunction with the subject and body of the email to allow a user to know that the report has been run


• Recipients: Email addresses will be specified here for all recipients of the report. Email addresses should be separated by a semi-colon.


• Get recipients from Excel Named Range: It is possible to also define the email addresses in the report itself and then assign an Excel named range to the cell(s). If there are named ranges in the report then they will appear in the dropdown below the checkbox. The Output tab allows the user to define how the file should be saved once it is finished running. This tab also enabled the user to turn on logging to troubleshoot errors with the Scheduler process as well as use Batch File Generation for the reports.

Output Tab
The Output tab allows the user to define how the file should be saved once it is finished running. This tab also enabled the user to turn on logging to troubleshoot errors with the Scheduler process as well as use Batch File Generation for the reports.

• Output Format: This dropdown list will allow the user to define the format of the finished report. The available options are:
  
  
  
  • Jet Workbook: This will save the report as a normal Excel file with all Jet Reports functions still in the report
  
  
  • Values Only Workbook: This will save the report as an Excel file with all Jet Reports functions removed. The recipient would not be able to refresh the report as it will be a static Excel file
  
  
  • Web Page: This will save the report as a HTML file with a single sheet. This should be used when there is a single sheet in the report
  
  
  • Web Page by Sheet: This will save the report as a HTML file with multiple sheets embedded in it. This should be used when there are multiple sheets in a report
  
  
  • PDF: This will save the report as a PDF file (if your version of Excel supports that ability - Excel 2007 and higher)
  
  
  
  

Remain tuned for more details, I will come up with more details and features in my upcoming posts.

Configuring Web Services to Use SSL (SOAP and OData)

Secure Sockets Layer (SSL) is a cryptographic protocol that helps provide security and data integrity for data communications over a network.

By encrypting your Microsoft Dynamics NAV web services using SSL, you make your data and the network more secure and more reliable.

Microsoft Dynamics NAV Web Services and SSL

Microsoft Dynamics NAV supports SSL authentication for SOAP and OData web services.

The server authenticates itself to the client, but the client does not authenticate itself to the server.

When the web service client connects to the Microsoft Dynamics NAV Server instance, the server replies by sending its digital certificate to the client. This certificate contains the server's public encryption key and the name of the authority that granted the certificate. The client verifies the certificate using the authority's public key.

We have covered above top related to Creating and Implementing Certificates for Microsoft Dynamics NAV Server. If not seen please follow below links for better understanding:

How to create a root CA and a private key file by using the makecert.exe utility

How to create certificate revocation list for the root certification authority

How to create and install a test certificate for the Microsoft Dynamics NAV Server computer

How to grant access to the certificate’s private key to the service account for the Microsoft Dynamics NAV Server

Implementing Security Certificates.

Configuring Microsoft Dynamics NAV Web Services to Use SSL

The first step is to prepare Microsoft Dynamics NAV to use SSL. This involves configuring the relevant Microsoft Dynamics NAV Server instance to specify SSL.

The following procedure uses Microsoft Dynamics NAV Server Administration tool to configure the Microsoft Dynamics NAV Server instance.

• Choose the Start button, and in the Search programs and files box, type Microsoft Dynamics NAV Administration, and then choose the related link.


• In Microsoft Dynamics NAV Server Administration tool, in the left pane, under Console Root, expand the node for the Microsoft Dynamics NAV Server computer. This is typically named Microsoft Dynamics NAV (Local), which is the local computer.

The Microsoft Dynamics NAV Server instances on the computer appear in the left pane and center panes.

In the left pane, choose the relevant instance to display settings for that instance in the center pane.

• At the bottom of the center pane, choose Edit.


• To configure SOAP web services to use SSL, expand the SOAP Web Services tab, and then select the Enable SSL

Make a note of the port that is used by SOAP web services. You will need the port number later on in this walkthrough.

• To configure OData web services to use SSL, expand the OData Web Services tab, and then select the Enable SSL

Make a note of the port that is used by OData web services. You will need the port number later on in this walkthrough

• Choose Save at the bottom of the center pane.


• In the right pane, under Actions, choose Login Account.


• Make a note of the service account information. You will need this information later on in this walkthrough.

Configuring the Access Control List and the Web Services Ports for SSL

An access control list (ACL) is part of the Windows security infrastructure and features. The ACL controls who can access resources on a computer.

Note

These procedures use the netsh tool (netsh.exe) for configuring the HTTP server. The netsh tool is supplied with Windows 7 and Windows Server 2008.

By default the netsh tool is located in the c:\Windows\System32 folder.

To configure the ACL

1. On the computer running Microsoft Dynamics NAV Server, open a command prompt as an administrator as follows:

1. From the Start menu, choose All Programs, and then choose Accessories.


2. Right-click Command Prompt, and then choose Run as administrator.

2. At the command prompt, type the following command to change to the Windows\System32 directory.

cd\Windows\System32

3. To view the ACL for the web services ports to determine if any entries are already using the relevant host name and port, type the following command:

netsh http show urlacl

4. Each entry is listed by the Reserved URL field, which has the format http://hostname:port. hostname is the name of the computer running the service and port is the port number the service runs on. A '+' (plus sign) in the Reserved URL field represents localhost, which indicates the computer that you are working on.


5. By default, SOAP and OData web services in Microsoft Dynamics NAV use ports 7047 and 7048, respectively, and connect to a Microsoft Dynamics NAV Server instance named DynamicsNAV80. The default reserved URL entries for these services are: http://+:7047/DynamicsNAV80/ and http://+:7048/DynamicsNAV80/.


6. You must delete any entries that use the same port as the SOAP or OData web services.

To delete an entry, type the following command:

netsh http delete urlacl url=http://hostname:Port/NAVserver/

Substitute Port with the port number of the SOAP or OData web service and NAVserver with the name of the Microsoft Dynamics NAV Server instance.

For example, to delete the default entries for SOAP and OData, use the following two commands:

netsh http delete urlacl url=http://+:7047/DynamicsNAV80/

netsh http delete urlacl url=http://+:7048/DynamicsNAV80/

7. To register the ports for the SOAP and OData web service with https, type the following command for each service:

netsh http add urlacl url=https://hostname:port/navserver user=DOMAIN\user

8. Substitute the following options with the proper values:

Option	Description
hostname	The name of the computer running Microsoft Dynamics NAV Server. Use + for localhost.
port	The port that is used by the web service.
navserver	The name of the Microsoft Dynamics NAV Server instance to use with the web service. The default is DynamicsNAV80.
DOMAIN\username	The domain and user name of the service account for Microsoft Dynamics NAV Server. If the service account for Microsoft Dynamics NAV Server is Network Service, then use "NT AUTHORITY\NETWORK SERVICE".

9. For example, if the service account for Microsoft Dynamics NAV Server has the domain ABC and the user name xyz, and then the command for the SOAP web service is as follows:

netsh http add urlacl url=https://+:7047/DynamicsNAV80 user="NT AUTHORITY\NETWORK SERVICE"

10. If the service account for Microsoft Dynamics NAV Server is Network Service, then the command is as follows:

netsh http add urlacl url=https://+:7047/DynamicsNAV80 user="NT AUTHORITY\NETWORK SERVICE"

11. To verify that your port has been registered, repeat step 3.

To configure the port to use the SSL certificate

1. At the command prompt, type the following command to view the current port configurations:

netsh http show sslcert

2. All port bindings to SSL certificates are listed. Each entry is indicated by the IP:port field, where IP is the IP address that uses the port (0.0.0.0 indicates all IP addresses) and port is the port number.


3. If SSL is already configured on the address and port that you want to use for SOAP or OData web services, then use the following command to delete the entry:

netsh http delete ssl ipport=ipaddress:port

4. Substitute ipaddress and port with the IP address and port number of the entry that you want to delete.


5. For example, to delete the entry on the default SOAP port, use the following command:

netsh http delete ssl ipport=0.0.0.0:7047

6. To bind an SSL certificate to the SOAP or OData web service port, use the following command:

netsh http add sslcert ipport=ipaddress:port certhash=thumbprint appid={id}

7. Substitute the following options with the proper values:

Option	Description
ipaddress	The IP address that can use the web service. Use 0.0.0.0 to include all IP addresses.
port	The port that is used by the web service.
thumbprint	The certificate thumbprint that you obtained in previous post.
id	A 32-digit hexadecimal number that identifies the Microsoft Dynamics NAV application. The id must be in the format {NNNNNNNN-NNNN-NNNN-NNNN-NNNNNNNNNNNN). If you do not know the appid, then use any value, such as {00112233-4455-6677-8899-AABBCCDDEEFF}.

8. For example, the following command binds a certificate that has the thumbprint c0d0f27095b03d4317e219841024328cef248779 to port 7047 for all IP addresses:

netsh http add sslcert ipport=0.0.0.0:7047 certhash=c0d0f27095b03d4317e219841024328cef248779 appid={00112233-4455-6677-8899-AABBCCDDEEFF}

Imp Note: - Please replace the port and thumbprint values with your valid values.

Restart the Microsoft Dynamics NAV Server Instance

You can use the Microsoft Dynamics NAV Server Administration Tool to restart the Microsoft Dynamics NAV Server instance.

To restart Microsoft Dynamics NAV Server

1. In Microsoft Dynamics NAV Server Administration tool, in the left pane, under Console Root, choose the Microsoft Dynamics NAV Server computer.


2. In the center pane, choose the Microsoft Dynamics NAV Server instance that you have configured, and then in the right pane under Actions, choose Restart.


3. After the instance restarts, close Microsoft Dynamics NAV Server Administration tool.

Verifying the Configuration

You should now be able to use web services that are encrypted with SSL. To verify this, type the following URL in the address bar for your browser.

https://localhost:7047/DynamicsNAV80/WS/services

The page lists any web services that have been published.