5 Replies Latest reply: Jul 21, 2014 1:29 PM by mac1085 RSS

    Date Range - CM14 and BI Publisher

    mac1085

      Is there a way to make a date range prompt for reports for CM14 with BI Publisher.

       

      I can get it to work in BI Publisher but it does not transfer to CM14.

       

      Any ideas?

        • 1. Re: Date Range - CM14 and BI Publisher
          Matias Guajardo Uribe-Oracle

          Hi,

           

          The prompt for criteria will need to be added to report's data model within BI Publisher.  After logging into the Oracle BI Publisher User Interface, do the following:

           

          1.- Click the Edit link for the data model that is associated with the report that you want to add the prompt to.  If you are prompted that the Default Data Source is invalid, select the correct data source from the available drop down list.

           

          2.- Click the Parameters link located towards the bottom left of the screen.

           

          3.- Click the green plus symbol to add a new, blank row.

           

          4.- For the Parameter Name field, enter the following: CRITERIA

          ***Note - Ensure that the Parameter Name field is populated with all capital letters***

           

          5.- For the Data Type, select String

           

          6.- For the Default Value, you will enter information about every field you would like to prompt on.  The format for this information will be: table_name.column*datatype(length)^.  This will be repeated for as many columns as you would like to be able to prompt on.  Here is an explanation of each component:

           

          • table_name - The table or view that the field being prompted on exists in.
          • column - The column name of the field being prompted on.
          • * - The asterisk connects the field's database information with the field's input specifications
          • datatype - The data type of the field being input.  This tells Contract Management how to handle the input. For example, if you use datetime, it will know to put a calendar picker in the Prompt for Criteria window when printing.
          • length - The maximum length that a user will be able to input in the Prompt for Criteria text box.  This value will not always be required, as datetime do not allow manual entry, only picker selection.
          • ^ - The carrot denotes the end of the column specification.  Any other columns could then be added after in a continuous string. You can add as many as you like.

           

          Example: The following would be used to be able to prompt on a Contract's To Vendor, Total Cost, and/or Document Date:

           

          cnmt.to_vendor*char(8)^cnmt.total_cost*int(16)^cnmt.document_date*datetime^

           

          7.- For the Parameter Type, select Text

           

          8.- Click the Save (Floppy Disk) icon in the top right to save the changes and test the report further.

           

          9.- Log into the Contract Management Administration page (http://server_name:port/exponline/adminlogon.jsp)


          10.- Click the Server Configuration link and then click the Import Reports & Forms button

           

          11.- Restart the Contract Management web server to make this new prompt available to your end users.

           

          Best regards,

          Matías G. Uribe

          • 2. Re: Date Range - CM14 and BI Publisher
            Rudy Ising

            Matias' directions are spot on for setting these up.  A couple of additional comments:

             

            1.  You may have only one "CRITERIA" parameter, so if you DO want to prompt for multiple criteria, make sure that you set them up as Matias illustrates in #6.

            2.  Date Ranges (greater than/less than) must be input by the user using the same methods as you did in older (Infomaker) versions.

            3.  For non-date fields, remember that the user must input them exactly as they appear in the database, which it be a code or abbreviation and using the same CASE as you find in the source data.

            4.  You must call on table.fieldnames; not an alias or expression in your SQL statement.

            • 3. Re: Date Range - CM14 and BI Publisher
              mac1085

              I am not familiar with the methods in Infomaker.

               

              How do I make it a date range? Does the user input the greater than or less than in the prompt box?

              • 4. Re: Date Range - CM14 and BI Publisher
                Rudy Ising

                When you set a date field in either Infomaker or BI, the USER is given a "criteria" pop up window with a box labeled "criteria" and another "Or".  Typically next to these is a "Calendar" icon.

                 

                In order to enter a date RANGE, you cannot use the calendar icon, but will TYPE in the boxes.  Use this sytax below:

                 

                1. Criteria:
                  1. “>1/1/12” (if you want greater or equal to
                    use “>=1/1/12”)
                2. Or:
                  1. “AND <12/31/12” (if you want greater or equal
                    to use “AND <=12/31/12”)

                 

                 

                Note:  you are basically attempting to say "greater than this date AND less than another date" which is why you MUST type the AND in the "Or" box.

                 

                If you bype all of this in the "Criteria" box you will get an error, so you have to split it up this way AND just type these in.  (If you click the Calendar icon, you will ONLY retrieve the specific dates you pick, not a range.)

                • 5. Re: Date Range - CM14 and BI Publisher
                  mac1085

                  Is there a way to create a date range in a Form?

                   

                  Also, is there a way to display the date range on the report?

                   

                  Thank you for your help.