1 2 Previous Next 28 Replies Latest reply: Jul 21, 2011 1:00 PM by 25631 Go to original post RSS
      • 15. Re: Is it possible to add a newline to a column?
        25631
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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