NAB: User Info as part of Data Source?

[opendave]

Maybe I am missing something, but I have many instances where users should only see data that pertains to them. Rather than load the store with all possible records, then filter out everything that is not relevant to the user, I would like the store to only be opened with their data to begin with. Effectively, I would like to pre-filter the data source and avoid the time and data waste of pulling in those unneeded records.

I have a few possible workarounds in mind, but if there is a built-in way to accomplish what I am looking for I would like to go that route.

Thanks!
David

[Johnny Major]

Is the initial filter by user id and if so you could update your data source SQL statement to pull the user and filter accordingly.

[opendave]

Thanks Johnny. I can use SESSION_USER to handle some of this. It would be great if we could use some sort of substitution variables in the SQL to get at things like the valence user id.

If I wanted the user to select a value from a drop list, say a Division Number, and then only open the data source including records for that Division...is there a way to do that? I do not see anything in the docs or examples with this sort of preselection.

[robert.swanson]

David,

There's no way to directly apply a substitution value into an SQL statement, but you could effectively get there using some QTEMP trickery...

For example, to suit your need you could create a single-record file in QTEMP called, say, CUR_DIV to hold the desired division number, then use that record to limit your selections in other files referenced in the data source. Your SQL would then look something like this:

SELECT * FROM sales100
               INNER JOIN cur_div on sales100.div=cur_div.div

The key would be to set up a program that would create the CUR_DIV file in QTEMP and populate it with the desired record (or it could be multiple records if the user belongs to multiple divisions). The program should also apply a job-level override to it. You could then have a pre-execution program on the data source call this program. Theoretically you could have a button or an exit program associated with a filter in your NAB app call it as well.


To illustrate this concept in action, let's say I want to create a QTEMP file object called CUR_SESS that will hold the current session record from VVSESSDATA. My intention is to include this single-record file in any SQL statements that need to get to the current session ID, Valence user ID, environment, or anything else I need from VVSESSDATA for the Valence session, which I can then use to constrain what's pulled from other files in the statement.

The first thing I want to do is create an RPG program to handle the task of setting up this QTEMP object for my NAB data sources. I will call the program SETCURSESS and specify it as the pre-execution program on any data sources that reference CUR_SESS. The code would look something like this:

 /copy qcpylesrc,vvHspec                                             
** --------------------------------------------------------------    
**     Object ID: SETCURSESS                                         
**   Description: Setup CUR_SESS QTEMP object for NAB data source use     
** --------------------------------------------------------------    
d curSid          s             64a                                  
d qtempSid        s             64a                                  
d qCmd            pr                  extpgm('QCMDEXC')              
d  cmd                         500a   const options(*varsize)        
d  cmdLen                       15p 5 const                          
 /copy qcpylesrc,vvDspec                                             
 /free                                                               
  exec sql set option commit=*none;                                  

  // get current session ID                                          
  curSid=vvIn_char('sid');                                           

  // create QTEMP object CUR_SESS if it doesn't already exist        
  if not vvUtility_objectExists('*FILE':'CUR_SESS':'QTEMP');         
    qCmd('CRTDUPOBJ OBJ(VVSESSDATA) FROMLIB(*LIBL) OBJTYPE(*FILE) '+ 
         'TOLIB(QTEMP) NEWOBJ(CUR_SESS) DATA(*NO) CST(*NO)':200);    
    qCmd('OVRDBF FILE(CUR_SESS) TOFILE(QTEMP/CUR_SESS) '+            
         'OVRSCOPE(*JOB)':100);                                      
  endif;                                                       

  // replace QTEMP record if it's not already set for current session      
  exec sql select vvsessid into :qtempSid from qtemp/cur_sess; 
  if qtempSid<>curSid;                                         
    if sqlcod=0;                                               
      exec sql delete from vvsessid; // remove existing record 
    endif;                                                     
    exec sql insert into qtemp/cur_sess                        
             (select * from vvsessdata where vvsessid=:curSid);
  endif;                                                       

  *inlr=*on;

Now, let's manually create a "template" CUR_SESS object, placing it initially in a library other than QTEMP. I need this to ensure NAB can see it when validating any data sources I create that reference CUR_SESS...

CRTDUPOBJ OBJ(VVSESSDATA) FROMLIB(VALENCE52) OBJTYPE(*FILE) TOLIB(VALENCE52) NEWOBJ(CUR_SESS) DATA(*NO) CST(*NO)

Now I can create a data source that references this CUR_SESS file. Let's say I want to use it to pull the current user's first name, last name and email address into my data source. That segment of the SQL would look like this:

SELECT vvfname, vvlname, vvemail
  FROM vvusers u
              INNER JOIN cur_sess c ON c.vvusrid = u.vvusrid

Note that there's no data in the CUR_SESS file I created above in the VALENCE52 library, so I won't expect to see anything in the data source preview initially. When you hit SAVE on the data source, be sure to specify SETCURSESS as the pre-execution program, so NAB will call it prior to running the data source and populating any associated widgets. SETCURSESS will place the appropriate record into QTEMP/CUR_SESS, so any widget I populate from the data source will show the expected data, as you can see in this grid widget preview...
nab_screenshot.jpg

[opendave]

Very nice - thanks!

I actually told someone yesterday that the option for a pre-execution program was not in 5.2 for some reason... Forgot it was on the save dialog and not in the config of the data source. Doh!

I can definitely make this work.