APEX: Report Column Wrapping

APEX 3.2.1.00.12
- Template 12
Database: 10gR2

Just a quick note, mostly for myself.

I was asked about getting a report column to wrap. The method being used was the CSS Style property of the Report Attribute (Column). The report template was the Standard Alternating Row Colors.

The report looks like this:



Very ugly, I know.

So how do you fix this?

You can fiddle with the CSS Style (I'm lazy) if you want, or you can do something like this.

In the HTML Expression box:



Add the following:

<table style="width:200px;">
  <tr>
    <td>#X#</td>
  </tr>
</table>

That #X# is the column/report name.

Using that will get you this:



The ideal would be to get the correct CSS in there, but like I said, I'm lazy. This worked for me going back to 1.5 (HTMLDB) on Template 12.

OBIEE: Report Drilldown

As I am doing mainly metadata work right now, I figured it would be good to get a better grasp of how the reporting works on the presentation layer, i.e. Answers.

To that extent, I've been having problems getting the drilldown reports to work the way I want them to. The problem is that the ID associated with the level is displaying, not the value.

I'm using the Time Dimension:

Total --> Year --> Quarter --> Month



After initially creating it, I create an Answers report with one column, CALENDAR_YEAR.



Might be hard to notice, but the year is hyperlinked, I should be able to click it and drill down to the quarter right?



That's not what I want. I don't want to see the ID associated with it. That's meaningless to the user. You might be thinking, "What an idiot, why did he make the ID available in the presentation layer?"

I just thought of that too...removed it, and I received an error.

Anyway, the same thing (%_ID columns showing up instead of their description).

Click on Year (again) and I get the Quarters...well, the ID anyway.



Click on the quarter (ID) and I get the month ID.



So how do I fix this?

First, go back to the Admin tool, find the dimension and double click on Month to bring up the properties page



Select the Keys tab



You should see CALENDAR_MONTH_ID with the Chronological Key box checked. Select New



Now check CALENDAR_MONTH_NUMBER and make sure that Drilldown is checked.



That should take you back to the Keys screen. Now either double-click CALENDAR_MONTH_ID or select it and click Edit down at the bottom



Now uncheck the Use for Drilldown box



Rinse and repeat for each of your dimension levels; Quarter and Year.

Go back to your report, remove the filters and remove everything but CALENDAR_YEAR, run the report, drill down and you should see this:



Voila!

You can use whatever columns you want to display. You don't have to use the Month Number, you could use March instead.

APEX: Database Object Dependencies

I have this tendency to talk about APEX a bit...at my current client, they recently began using it. When one of their applications broke (built by a consultant), I was asked to help out.

Love the APEX.

In particular, I was asked if I could find out how the security was being implemented. Active Directory was being used to authenticate, but they weren't quite certain how authorization was used. I was given a package body and got started.

First thing I thought of was the Database Object Dependencies report. This is one of my all-time favorite features of APEX. It allows you to easily find all the references to database objects and includes a link to see each page where said object is used. Makes learning about an application super easy.

To get to this report, go to your application home. Off on the right side, you'll see a link that says Application Reports.



That first section there under Application you should see a link for Database Object Dependencies



You'll probably have a blank page, go ahead and click on the Compute Dependencies button



You'll get a lovely report like this



Now you can drill down and find the pages where the object is referenced. I chose DEMO_CUSTOMERS



Click on the page number to go to...you guessed it, that page. Remember the name of the component, locate it and see your object.

How nice is that?

OBIEE: Single/Detail Record View

Recently, I've been searching for a Single Row/Detail type report in Answers. Sadly, there isn't one so named.

Here's what I could find in Answers:



Nothing jumps out at me, so, Twitter to the rescue again



Within minutes...I had an answer from Mr. Christian Berg. What was that answer? The Narrative View.

Here's the screen for the Narrative View:



According to Mr. Berg, you reference the columns using the @1 (@2, @3, etc) syntax, the number representing the column number. So I tried that



Run it...



That's not what I want...

OK, so let's see if I can add some HTML to it. First I check the Contains HTML Markup box and then I enter in the following in the Narrative Text Area:

@1<br>

@2<br>

@3<br>

@4<br>

@5<br>

@6<br>

@7<br>

@8<br>

@9<br>

Here's what it looks like:



Better, but not exactly what I want. Now that I know I can use HTML though, it should be very easy.



Voila!

SQL Developer: Drill Down Reports

Finally, finally I've figured this out. I've googled "SQL Developer Drillable Reports" to no avail. The solution kept alluding me.

The first result you should get back is one from a fellow Tampan (Tampon?), Lewis Cunningham, from July 2006. OK, it's a bit old (I think it was still called Raptor back then), but I'll give it a try.

In it, Lewis talks about creating additional "pseudo" columns, SDEV_LINK_NAME, SDEV_LINK_OWNER, SDEV_LINK_OBJECT which appear to map to the corresponding columns in DBA_OBJECTS.



I tried that, and got...nothing. I tried changing the alias(es) to match the column I was using, again, to no avail.

Let me back up just a tad, I'm trying to create some reports based on the PLSQL_PROFILER_% tables:

* PLSQL_PROFILER_RUNS
* PLSQL_PROFILER_UNITS
* PLSQL_PROFILER_DATA

It's annoying to have to rewrite the SQL everytime. I did create a @profile script, but I had to pass the RUNID; so first, I had to know the RUNID.

So I took to Twitter as I know Kris Rice hangs out there sometimes.







That was last week, and I have been unable to get this to work. I could have sworn Kris had a good tutorial on it, but I think I confused it with the extensions you can create.

Anyway, I'm at it again tonight and I end up back at the link Kris originally pointed me to. For some reason (cough) I missed this crucial little nugget this first time

(the bind variable is case-sensitive)

Really? Could it be that easy? I UPPERed RUNID and voila! It worked!

To recap, go to the Reports tab, right click on a folder (I have one named "profiler") and select Add Report.



I fill out the Name, Description and Tooltip (optional)



Hit Apply which saves my report. Now I want a report that on PLSQL_PROFILER_UNITS that accepts the RUNID as an IN parameter.

First, create the report:



Go to the Binds tab and fill in the fields



Go to the Advanced tab and fill in the name of the report



Now, select your first report, right click, go to Reports and select the report you just created





Perfect!

Just a small reminder, the bind parameters are CASE SENSITIVE!