Home » Developer & Programmer » Reports & Discoverer » a parameter Organizations query
a parameter Organizations query [message #613659] Fri, 09 May 2014 01:00 Go to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi all,

(1) User want a report
(2) which will have a parameter Organizations
(3) My manager want to display only those organizations for which he is authorized
No other org should be visible
how do i write the query? Please help me on that query... Smile


Thanks
Re: a parameter Organizations query [message #613663 is a reply to message #613659] Fri, 09 May 2014 01:08 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Seems rather easy:

select ...
from some_table
where organization = :par_organization
...


As (2) says that report should accept only one parameter - organization - the only mystery is (3) - how do you know who is authorized for which organization? Once you find that out, you'd have to create list of values for the PAR_ORGANIZATION parameter which will display only organizations current user is allowed to see.
Re: a parameter Organizations query [message #613666 is a reply to message #613663] Fri, 09 May 2014 01:18 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Quote:
(3) - how do you know who is authorized for which organization? Once you find that out, you'd have to create list of values for the PAR_ORGANIZATION parameter which will display only organizations current user is allowed to see.


Hi Littlefoot, thanks for reply ..could you please help me on that query
Re: a parameter Organizations query [message #613669 is a reply to message #613666] Fri, 09 May 2014 01:29 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid I can not. I have no idea how is this information stored in your database. It *might* be something like
select t.organization
from organizations t
where t.organization_id in (select u.organization_id
                            from user_organizations u
                            where u.user_id = <user_which_runs_the_report>
                           )
Re: a parameter Organizations query [message #613671 is a reply to message #613669] Fri, 09 May 2014 01:49 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Thanks Littlefoot.. Smile

And i have one question on this is there any validations means is this (for validation valid trigger) trigger not mandatory right?
Re: a parameter Organizations query [message #613672 is a reply to message #613671] Fri, 09 May 2014 02:00 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't understand the last question.
Re: a parameter Organizations query [message #613676 is a reply to message #613672] Fri, 09 May 2014 02:10 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
For data validation, validate trigger is not needed right?

Thanks
Re: a parameter Organizations query [message #613682 is a reply to message #613676] Fri, 09 May 2014 02:31 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is not mandatory.

For example, you can have a check constraint on a table column which takes care about valid values. Or, referential integrity constraint, or unique constraint ... all of them won't let you enter invalid values (so yes - you don't need to have a WHEN-VALIDATE-ITEM trigger in a form).

Is this question related to the original problem? If so, you don't have to write any kind of a trigger. "List of values" I mentioned can be created in Reports Builder itself (but you'll have to display its Parameter Form so that user can select from it). Or, as you already know, it can be created in Forms Builder and you'll just pass its value to a report. In that case (LoV in your form), set item's "Validate from list" property to "Yes" - it'll do the validation (i.e. user won't be able to enter values different from the ones contained in LoV).
Previous Topic: Pdf Search Not working
Next Topic: excel output in 9ias
Goto Forum:
  


Current Time: Thu Mar 28 11:17:04 CDT 2024