How to prompt a user for an optional parameter if the value/data in the dimension is NULL?

[former_member1312931]

Hello Experts,

We have reports at both District level and Campus level. Now our requirement is to use a single report to work for both District and Campus. The main issue with using a single report for both district level and campus level is, district level report has an optional parameter for campus id. The District user can run the report for single or multiple or all the campuses that are associated to the District, the user belongs to. Whereas, when the campus user runs report, the report should only show the data of the campus, the user belongs to.

Since, we are using a single report, when the campus user runs the report, the user is prompted for campus id (it should not prompt, as campus user should not run for different campus), so I supressed all the campuses, except the campus, the campus user belongs to. And when the report is run by a district user, the user is prompted and it works as expected.

My question is "can we check for a database field or dimension and if it is NULL then prompt the user for (optional) parameter and if it is not null, then use the db field or dimension value to filter data within the report".

The data is stored like below in the database

User_Name          FY               Coll_Name          District_ID          Camp_ID

abc                      2011            Fall                    123456               [NULL]               (this user is a District user)

xyz                      2011            Fall                    123456               789                    (this is a Campus user)

When abc runs the report, the report should prompt for optional parameter as the camp_id is null and when xyz runs the same report, the report should not prompt for parameter and run for 789 campus.

The reports are based on unx universe and created using Crystal Reports for Enterprise. I am using a database level security table and a filter table.user_id=@Variable('BOUSER'). I tried a where clause like

if IsNull(Table.camp_id) then @Prompt('Enter Campus ID','A','Class\Object',Free,Multi,....) else Table.Camp_id=Table2.Camp_id. I am getting Data Federator Driver error. Can anyone help me out.

Any help is appreciated.

Thank you,

Sriram Thotakura

[Former Member]

Take a look within the universe restrictions. It can be found in BOXI3 under Tools -> Manage Security -> Manage Access Restrictions but I haven't seen UDT/IDT to know where it is in there.

What you can do is have two  predefined conditions and within the manage access restrictions, use one to force the 789 restriction and the other to force the prompt based on the user/group membership.

[Former Member]

Hi Sriram,

You won't be able to compare an ID with a BOUSER, right.

It has to be a user_name.

Secondly,for prompting, can you test with a predefined condition whose logic would be,

if (Table.camp_id) is Null then table.Camp_id=@Prompt() else table.camp_id=table2.camp_id

-Prathamesh