This discussion is archived
1 Reply Latest reply: Dec 27, 2012 8:07 AM by matthew_morris RSS

Dynamic Reports

wrozansk Newbie
Currently Being Moderated
Hello everyone,

I want to create a dynamic report based on a value from a select list. 2 tables are involved in this:

Table A:
- category_id
- category_name
- category_subname

Table B:
- category_id
- other stuff

The report is created on Table B and contains all collumns from this category. The Select List is named as P4_SHOW and is created on a LOV based on Table A. The LOV query is as follows:

select distinct CATEGORY as display_value, CATEGORY as return_value
from SD_ISSUE_CATEGORIES
order by 1

NOTE: I want the select list to display only the category_name, and for some reason if I change the return value from CATEGORY to CATEGORY_ID, I get multiple selections of the same name, as if the distinct part did not kick in.

If someone selects a category name, I'd like to generate a report that contains only rows with the category_id corresponding to the category_name. I've been struggling for some time with this and reada few posts on the internet, but could not find an answer for this.

Kind regards,

Edited by: 978908 on Dec 27, 2012 7:47 AM
  • 1. Re: Dynamic Reports
    matthew_morris Expert
    Currently Being Moderated
    NOTE: I want the select list to display only the category_name, and for some reason if I change the return value from
    CATEGORY to CATEGORY_ID, I get multiple selections of the same name, as if the distinct part did not kick in.
    The CATEGORY_ID values are different. Presumably you have something like:
    CATEGORY     CATEGORY_ID
    BLUE         1
    BLUE         2
    BLUE         3
    If you use CATEGORY for both the display and return values, the DISTINCT will show only a single row for BLUE.
    If you use CATEGORY for the display value and CATEGORY_ID for the return value, the DISTINCT will show three rows for BLUE.

    I don't know the logic of your tables, so I can't say why this is the case, but it's the only explanation that matches your description.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points