oracle 尝试创建查询报告的 APEX 链接列

Trying to create an APEX Link Column that queries a report

提问by U.S. Coding Team - Benchwarmer

Basically, say I have a report A and I want to add a link column to it, and I set its target to a page in the application...the page its referring to has another report (lets call it B)

基本上，假设我有一个报告 A 并且我想向它添加一个链接列，并且我将其目标设置为应用程序中的一个页面......它所指的页面有另一个报告（我们称之为 B）

Is it possible for me to somehow make it so that table A's link column opens the page with Report B with rows that have the same column value for one of their columns?

我是否有可能以某种方式使表 A 的链接列打开带有报表 B 的页面，其中行的列具有相同的列值？

---

---

Example here:

这里的例子：

A

一种

Name Num1 Num2
--------------
A    5    3
B    3    3
C    4    2

B

乙

Name Quantity Serial
--------------------
D    2        3
E    1        8
F    4        6

So if I click the link column for row A, I want it to open report B and only shows rows where its Num2 = Serial, so only row D would show since it is the only one that equals 3

因此，如果我单击 A 行的链接列，我希望它打开报告 B 并仅显示其 Num2 = Serial 的行，因此只有 D 行会显示，因为它是唯一等于 3 的行

采纳答案by Richard Pascual

Using Report Linking to Filter Other APEX Report Outputs

使用报告链接过滤其他 APEX 报告输出

I had some fun with this one although. The "A" and "B" stuff was pretty dry, so I decided to create a data set that was more engaging, and perhaps clearer to understand for the rest of us... :) This is how the data-ecosystem was broken down, and the way I fulfilled the OP requirements.

尽管如此，我还是玩得很开心。“A”和“B”的东西非常枯燥，所以我决定创建一个更吸引人的数据集，也许我们其他人更容易理解...... :) 这就是数据生态系统被破坏的方式下来，以及我满足 OP 要求的方式。

The Test Schema:

测试架构：

Welcome to the manufacturing facilities of the "Recipe Stack" Food Works. The schema design and ERD (Entity Relation Diagram) is below, with the sample data used for this demonstration:

欢迎来到“食谱栈”食品厂的生产设施。架构设计和 ERD（实体关系图）如下，示例数据用于此演示：

The data relations are as follows:

数据关系如下：

1. The staff at the Stack Food-Works keeps an inventory of all ingredients for the types of meals and prepared foods they manufacture. Each ingredient has a unique ID (INGREDIENT_ID) and the staff tracks the amounts of each item in their pantry.
2. Each ingredient can be used in multiple recipes, but they will be used exactlyonce for a given recipe.
3. The ENTREE_RECIPEtable has a COMPOSITE KEYwhich means it is the combination of the two pieces of this composite key (ENTREE_NAMEand INGREDIENT_SEQ) that should be unique.
4. There is a FOREIGN KEYrelation between the INGREDIENT_IDvalues of both tables.

1. Stack Food-Works 的工作人员为他们生产的膳食和预制食品的类型保留所有成分的清单。每种成分都有一个唯一的 ID ( INGREDIENT_ID)，工作人员会跟踪食品储藏室中每种物品的数量。
2. 每种成分可以在多个配方中使用，但它们将被用于精确地一次给定的食谱。
3. 该ENTREE_RECIPE表有一个复合键，这意味着它是该复合键（ENTREE_NAME和INGREDIENT_SEQ）的两部分的组合，应该是唯一的。
4. 两个表的值之间存在FOREIGN KEY关系INGREDIENT_ID。

Report Display Requirements (APEX and SQL Design Elements)

报表显示要求（APEX 和 SQL 设计元素）

Selecting an item from the FIRST report is used as the input and the restriction/filter criteria of the second report.

从第一个报告中选择一个项目用作第二个报告的输入和限制/过滤条件。

User Case #1:

用户案例#1：

1. User Selects a Ingredient ID from the list of available ingredients in the pantry.
2. Input from (1) filters output of the RECIPE REPORT. This is a list of all the recipes that have the chosen ingredient in their formula.

1. 用户从食品储藏室的可用成分列表中选择一个成分 ID。
2. 来自 (1) 的输入过滤RECIPE REPORT 的输出。这是在其配方中包含所选成分的所有食谱的列表。

User Case #2:

用户案例#2：

1. User Selects an ENTREE_NAMEfrom the RECIPE REPORT. The ENTREE_NAMEis used to deliver a third report: the RECIPE FORMULAwhich is the full recipe for the entree item that was selected from the previous report.

1. 用户ENTREE_NAME从食谱报告中选择一个。本ENTREE_NAME是用来提供第三个报告：食谱配方是完整的配方为从上次报告所选择的主菜项目。

Testing Tools

测试工具

(You usually need these these for the more complex pages, so it's a good start to use or develop them for the easier ones...)

（对于更复杂的页面，您通常需要这些，因此为更简单的页面使用或开发它们是一个好的开始......）

I made my own, but you can also invoke the SESSIONlink on the developer's tool bar at the bottom of the APEX page on your running application (when it is displayed).

我创建了自己的，但您也可以调用SESSION正在运行的应用程序（显示时）APEX 页面底部的开发人员工具栏上的链接。

Here's my idea; it's a header region that also has a button to RESETinput values so that I can clear the cache and retest or try other examples. I'll show later how you can use this link to see what is going on. You can see it in the discussion of testing at the bottom of this guide.

这是我的想法；它是一个标题区域，还有一个用于RESET输入值的按钮，以便我可以清除缓存并重新测试或尝试其他示例。稍后我将展示如何使用此链接查看正在发生的事情。您可以在本指南底部的测试讨论中看到它。

APEX Report/Page Design and SQL Parametrization

APEX 报表/页面设计和 SQL 参数化

My columnn linking scheme looks similar to the previous post such as the one from FTaveras. This is how my linking works. What's different is that I do not go to another page, I simply go BACK to the same page I came from. Redirects and Branches apparently don't care if they are simply returning to the same location.

我的专栏链接方案看起来类似于上一篇文章，例如来自FTaveras 的文章。这就是我的链接工作方式。不同的是我不会去另一个页面，我只是回到我来自的同一页面。重定向和分支显然不关心它们是否只是返回到同一位置。

What is different on the return trip is that page parameters that were originally null or unpopulated NOW have a value. That value now brings life to the reports on the page that were empty.

回程的不同之处在于最初为空或未填充的页面参数现在具有值。现在，该值使页面上的空白报告变得生动起来。

Step 1: The PANTRY REPORT

第 1 步：食品储藏室报告

1. Output: Query all items from the FOOD_SUPPLIEStable.
2. Inputs: Supply links by INGREDIENT_IDto filter the RECIPE_REPORToutput.

1. 输出：查询FOOD_SUPPLIES表中的所有项目。
2. 输入：提供链接INGREDIENT_ID以过滤RECIPE_REPORT输出。

How to do it: (hint)To accomplish this, define the report column/field value in your report layout design page as a "linked" column and assign its value as a page item. The page item will be referenced in the SQL query of the next report...

怎么做：（提示）要完成此操作，请将报表布局设计页面中的报表列/字段值定义为“链接”列，并将其值指定为页面项。该页面项将在下一个报表的 SQL 查询中引用...

Step 2: The RECIPE REPORT

第 2 步：食谱报告

1. Output: Query all records from the ENTREE_RECIPEtable which have the INGREDIENT_IDfrom Step 1 within their formula.
2. Input: Supply links by ENTREE_NAMEto filter the RECIPE_FORMULAoutput.

1. 输出：查询ENTREE_RECIPE表中INGREDIENT_ID公式中包含步骤 1 中的所有记录。
2. 输入： 提供链接ENTREE_NAME以过滤RECIPE_FORMULA输出。

How to do it: (hint)Include the page item defined from Step 1within the SQL query of this report:

怎么做：（提示）在此报告的 SQL 查询中 包含在步骤 1 中定义的页面项：

    SELECT * FROM entree_recipe 
     WHERE ingredient_id = :P3_INGREDIENT_ID 

Step 3: The FORMULA REPORT

第 3 步：公式报告

1. Output: Query all records from the ENTREE_RECIPEtable which have the ENTREE_NAMEselected from Step 2.

1. 输出：查询ENTREE_RECIPE表中具有ENTREE_NAME步骤 2 中选择的所有记录。

Debug and Test Run

调试和试运行

Most will be able to get this far without any problems. If not, here are a couple of examples of how you can debug and test your work. There may be some built-in tools and packages that already exist within Apex, so any suggestions on alternate approaches are welcome in the comments...!

大多数人将能够做到这一点而不会出现任何问题。如果没有，这里有几个示例说明如何调试和测试您的工作。Apex 中可能已经存在一些内置工具和软件包，因此欢迎在评论中提供有关替代方法的任何建议......！

Using the APEX Developer SESSION Output

使用 APEX Developer SESSION 输出

After selecting the inputs for the trial run, click on the SESSIONlink on the developer toolbar at the bottom of the page. This is an example output:

选择试运行的输入后，单击SESSION页面底部开发人员工具栏上的链接。这是一个示例输出：

Note that the page items that were set for that session are displayed. The inputs I used for this test were:

请注意，会显示为该会话设置的页面项目。我用于此测试的输入是：

1. INGREDIENT_ID: 6432
2. ENTREE_NAME: peach cobbler (fresh)

1. INGREDIENT_ID: 6432
2. ENTREE_NAME：桃子馅饼（新鲜）

Extra Credit:This one is an alternate approach. It may be useful to design something like this as an add-on to any app you design. You do not need to remove it from your app when you push it to production because there is a "conditional display/suppress" feature for page regions. (check it out)

加分：这是一种替代方法。将此类内容设计为您设计的任何应用程序的附加组件可能会很有用。当您将其推送到生产环境时，您无需将其从您的应用中删除，因为页面区域具有“条件显示/抑制”功能。（一探究竟）

Simply set a global parameter as a "mode" on your Apex app. Set the value to "DEBUG" or "TEST" or whatever and key all your instances of this page region to display only when the global parameter is set to it.

只需在您的 Apex 应用程序上将全局参数设置为“模式”。将值设置为“DEBUG”或“TEST”或其他任何值，并键入此页面区域的所有实例以仅在全局参数设置为它时显示。

Wrap Up and Discussion

总结和讨论

Hopefully, you've enjoyed your visit to the "Stack Food Works" (no tasting or sampling from the line, please).

希望您对“Stack Food Works”的访问感到愉快（请不要在生产线上品尝或取样）。

This has been more of a holistic approach to Apex app design. It helps to have a methodology to map out each step, and a way to check your work at both the beginning and the end of your development process. Using smaller examples like this demo to apply these methods provides a chance to understand Apex development as a Software Creation PROCESS.

这更像是 Apex 应用程序设计的整体方法。有一个方法来绘制每个步骤，以及在开发过程的开始和结束时检查您的工作的方法。使用像这个演示这样的小例子来应用这些方法提供了一个机会来理解 Apex 开发作为一个软件创建过程。

回答by Ftaveras

Yes it's possible.

是的，这是可能的。

1. On page B, add and hidden Item name for example P(#)_SERIAL where (#) is your current page number.
2. Modify your query and add one line like AND SERIAL=:P(#)_SERIAL.
3. On Page A go to "Report Attributes" tab on the report region, click edit.
4. On the column link section configure your link to page B and set the hidden item on page B to the value on report column of page A.

1. 在 B 页上，添加和隐藏项目名称，例如 P(#)_SERIAL，其中 (#) 是您当前的页码。
2. 修改您的查询并添加一行，如AND SERIAL=:P(#)_SERIAL.
3. 在页面 A 上，转到报告区域上的“报告属性”选项卡，单击编辑。
4. 在列链接部分配置到页面 B 的链接，并将页面 B 上的隐藏项设置为页面 A 报告列上的值。

Name: Item 1 [P(#)_SERIAL] Value: #Num2#

名称：项目 1 [P(#)_SERIAL] 值：#Num2#

Demo

演示