Restricted Account Level & Detail of All Accounts in one

building reports webintelligence
1

Hi Friends,

I have a requirement for a Webi Report (XI R2) which will be used by customers or product buyers or end users…

The report would show sales and estimated forecast information of products in Report Tab 1 and in Report Tab 2 will show detailed sales report information corresponding to the User.

Lets say user has access to Accounts A & B. Report Tab 1 will show information across all the accounts (Account Name will not be there) and Report Tab 2 will show information only on Accounts A & B which user has access.

Can this be done in Webi? If yes, Please guide me how…

lnarayanan86 :india: (BOB member since 2009-08-05)

2

Well first you need to figure out where you’re going to store, or where you already store each User’s access levels.

Once you do that, you need to make sure to tie it into the users BO Login ID.

So that you can end up with a table/view similar to

User Name, User BOBJ Login ID, Account Number
Mr. Smith, MSmith, A
Mr. Smith, MSmith, B
Mr. Jones, MJones, A
Mr. Jones, MJones, C
etc…

Then in your universe, create a derived table something like:

Select * From AccountInformationDetail a
JOIN UserSecurityTable b on a.AccountNumber = b.accountNumber
WHERE b.UserLoginID = @BOUser()

I may have that syntax not 100% but the idea is to take your data table, join it with your security table and only pull data where the account ID matches the user’s account. @BOUser will return ‘MJones’ or 'Msmith" etc…

From there, create any object you want from that derived table and it will automatically only return results where the user has rights to see data.

If you also need objects from ALL the data, just build those objects from a seperate table which does not have the join with BOUser.

Note: on Large tables this can GREATLY increase your query time, especially if your indexes/partitions are not set up correctly in the database. I highly recommend you find a way to make sure the Account ID can be tied to a numeric value (Foreign key reference perhaps) instead of text as that should speed up the join considerably.

Good luck.

JPetlev (BOB member since 2006-11-01)

3

Thanks JPetlev.

Am not sure whether user privileges are managed on Database which am using or a different one… But I implemented a different approach as this report will be integrated with Portal… Portal imposes user access restriction (i have no idea how its imposing restriction) …

So here what I did… (not a great solution though)


I added additional data provider where a prompt for Account to which user 
 ( I hope from Protal they will Provide value for the Prompt).

Then using User Response Method, obtained the prompt values which will be holding list of accounts.

Inlist method not working over variable having list of values. 
Used Match method to check whether user has access to account. 
If yes then particular account data will be shown

lnarayanan86 :india: (BOB member since 2009-08-05)

4

As long as you can get a list of accounts the user has access to on the report side, your solution will work, however there’s a rather large security hole you need to be aware of if things are not set properly.

Since you’re doing everything on the REPORT, you need to remember that the data (aka if user Exports to csv) will still contain the full results of the query which would include the data you do not want users to see.

This assumes that the output users see is a WEBI document of course. And if so, you need to work with your BO admin to ensure that users do not have any rights to export to CSV on that report, as well as removing the rights to edit the query and report itself. Otherwise a savy user could just go in and remove any filters you have in place.

If you’re automatically exporting and delivering a PDF or Excel file and that’s all the users ever see or have to work with, you should be find with your solution and can skip any security concerns.

Good luck.

JPetlev (BOB member since 2006-11-01)

5

Thank god…

You saved me from big trouble… Eye Opener!!

I’ll talk to BO Admin to check whether end users have right to export CSV. If not then need to deliver to their inbox directly as excel or pdf.

Once again Thanks a lot for bringing this issue into notice.

lnarayanan86 :india: (BOB member since 2009-08-05)

6

Not a problem, the key is to remember that a CSV output is not a report output but rather a ‘data provider data dump’. Even worse is if you have 2 queries, it dumps them onto the same tab which makes the CSV pretty usless.

I don’t know why Business Objects labels it under “Save my Report As”, when in reality it should be labled “Save my Query Data as”.

BTW - I do not know if this was changed in XIR 4 yet… I’m going on XIR2/3 knowledge.

JPetlev (BOB member since 2006-11-01)