Showing posts with label My. Show all posts
Showing posts with label My. Show all posts

Wednesday, 30 September 2015

Creating My First Report using PowerPivot

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.

Thursday, 24 September 2015

Creating My First Report Using Jet Reports

Today I will discuss, how to create reports in Excel using Jet Reports.

Below links will be helpful to refresh what I have shared till now:

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

Using Jet Report NL Function

Using Jet Report NF Function

Using Link in Jet Reports

We will be using NL Functions.

Also we will introduce few Arguments for NL Function.

Below Table describes the same:































































ParameterArgumentDescription
What
"Link"Returns a string value that can be used as a filter in another NL function
"LinkField"Returns a string used to retrieve a field from a link table in an NL(Table) function.
"Table"Creates an Excel table object based on the field values returned. Leaving the Field argument blank returns all fields. Use a Field Cache to return multiple fields.
FilterField
"Headers="Overrides field headers with the array of headers specified by the Filter argument. For use with an NL(Table) or NL(Lookup) function.
"TableName="Specifies the name to use for the Excel table object created by NL(Table) with the name in the Filter parameter. Use this to refer to the table by name from a Pivot table.
"Filters="Specifies a set of filters for the query with an array of filters specified by the Filter argument.
"InclusiveLink="Links the primary table to the one specified by the Filter argument for the purpose of retrieving data.
"IncludeDuplicates="When the value of the Filter argument is TRUE, specifies that all matching records from the source data will be included in the NL(Table) results.

We will design below report to see how Jet Report is designed.
FirstJetReport-1

Below describes the Formulas need to be inserted in respective cells.

Make sure you add ‘=’ in front of Formulas as defined in Data Type Column.






















































































































Cell ReferenceFormulaData Type
F10NL("Link","Item",,"No.","=Item No.")Formula
G10NL("Link","Customer",,"No.","=Source No.")Formula
F11Entry No.Text
G11Document No.Text
H11Posting DateText
I11Item No.Text
J11Item – DescriptionText
K11Gen. Prod. Posting GroupText
L11Customer No.Text
M11Customer   NameText
N11Item Ledger Entry QuantityText
F12Entry No.Field Names
G12Document No.Field Names
H12Posting DateField Names
I12Item No.Field Names
J12NL("LinkField","Item","Description")Formula
K12NL("LinkField","Item","Gen. Prod. Posting Group")Formula
L12Source No.Field Names
M12NL("LinkField","Customer","Name")Formula
N12Item Ledger Entry QuantityField Names
E13NL("Table","Value Entry",$F$12:$N$12,"Headers=",$F$11:$N$11,

"TableName=","ValueEntry","Filters=",$C$5:$D$8,"InclusiveLink=",$F$10,

"InclusiveLink=Value Entry",$G$10,"IncludeDuplicates=","True")
Formula

Although you can directly key in the text for NL commands and functions as formula. Below I show the Function Wizards for better understanding and how to use the same. All marked as Formula in above Table.

For Rest marked as Text or Field Names, you can key them directly in respective cells.
FirstJetReport-2
FirstJetReport-3
FirstJetReport-4
FirstJetReport-5
FirstJetReport-6
FirstJetReport-7

The output of Report will as below:
FirstJetReport-8

Stay tuned for upcoming posts for more detailed information.

I understand above example is bit complex I will come up with more simple and basic reports for beginners in my upcoming posts.