Tuesday, March 17, 2020

SSRS - Nested Lookup Functions


Sometimes, you have no access rights in the database to create some views or getting the data from the multiple sources, then you have to use available resources/techniques in that specific tools.

You have to create a vendor detailed report in SSRS and you have three datasets for Sales Orders, Customer Info and Vendor Info where Vendor Info is not directly linked with Sales Orders dataset but Sales Orders dataset is linked with Customer Info dataset based on the common field “CustId” and Customer Info dataset is linked with Vendor Info dataset based on the common field “VendorId” as given below:
Orders
OrdId
OrdDate
CustId
ItemId
ItemQty
SalesAmount
201902
1/21/2019
101
201
4
80
201903
2/11/2019
102
202
5
125
201904
2/27/2019
103
204
8
220
201905
3/1/2019
104
201
5
100
201906
3/10/2019
101
206
4
80

Customer Info
CustId
Name
Address
VenderId
101
Ryan Arjun
Delhi
103
102
Rosy Gray
Noida
104
103
John Will
Punjab
102
104
Kimmy Wang
Mumbai
102

Vendor Info
VenderId
Name
101
Josh Wilson
102
Robert Ramsay
103
Vibhor McHill
104
Donald Trump


Now, you have to create a report to show the customer name and vendor name as given below
Vendor Details Report
OrdId   
OrdDate              
CustomerName
Vendor Name
ItemId 
Qty              
Amt
201902 
1/21/2019
Ryan Arjun         
Vibhor McHill
201        
4             
$80.0
201903 
2/11/2019
Rosy Gray           
Donald Trump
202        
5             
$125.0
201904 
2/27/2019
John Will             
Robert Ramsay
204        
8             
$220.0
201905 
3/1/2019
Kimmy Wang    
Robert Ramsay
201        
5             
$100.0
201906 
3/10/2019
Robert Ramsay
Vibhor McHill
206        
4             
$80.0

There are very simple way/steps to accomplish these requirements -
Step1:  For example, your first look up will return a vendor ID (dsSalesOrders) from a customer order (dsCustomers).
Create a Hidden Text field on your Tablix report (TxtVendorId.value) that will hold your first lookup value.
If you don't want to show the first level value then you can hide it from the report.
Just right click on the TxtVendorId and choose expressions. In Expression window, just set the below expression -
 
= Lookup(Fields!CustID.Value,Fields!CustID.Value,Fields!VendorID.Value,"dsCustomers")


Step 2: But now you need to find the vendor name from a Vendor Master table (dsVendors). You can do another look up to the Vendor Master table using the ReportItems! TxtVendorId 1.value (Hidden field on the report) as the first value of the look up.
Right click on the Vendor Name details column and choose expressions. In Expression window, just set the below expression -
 
= Lookup(ReportItems! TxtVendorId.Value, Fields!VendorID.Value, Fields!Name.Value,"dsVendors")


That way you do not get the scope issue.



To Learn more, please visit our YouTube channel at - 
http://www.youtube.com/c/Sql-datatools
To Learn more, please visit our Instagram account at -
https://www.instagram.com/asp.mukesh/
To Learn more, please visit our twitter account at -
https://twitter.com/macxima


Monday, March 16, 2020

Talend ETL - Email Validation


How to verify that the email address column's data is having @ or [.] if not then load rejected data on different table. As an addition to that Talend supplies many Apache Commons libraries which have hundreds of really useful, efficient and community (Java community) checked/built solutions. The Apache Commons Validator library comes with a whole host of validation methods for Emails, Phone Numbers, URLs, etc.



Processing file data – We are using the below data to validate the correct email.
Id
Name
Age
Email
201
Ryan Arjun
22
Ryan.Arjun@gmail.com
202
Mini Cooper
18
Mini.cooper@data.net
203
Kimmy Wang
34
Kimmy_Wang@dataspan.co.uk
204
Bill Willson
45
bill.willson@@microsoft.com
205
Donald Trump
56
donald..trump@usgov.gov

How to write Custom Code?
In the Repository, right click on Code, create a folder (here called "custom") then right click on "custom" and create a routine then define the function to validate the email address as given below:

package routines;
import java.util.regex.*;
public class CheckEmail {
    public static boolean isEmailValid(String email) {
                    String regex = "^[\\w!#$%&'*+/=?`{|}~^-]+(?:\\.[\\w!#$%&'*+/=?`{|}~^-]+)*@(?:[a-zA-Z0-9-]+\\.)+[a-zA-Z]{2,6}$";
                    Pattern pattern = Pattern.compile(regex);
                    Matcher matcher = pattern.matcher(email);
                    return matcher.matches();
    }
}

To build this job, you need the following processing components -

tFileInputDelimated: We can use this component to read a file and separate fields contained in this file using a defined separator. It allows you to create a data flow.

tLogRow: This component is used to monitor data processed and displays data or results in the Run console. This component can be used as intermediate step in a data flow or as a n end object in the Job flowchart.

tFilterRow: This component filters input rows by setting one or more conditions on the selected columns. It  helps parametrizing filters on the source data. This component is not startable (green background) and it requires an output component.

tMap: This component is an advanced component, which integrates itself as plugin to Talend Studio. It  transforms and routes data from single or multiple sources to single or multiple destinations. Possible uses are from a simple reorganization of fields to the most complex Jobs of data multiplexing or demultiplexing transformation, concatenation, inversion, filtering and more.
The use of tMap supposes minimum Java knowledge in order to fully exploit its functionalities. This component is a junction step, and for this reason cannot be a start nor end component in the Job.

To Learn more, please visit our YouTube channel at - 

To Learn more, please visit our Instagram account at -
To Learn more, please visit our twitter account at -
https://twitter.com/macxima