This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Jul 21, 2011 10:57 AM by 807466 Go to original post RSS
  • 15. Re: Is it possible to add a newline to a column?
    25631 Newbie
    Currently Being Moderated
    Hi Tajuddin and Austin,

    Thanks for both of your input. What I am looking to do is the following

    Run a query returning 4 columns
    Then display each of the columns in this format for each row returned from the query

    Row 1

    label1: col1 label2: col2

    label3: col3 label4: col4

    Row 2

    label1: col1 label2: col2

    label3: col3 label4: col4

    Row 3

    label1: col1 label2: col2

    label3: col3 label4: col4

    etc

    Kind of like a modified "Vertical Layout". Thus I am assuming I need to create a custom row layout correct?

    Thanks again guys
  • 16. Re: Is it possible to add a newline to a column?
    807466 Journeyer
    Currently Being Moderated
    Yes you will need a Custom Report Template. This is very different from adding a
    <br/>
    tag and is more complex. However, it is possible. The issue you are going to have is that when you change your query, you will have to change the Report Template as well. Unless somebody else has a better way, you are going to have to hard code your column alias names into the template. Also, if you are wanting to have this format in multiple reports throughout your application, you will need a custom Report Template for each unique report. I am really hoping that somebody else out there who is much smarter than I am has a better way to implement this solution... I am attempting to create a sample Report Template for you in your application.

    Standby

    Austin
  • 17. Re: Is it possible to add a newline to a column?
    VANJ Journeyer
    Currently Being Moderated
    Unless somebody else has a better way, you are going to have to hard code your column alias names into the template. Also, if you are wanting to have this format in multiple reports throughout your application, you will need a custom Report Template for each unique report
    The documentation makes it appear as if it is possible to use #1#, #2#, etc notation to reuse "named" column templates as well. I haven't tried it but what I gather is that if you create a N-column template (pre-defined N) with full control over each row's presentation and as long as you use the #1# notation, you can reuse it for any other report with N columns.
  • 18. Re: Is it possible to add a newline to a column?
    807466 Journeyer
    Currently Being Moderated
    This one may be above my skill level... I am trying to setup the column template conditions correctly but I can't seem to be able to reference the column name. I can only reference the column value, which will not help in this situation.

    Just read VANJ's message.. I'm on it, and testing now.

    UPDATE2: #1# notation returns the value of column 1, not the name of the column... bummer. The quest continues.

    Austin

    Edited by: AustinJ on Jul 20, 2011 11:30 AM

    Edited by: AustinJ on Jul 20, 2011 11:31 AM
  • 19. Re: Is it possible to add a newline to a column?
    807466 Journeyer
    Currently Being Moderated
    The behavior I have seen on the site is not consistent with the documentation. Below is an excerpt from the link provided by VANJ.
    Column Templates
    Column templates define the look of each column. You can define up to four column templates; each can be conditional. For example, you can have different background colors for even and odd rows, or highlight rows that meet a PL/SQL defined condition.

    In each Column Template, you define the look of each column. Column Templates support the substitution strings described in Table 10-6.

    Table 10-6 Column Template Substitution Strings

    Substitution String Description
    #ALIGNMENT#
    Determines the column alignment. Specified by the user.

    #COLCOUNT#
    Defines the count of the number of columns.

    #COLNUM#
    Defines the current column number.

    #COLUMN_HEADER#
    Defines the column header.

    #COLUMN_VALUE#
    Replaced with the value of the column.

    #ROWNUM#
    Specifies the current row number.



    Consider the following example:

    <td> #ALIGNMENT#>#COLUMN_VALUE#</td>
    If you actually ran this report, these substitution strings would be replaced with values generated by the results of a SQL query.

    By creating conditions, you can create a report that displays columns differently depending on whether the specified condition is met. To specify a column template be used conditionally, select a condition type from the Column Template Condition list. Valid values include:

    •Use Based on PL/SQL Expression. Conditionally formats columns based on data in that row.

    •Use for Even Numbered Rows. Conditionally formats even numbered rows.

    •Use for Odd Numbered Rows. Conditionally formats odd numbered rows.

    If you select Use Based on PL/SQL Expression, the next step is to enter a PL/SQL expression in the Column Template Expression field. For example, the following expression displays a value in bold if the value is greater than 2000:

    #SAL# > 2000
    Note that you could also use the substitution string #ROWNUM#. For example:

    #ROWNUM# > 2000
    >

    After reading this, it would appear that we could put #COLNUM# = 1 in the Column Template 1 Expression, however, when I do this, I get the following output when I run the page:
    ORA-06550: line 1, column 34: PLS-00103: Encountered the symbol "#" when expecting one of the following: ( ) - + case mod new not null table continue avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall merge year month day hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timestamp interval date Error ERR-1025 Error processing PLSQL expression. #COLNUM# = 1
    Could this be a bug? Or am I reading it incorrectly? Is #ROWNUM# the only substitution string in Table 10-6 that is usable in the Expression field?

    Austin
  • 20. Re: Is it possible to add a newline to a column?
    VANJ Journeyer
    Currently Being Moderated
    ERR-1025 Error processing PLSQL expression. #COLNUM# = 1
    Try enclosing #COLNUM# in single quotes
  • 21. Re: Is it possible to add a newline to a column?
    807466 Journeyer
    Currently Being Moderated
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    Error ERR-1025 Error processing PLSQL expression. '#COLNUM#' = 1
    >

    It would appear that the single quotes does nothing for substitution. It is now comparing the value #COLNUM# to the number 1 which is a type mismatch and the subsequent conversion error.

    Austin
  • 22. Re: Is it possible to add a newline to a column?
    VANJ Journeyer
    Currently Being Moderated
    Hmm, smells like a bug to me, maybe someone from Oracle can chime in...
  • 23. Re: Is it possible to add a newline to a column?
    807466 Journeyer
    Currently Being Moderated
    It is possible that it is bug, but I would expect that in the Column Template Expression the only valid substitution strings are:
    #1#, #2#, #3#, etc
    #SAL#,#EMPNO#,#DEPT#, etc (this would return the value of the column)
    and
    #ROWNUM#

    The documentation does not explicitly state that these are the only valid substitution strings but the line
    Note that you could also use the substitution string #ROWNUM#. For example:
    seems to infer that the #ROWNUM# string is the only other valid substitution string available.

    If this is the case, then that is unfortunate in my opinion. I can see where spreading the contents of a single SQL row over multiple HTML rows in a table could be a useful feature. In order to do that, you would need to reference which column in the SQL row you are on. Then again, perhaps there is another way.

    I do hope a developer or an Oracle representative could comment on this.

    Austin
  • 24. Re: Is it possible to add a newline to a column?
    25631 Newbie
    Currently Being Moderated
    Hi Austin,

    Wow thank you so much for all of your efforts! I really appreciate it. But I could hardcode the column values so that it would work for this one report but not be generic correct? But replacing

    #COLNUM#=1
    with
    #1#

    etc. correct?

    Thanks again
  • 25. Re: Is it possible to add a newline to a column?
    fac586 Guru
    Currently Being Moderated
    AustinJ wrote:
    After reading this, it would appear that we could put #COLNUM# = 1 in the Column Template 1 Expression, however, when I do this, I get the following output when I run the page:

    ORA-06550: line 1, column 34: PLS-00103: Encountered the symbol "#" when expecting one of the following: ( ) - + case mod new not null table continue avg count current exists max min >prior sql stddev sum variance execute multiset the both leading trailing forall merge year month day hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time >timestamp interval date Error ERR-1025 Error processing PLSQL expression. #COLNUM# = 1


    Could this be a bug? Or am I reading it incorrectly? Is #ROWNUM# the only substitution string in Table 10-6 that is usable in the Expression field?
    VANJ wrote:Hmm, smells like a bug to me, maybe someone from Oracle can chime in...
    If it is a bug then it's likely to be one of documentation rather than omission in the report template page.

    The online help accessed through the labels of the report template properties provides the clearest information on this, and <tt>#COLNUM#</tt> is only indicated in the Column/Row Templates themselves, not the Expressions.

    I gave up after 3 years of asking for the docs to contain chapter and verse on which substitution strings were supported where...

    See +{thread:id=2253993}+ for a similar thread.
    AustinJ wrote:
    If this is the case, then that is unfortunate in my opinion. I can see where spreading the contents of a single SQL row over multiple HTML rows in a table could be a useful feature. In order to do that, you would need to reference which column in the SQL row you are on.
    Can you explain that more fully? I don't [yet] see how it would help.

    Edited by: fac586 on 21-Jul-2011 17:35

    OK, now I've looked at the template in the OP's workspace I understand. You're trying to use a Generic Columns (column template). This needs to be Named Column (row template), in which you specify the HTML mark-up for the entire row, not per column. See the linked thread above.
  • 26. Re: Is it possible to add a newline to a column?
    Bob37 Pro
    Currently Being Moderated
    You need a concatenation after the <br>...
    
    
    SELECT e.evaluation_note_id, l.description, u.FIRST_NAME || ' ' || u.LAST_NAME || ' <br/>' || debriefer, e.evaluation_note, 
    '<b><a href="#">Notes</a>' "Notes"
      FROM evaluation_notes e, users u, lookups l
     WHERE u.user_id = e.created_by
       AND e.evaluation_id = :P2_EVALUATION_ID
       AND UPPER(l.lookup_type) = 'EVALUATION_TYPE'
     ORDER BY e.evaluation_note_id
    
    
    Note I added it between <br/> and debriefer.  Add additional fields you want with additional || for line two
    
    Example:
    
    Select col1 || col2 || '<br/>' || col3 || col4 || col5 || col6
    
    Results in 
    Last Name First Name
    Street City State Zip
    If the col fields related to a name and address as an example
    Edited by: Bob37 on Jul 21, 2011 1:43 PM
  • 27. Re: Is it possible to add a newline to a column?
    807466 Journeyer
    Currently Being Moderated
    Thank you fac586. I knew it was possible!

    Thanks for reminding/teaching us about the Named Column template.

    Austin
  • 28. Re: Is it possible to add a newline to a column?
    25631 Newbie
    Currently Being Moderated
    Thanks so much for a Solution Paul! And thanks again for all of your help as well Austin and everyone else...
1 2 Previous Next

Legend

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