Filter A report with userID

[Tommy Thompson]

Re: Filter A report with userID

Got it to work using .dbf file and an existing report. Basically followed Andrew's steps using a dropdown box. Hope the attached images help explain what I did.

[Brian Roche]

Re: Filter A report with userID

Hi AK

Are you going to post the solution? :)

[robhaigh]

Re: Filter A report with userID

ok here goes...!

Quick summary of my situation.
- MS Access Database with a Alpha Web Project front end.
- Using built in Web Security to authenticate each user.
- web pages filter the data to only show the data relevant to each user.
- built a web page that has a button to run a report.
- report showing everyone's data, want it to show only the data relevant to logged in user.

more background!
(Apologies for this perhaps being a little over simplified, but I am trying to do this step by step!).

Ok, I will run through a couple of key points about my data so that you can hopefully apply the solution to your own problem.

my company manufactures kitchens.
my back end data is a list of records detailing delivery dates for those kitchens.
My back end data has a field called 'Contract Manager', in this field is stored the initials of the contract manager who is responsible for the kitchen e.g 'AD'


WEB SECURITY BIT
Using Web Security I have setup each user that can access the web pages.
In the field 'External User ID Value' I set this to the users initials.
e.g I have a users called 'AndyChambers', so therefore i set 'External user ID value' to AD.

Using web security, under 'web security configuration', under 'customize options', I check the box that says 'Enable external user identifier field'
I then fill in the box that says ' Session variable for identifier field' with '__protected__filteruserid'

this as I understand it lets me use the 'external user ID value' (in this example 'AD') throughout my web application.

so far so good, I have setup users and assigned thier initials to a session variable.
(All this info on how to setup web security was taken from the forum and help pages).

REPORT BIT
Ok, now I need to write my report.
I design my report in alpha five , it uses a 'ad Hoc connection string' to connect back to my MS Access database, hopefully you should be ok with doing this bit as it can all be done using the wizards built into Alpha.
Test the report, you should see a nice list showing all your data.
name the report something you will remember (In my example I have called it 'IncompleteKitchens')

Ok, now , right click on the Report you have just built and select 'SQL datasource' , 'Edit datasource definition'.
you should see a box saying 'Edit the ad hoc datasource', click OK
Click Next and you will see a screen showing a list of the fields used on your report.
click on 'Define Argument'
Add an argument called 'CM', (in my example I have called it CM , you can call it whatever you like , just for safety make sure its something different to any of your field names. Again in my example , I have left all the default settings its just a straightforward 'Character' argument....... REMEMBER WHAT YOU HAVE CALLED THIS ARGUMENT..we will use it in a minute).

click OK, then finish to finish editing the report.

Stuff to remember from this bit
'IncompleteKitchens' - The name of the report we have written.
'CM' - The name of the argument we used within this report


THE WEB PAGE BIT.
Ok, here comes the clever bit! (well i thought so!!)

setup a new web page component (dialog page)
On that page put a new control - a list box and in the choices of that list, list the names of the reports you have (In my example I ony have one report so I populate the choices with 'Incomplete Kitchens')
(NOTE - I have to work on this selection list a little, more info on this later!)

now , go select 'Properties' and 'after validate'

this is the bit I struggled with, what we want to do is run a report, but filter it down so that it only whows the current users info. i thought this would be done using the 'filter' property when running reports.... but , as you will see you use the 'args' property!!

ok, now click on Insert button, select 'Genies' then 'print report'

in the 'report name' dropdown select the report we designed (in my example - Incomplete Kitchens'
Now, click on the Add button next to Variables.
in the pop up box that appears name the variable 'cuser' (without the single quotes round , I just put them there so its easier to read, if you are confused the variable name is cuser hopefully you know what I mean now!)
click OK

Now tick the box that says 'does the SQL statement use arguments'
now click the button that says 'Add' next to arguments
In the pop up box the argument name is 'CM'
click OK
click OK again

you will now get some code automatically inserted the screen that looks like this

=====
IF eval_valid("cuser") = .f. Then
goto skipreport
end if
dim args as sql::arguments
args.set("CM",alltrim(cuser))
Dim filename as c
dim libfolder as c
libfolder = a5_removetrailingbackslash(Request.ApplicationRoot) + chr(92)
reportname = "IncompleteKitchens@" + libfolder + "contractmanagement.alb"
filename = session.session_folder + "tempreport.pdf"
filename = report.saveas(reportname,"pdf","","",filename,.f.,null_value(),args)
if file.exists(filename)
currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + time("hms3")
end if
skipreport:
===


what will be different in your case will be where it says
reportname = xxxxxxxxxxxxxxxx
this will have the name of your report in it

ok, now we have to add a couple of lines of code and also remove a couple.

make the code look like this.

======================
dim cuser as c
cuser = session.__protected__filteruserid


dim args as sql::arguments
args.set("CM",alltrim(cuser))

Dim filename as c
dim libfolder as c
libfolder = a5_removetrailingbackslash(Request.ApplicationRoot) + chr(92)
reportname = "IncompleteKitchens@" + libfolder + "contractmanagement.alb"
filename = session.session_folder + "tempreport.pdf"
filename = report.saveas(reportname,"pdf","","",filename,.f.,null_value(),args)
if file.exists(filename)
currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + time("hms3")
end if
skipreport:

===================

what I did...
added the first couple of lines

dim cuser as c
cuser = session.__protected__filteruserid

this, gets the 'external user id value' we setup way back in Web security and puts it in a variable called 'cuser'


got rid of

IF eval_valid("cuser") = .f. Then
goto skipreport
end if


dont want this bit of code, I just want to press the button and the report runs


THE LAST BIT
Ok, all the code is written.
Now we just need to create a A5W page that has that Web component on it. hopefully you should be ok with this.


AND FINALLY...
that should do it.

What it doesnt do.....
Ok, we have a form with a drop down list , with a list of report names in it.
click on the submit button and our 'incompletekitchens' report runs
yep, fine...
BUT, if the list has more reoprt names in it, no matter which one you select from the list, when you press the submit button the 'incompletekitchens' report runs.
this is controlled by the line
reportname = xxxxxxxxxxxxxxxxxxx

this line will have to be changed in the future to take into account the users selection from the drop down list. This is covered in the video tutorial on the alpha website.
For now, I am just happy to have a report that filters!




phew..... a bit long I know, sorry and all that..... hopefully you should be able to follow this through and see what I did... if not drop me a line.

[akmackenzie]

Re: Filter A report with userID

Well sounds good yes, but, and maybe im missing something here so sorry if im completely wrong! but your not creating a report that corresponds to the userid? You seem to have created an argument which doesnt correspond to what you are selecting

[robhaigh]

Re: Filter A report with userID

oops... just noticed I missed a bit out.

missed the WHERE bit of the report.

will edit my looooooong post to add it.

[robhaigh]

Re: Filter A report with userID

ok... revised reply.



ok here goes...!

Quick summary of my situation.
- MS Access Database with a Alpha Web Project front end.
- Using built in Web Security to authenticate each user.
- web pages filter the data to only show the data relevant to each user.
- built a web page that has a button to run a report.
- report showing everyone's data, want it to show only the data relevant to logged in user.

more background!
(Apologies for this perhaps being a little over simplified, but I am trying to do this step by step!).

Ok, I will run through a couple of key points about my data so that you can hopefully apply the solution to your own problem.

my company manufactures kitchens.
my back end data is a list of records detailing delivery dates for those kitchens.
My back end data has a field called 'Contract Manager', in this field is stored the initials of the contract manager who is responsible for the kitchen e.g 'AD'


WEB SECURITY BIT
Using Web Security I have setup each user that can access the web pages.
In the field 'External User ID Value' I set this to the users initials.
e.g I have a users called 'AndyChambers', so therefore i set 'External user ID value' to AD.

Using web security, under 'web security configuration', under 'customize options', I check the box that says 'Enable external user identifier field'
I then fill in the box that says ' Session variable for identifier field' with '__protected__filteruserid'

this as I understand it lets me use the 'external user ID value' (in this example 'AD') throughout my web application.

so far so good, I have setup users and assigned thier initials to a session variable.
(All this info on how to setup web security was taken from the forum and help pages).

REPORT BIT
Ok, now I need to write my report.
I design my report in alpha five , it uses a 'ad Hoc connection string' to connect back to my MS Access database, hopefully you should be ok with doing this bit as it can all be done using the wizards built into Alpha.
Test the report, you should see a nice list showing all your data.
name the report something you will remember (In my example I have called it 'IncompleteKitchens')

Ok, now , right click on the Report you have just built and select 'SQL datasource' , 'Edit datasource definition'.
you should see a box saying 'Edit the ad hoc datasource', click OK
Click Next and you will see a screen showing a list of the fields used on your report.
click on 'Define Argument'
Add an argument called 'CM', (in my example I have called it CM , you can call it whatever you like , just for safety make sure its something different to any of your field names. Again in my example , I have left all the default settings its just a straightforward 'Character' argument....... REMEMBER WHAT YOU HAVE CALLED THIS ARGUMENT..we will use it in a minute).

Now we need to add a WHERE statement
click on the WHERE button
click on 'Add new condition'
in the 'Select a coloum' pull down the list and select the field you wish to compare 'CM' against (In my example I select 'Contract Manager')
In 'select an operator' I select 'is equal to'
In 'Specify a value' I click on 'Insert and argument' and pick the pop up box that comes up saying ':CM'
Click OK

click OK, then finish to finish editing the report.

Stuff to remember from this bit
'IncompleteKitchens' - The name of the report we have written.
'CM' - The name of the argument we used within this report


THE WEB PAGE BIT.
Ok, here comes the clever bit! (well i thought so!!)

setup a new web page component (dialog page)
On that page put a new control - a list box and in the choices of that list, list the names of the reports you have (In my example I ony have one report so I populate the choices with 'Incomplete Kitchens')
(NOTE - I have to work on this selection list a little, more info on this later!)

now , go select 'Properties' and 'after validate'

this is the bit I struggled with, what we want to do is run a report, but filter it down so that it only whows the current users info. i thought this would be done using the 'filter' property when running reports.... but , as you will see you use the 'args' property!!

ok, now click on Insert button, select 'Genies' then 'print report'

in the 'report name' dropdown select the report we designed (in my example - Incomplete Kitchens'
Now, click on the Add button next to Variables.
in the pop up box that appears name the variable 'cuser' (without the single quotes round , I just put them there so its easier to read, if you are confused the variable name is cuser hopefully you know what I mean now!)
click OK

Now tick the box that says 'does the SQL statement use arguments'
now click the button that says 'Add' next to arguments
In the pop up box the argument name is 'CM'
click OK
click OK again

you will now get some code automatically inserted the screen that looks like this

=====
IF eval_valid("cuser") = .f. Then
goto skipreport
end if
dim args as sql::arguments
args.set("CM",alltrim(cuser))
Dim filename as c
dim libfolder as c
libfolder = a5_removetrailingbackslash(Request.ApplicationRoot) + chr(92)
reportname = "IncompleteKitchens@" + libfolder + "contractmanagement.alb"
filename = session.session_folder + "tempreport.pdf"
filename = report.saveas(reportname,"pdf","","",filename,.f.,null_value(),args)
if file.exists(filename)
currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + time("hms3")
end if
skipreport:
===


what will be different in your case will be where it says
reportname = xxxxxxxxxxxxxxxx
this will have the name of your report in it

ok, now we have to add a couple of lines of code and also remove a couple.

make the code look like this.

======================
dim cuser as c
cuser = session.__protected__filteruserid


dim args as sql::arguments
args.set("CM",alltrim(cuser))

Dim filename as c
dim libfolder as c
libfolder = a5_removetrailingbackslash(Request.ApplicationRoot) + chr(92)
reportname = "IncompleteKitchens@" + libfolder + "contractmanagement.alb"
filename = session.session_folder + "tempreport.pdf"
filename = report.saveas(reportname,"pdf","","",filename,.f.,null_value(),args)
if file.exists(filename)
currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + time("hms3")
end if
skipreport:

===================

what I did...
added the first couple of lines

dim cuser as c
cuser = session.__protected__filteruserid

this, gets the 'external user id value' we setup way back in Web security and puts it in a variable called 'cuser'


got rid of

IF eval_valid("cuser") = .f. Then
goto skipreport
end if


dont want this bit of code, I just want to press the button and the report runs


THE LAST BIT
Ok, all the code is written.
Now we just need to create a A5W page that has that Web component on it. hopefully you should be ok with this.


AND FINALLY...
that should do it.

What it doesnt do.....
Ok, we have a form with a drop down list , with a list of report names in it.
click on the submit button and our 'incompletekitchens' report runs
yep, fine...
BUT, if the list has more reoprt names in it, no matter which one you select from the list, when you press the submit button the 'incompletekitchens' report runs.
this is controlled by the line
reportname = xxxxxxxxxxxxxxxxxxx

this line will have to be changed in the future to take into account the users selection from the drop down list. This is covered in the video tutorial on the alpha website.
For now, I am just happy to have a report that filters!




phew..... a bit long I know, sorry and all that..... hopefully you should be able to follow this through and see what I did... if not drop me a line.

[akmackenzie]

Re: Filter A report with userID

Ok well now it makes more sense yeh.

Over the weekend (if i dont have time today) im going to produce a method using a dialog, in my experience much easier and more controlled and shouldnt have any lasting problems, see if it helps yourself or brian

[Brian Roche]

Re: Filter A report with userID

Will try it today and let you know,
Thanks a bunch.

Will also put a document together with screen shots etc to add to the mix and keep for my own sanity.

thanks again.

[NoeticCC]

Re: Filter A report with userID

Will also put a document together with screen shots etc to add to the mix and keep for my own sanity.

Very good idea... I often find myself trying to solve problems and then realising I'd solved the same problem several years ago, but without documenting it I always keep forgetting!

[Brian Roche]

Re: Filter A report with userID

Yahoooo, I got it. Or I should say you got it. Thanks
Mine was slightly different as I used code in the Login page to assign the Session variable as the userid from the login field. i did not need to set the Web Security user external field.

Now I've just got to figure out how to include a date range in all of this as well.

Thanks for all the support.

[Terry]

Re: Filter A report with userID

I would like to accomplish the same type of filtering using the Windows environment variable "username" for folks on our intranet - this would avoid having to login to Alpha separately. Is this possible? This will be my first Alpha application.

Terry

[Brian Roche]

Re: Filter A report with userID

Is this a Web App or a 32 bit app?

[Terry]

Re: Filter A report with userID

Is this a Web App or a 32 bit app?

Currently, it is an Access application located on a corporate server. Each user has to transfer the entire table across the network to execute the filter on their machine. The network reaches offices located across the US, so the performance at remote offices is less than desirable. The filter is currently based on the username environment variable, which picks up the Windows login id - it is also stored within the table. My goal is to do the filtering on the server to minimize the amount of data transferred over the network. It appears to me that the Alpha Application Server will handle the query on the server, but I need to pick up the username from the client machine and pass it through the browser to the App Server. Because each user is already logged into their machine and verified, I would prefer not to have a separate login for Alpha.

Terry

[robhaigh]

Re: Filter A report with userID

Currently, it is an Access application located on a corporate server. Each user has to transfer the entire table across the network to execute the filter on their machine. The network reaches offices located across the US, so the performance at remote offices is less than desirable. The filter is currently based on the username environment variable, which picks up the Windows login id - it is also stored within the table. My goal is to do the filtering on the server to minimize the amount of data transferred over the network. It appears to me that the Alpha Application Server will handle the query on the server, but I need to pick up the username from the client machine and pass it through the browser to the App Server. Because each user is already logged into their machine and verified, I would prefer not to have a separate login for Alpha.

Terry

terry,
looks to me that you need to give alpha a go. I realise what you are saying about the user having to re-authenticate, sometimes its a pain getting users to login, then login again, remember this password and that password. But, in the long run its probably best if you go down the web security route with alpha, keeps everything secure and 'un-hackable'. Passing windows usernames over the net doesnt sound avery secure idea (unless someone can post up a fix that does it securely!).
for now, (well, at least to get you up and running and testing your app), I would stick with the built in stuff within alpha.
sounds a very similar situation to myself, MS Access database wanting to be delivered over the web, but securely so user can only see his/her own data.
As an aside, each of my users makes the initial link to the company network via a VPN connection, this adds another level of security.

hope you manage to get your app un and running... good luck!
Rob.

[Steve Wood]

Re: Filter A report with userID

If you can figure out on the network side how to get the username password from the windows login, you can use the Alpha function a5ws_login_user() to log the user in to the application.

E.g.: mydomain.com/logmein.a5w?u=stevep=wood will work if the logmein.a5w page contains:

a5ws_login_user(u,p)

You can see how unsecure it is, but it will work. You could at least use a form POST method so the parameters do not show in the URL. You can easliy get the value for "u" above api_getusername(), but I'm almost positive you can't get the password.