Filter A report with userID

[Brian Roche]

Hi All,

I have the login userid captured to a session variable
I use the session variable to filter the records in all tables
I hae a report (Members) that prints all the members in a PostgreSQL table
I call this from a Dialog on a screen that uses the session variable as the filter
The report prints to PDF - All works great

How do I get the report to use this same filter?

Thanks in advance,

Regards,

Brian.

[Tommy Thompson]

Re: Filter A report with userID

You ever figure out how to do this? I'm having problems with this also.

[NoeticCC]

Re: Filter A report with userID

Try defining the report to filter on an argument (either the SQL datasource or at report level), then use the Print Genie in the AfterValidate event of the dialog to map the arguments to variables.

[robhaigh]

Re: Filter A report with userID

hi,
trying to figure this one out myself.

Cant figure out where to put arguments, variables, code, session variables etc etc etc etc..... just really confused. Can someone do a step by step guide.

many thanks

rob.

[Brian Roche]

Re: Filter A report with userID

Yes, thats my issue as well. I am very new to the product and have don't use it intuitively yet like I do other ones I have.

Looks like there is a lot of power there and I am excited about what I can do on the web. Have a small app done already all I am waiting to solve is this issue before I release it.

Thanks in advance.

[Brian Roche]

Re: Filter A report with userID

Hey Steve and other people who help on the forum,
After reading some threads - want you to know that you are really appreciated and your fast response and helpful hints are great.

Thanks

Brian Roche (Newbie)

Please no one respond to this post.

I contribute to a number of other forums for different products and they are all the same.

[akmackenzie]

Re: Filter A report with userID

I use arguments a lot in Reports to filter, but normally using SQL data, so i will try to explain it for using an SQL based report! (and coming from a n00b aswell i may get somethings wrong)

(1) Right click on the Report - SQL Data source - Edit data source definition.
(2)Click on Define Arguments, and Name the Argument (i presume in your example you would call it userloginid or something along those lines.)
(3)Now you make the field that you want to filter (for example the member) equal the argument, so in the SQL script type

where (memberid = :userloginid)

(the first part of the expression should be the unique value that identifies each member hence why i exampled it as memberid)

Or alternately use the filter in the SQL genie to add this.
The colon is important as it defines it as the arguments.

Now to use this on the web page.
(4)In your dialogue, use a dropdownbox or something that the user will be able to select the memberid, whether they select the actual memberid, or the member name (but chose to store the memberid). This is done in the dropdownbox - properties - choices and make it a dynamic selection.

(5)Now in the aftervalidate events in the dialog properties, you want to go to insert - genie - print report genie. Select your report. This is the part an SQL report differs from a report based on an alphafive database table. But anyway, add a variable, the variable is the name of the dropdownbox (not the label of dropdownbox). Next check 'Does the SQL statement use arguments'. Type in the name of the argument you defined in the SQL datasource before (eg userloginid) and the genie will write a script with your arguments.

So i hope (i really really hope) that when you select the member from the drop down box and click submit on the dialogue it should print the report corresponding to that member!

I may have missed something out, apologies if i have.

Now if your report is based on an alpha five database table you define the arguments differently in the print report genie (not familiar with this so dont want to explain something and probably it being wrong!)

Hope this helped

[robhaigh]

Re: Filter A report with userID

think we are probably on the right lines, from digging around on the forums it looks like I have to use an argument of some sort.
I will try to explain the problem fully and hopefully you can see where I am going wrong.

- setting up a simple web front end to access a database (my back end database is MS Access)
- using the details show in the forum this front end is password protected using the web security feature. this feature lets me setup users and groups.
Again, using the demo's from the forum setup the users, gave them all a value in the 'external user ID value' (in my case it is the users initials i.e AC or RH or AB etc etc).
In 'security settings' under web security I set the option 'session variable for identifier field' to __protected_filteruserid (Again, this was done using the examples on the forum, something about setting it to this makes the userid non-hackable from the web pages).

Ok, so, now I have users setup, I have each user with a 'external user ID value' which is set to thier inititals.

so far so good.

Now, I have a web components page that simply lists data from my back end database in a grid view. This web page's query has a filter set as follows:-
[Contract Manager] = :userid

this works fine, the page only shows data where the [contract manager] field in my data is equal to the userid, i.e the'external user ID' I have set against each user under web security.

again, so far so good!

now, the problem I am having, and I think is the exact same problem as at the beginning of the thread is ' how do I use this 'external user ID' to filter a report.

I have tried the following.



(1) Right click on the Report - SQL Data source - Edit data source definition.
(2)Click on Define Arguments, and Name the Argument

Ok, done this, I created an argument called 'userid'

(3)Now you make the field that you want to filter (for example the member) equal the argument, so in the SQL script type

yep, done this, clicked on the 'where' button and setup the condition

([Contract Manager] = :userid)
saved the report

Now to use this on the web page.
(4)In your dialogue, use a dropdownbox or something that the user will be able to select the memberid, whether they select the actual memberid, or the member name (but chose to store the memberid). This is done in the dropdownbox - properties - choices and make it a dynamic selection.

didnt do this as I want the userid to come from the 'external user ID' setup against each user, I dont want the user being able to run reports for other users.

(5)Now in the aftervalidate events in the dialog properties, you want to go to insert - genie - print report genie. Select your report. This is the part an SQL report differs from a report based on an alphafive database table. But anyway, add a variable, the variable is the name of the dropdownbox (not the label of dropdownbox). Next check 'Does the SQL statement use arguments'. Type in the name of the argument you defined in the SQL datasource before (eg userloginid) and the genie will write a script with your arguments.

got confused what to do next, used the idea from this point and used print report genie to add my report. Selected my report, added a variable called 'Contract Manager'.
ticked the box saying 'does the SQL statement use argument'
Added a argument called 'userid'

saved everything, published.

now, when I go to the web page that has a button on it to run my report, i click the button, the page flashes for a second but no report.
If I remove all arguments filters etc etc and just use the report , it works, but I see data for all contract managers..
how do I get filter to work!!?


hope this explains my (our) problem.. apologies for it being a little long!!

if possible, can someone do a step by step reply, I know its a pain, but the problem seems to be on the forum that any reply says something like 'just use an argument' and filter it on the session id field thingy.... (sorry, I know replies are not that bad, but as a noob the replies I have read seem to give a tantelising glimpse of the solution, but not a full idiots guide solution!).... :-)
many, many thanks for any help, beers all round for a solution!

[akmackenzie]

Re: Filter A report with userID

Ah i see, i think the solution i put forward will only work for a dialogue (as its all i ever use) as its easy to map the argument to the variable. (the variable being the drop down box)

In your particular problem i dont know what the variable would be, i think this is the problem.

However (still with the dialogue idea) in the controls -drop down box properties - choices , can you not just filter the list to only show the currently logged in user (like your grid) so other users can only show the report of their own and not able to select others? Remembering to store the userid

So use the same filter: where [Contract Manager] = :userid

[robhaigh]

Re: Filter A report with userID

excellent idea !!(fresh pair of eyes sometimes does wonders for a problem!!)
will give it a go now and post up results.


thanks.

[Brian Roche]

Re: Filter A report with userID

Thanks a bunch ak,

I have been hitting all around the methodology you explain and not getting it quite right. I will follow the steps you describe. If I get it done I will post a step by step with what I/You did.

I filter the SQL Tables based on the a session variable. this variable is assigned at login time and is the session variable I want to filter by. I think with your help I will get there.

Thanks Again

Brian.

[akmackenzie]

Re: Filter A report with userID

Rob did you try the method of the dialog where the choices could only relate to the current logged in session variable? I havent had time to try this but i see no reason why it shouldnt work?

[robhaigh]

Re: Filter A report with userID

cracked it.
couldnt use dialog box (opened up another can of worm trying filter the dialog box!!).
stuck with using the session variable

solution is pretty much as above..... too knackered today to post up my findings, (sick of the site of my web pages at the mo !!!) so will go home for a lie down and post up my results tomorrow.

[akmackenzie]

Re: Filter A report with userID

Nice one bub,

Dont forget to post tomorow want to find out how you acheived it!

[Brian Roche]

Re: Filter A report with userID

Hi Rob,

Anxiousley waiting for your post on the solution for the UserID.....
I am still far>>>> around with this one - almost there but not quite.

Thanks.