HOW TO: Create a line-delimited file within Bi Publisher using SQL query data set

I am bringing back two columns, Username and Inactive. I want to convert my SQL query returning 2 columns by a comma delimited into a line-delimiter. The intention here is to loop through each column, for each row, and display this in a line-delimited format
August 14, 2013
Bi-Publisher-11g

-- From
RedfernD,INACTIVE
RedfernA,INACTIVE
WilsonDa,INACTIVE

-- To
RedfernD
INACTIVE
RedfernA
INACTIVE
WilsonDa
INACTIVE

As I was working on a mac OS X, and my windows laptop has restricted admin access, I was result in creating a custom report without installing BI Publisher Tool as a windows plugin.

The thing with BI is that if you wish to create a CSV file, it will ignore the template data group structure you import and instead become stubborn and do it's own thing. BI publisher administers it's own CSV setting, which can be located within -- Administration -- Runtime Configuration -- Properties and scroll down to see the CSV settings.

Also to my knowledge, BI Publisher 11g doesn't create .txt formats from within the GUI, which blows my mind because I would of thought it would be the simplest form of a report. The other formats are based on the data structure though you can also enforce the specific format as described within the post, how to create a CSV file in BI publisher.

As I result, I created a .xls file and make that line-delimited based on the results I received back from the SQL query.

Oh, another thing to note is that you cannot create a line delimiter file within the xmlpserver console. This has to be completed by creating a rtf file (designed from within MS word) and imported that way.


Step one: Create a Data Model with the SQL query imported

This is fairly intuitive to complete. Because BI is limited to creating comprehensive Joins, CTEs, view creates yarda yarda, I would recommend to create your SQL query from outside the BI product and import the SQL script Also, ensure you know the column names, this is important information to use within your RTF template within MS word.


Step Two: Create your template (RTF)

Next, open MS word to create a new template. Open a new document and add all the static contents you want in your document. In this report I simply have one line to show the for loop that I will be attempting.

Next, you need to create a text form field. The link I found useful for this here: How to create a text form field You can give any name of the form field though you do need to be specific when it comes to adding the help text. click on Add Help Text... and a new box will open. Click on Type your own: and enter the name of the column you want to display. In this case, I am entering the USERNAME column, of which is surrounded by <? and ?> values. Also note that this is case sensitive.

https://technicalconfessions.com/images/postimages/postimages/_218_10_MS word rtf template for report.png

You can also see that I created another form field with the other column called INACTIVE.

https://technicalconfessions.com/images/postimages/postimages/_218_11_Username as form field.png

Before and after the form fields are other form fields called E and F. This is the start and end of the loop. Again, the form fields can be called anything you wish. This is the next step.


Step Three: Create the for-loop

https://technicalconfessions.com/images/postimages/postimages/_218_12_Inactive as form field.png

As mentioned just above, there's an E and F form field, which are the place holders for my for-loop. All the SQL returned information reside from within the for loop to show the columns as a loop.

https://technicalconfessions.com/images/postimages/postimages/_218_13_for each loop as form field.png

Within the first, I have added <?for-each:SOMETHING> and within the end, I've added the <?end for-each>. The SOMETHING is dependent on your data structure

https://technicalconfessions.com/images/postimages/postimages/_218_14_end of for loop in form field.png

This can located within the sample.xml file. Within the data model, click on the 'sample data', in this case is my sample.xml. to understand the layout. When I click on the sample data, I'm presented with the following XML.

https://technicalconfessions.com/images/postimages/postimages/_218_15_line delimeter within bi publisher.png

As you can see, the root tag is the DATA_DS, then G_1, then it's the USERNAME and INACTIVE values. so to navigate to the USERNAME and INACTIVE, you need the E field form to be <?for-each:DATA_DS/G_1?>

Once you've played about with the layout, same the RTF in preparation to import it into BI publisher.

About the author

Daniel is a Technical Manager with over 10 years of consulting expertise in the Identity and Access Management space.
Daniel has built from scratch this blog as well as technicalconfessions.com
Follow Daniel on twitter @nervouswiggles

Comments

Other Posts

NameID element must be present as part of the Subject in the Response message

ShibbolethSAML

August 7, 2018
Created by: Daniel Redfern
NameID element must be present as part of the Subject in the Response message, please enable it in the IDP configuration.
Read More...

HOW TO provision AD group membership from OpenIDM

OpenIDMICFAD-connector

June 15, 2018
Created by: Daniel Redfern
For what I see, there's not too many supportive documentations out there that will demonstrate how provision AD group membership with the ICF connector using OpenIDM. The use of the special ldapGroups attribute is not explained anywhere in the Integrators guides to to the date of this blog. This quick blog identifies the tasks required to provision AD group membership from OpenIDM to AD using the LDAP ICF connector. However this doesn't really explain what ldapGroups actually does and there's no real worked example of how to go from an Assignment to ldapGroups to an assigned group in AD. I wrote up a wiki article for my own reference: AD group memberships automatically to users This is just my view, others may disagree, but I think the implementation experience could be improved with some more documentation and a more detailed example here.
Read More...

ForgeRock OpenIDM - InvalidCredentialException: Remote framework key is invalid

ICFIDMOpenIDMOpenICF

November 8, 2017
Created by: Daniel Redfern
In the past, the similar error occurred though for the Oracle Identity Management solution. invalidcredentialexception remote framework key is invalid Because they all share the ICF connector framework, the error/solution would be the same.
Read More...

org.forgerock.script.exception.ScriptCompilationException: missing ; before statement

IDMsync.confforgerockopenidm

November 8, 2017
Created by: Daniel Redfern
org.forgerock.script.exception.ScriptCompilationException: missing ; before statement
Read More...

ForgeRock IDM - org.forgerock.script.exception.ScriptCompilationException: missing ; before statemen

OpenIDMsync.confForgeRock

September 17, 2017
Created by: Daniel Redfern
ForgeRock IDM - org.forgerock.script.exception.ScriptCompilationException: missing ; before statement
Read More...

Caused by: org.forgerock.json.resource.BadRequestException: Target does not support attribute groups

OpenIDMForgeRockICFConnector

September 17, 2017
Created by: Daniel Redfern
When performing the attempt of a reconciliation from ForgeRock IDM to Active Directory, I would get the following error
Read More...

ForgeRock OpenIDM - InvalidCredentialException: Remote framework key is invalid

OpenIDMForgeRockICFConnectorAD

September 17, 2017
Created by: Daniel Redfern
In the past, the similar error occurred though for the Oracle Identity Management solution. invalidcredentialexception remote framework key is invalid Because they all share the ICF connector framework, the error/solution would be the same.
Read More...

ERROR Caused by com.google.api.client.auth.oauth2.TokenResponseException 400 Bad Request - invalid_g

OpenIDMIDMGoogleGoogle-AppsICFreconciliation

September 12, 2017
Created by: Daniel Redfern
During the reconcilation from OpenIDM to the ICF google apps connector, the following error response would occur. ERROR Caused by com.google.api.client.auth.oauth2.TokenResponseException 400 Bad Request - invalid_grant
Read More...

forgerock-openidm-encryptedjwt-error

OpenIDMIDMForgeRockJWTIAM

August 29, 2017
Created by: Daniel Redfern
Received the JWT error
Read More...

Unexpected character ('¾' (code 190)): expected a valid value

ForgeRock-OpenIDMOpenIDMIDMKeystore

June 25, 2017
Created by: Daniel Redfern
Unexpected character occurred when the IP addresses changes and the virtual instance was migrated into a separate network subnet.
Read More...