30 responses

  1. Ronald
    September 19, 2012

    Hi Jeff,
    I just added a few tweaks to make it generally usable. It makes sure that the dbid, instance_number and startup_time are the same for both queries. I hope it is usable for others too. It would not hurt to make awr reports accessible for humanoids using SQLDeveloper. It’s a nice tool with growing power.

    horizontal

    horizontal

    :START1
    and startup_time = :SUPT1
    and instance_number = :INUM1
    and dbid = :DBID1
    ORDER BY 1 asc]]>

    horizontal

  2. Stuart
    May 1, 2013

    Hi Jeff,

    Thanks for sharing this. I had a go, and what a difference it made having the ability to drill down to another level.

    Are we ever going to see this type of feature (nested child reports, grandparent reports) built into SQL Developer? I’d be hanging out for that release if/when it came…

    Stuart.

    • thatjeffsmith
      thatjeffsmith
      May 1, 2013

      I just need more people asking for it. I’m already sold on the idea. We have some exciting stuff for reports in 4.0 due soon. This won’t be in it, but maybe we can stir up some excitement for multiple-level reporting.

      • Stuart
        May 1, 2013

        Maybe people just don’t know about the user defined reporting in SQL Developer?! Pity,… it is such a great feature. Only limiting SQL Developer to Parent-Child reports is one reason why people at our site don’t use it (it simply doesn’t provide enough drill down capabilites).

        Thanks for the info on SQL Developer 4. I must say that reporting enhancements is the first thing I look for in the new features list.

        Stuart.

      • thatjeffsmith
        thatjeffsmith
        May 1, 2013

        I spend a lot of time on promoting existing features to users. Folks use what they know, do what works, etc. If you look for it though, it’s very hard to miss. It’s a popular feature for many folks, so I think anything we do here has large potential to benefit many people. By that I mean, for the folks that like the reporting, they build LOTS of reports. I can think of 2 things you’ll like A LOT in 4.0, stay tuned.

        I’ll try to remember to email you when it’s out so you don’t miss it, or the features.

        BTW, I miss the pumpkin soup and toast that is so good in NZ :( I need to make it back someday.

      • Stuart
        May 1, 2013

        Maybe get a free trip down under by being a guest speaker t the next NZOUG? ;-)

        We just had one in March, so expect the next one to be late 2014.

      • thatjeffsmith
        thatjeffsmith
        May 3, 2013

        Get me an invite, and I’ll have a hard time saying ‘no!’ This has been my secret plan for awhile. Just waiting for the kids to get a bit older before I leave town for a few weeks with their momma back to our favorite place.

  3. Andrey Goryunov
    June 12, 2013

    Hi Jeff,
    is it possible to configure Drill Down feature for child reports through edition of reports XML?

    Andrey.

    • thatjeffsmith
      thatjeffsmith
      June 12, 2013

      The developer says ‘Yes,’ but I haven’t confirmed that independently, yet. A topic for tomorrow perhaps?

  4. Andrey Goryunov
    June 12, 2013

    I was able to add drill down report to a child (inserted … within tag for child) but it appeared in a menu for parent report as well and when I put several child reports there and defined drill down for them the same as for other children, for parent report I got it duplicated several times

  5. Luc Poitras
    July 17, 2013

    Greetings Jeff!
    I’m a SME in accounting/finance. I have to tell a programmer what I want in my database and what “machinations” I want done in it. Can you suggest a pro-forma document I could provide to the programmer? ( maybe direct me to an example).
    Thanks.

    Luc (Montreal, Canada)

    • thatjeffsmith
      thatjeffsmith
      July 17, 2013

      My first instinct was to suggest building a logical data model…but I think I need to know more about what you mean by a ‘machination.’

      • Luc Poitras
        July 17, 2013

        Thanks Jeff.
        What reports will be produced…source of the data for each report. Some forms will be multi-level…need to enter an asset then another one and so on.
        Some fields will have drop-down choices. How do I present all of this in a document to the programmer? That’s what I’m looking for. Hope you can help.
        Luc

      • thatjeffsmith
        thatjeffsmith
        July 17, 2013

        I would create a requirements doc for each report where you describe the business problem you’re trying to solve. You could mock up screenshot examples, but trust the developer to handle the implementation/tech stuff.

      • Luc Poitras
        July 17, 2013

        Thanks Jeff!
        -Luc

  6. vignesh
    February 10, 2014

    Hai friends am using oracle sql server…am create two tables with foreign key support
    but.after finish my work.try to delete table.while deleting.it’s show some error message first delete child table . i don’t know how to find which is child table ..please help me. anybody……..

    integrity constraint (SYSTEM.FK1) violated – child record found
    02292. 00000 – “integrity constraint (%s.%s) violated – child record found”
    *Cause: attempted to delete a parent key value that had a foreign
    dependency.
    *Action: delete dependencies first then parent or disable constraint.

    • thatjeffsmith
      thatjeffsmith
      February 11, 2014

      I’m guessing you mean Oracle SQL Developer :)

      You can’t delete a parent record if it will leave orphan rows. You say you created two tables with foreign keys – have you already forgotten those two tables?

      You can see the referential integrity constraints for each table on the table editor. Open a table, go to the Constraints page. Look for constraint_type of ‘Foreign_Key.’ Then scroll over for the R_TABLE_NAME – that will tell you where your parent record is. If you want to know what the child tables are, you should be able to import your DD to a data model and see that visually.

  7. Greg Smith
    March 25, 2014

    Hi Jeff,

    I was able to “hack” the XML as per this post and get Grandparent=>Parent=Child to work.

    I’m even able to have 2 tabs at the Parent level (each with it’s own child).

    Basically I’m asking at the Grandparent level for the user to tell me the “Batch Job” they want to know about.

    Parent level then gives the “executive summary” of what happened

    Grandchild level will allow a further drill into the details.

    Is there a way I can have the selection of “Job” at the Grandparent level also select the right TAB at the parent level? By default it always passes the data from GP to the leftmost Parent Tab (which is only appropriate for 1 choice at the GP level).

    If I have 20 reports at the GP level
    I’ll have 20 tabs with the right query at the Parent Level
    Then 1 Child for each parent that has the right detail query at the child level.

    I was hoping there might be some sort of

    onclick=”onSelectChildTab(this, 1)

    type syntax like you get when doing and export of a report that has a Parent => multiple children when viewing the HTML output

    Thanks,

    Greg

  8. Greg
    March 27, 2014

    horizontal

    null

    horizontal

    null

    horizontal

    null

    horizontal

    null

    horizontal

    null

  9. Nora
    June 27, 2014

    Why not make it a simple master detail with the master query being:
    SELECT * FROM
    (SELECT dbid, instance_number inst_id,
    LAG(snap_id) OVER (PARTITION BY startup_time ORDER BY snap_id) prev_snap_id, snap_id, begin_interval_time, end_interval_time
    FROM dba_hist_snapshot
    ORDER BY 3

    and the child query being:
    SELECT output FROM TABLE (dbms_workload_repository.awr_report_text(:DBID, :INST_ID, :PREV_SNAP_ID, :SNAP_ID))

    • thatjeffsmith
      thatjeffsmith
      June 27, 2014

      Sure – but the main reason I wrote the post was to show how to do multiple levels of child reports.

  10. Kelvin
    October 30, 2014

    Hi, and what about drill down from a child report?

    • thatjeffsmith
      thatjeffsmith
      October 30, 2014

      You can probably add it by hand by editing the XML

  11. Mirjana
    February 10, 2015

    Hi, I managed to get grandparent-parent-child report and it works. But when I generate HTML report the child portion of the report is not there. I’m on SQLDeveloper 4.0.0.13.
    Is this going to be supported in the new release?

  12. Satya
    February 20, 2015

    Hi, I used this method to create multiple levels of child reports in the past. When I just tried in the latest version of SQL Developer, it doesn’t seem to work. Is that right or am I possibly doing something wrong?
    Thanks.

  13. Koen
    March 18, 2015

    Hi Jeff,
    First of all, thank you very much for your great blog, I return to it quite often.
    I did get all of this to work BUT…
    a) If I add a drill-down to a child or grandchild, it also comes up on the parent (respectively grandparent). I’d like to change that since the specific drill-down doesn’t apply there but can’t seem to get it out even though I put removeFromParent=”true” in the XML (attribute doesn’t do what I hoped it would but it was worth a try…)
    b) What’s worse; the drill-down from a child doesn’t work the first time I call it. Well, it opens up the report but the binds are not passed. The only way I can get a functional drill-down from child so far is: first drill down from parent, after the report opened click “back”, select a record in the child and do the drill-down from child. So if I make my selection in parent and child and drill down from the child first time around, the binds come up as Null; I always have to do the round-trip from parent first.
    c) If I do a drill-down from a child, the “back” button doesn’t work.
    d) Drill-down from the toolbar never passes binds, regardless of the selection and sequence of events/clicks.

    That’s quite a couple of issues to put in one reply but if you could give me a hint on how to resolve b), that would be really, really appreciated :-)

    Now I realize this post was about multiple levels of child reports hence all the drill-down questions may be a bit out of place but the functionality just screams to be used this way; get to a specific item/selection with a couple of clicks in your dashboard report and run a detailed (drill-down) report without having to pass all those binds by hand (copy-paste).

    (Version 4.0.3.16)

Leave a Reply

 

 

 

Back to top
mobile desktop