Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Sunday, 17 April 2016

Create Oracle report Using Wizards



Report Builder has a Report Wizard, Data Wizard, and Web Wizard to automate the creation of reports. Most often, you can create a report by starting with one of the wizards, and then refining the report that the wizard creates using the data model view and the layout view.

To create a report using report wizard

1. If the Welcome dialog box appears (the following screen), click Use the Report Wizard and click OK.


If the Welcome dialog box does not appear, choose File->New->Report. Click Use the Report Wizard and click OK.

2.You will receive the following screen unless you selected not to display it in the future previously.    Click next




3. Choose the type of layout you would like to generate from following screen.
   Click next


4. Select Report type; this will affect report layout.  you can see the different layouts by clicking their corresponding buttons and looking at the sample layout on the left. After you select the type you want and give Tile of report

Click next


5. Choose a data source and Click next


6. you need to build the and placed your query as below and click Next


7.If you did not connect the database, connect database and click on Next


8. Select the fields you would like to display in the report in the order you want as shown in the next screen. Then click Next


9.Select the fields you would like to display in the report in the order you want as shown in the next screen. Then click Next


10.If you want change label of report columns you can change in following screen and click on Next

11.If you choose any template, fonts, colors, line widths and structure for your report and click Next and click finish you can find report out as below


You May Like:

Share this article :

2 comments:

  1. Hi Bru,


    This is indeed great! But I think perhaps you are generally referring ,which is getting unsustainable.


    I need to check various columns of a particular table to see if they are not really being populated or the data is always the same. So that we can perhaps remove some columns going forward. This table will be filtered by std_job_no column as i have a list of values for this column which pertain to a particular area of the data.

    To retrieve the column names for the table i have written this which produces the correct results.. note i do not require std_job_no column as i will be using this later as part of the group by query.

    SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
    WHERE OWNER ='ELLIPSE'
    AND TABLE_NAME='MSF690'
    AND COLUMN_NAME<>'STD_JOB_NO'
    ORDER BY TABLE_NAME

    Now i want to loop through these column names and substitute column_name into the following query

    select
    COL,UMN_NAME, COUNT(STD_JOB_NO)
    from msf690
    where std_job_no in (
    '009045', '009053', '009188', '009189', '009190', '009236', '009275', '009310', '009319', '009320', '009321', '009322', '009323', '009324', '009325', '009326', '009327', '009328', '009419', '009459', '009460', '009461', '009462', '009463', '009464', '009582', '009590', '009591', '009594', '009616', '009617', '009618', '009619', '009620', '009621', '009622', '009623', '009624'
    )
    and dstrct_code='RTK1'
    GROUP BY COLUMN_NAME

    So i would then get the list of all values for each column name and how often they are populated.

    It should be something along the lines of..

    DECLARE

    BEGIN
    FOR RRR IN (

    SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS
    WHERE OWNER ='ELLIPSE'
    AND TABLE_NAME='MSF690'
    AND COLUMN_NAME<>'STD_JOB_NO'
    ORDER BY TABLE_NAME
    )
    LOOP
    EXECUTE IMMEDIATE 'select ' ||
    RRR.COLUMN_NAME || ', COUNT(STD_JOB_NO)
    from msf690
    where std_job_no in (
    '009045', '009053', '009188', '009189', '009190', '009236', '009275', '009310', '009319', '009320', '009321', '009322', '009323', '009324', '009325', '009326', '009327', '009328', '009419', '009459', '009460', '009461', '009462', '009463', '009464', '009582', '009590', '009591', '009594', '009616', '009617', '009618', '009619', '009620', '009621', '009622', '009623', '009624'
    )
    and dstrct_code='RTK1'
    GROUP BY RRR.COLUMN_NAME'

    and then i want to dbms_output this without inserting the values into a table as I don't have permissions to create tables.








    Great effort, I wish I saw it earlier. Would have saved my day :)


    ,Merci

    ReplyDelete
  2. Hi Dayakar,


    In debt to you for making my learning on the Create Oracle report Using Wizards area so hassle-free! I lay my faith on your writings.

    I am facing the below issue(Screen shot) with CREATE_JOB procedure. The issue is with CREDENTIAL_NAME attribute.

    More information on this:

    I tried to create the job for one of the

    predefined chain.


    why I am using CREDENTIAL_NAME attribute is, in one of the chain step I am calling LINUX script.

    I read multiple articles and watched many videos about how to use this tool - and was still confused! Your instructions were easy to understand and made the process simple.


    Many Thanks,
    Ajeeth

    ReplyDelete