Tag: Report

Convert to PDF, Development Tips, Excel, How To, Information, Office Integration, Tip & Tricks, Word

Appending other information to Navision Reports

Ashwini Tripathi

There is always requirement from many customers to Append information to the Navision Reports.

Recently one of my reader have requested me for any such functionality which can help him Append information from Word or Excel Files to Navision Reports.

While searching for solution for such requirement i came across this scope if we can do the same from outside Navision integrating the functionality with Navision.

Although i am not sure how much this post will answere to the requirement raised by my this reader but still this can be an work around to this requirement.

In my previous 2 posts i have answered this approach, Let us assemble all of them to provide solution to this requirement.

Please see my privious posts, i will not repeat already covered part in this post. Here is the link to those posts:

Converting Office Files into PDF from Navision

Merging PDF Files in Navision

In this post i will be using these functions discussed in above posts.

The Same Setup will be applicable to this post too.

PDFConvMerge-2-2

Since we are appending our other information to the Navision Report so we will be at First Step saving our Customer Sales Invoice to PDF.

Later some information which need to be appended to this report is in Word File the path of which is stored on Customer Card.

How to work with Converting Office file to PDF is already discussed in previous post Link shared above.

So Now we have Our Report in PDF and Additional information to be appended to Report is also in PDF. Now it will be easy for us to Merge both the File to have single PDF File.

How to Merge these files is also already discussed in my previous post. Link shared above.

Let us see how we can assemble above both functionality to get our required result.

Approach is very simple, i have created a button on Sales Invoice to Print the Report which performs above discussed process.

PDFConvMerge-5

On Sales Invoice Page I have added one Button which calls my Function to Print & Merge the document, it takes Invoice No & Sell-to Customer No. as a parameter.

PDFConvMerge-6

I have created a function to Save the output of Report to PDF, using standard functionality available in Navision.

PDFConvMerge-7

The PrintSalesInvoice function called from Sales Invoice Page with Invoice No & Sell-to Customer No.

This Function calls the SaveReportAsPDF explained above.

Converts the Office file that is attached on Custommer Card, discussed in previous post Link provided above.

Calls the MergePDFFiles, discussed in previous post Link provided above.

So Now at single click of Button entire task is done for us.

To keep it simple no additional checks and other routine tasks have been included like after task is completed clean up of all temporary files and moving the Result file to desired Location etc.

Now you know the logic so you can tweak the code accordingly to get the functionality working as per your requirement.

Thats all for this post. I will come up with more information in my up coming posts.

You keep you requirements posting to me, i will try my best to answer your queries in form of my future posts, at earliest possible from my end.

Till then kee exploring & Learning.

Advertisement
Corfu Navision 2016, Development Tips, How To, Information, Report, Tip & Tricks

Controlling User wise Report Print & Save As… Permission

Ashwini Tripathi

 

It is always asked from several users to have a control on Printing of reports from Preview.

Normally you have two options to print the Report in Navision.

  • From Request Page

PrintPreview1

  • From Print Preview

PrintPreview2

If you check document Reports you will notice that you don’t have option to Print the Report from Preview, but you can print from Request Page.

Why it doesn’t allow in such Reports?

It is because these report uses statement like:

IF NOT CurrReport.PREVIEW THEN

If the C/AL code for the report uses the PREVIEW function to disable printing from the preview, then users cannot change the display mode to print layout.

Using these option I have tried to disable Printing of Report from Request Page and Print Preview.

PrintPreview3

 

Add a Boolean type Field in User Setup Table & Page as shown above.

Now the report which we want to restrict from printing we will add piece of code.

Although there are other ways to do this but to keep it simple I have followed this approach. Once you are ok with logic you can implement the same in your way, in fact more better way than this sample demo.

PrintPreview4

If I run the report by setting Allow Report Printing to FALSE, it will not show me the Print & Save as Option on Preview.

PrintPreview5

Also if I Try to Print or Save As… from Request Page it will Flag me an error as above.

So now you know the logic behind it, what you are waiting for. Give your user to control the Printing & Save As… option.

Now in last Cumulative Updates also Microsoft added option to control this,  but it is at Service Level, if you want to control at user Level this is one option, wait for any further Cumulative Update in which Microsoft may add this capability or explore and find any other option and share with others.

That’s all for this post. I will come up with more details in my upcoming posts.

Till then keep exploring & learning.

 

 

Development Tips, How To, Information, Report, Tip & Tricks

Open Report Design in Visual Studio from Custom Report Layouts

Ashwini Tripathi

You may have noticed that when we try to edit Report Layout from Custom Report Layouts Page, it always open in Word or Report Builder no direct option to edit it in Visual Studio.

This requirement leaded to the necessity of the below customization, It can be done other ways also but the easiest way to do it one which i am going to discuss in my today’s post. Hope you will like it.

CustRepLayout-1

I have provided with the option where user can select the desired Editor either he wants to open it in Report Builder or Visual Studio.

To achieve this i have added one field in Table 9650 – Custom Report Layout with desired options as shown below.

CustRepLayout-2

And made this field available in Page Custom Report Layouts – 9650 as shown below.

CustRepLayout-3

Now in Codeunit 9652 – Edit RDLC Report Layout I have replaced the code shown in Yellow with the code shown in Red box.

CustRepLayout-4

CustRepLayout-5

Thats all, what you are waiting for go-ahead and design the reports in your way.

I will come up with more other topics in my upcomming posts, till then keep practicing and learning.

 

Data Model, Excel, How To, PivotTable, Power View, PowerPivot

Create a Power View report

Ashwini Tripathi

In the previous post, we created an Excel workbook with a PivotTable containing data about Olympic medals and events. If you didn’t saw the previous post you can access from here.

Use hierarchies in PivotTables

In this post, we will create a Power View report to visually represent the Olympics data.

In Excel, click INSERT > Reports > Power View Reports.
ExcelData-22

ExcelData-23

ExcelData-24
Select City from Host & Sport from Medal Table. Apply Count (Not Blank) to Sport Field using dropdown list next to Field Name.
ExcelData-25
From design switch the Visualization to Map.

On the map, blue circles of varying size indicate the number of different sport events held at each Olympic Host location.
ExcelData-26
Drag Season Field to Color area. This will add different colors for different Seasons. In our case summer/Winter Red/Blue colors.

Just in few clicks now we have a Power View report that visualizes the number of sporting events in various locations, using a map, color-coded based on season.

Will come with more details in my upcoming posts, till then stay tuned, keep learning and practicing.

Development Tips, Excel, How To, Office Integration, PowerPivot, Report

Creating My First Report using PowerPivot

Ashwini Tripathi

In my earlier posts we have created the Data model for Analysis.

If you missed them you can follow below links to refresh all information.

PowerPivot for Excel

PowerPivot Creating a Data Model in Excel 2013

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Add relationships to Data Model in PowerPivot

How to add Filter for data retrieval in PowerPivot Data model.

Create a calculated column in PowerPivot

We have learned basic actions, let’s create our first Report.

I will be creating Pivot Matrix Customer Vs Item Sales.

Open the Excel in which we created our Data model and imported our table data.
PowerPivot-21
Select PivotTable From Ribbon under Home Tab.
PowerPivot-22

Select New Worksheet or Existing Worksheet as per the case.
PowerPivot-23
You will see a Pivot is inserted, in right side you will find all the tables available in the Data model of this Worksheet.
PowerPivot-24

Design the Pivot as above screenshot.
The output of this report will be as below if executed without Date Filter.
PowerPivot-25
I will come up with more details and features of PowerPivot in my upcoming posts. Till then keep practicing and stay tuned for more details on this topic.

Development Tips, Excel, Jet Reports, Office Integration, Report

Excel – Jet Report 2015 for Navision 2015

Ashwini Tripathi

During the September month my most of the post was dedicated to Jet Reports.

There is many thing to share, which I will keep adding time to time.

For your reference here I present all the links related to this topic.

Jet Report for Excel – Navision 2015

Installing Jet Express for Excel – Navision 2015

Installing and Publishing the Jet Business Objects on the Microsoft Dynamics Server

Publishing the Jet Data Source Codeunit to the Web Service

Enable SOAP Services and identify connection parameters

Configuring a Data Source in Jet Express

Specify your Jet Interface Language

Uninstalling Jet Express

Using the Jet Ribbon Jet Essentials 2015 Update 1 for Navision 2015

Using Jet Report NL Function

Using Jet Report NF Function

Using Link in Jet Reports

Creating My First Report Using Jet Reports

Creating Simple List Report in Excel Using Jet Reports Part-1

Using NL( Lookup ) in Jet Reports Part-1

Creating Simple List Report in Excel Using Jet Reports Part-2

Using NL( Lookup ) in Jet Reports Part-2

Using NL( Lookup ) in Jet Reports Part-3

Using NP Function in Jet Reports

Using GL Function in Jet Reports

Creating Report in Jet Using NL, NF, NP & GL & Excel Formulas

Snippets in Jet Report

How to Use the Jet Report Scheduler

Other options for creating Report in Jet

Remain tuned I will be back with some other topics soon. I am just leaving this topic as of now but will keep adding more details on Jet reports time to time.

Development Tips, Excel, How To, Jet Reports, Office Integration, Report

Other options for creating Report in Jet

Ashwini Tripathi

Jet Report

Report Wizard

An entire report can be created from a single table using the Report Wizard. The Report Wizard allows data to be grouped, filtered, sorted, subtotaled, and formated.

 

Report Builder

The Report Builder creates reports based on Jet Data Views.  Jet Data Views define table relationships, available fields, field captions, and table captions for a particular reporting area, such as sales, inventory, payroll, etc.  Jet Data Views can be created using the Data View Creator.

Before Using the Report Builder

Before you use the Report Builder you will need to import or create data views and categories.

A set of data views for Dynamics NAV can be found on Jet Web Site you can access the same from here. You can also click on the Download Data Views link within the Report Builder.

After downloading the file, you should open the .zip folder and extract the data view category (.jdc) file.

Importing Data Views and Categories

To import data views and categories, go to the Data Source Settings and select File -> Import -> Data View Categories.

 

Table Builder

An entire report can be created from a single or more table using the Report Wizard. The Table Wizard allows data to be grouped, filtered, sorted, subtotaled, and formated.

 

Browser

Provides browsing window to Select Tables and Fields, using which you can directly create an NL/ NF Function with their parameters and arguments.

 

This way I reach to end of my Jet Report Introduction Series. In future I will keep adding more details.

 

Development Tips, Excel, How To, Jet Reports, Office Integration, Report

How to Use the Jet Report Scheduler

Ashwini Tripathi

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.

JetSchedule-1

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

JetSchedule-2

The Scheduled Task window will now appear.

Reports Tab

JetSchedule-3

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

JetSchedule-4

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

JetSchedule-5

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
JetSchedule-6
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.

JetSchedule-7

  • 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.

Development Tips, Excel, How To, Jet Reports, Office Integration, Report

Snippets in Jet Report

Ashwini Tripathi

Snippets are small, reusable report parts that can be shared between Jet users.

Configuring the Snippet Folder and Sharing Snippets

Snippets are stored in the “Jet Reports Snippets” folder located in your My Documents directory.

This location can be changed in the Application Settings.  Each snippet is stored in a *.snippet file.  To share snippets, copy the snippet files from one user’s snippet directory to another’s.  Close and open the Snippets window and the newly added snippets will become available.

Snippet-1

Creating and Using a Snippet

Creating a Snippet

To create a snippet, open the Snippet tool.  Highlight the range of cells containing the piece of functionality for which to make a snippet.  Then, click the New Snippet button in the Snippet tool.

I have created a simple Report for Active Customers, which Lists all the customers where Blocked = ‘’

Snippet-2

We want to save this as a snipped so that any user can reuse it if required.

Snippet-3

  • Click the Snippet Button
  • Select the range of cells you want to save as snippet.
  • In Snippet Window click the New Snippet
  • Give the meaningful name to your Snippet.

Using a Snippet

To use a snippet, drag and drop it from the Snippets window to any cell of your workbook.  Any existing Excel formulas, text, or formatting in these cells will be overwritten.

Rename

You can rename a snippet by selecting it and pressing F2 or by right-clicking it and selecting Rename.

Delete

Delete a snippet by selecting it and then pressing the Delete button in the Snippet tool or by pressing the delete key.

Replace

You can replace the contents of the current snippet by selecting the region of the worksheet that you would like to use as the contents, selecting the snippet you wish to replace in the Snippet tool then pressing the Replace button.

Organizing Snippets

Snippets are organized in a folder structure.  Snippets can be organized into folders using drag and drop or cut/copy/paste within the Snippet tool.

Will come up with more information and other features.

Development Tips, Excel, How To, Jet Reports, Office Integration, Report

Creating Report in Jet Using NL, NF, NP & GL & Excel Formulas

Ashwini Tripathi

Dear friends today I will discuss report “General Ledger Budget to Actual by Period” and demonstrate the usage of NP & GL Functions.

This report will contain all the Functions, Commands we discussed till now and usage of NP & NL Functions.

You can refer my earlier posts for more detailed information which will help you understanding this report better, for your convenience I am providing link to previous posts which may help you understanding the terms being used in this report.

Using Jet Report NL Function

Using Jet Report NF Function

Using NL( Lookup ) in Jet Reports Part-1

Using NL( Lookup ) in Jet Reports Part-2

Using NL( Lookup ) in Jet Reports Part-3

Using NP Function in Jet Reports

Using GL Function in Jet Reports

Let’s start with creating Option Page before we start with report creation:
NPGLUsage-1

If you see in above sheet few filters are defined for the report, most of them are normal and Lookup which we have already discussed in previous report and posts.

Please be sure ‘=’ have been removed from formulas for presentation purpose, make sure you add them when use in your report.

Here new thing which we see is in E3 Cell:

=NP(“DateFilter”,StartDate,EndDate)

Here NP function creates a filter variable date filter which takes the StartDate & EndDate to create filter in Navision format like 01/01/2015..31/12/2015, which can be used in other Jet Functions as an parameter.

Option denotes these value will be asked from user when report is executed.

Lookup provides List of Values for selection to the user.

All text in A Column & Row 1 are the keywords or reserved words of the Jet Reports.

All text B3..B8 are Text or Option Heading which will be displayed in Option form when report is executed.

All text C3..C8 are the default Values for the options, remember (*) means no filters applied or include all. Don’t Forget to define Name of the cells in Name box, you will find this in the Left of the Formula Bar. The name I am using in my report defined below, this will help us using these user friendly name as filter in our Functions.

Cell Name
C3 StartDate
C4 EndDate
C5 GLAccountNo
C6 BudgetName
C7 PeriodType
C8 BlankZero
E3 DateFilter

Let’s Start our Report Design, Insert one more sheet for report format design.

Our design will be as follows, we will discuss the formula used in these columns later below in this post.

NPGLUsage-2

The Jet Formulas we are using in above sheet is as below:

Cell Formula
I3 =NL(,”Company Information”,”Name”)
J4 =PeriodType
J5 =NP(“DateFilter”,StartDate,EndDate)
J6 =NP(“Eval”,”=Today()”)
K8 =NL(“Columns=5”,NP(“Dates”,StartDate,EndDate,PeriodType))
K9 =NL(,NP(“Dates”,K8,”30/12/2050″,PeriodType,TRUE))
C12 =IF(AND(Heading=FALSE,BlankZero=”Yes”,MIN(K12:Q12)=0,MAX(K12:Q12)=0),”Hide”,”Show”)
D12 =NL(“Rows”,”G/L Account”,,”No.”,GLAccountNo,”Date Filter”,DateFilter)
E12 =NF($D12,”Account Type”)
F12 =OR(AccountType=”Heading”,AccountType=”Begin-Total”)
G12 =NF($D12,”Indentation”)
H12 =IF(E12=”Posting”,NF(D12,”No.”),IF(OR(E12=”Total”,E12=”End-Total”),NF(D12,”Totaling”),”0″))
I12 =REPT(” “,G12*5) & NF($D12,$I$11)
K12 =GL(“Budget”,$H12,ColumnStartDate,ColumnEndDate,,,,,,,BudgetName)
L12 =GL(“Balance”,$H12,ColumnStartDate,ColumnEndDate)

We can mix and match Excel formulas too to achieve data we require in our report especially any calculation of values from other cell values. You may find many of them is being used in this report too. You can apply formatting of Excel for better presentation of your reports. Sometime cell references to help in repeating the value to the cells and making available to access to upcoming cells when report is executed.

In Cell C1 [Hide+?] denotes this column will be used to get decision at run time like if we want to hide or show the respective row. As this value will not be available at design time, but when report is executed some rows we want to hide from the output of the report to user. Anything we are sure and know well in advance that this row need to be Hide we can key [Hide] in column A of that Row.

See in Cell C12 formula: [=IF(AND(Heading=FALSE,BlankZero=”Yes”,MIN(K12:Q12)=0,MAX(K12:Q12)=0),”Hide”,”Show”)]

Here decision is taken either we need to Show/Hide this row from output depending upon the test value. This value will be only available when data is retrieved and presented in Report, at design time we cannot predict what will be the value in these column and what will be the result of our test.

If we want to Hide any Row we Key [Hide] in A Column of that Row, Similarly if we want to Hide any Column we Key [Hide] in Row 1 of that Column.

Column L8 & L9 simply copy Value of K8 & K9 Respectively. K10 =K8 here too value is copied.

Rest All Values are Simple text used for Heading in Report Output.

[=REPT] this is Excel Formula Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax: REPT(text, number_times)

The Cell M12 usage simple Excel Formula [=K12-L12].

The Cell N12 also usage simple Excel Formula [=IF(K12=0,””,ROUND((M12/K12),2))]

On executing the Report I fill below Filters:

NPGLUsage-3

Applying above Filters the Output of report from my Standard Navision 2015 Report I get below Output:

NPGLUsage-4

Due to size limit I have reduced the zoom of the excel so that the exact report output in full can be shown.

Remain tuned for more information.

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