Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Dynamic Sql pivot procedure - HELP!

RichDWOct 4 2019 — edited Oct 18 2019

Apex 5.1.4
Oracle Express 11g

Can someone help me with the procedure and or my call to the procedure...?

I have a reference table with 'agent' names, and a primary table of 'referral' records.
Each referral record contains an agent.id in the referral.agentid field.

I am trying to build a dynamic pivot to return the dates of referrals and the agent names associated with
each referral - for a period... grouped by the referral date (senton).  So ideally the output would be

dates        name1  name2  name3  name4
09/23/19       0       1             3             0
09/24/19       4       2             0             5

Agents are grouped by office (agent.officeid); name stored as agent.name.  Each office can
have different number of Agents... so part of the procedure call will eventually include the officeid

For the 'IN' part of the pivot, I pull a list of all agent names associated with the
office in question:

select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME)  into v_list 
   FROM agent
   WHERE officeid=11;

This example office output is:
Alvin Ross,Betty Simons,Chanel Cooper,Daniel Scoggins,Dena Strother,Doris Hendricks,Jessica Elledge,Joshua Stephens,Terrence Simpson,Test Region

Then I add single quotes around each name:

v_alist := v_alist || '''' || replace(v_list, ',', ''',''') || '''';

resulting in:
'Alvin Ross','Betty Simons','Chanel Cooper','Daniel Scoggins','Dena Strother','Doris Hendricks','Jessica Elledge','Joshua Stephens','Terrence Simpson','Test Region'

The pivot SQL is:

select *
       FROM (SELECT referral.senton, agent.name
               FROM referral  
                 join agent on referral.assignedto=agent.id)

               PIVOT (Count(*) FOR name IN
                    ('||v_alist||'))';

Each section above is working individually but I am having problems getting this all in one procedure
to return what is expected.  My inexperience raises it's ugly head at this point as once I have it
in a procedure, I have difficulty diagnosing what is broken.  I've borrowed an example from
https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql

I access the procedure with:

begin
print_pivot();
end;

Issues that I have identified:
1- simplest form of procedure (no group by, no from/to, no officeid) errors with
     ORA-06550: line 2, column 1:
     PLS-00306: wrong number or types of arguments in call to 'PRINT_PIVOT'        (there are no arguments?!)
2- have yet to group by senton date
     when adding group by to either part of the pivot stmt: errors with 'not a group by expression' or 'invalid identifier'

3- have yet to add code for the from/to dates and the officeid
   

The complete Procedure is:

create or replace PROCEDURE Print_Pivot(
  pcursor OUT sys_refcursor,
  presult OUT varchar2
    )
AS
v_list varchar2(4000);
v_alist varchar2(4000);
v_query varchar2(4000);

BEGIN
PRESULT := 'Nothing';

-- pull list of agent names for that office then insert single quotes around names in list

select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME)  into v_list 
   FROM agent
   WHERE officeid=11;

v_alist := v_alist || '''' || replace(v_list, ',', ''',''') || '''';

-- dynamic query

  V_QUERY := 'select *
                from (SELECT referral.senton, agent.name
                       FROM referral  
                       join agent on referral.assignedto=agent.id)

                PIVOT (Count(*) FOR name IN
                    ('||v_alist||'))';

-- run query
    OPEN PCURSOR
     FOR V_QUERY;

PRESULT := 'Success';

Exception
WHEN OTHERS THEN
PRESULT := SQLcode || ' - ' || SQLERRM;

END Print_Pivot;

This post has been answered by fac586 on Oct 17 2019
Jump to Answer

Comments

fac586

RichDW wrote:

Apex 5.1.4
Oracle Express 11g

Can someone help me with the procedure and or my call to the procedure...?

I have a reference table with 'agent' names, and a primary table of 'referral' records.
Each referral record contains an agent.id in the referral.agentid field.

I am trying to build a dynamic pivot to return the dates of referrals and the agent names associated with
each referral - for a period... grouped by the referral date (senton). So ideally the output would be

dates name1 name2 name3 name4
09/23/19 0 1 3 0
09/24/19 4 2 0 5

Agents are grouped by office (agent.officeid); name stored as agent.name. Each office can
have different number of Agents... so part of the procedure call will eventually include the officeid

For the 'IN' part of the pivot, I pull a list of all agent names associated with the
office in question:

select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME) into v_list
FROM agent
WHERE officeid=11;

This example office output is:
Alvin Ross,Betty Simons,Chanel Cooper,Daniel Scoggins,Dena Strother,Doris Hendricks,Jessica Elledge,Joshua Stephens,Terrence Simpson,Test Region

Then I add single quotes around each name:

v_alist := v_alist || '''' || replace(v_list, ',', ''',''') || '''';

resulting in:
'Alvin Ross','Betty Simons','Chanel Cooper','Daniel Scoggins','Dena Strother','Doris Hendricks','Jessica Elledge','Joshua Stephens','Terrence Simpson','Test Region'

The pivot SQL is:

select *
FROM (SELECT referral.senton, agent.name
FROM referral
join agent on referral.assignedto=agent.id)

PIVOT (Count(*) FOR name IN
('||v_alist||'))';

Each section above is working individually but I am having problems getting this all in one procedure
to return what is expected. My inexperience raises it's ugly head at this point as once I have it
in a procedure, I have difficulty diagnosing what is broken. I've borrowed an example from
https://stackoverflow.com/questions/15491661/dynamic-pivot-in-oracle-sql

I access the procedure with:

begin
print_pivot();
end;

Access it from where?

Issues that I have identified:
1- simplest form of procedure (no group by, no from/to, no officeid) errors with
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'PRINT_PIVOT' (there are no arguments?!)

According to the procedure definition there are two OUT parameters, and these are not supplied in the call above.

2- have yet to group by senton date
when adding group by to either part of the pivot stmt: errors with 'not a group by expression' or 'invalid identifier'

3- have yet to add code for the from/to dates and the officeid

The complete Procedure is:

create or replace PROCEDURE Print_Pivot(
pcursor OUT sys_refcursor,
presult OUT varchar2
)
AS
v_list varchar2(4000);
v_alist varchar2(4000);
v_query varchar2(4000);

BEGIN
PRESULT := 'Nothing';

-- pull list of agent names for that office then insert single quotes around names in list

select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME) into v_list
FROM agent
WHERE officeid=11;

v_alist := v_alist || '''' || replace(v_list, ',', ''',''') || '''';

-- dynamic query

V_QUERY := 'select *
from (SELECT referral.senton, agent.name
FROM referral
join agent on referral.assignedto=agent.id)

PIVOT (Count(*) FOR name IN
('||v_alist||'))';

-- run query
OPEN PCURSOR
FOR V_QUERY;

PRESULT := 'Success';

Exception
WHEN OTHERS THEN
PRESULT := SQLcode || ' - ' || SQLERRM;

END Print_Pivot;

These issues are only really of academic interest as a procedure returning a REF CURSOR result is effectively useless in APEX.

Instead, create a classic report region with a Function body returning SQL query data source that generates and returns the text of the dynamic pivot query:

(The WHEN OTHERS clause is useless too...)

RichDW

fac586 - thank you for the direction.

I changed the report type to 'Classic Report (based on Function)' with source 'PL/SQL Function Body returning SQL Query':

Declare
v_list varchar2(4000);
v_alist varchar2(4000);
v_query varchar2(4000);

BEGIN

-- pull names from table column

select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME) into v_list
FROM agent
-- WHERE officeid = NV('P60_WG');
-- WHERE officeid = :P60_WG;
WHERE officeid = 11;

-- concatenate names in list inserting single quotes
v_alist := v_alist || '''' || replace(v_list, ',', ''',''') || '''';

-- dynamic query
v_query := 'select *
from (SELECT referral.senton, agent.name
FROM referral
join agent on referral.assignedto=agent.id)

         PIVOT (Count(\*) FOR name IN ('||v\_alist||') )';

return(v_query);

END;

I also changed the Attributes:Heading:Type from Custom Headings to PL/SQL Function Body:

Declare
v_list varchar2(4000);
v_alist varchar2(4000);

BEGIN

-- pull names from table column
select LISTAGG (NAME, ',') WITHIN GROUP (ORDER BY NAME) into v_list
FROM agent
WHERE officeid = NV('P60_WG');

-- concatenate names in list inserting Colon between names
v_alist := v_alist || 'SENTON:' || replace(v_list, ',', ':');

return(v_alist);

End;

With a hard coded value in my report SQL (WHERE officeid=11;)

and using a Bind variable in the PLSQL for the header query (WHERE officeid =NV('P60_WG';) )

the report works well (disregard the parameters region at this time):

output-HardcodedQuery-CustomheadingBind.JPG

However when using a bind variable in the report SQL (WHERE officeid= NV('P60_WG');) - I get an error:
failed to parse SQL query:
ORA-01403: no data found

Why am I having trouble using a page item value within the PL/SQL function?

Here is the debug for the failed report using a bind variable:

|

Execution

|

Message

|
|

  1. 0.00000

|

Reset NLS settings

|
|

  1. 0.00000

|

alter session set
NLS_COMP='BINARY' NLS_SORT='BINARY' NLS_CALENDAR='GREGORIAN'
NLS_TERRITORY='AMERICA' NLS_LANGUAGE='AMERICAN'

|
|

  1. 0.00000

|

...NLS: Set Decimal separator="."

|
|

  1. 0.00000

|

...NLS: Set NLS Group separator=","

|
|

  1. 0.00000

|

...NLS: Set g_nls_date_format="DD-MON-RR"

|
|

  1. 0.00000

|

...NLS: Set g_nls_timestamp_format="DD-MON-RR
HH.MI.SSXFF AM"

|
|

  1. 0.00000

|

...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR
HH.MI.SSXFF AM TZR"

|
|

  1. 0.00000

|

...Setting session time_zone to -04:00

|
|

  1. 0.00000

|

R E Q U E S T show

|
|

  1. 0.00000

|

Language derived from: FLOW_PRIMARY_LANGUAGE, current
browser language: en

|
|

  1. 0.00000

|

alter session set nls_language='AMERICAN'
nls_territory='AMERICA'

|
|

  1. 0.00000

|

NLS: CSV charset=WE8MSWIN1252

|
|

  1. 0.00000

|

...NLS: Set Decimal separator="."

|
|

  1. 0.00000

|

...NLS: Set NLS Group separator=","

|
|

  1. 0.00000

|

...NLS: Set g_nls_date_format="DD-MON-RR"

|
|

  1. 0.00000

|

...NLS: Set g_nls_timestamp_format="DD-MON-RR
HH.MI.SSXFF AM"

|
|

  1. 0.00000

|

...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR
HH.MI.SSXFF AM TZR"

|
|

  1. 0.00000

|

Setting NLS: NLS_DATE_FORMAT="DD-MON-RR
HH24:MI"

|
|

  1. 0.00000

|

...NLS: Set g_nls_date_format="DD-MON-RR
HH24:MI"

|
|

  1. 0.00000

|

...NLS: Set g_nls_timestamp_format="DD-MON-RR
HH.MI.SSXFF AM"

|
|

  1. 0.00000

|

...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR
HH.MI.SSXFF AM TZR"

|
|

  1. 0.00000

|

NLS: Language=en

|
|

  1. 0.01600

|

Application 201, Page Template: 65224001814607015

|
|

  1. 0.00000

|

Authentication check: Custom_Security (NATIVE_CUSTOM)

|
|

  1. 0.00000

|

Session State: fetch from database (exact)

|
|

  1. 0.00000

|

... sentry+verification success

|
|

  1. 0.00000

|

...Session ID 2409047431057 can be used

|
|

  1. 0.00000

|

...Setting session time_zone to -04:00

|
|

  1. 0.00000

|

...Check for session expiration:

|
|

  1. 0.00000

|

Branches - point: BEFORE_HEADER

|
|

  1. 0.00000

|

Display Page from Cache

|
|

  1. 0.00000

|

Fetch application meta data

|
|

  1. 0.00000

|

Setting NLS: NLS_DATE_FORMAT="DD-MON-RR
HH24:MI"

|
|

  1. 0.00000

|

...NLS: Set g_nls_date_format="DD-MON-RR
HH24:MI"

|
|

  1. 0.00000

|

...NLS: Set g_nls_timestamp_format="DD-MON-RR
HH.MI.SSXFF AM"

|
|

  1. 0.00000

|

...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR
HH.MI.SSXFF AM TZR"

|
|

  1. 0.00000

|

...http header processing

|
|

  1. 0.00000

|

...set mime type: text/html

|
|

  1. 0.00000

|

...set additional http headers

|
|

  1. 0.00000

|

Process point: BEFORE_HEADER

|
|

  1. 0.00000

|

Processes - point: BEFORE_HEADER

|
|

  1. 0.00000

|

...close http header

|
|

  1. 0.00000

|

Show page template header

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/jquery/1.12.3/jquery-1.12.3.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/jquery/2.2.3/jquery-2.2.3.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Set RequireJS define jquery=apex.jQuery

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/core.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/debug.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/util.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/locale.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/lang.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript

file=/i/libraries/apex/message.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=wwv_flow.js_messages?p_app_id=201&p_lang=en&p_version=5.1.4.00.08-21551848&p_builder=Y

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/storage.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/navigation.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/event.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/server.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/page.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/region.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/item.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/apex_5_0.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/widget.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/widget.util.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/dynamic_actions_core.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/dynamic_actions.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/jquery-ui/1.10.4/ui/jquery-ui-1.10.4.custom.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/actions.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/widget.checkboxAndRadio.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/widget.popupLov.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/widget.selectList.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript

file=/i/libraries/apex/widget.textarea.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/widget.yesNo.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/widget.menu.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/widget.toggleCore.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/widget.collapsible.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/tooltipManager.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Load JavaScript
file=/i/libraries/apex/theme.js?v=5.1.4.00.08

|
|

  1. 0.00000

|

Rendering form open tag and internal values

|
|

  1. 0.00000

|

Evaluate which regions should be rendered for display
point REGION_POSITION_07

|
|

  1. 0.00000

|

...No regions to render

|
|

  1. 0.00000

|

Authorization Check: "MUST_NOT_BE_PUBLIC_USER"
Component: ""

|
|

  1. 0.00000

|

... passed

|
|

  1. 0.00000

|

Authorization Check: "MUST_NOT_BE_PUBLIC_USER"
Component: ""

|
|

  1. 0.00000

|

... passed

|
|

  1. 0.00000

|

Authorization Check: "MUST_NOT_BE_PUBLIC_USER"
Component: ""

|
|

  1. 0.00000

|

... passed

|
|

  1. 0.00000

|

Authorization Check:
"CC-MGMT/SUPV/LEAD/STATE/REGION/QA" Caching:
"BY_USER_BY_SESSION" Component ""

|
|

  1. 0.00000

|

... passed (using
cache)

|
|

  1. 0.00000

|

Authorization Check: "CC-MGMT/SUPV/LEAD/QA"
Caching: "BY_USER_BY_SESSION" Component ""

|
|

  1. 0.00000

|

... passed (using
cache)

|
|

  1. 0.00000

|

Authorization Check: "CC-MGMT" Caching:
"BY_USER_BY_SESSION" Component ""

|
|

  1. 0.00000

|

... passed (using
cache)

|
|

  1. 0.00000

|

Authorization Check: "CC-MGMT" Caching:
"BY_USER_BY_SESSION" Component ""

|
|

  1. 0.00000

|

... passed (using
cache)

|
|

  1. 0.00000

|

Authorization Check: "CC-MGMT/SUPV" Caching:
"BY_USER_BY_SESSION" Component ""

|
|

  1. 0.00000

|

... passed (using
cache)

|
|

  1. 0.00000

|

fac586

Both debug traces are using hard-coded parameters...

RichDW

Both debug traces do use bind variables, however

in the report that works there is a hard coded value in the Report SQL and a BIND variable in the Header SQL.

in the report that does not work, there is a BIND variable in both the Report AND Header SQL - ??

NV('P60_WG') = 11

In the failed debug I do see something that only shows up (added by Apex) when using the Bind variable:  order by 1 desc   nulls last

parse query as: DEV

  1. 0.01600

...Execute Statement: select *

       from (SELECT
  referral.senton, agent.name

             FROM
  referral  

               join
  agent on referral.assignedto=agent.id)

             PIVOT
  (Count(*) FOR name IN ('Alvin Ross','Betty Simons','Chanel Cooper','Daniel
  Scoggins','Dena Strother','Doris Hendricks','Jessica Elledge','Joshua
  Stephens','Terrence Simpson','Test Region') )

order by 1 desc
  nulls last

fac586

Sorry, didn't see the differences in the commented-out lines.

However, I can't see any error messages either. Where and when does the ORA-01403 exception occur? Does it appear in any of the debug traces?

RichDW

I've been out of office for a few days -

I do not get any error message in the debug.  The error only shows within the report at run time.  Errors only when using a bind variable in the data sql.

fac586

RichDW wrote:

I've been out of office for a few days -

I do not get any error message in the debug. The error only shows within the report at run time. Errors only when using a bind variable in the data sql.

If debug mode is enabled I would expect to see the error somewhere in the debug log. Are you checking the entries for AJAX requests as well as page show processing?

I can't reproduce any problem when using an NV() reference in a pivot report query. Suggest you replicate what you are doing in an example on apex.oracle.com.

RichDW

I've read that - however it too does not work with a page item variable.

RichDW

fa586:

I've put the relevant code out on apex.oracle.com

Bottom line is that it seems the 'Function body returning sql' will not work if you have a page item (or variable) in your code.  Hard code the value instead and it works fine.  I've altered the code a bit, moving the query to pull the 'IN' list of names used in the pivot query from the AGENT table into a function - returning a list - which is used in the function to build and return the sql query.

The page item P60_WG provides the 'workgroup' number - it is hard coded in the function to 'ESU' or '11' - On the form, the dropdown select would be 'ESU' and then refresh the page if you have to after page load to get it into session.

Workspace; Richdw

ID:  Test

PW: Today123$

Page 60 - 'Referrals Assigned during Period'

If you use a hard code value on the below line: it works fine.

select F_WGpivot(11) into v_query from dual;

If you use

select F_WGpivot(:P60_WG) into v_query from dual;

it fails

Do you have another suggestion?  I would dearly love to include a pivot report in my app - but I need to have variable columns as each workgroup has differing numbers of staff and of course they have different names.

RIch

fac586
Answer

RichDW wrote:

Bottom line is that it seems the 'Function body returning sql' will not work if you have a page item (or variable) in your code. Hard code the value instead and it works fine. I've altered the code a bit, moving the query to pull the 'IN' list of names used in the pivot query from the AGENT table into a function - returning a list - which is used in the function to build and return the sql query.

The page item P60_WG provides the 'workgroup' number - it is hard coded in the function to 'ESU' or '11' - On the form, the dropdown select would be 'ESU' and then refresh the page if you have to after page load to get it into session.

The problem is related to the use of a page item in the query generation function, but only indirectly. The real issue is having the region Use Generic Column Names property set to No. If the workgroup ID is hard-coded the function returns a valid list of 10 agents at design time. APEX uses this to generate the pivot query with the SENTON column + a fixed set of 10 agent columns (look at the column list in Page Designer). If a page item is used instead, this has no value in session state in the builder at design time, the function returns an empty list of agents, and the APEX generated pivot query projection only contains the SENTON column.

The ORA-01403 runtime error was probably caused by the report being sorted by a column that no longer existed after it was regenerated to use the bind variable.

When creating dynamic pivot reports using the PL/SQL Function Body returning SQL Query you need to set Use Generic Column Names to Yes, with Generic Column Count set to the maximum number of columns that will be returned. See example on page 586 of your demo application.

There are a few other things that can be tidied up:

  • Using Execute PL/SQL Code with null; code blocks to set parameter values in session state values via the Items to Submit property is seriously obsolete. Just list the parameter items in the Page Items to Submit source property of the relevant region(s).
  • Individual refresh dynamic actions on each parameter item aren't necessary. As these items are all in the same region, a single Change action at region level can be used to handle all of them.
  • Adding double-quoted column aliases to the pivot column list enables the basic Column Names heading option to be used, dispensing with the need for a repetitive PL/SQL Function Body block.
Marked as Answer by RichDW · Sep 27 2020
RichDW

fac586,

Thank you for this. 

From what I read I was under the impression I needed to turn off Generic Column Names because I would never know how many columns I would have - and I thought I needed an exact number:  hence the column header function.  Nothing I read said you could set a maximum number of generic columns instead of setting an exact number.  This works great.

Thanks as well for the tips - very helpful.

I will have to do some more reading to understand the syntax used in the functions in your package; as I am not a trained developer.  Next steps will be to aggregate the pivot to sum up the number of 'referrals' for each agent per day; then to filter the report to a period based on the date parameters on the form.

I appreciate your support and the time you spent in mentoring.

1 - 12

Post Details

Added on Oct 4 2019
12 comments
2,653 views