A simplex apex DEPT/EMP based report

Today i've been doing some more work with Apex, i don't do that much of this and i'm entirely self taught (that's a disclaimer if how i do this following demo is done badly....).

I wanted to display a simple report screen where a simple drop down list at the top influences what is shown in the bottom half of the screen - so sort of a parent/child report (well kind of). I got it working how i wanted in the end and thought i'd share it here as much for myself as anything so i know how to do this when i come to it again.

I don't want to use my actual screen as it contains loads of company data so i've repeated the example using the emp/dept tables that everyone is likely familiar with.

Getting these loaded in now however is quite a pain so i just cut and pasted the SQL to do that from the following site. I've blogged before about how over complicated the whole demo install process is - anyway ignoring that for now- i just created emp and dept and filled them with demo data.

The plan for the screen is to then choose the dept at the top and display the employees for that department below - should be simple enough......?

Now i've done this by adding a page to an existing app - but you could just as well create a brand new one with a single page.

So first up i click 'create page' => 'blank page' => 'next' => chose a page name here - i used 'demopage' => then on to next screen and 'do not use tabs' => then confirm

Now i just have a page with nothing on it - if you run it you just get a blank screen.

Now i need to create a list of values element to appear in the top part of the screen - there are a few ways to do this - i'm creating it as an LOV in the shared components area

So i now click 'Shared Components'=> Lists of Values=> create from scratch=>next=> give it a name - i chose 'deptlist'  and made in dynamic as i want the list to be created from whats in the table ot a hardcoded list in APex. 

On the last screen i now have to create some SQL to build the LOV - this must return both a name and an identifier so - in my case its just a simple

select dname,deptno from dept order by 1 ;

Which returns this in sqlplus

DNAME              DEPTNO
-------------- ----------
ACCOUNTING             10
OPERATIONS             40
RESEARCH               20
SALES                  30

The ordering is of course up to you - it's the order it will appear in on screen.

Now i have an LOV object but i make no reference to it on screen yet so lets do that.

I go back and edit my demopage and click on the create region icon.

Here i again choose the html option=> then 'html' => next => i give it a title of 'headerregion'=>next=> next => create region

If i run the page now it looks like this

i.e. - particularly dull - i need to add on the lov to that header region - so lets do that

So i click on the + icon in the items area of the page and choose the 'select list' from the choice, i give it a name of selheader, then next , next, on the lov screen i choose the lov i created earlier and choose display null value as No, then next and create item.

Now when i run the screen i see this

A step forward - now i need the employee details screen

So lets add a new reports region, so i click the + button next to the region section on the admin screen.

From there i choose report => classic report +> give it a name i chose 'employees'=> then i enter the select statement in my case (being lazy) i just choose

select * from emp where deptno = :SELHEADER (to pass in the id number selected from the top half of the screen)

and i also set 'page items to submit' to be SELHEADER

i then click create region and skip the other 2 screens.

**** AN important side note here on how LOV's are working - the display value on screen is not the value of the page item value variable - this is instead the deptnovalue. So the LOV query you define is essentially 

select "display value", "item variable value" from blah blah blah - 

this is an important point to realise as you may think the item has a certain value when it doesn't and if you refer to that value in other SQL you may not get the behaviour you expect ******

Now my screen looks like this - but the screen doesn't work - choosing the value at the top has no effect

Now the reason for this is that in the SELHEADER item i did not set 'page action when value changed' - if i now change this to 'submit page' then the screen does work - though not 100% how i want

if i choose RESEARCH for example i now get this

This works great - however on screen load when ACCOUNTING is the default nothing shows, if i click RESEARCH and then go back and click ACCOUNTING it works OK - but the initial screen shows no data - which is not what i want

The easy way out is to just re-enable null values on the SELHEADER item and then give the null display value a value of 'select department...'

So i do that and the screen now looks like this

Now whatever i choose is different to the dummy value first shown and causes the bottom screen query to be re-executed - so i get this

And there you have it - a very simple example of how to do this kind of screen - it's very easy and i often find simple examples in Apex are hard to find. I think a firm grip of the basics is very important as with such a flexible tool there are many ways to do the same thing and you can easily miss the simple solution (as i have done many times in the past)