Connect a SQL Server database to your workbook

How to Connect excel With a SQL Server database?

Solution :

In Office 2017, Go to Excel > Data >From Other Sources > From SQL Server 

See Following Figure 1.1

See Following Figure 1.2

1. Server Name : Enter here Instance Name of SQL 2014

2. Log on Credentials: here you have to choice

 [a] Use Windows Authentication : Excel connect with server admin login

 [b] Use the following user Name and password
User Name : sa
Password :xxxxxxxxx

See Following Figure 1.3

See Following Figure 1.4

 

See Following Figure 1.5

 

See Following Figure 1.6

 

Now As per View Procedure you will get Result on your excel file. if you like to Compare "excel data" with SQL view Procedure, run "Select * from <ViewProcedureName>,

E.g. "select * from Exporttoexcel_2018, as per documentation

How to Create View Procedure 

Example1

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";

 

Calculate EMI in Multiple Way

Read Following EMI Calculation Method

[1] Fixed interest rate method

[2] Reducing balance method

Let’s understand the two methods of interest calculation – fixed/simple interest calculation method and reducing balancing method. To attain more clarity on this, the following conditions shall be used –

• Loan worth is Rs. 1,50,000 = L
• Loan Tenure is 2 year =T
• Rate on interest is 13% = P

[1]
Interest Amount = L * P
Interest Amount = 150000 *13%
Interest Amount = 19500

[2]
2 Year Total Interest Amount = Interest Amount * T
2 Year Total Interest Amount = 19500 * 2
2 Year Total Interest Amount = 39000

[3] 
Total Outstanding Amount=2 Year Total Interest Amount+L
39000+1,50,000
189000

[4]
Convert to Monthly Basis EMI Amount
Total Outstanding Amount=189000/(L*12)
Total Outstanding Amount=189000/(2*12)
Total Outstanding Amount=7875

Item Master

How to Create New items?

Solution

See following Screen Shot:

 Item Code:

Have to enter Manual

Item Name:

Enter Item Name (Put Save to Short Name and Alias Name)

Item Type:

                For Trading item select “Finished Item Group”

Main Group:

Select Main Group (for New Main group, pls go to ‘Admin> Item > Main Group Master’ enter new Record)

Sub Main Group:

Select Sub Main Group (For add New Sub Main group in List, pls go to ‘Admin> Item > Sub Main Group Master’ enter new Record)

Upper unit

                Set “Procurement” Unit of Measurement, and set “Is Primary”

Middle Unit

Set “Consumption” Unit of Measurement, and set “Is Base”

HSN/SAC Code:

                Define here HSN/SAC Code here, for Calculate GST Rate item Wise. And this is Mandatory Field, 

(HSN Code means Harmonized System of Nomenclature used for classifying the goods under the GST, Goods and Service Tax. The SAC code means Services Accounting Code under which services fall under GST are classified.)

Pack Format

                Enter here item Packing details if you want to enter.

 

Trip Master Configuration in Food Product

Query: 1 How to Make Trip in ERP?

Solution:

Step: 1

Open Inventory Module > Master >Trip Master

Step: 2

See the Following Screen Shot.2.1

See the Following Screen Shot.2.2

Two types of provision you have to set Trip timing in ERP

[1] From Division Type –To Division

[2] From Division –To Division

Trip Number:

 Here you have to set Trip Type (e.g.  After noon, Morning, First Shift, Second Shift, Etc...).  

(Note : 

1. Type not changeable after save configuration.

2. if you want emergence Order request then you have to create one more trip Type with name include “SPECIAL ORDER” Word)

Funcation used at

Trip timing Option using in Stock Transfer Request (Inventory > Transaction)