Use Dynamic URL in Oracle BI Report for Drill Down Reports

Creating a link that includes parameters for every single value for going Detail BI Report about the record.

Hi Dear All,

I want to explain this subject that seems small and unimportant. But not. Too much information in a report can confuse our mind and it’s not necessary some values in a report. It will be better to see in a detail report. Because of this reason, we are using two reports generally. One of them is the Main Table, the other one is the Detail Table. Developers like us can see this order among tables in Databases.

So basically, our main purpose is that we want to reach detailed information of one record. We must use parameters and need to jump another BI report (Detail Report) from the Main Report. We can use BI analytic reports with the Action Link function easily. But we can’t action link on BI Reports. For this, we will use the URL property for a column in the Oracle BI Report.

Oracle users know, almost every module connects with General Accounting Module and today we will use GL tables for understanding clearly. We will reach detailed information of Journals by using journal header information. We will see the account transactions.

So, we should create two reports. Journal Header Report and Journal Lines Report. When I click the link in Journal Header Report, Journal Lines Report is opened and I see the account transactions for the journal header record.

— Journal Header Table Select GLHeader.LEDGER_ID, GLHeader.JE_HEADER_ID, GLHeader.JE_CATEGORY, GLHeader.JE_Source, GLHeader.Name, GLHeader.Description, GLHeader.POSTED_DATE, ‘Go Detail Table Of This Record’ as Link From GL_JE_HEADER GLHeader Order By GLHeader.POSTED_DATE DESC

That is enough for value for now. We will use Ledger_Id and JE_HEADER_ID as a parameter. So I added because of this reason and to see which record comes to us in Detail Report. As you can see the last value is our link value. When users click the value, we are expecting to jump the detail report for the journal header record. Firstly, you create a Data Model and Report. You should edit the report template to see a better view. We need to view the report Interactive, not PDF, DOC, XLS, etc. If not links don’t work. So we change like this;

— Journal Lines Table Select GLLines.LEDGER_ID, GLLines.JE_HEADER_ID, GLLines.JE_LINE_NUM, GLLines.DESCRIPTION, GLLines.STATUS, GLLines.EFFECTIVE_DATE, GLCODE.Segment2 Account, AccountDesc.Description AccountDesc, GLLines.ENTERED_DR, GLLines.ENTERED_CR, GLLines.ACCOUNTED_DR, GLLines.ACCOUNTED_CR, GLLines.CURRENCY_CODE From GL_JE_LINES GLLines Inner Join GL_CODE_COMBINATIONS GLCODE Inner Join FND_VS_VALUES_VL AccountDesc ON AccountDesc.Value = GLCODE.Segment2 and AccountDesc.ATTRIBUTE_CATEGORY = ‘Account’ On GLCODE.CODE_COMBINATION_ID = GLLines.CODE_COMBINATION_ID Where GLLines.LEDGER_ID = (:LedgerId) and GLLines.JE_HEADER_ID = (:HeaderId) Order By GLLines.JE_LINE_NUM

We wrote SQL codes for journal lines like this. The main point is, we added Ledger Id and Journal Header Id as a parameter inside the code. Thanks to the piece of code, the report can run according to parameters and use URL links that include parameters.

And I added Account and Account description. Because we want to see account transactions. We can say the salt bae effect of this code. Again, we prepared a data model and report for journal lines. One more thing to do, parameters must disappear when we run the journal lines report. We close like this;

The main elements are ready. We should adjust finely for the journal header report. But before this, we should prepare a URL link.

We need to use xmlpserver for creating link. Keyword is xmlpserver. You shouldn’t use Analytics for this, So the link doesn’t work actually. You should use the Detail Table link and Main Table parameters for creating. You need to combine two of them. Link should be like that;

https://your_server.com/xmlpserver/folder_1/folder_2/report_name.xdo?Parameter_Name1={/Data_Source_Name/Group_Name/Value_Name} &Parameter_Name2={/Data_Source_Name/Group_Name/Value_Name2}

That’s all. Then, we need to move on to the report editing step

So, you can say where comes DATA_DS, G_2 names from? It’s simple. They are coming from Journal Header Table. When you edit the report template, you can see the left top side.

It’s time to paste our link created before. But, Where? Firstly, we click the column that we want to use as a link then we are going to Property and find the URL. That’s it. You can paste it here.

We completed all things and we can run the report and test. Click the ‘Go Detail Table of This Record’