• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:March 21, 2021
  • Reading time:8 mins read
You are currently viewing How to Create BI Report using static CSV file
How to Create BI Report using static CSV file

In this article we will look into an interesting feature of creating a BI Report using a static CSV file.

Sometimes, we might get some input file from vendors and we need to fetch other details of employees within that list of employees or validate some fields. And this feature will be handy in such cases.

Table of Contents

Use Cases:

  • We can fetch additional details for employees from the files sent by Vendors.
  • We can prepare HDL DAT file from the input file shared by the client. We can get additional fields and create etext template to get the HDL DAT file.
  • We can import external CSV data with different delimiters (comma, tab, pipe, semicolon) and show that info on the existing report or a new report.

Without delay, let’s get into details on how to do it.

Creating Data Model using Static CSV file

First let’s consider we have a sample CSV file with pipe delimiter something like below with the first field being person number and other fields having something else.

120|Manager|Consulting
519|Test Assistant|Support
3672|Technical Lead|Quality
5149|Assistant Manager|Support
4626|Consultant|Consulting

Now, lets try to import this file to Data Model and map the person number to another SQL based data set and fetch employee’s Name.

Let’s Navigate to BI Catalog and select New Data Model.

image 27 - How to Create BI Report using static CSV file

On the next screen, give some name to Data Set and select Data Source as Local and click the upload button to upload the local CSV file.

if the first row has header with field names, then check the box which will skip the header row from showing up on the data.

We can use CSV file with 4 delimiters (pipe | OR semicolon ; OR Tab OR Comma ,)

image 30 - How to Create BI Report using static CSV file
image 29 - How to Create BI Report using static CSV file

Browse for the local csv file and click upload.

Once we upload and select the delimiter as pipe and click ok the data set will get saved like below:

image 31 - How to Create BI Report using static CSV file

Click on Data and view data

image 32 - How to Create BI Report using static CSV file

Now, lets, go back to Diagram tab and add a new SQL based data set to pull the employees names using below query.

select papf.person_number, ppnf.full_name 
from per_all_people_f papf, per_person_names_f ppnf
where papf.person_id = ppnf.person_id
and sysdate between papf.effective_start_date and papf.effective_end_date
and ppnf.name_type = 'GLOBAL'
and  sysdate between ppnf.effective_start_date and ppnf.effective_end_date
image 33 - How to Create BI Report using static CSV file

Now, let’s map the Column1 to Person Number field so that we get the relevant person names. Drag and Drop the Column1 on to Person Number field and we can check the mapping.

image 34 - How to Create BI Report using static CSV file
image 35 - How to Create BI Report using static CSV file

Now go to data tab and view the data.

image 36 - How to Create BI Report using static CSV file

We can see that we are able to get the Name from the SQL data set which is mapped from column1 from the static input file.

Create an online report layout and drag fields from two datasets like below:

image 37 - How to Create BI Report using static CSV file

Final Output will look like:

image 38 - How to Create BI Report using static CSV file

We can create BI template as per the requirement based on the requirement using rtf/etext if HDL DAT file is the requirement.

Download Files:

You do not have access to this VIP only Content. Please subscribe to VIP Plan to check this section.