Access contents of UCM file using BI Report

If the third party system is preparing the DAT file and putting in the zip file on UCM, then you can use the Initiate Data Loader flow to upload the file to HDL.

Now if there are multiple vendors and if the individual files are getting uploaded to UCM. And you want to consolidate all of them into a single file and initiate the process only once.. Can you do it? Or can the HDL formula read two input files?

The HDL formula flow (Load data from file) can take only one Content ID as input. So if there are multiple files, then you need to submit the process multiple times.

So, it boils down to getting a single file on the UCM which we can achieve using BI Report?? Do you believe it?? You can read the contents of the files in UCM and append them to make a single file and upload it back to UCM.

Note: Currently it is possible to read only the .TXT files from UCM in BI Report. The file extension should be .txt only. It cannot read the .csv, .dat, .zip etc. So you can request the vendor to send in .txt format only and mostly all vendors support multiple formats and it shouldn’t be an issue.

Now Let’s look into how to access files on UCM in BI Report.

Let me upload two text files on to UCM.

File1.txt
LABC2020/01/014712/12/31 UBICACION 1HCM001COMMONCOA
BBCD2020/01/014712/12/31 HCM001COMMONPUESTO 1FULL_TIMEAREG
File2.txt
Regular EarningsUS Legislative Data GroupE142019/06/292019/07/12
Overtime EarningsUS Legislative Data GroupE122019/06/292019/07/12

Now we will access these files using BI Report and combine them into single file and upload it back to UCM. And then it can be used by any other flows which is beyond the scope of this article.

Now let’s navigate to BI Report and create the datasets using Content Server.

Pre-requisite to add the Content Server Data Set is there is should be another SQL dataset which should have the document id fetched. And then the Content Server data set will link to the SQL data set and get the document content for the document id set by the other data set.

Data Model Snapshot:

G_1 SQL Query and Snapshots:

select 'Dummy' Key, a.* from revisions a where ddocname in (:CONTENT_IDS)

G_2 UCM Snapshots:

Here the DID from G_1 should be selected in the Document ID. Which will be used as key to get the document text from UCM.

We will add a parameter to get the multiple content ids:

Now, we will go to Data page and test it for a couple of Content IDs: UCMFA02586299,UCMFA02586300

Now a simple etext template can be created to append all DOCUMENT fields from G_2 level.

Now, if we run the report for the Content IDs, we get the output like below:

Now, as we got the consolidated output, next step is to write the bursting query to upload the file to UCM

Bursting Query:

SELECT
'Dummy' as KEY -- Split Key from data model SQL
,'Test' template -- Report template name
,'en-US' locale
,'TEXT' output_format -- Output type
,'WCC' del_channel -- To send to content server
,'FA_UCM_PROVISIONED' parameter1 -- server name (BI Administration > Manage BI Publisher > Delivery > Content Server)
,'FAFusionImportExport' parameter2 -- security group name
,'HCM_IMPL' parameter3 -- Author of the file
,'Consolidated File1&2' parameter5 -- Title
,'File3' parameter6 -- Output file name
,'' parameter7 -- Comments (Optional)
,'' parameter8 -- Content ID (Optional) If you specify the ID, it must be unique. If you don't specify the ID, the system generates a unique one
,'false' parameter9 -- Custom metadata (true/false). Specify ‘false’
,'' parameter10 -- Folder Path
FROM dual

Once bursting query is setup, we need to enable the bursting option in the BI Report properties.

Then we can schedule this report with the input parameters to test this functionality.

Click on More > Schedule for BI Report

We need to schedule the report since bursting doesn’t work if we open the report. Only with schedule/through payroll flow, bursting will work.

Consolidated File Contents:

Finally we are done with the demonstration of the BI Report to get the UCM file contents and display on the output.

Hope this helps when you get similar requirement.

Catalog files for reference: Download Link