This content has been marked as final. Show 9 replies
This is a basic sql/plslql question and not APEX, you should post them into the relevant forum so that you will get quick response.
Basically your end query should look something like this
So you will have to amend your plsql block to produce this query
//concatenate the real column value select 'f?p=107:40:1467119559713::::IREQ_STATUS:'||COLUMN_NAME link, COLUMN_NAME name, count(activities_id) value from
Thanks for your reply. The PLSQL forum keep telling me to post in this forum, so...
I figure plenty of people here will have done this.
I've updated my query to show it more clearly - it follows the same (sensible) syntax you are suggesting, but still not working:
The filter it links, for example, will be STATUS:COLUMN_NAME (where STATUS is the column ALIAS and COLUMN_NAME is the name of the column rather than the contents (which would be 'Possible', 'Planned' etc).
DECLARE vSQL VARCHAR2(10000); vTABLE VARCHAR2(30) := substr(:P32_CATEGORY,-(length(:P32_CATEGORY)-instr(:P32_CATEGORY,'-'))); vJOIN VARCHAR2(1) := substr(:P32_COLUMN,instr(:P32_COLUMN,'-1-')+3,1); vJOINTBL VARCHAR2(30) := substr(:P32_COLUMN,instr(:P32_COLUMN,'-3-')+3,instr(:P32_COLUMN,'-4-')-instr(:P32_COLUMN,'-3-')-3); vJOINCOL1 VARCHAR2(30) := substr(:P32_COLUMN,instr(:P32_COLUMN,'-2-')+3,instr(:P32_COLUMN,'-3-')-instr(:P32_COLUMN,'-2-')-3); vJOINCOL2 VARCHAR2(30) := substr(:P32_COLUMN,instr(:P32_COLUMN,'-4-')+3,instr(:P32_COLUMN,'-5-')-instr(:P32_COLUMN,'-4-')-3); vALIAS VARCHAR2(30) := substr(:P32_COLUMN,-(length(:P32_COLUMN)-instr(:P32_COLUMN,'-5-')-2)); vPARAM VARCHAR2(30) := substr(:P32_COLUMN,0,instr(:P32_COLUMN,'-1-')-1); vVALUE VARCHAR2(30) := substr(:P32_DISPLAY,0,instr(:P32_DISPLAY,'-')-1)||'('||substr(:P32_DISPLAY,-(length(:P32_DISPLAY)-instr(:P32_DISPLAY,'-')))||')'; BEGIN IF :P32_DISPLAY is not null AND :P32_COLUMN is not null THEN vSQL:='select ''f?p=&APP_ID.:40:'||:app_session||'::::IREQ_'|| vALIAS ||':'|| vPARAM; vSQL:= vSQL ||':'' link, '; vSQL:= vSQL || vPARAM ||' name, '; vSQL:= vSQL || vVALUE ||' value '; vSQL:= vSQL || 'from '|| vTABLE; IF vJOIN ='1' THEN vSQL:= vSQL ||' join ' || vJOINTBL; vSQL:= vSQL ||' on ' || vJOINCOL1; vSQL:= vSQL ||' = ' || vJOINCOL2; END IF; vSQL:= vSQL ||' group by ' || vPARAM; vSQL:= vSQL ||' order by ' || vVALUE; ELSE vSQL:='select null link, ''All work activities'' name, count(*) value from aa_wl_activities3'; END IF; return vSQL; END;
So vPARAM is producing COLUMN_NAME within the link, yet it is producing 'Possible', 'Planned' as status items to label the chart. I am confused!
Can anyone shed any light?
If you debug your page then view debug > you should see the sql query that was returned from this function body > then you can see what is going wrong
Other option is to setup an example on apex.oracle.com
I've copied the app so you can take a look. I really would be grateful for any help.
To see the problem, go to "MI - Charts" and click "Pivot pie chart". Make some selections, e.g. Work + Count rows + Frequency and click "Redraw".
You should see the chart split its data by Frequency. Now if you click on a segment of the pie, it should take you to page 40 (tick) with a filter for e.g. FREQUENCY: ANNUAL. Instead it tries to filter on FREQUENCY: NAME (where NAME is the column name).
You can see the code I'm using in previous posts in this thread.
Can anyone help?