How do i pull out user information before generating main report (which uses user information)

Hi Expert,
I want to pull out user information before generating main report, so that i can use user inforamation in generating main report.
I tried
(1) Sub-report with record selection - {USR_Stage.ID_USER} = CurrentCEUserName, and pulled user information. i used shared variables to use in main report, however i could not be able to use shared variables in record selection of the main report.
(2) sql expression able to pull user information, unfortunately i could not be able to use shared variables, niether formulas in it.
Basically, we have many departments in our and the users are allowed to see data for their own department only, because of that i am trying to create a report which identifies an user and then the report will show only data for department which user is belongs to.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
The correct method for accomplishing your goal would be to use a Business View as the data source for your report. Business Views allow both row and column level security.  Properly written and filtered against User Groups, a report based on a Business View will allow you to schedule an instance once and whomever views it will only see the data they're suppose to see.
As you found out you can't use a shared variable in the record selection however you can link the subreport to the main report on a formula that returns the CurrentCEUserName as its value.  The link can be used for filtering data.

sanjeev410Author Commented:
HI mlmcc
Please could you be able explain me how to link sub-report to the main report?
with an example if its possible.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Look at this sample

To see the links
Right click the subreport

sanjeev410Author Commented:
Hi mlmmc,
Thank you for prompt reply, unfortunately this logic is for sub-report (main report to sub-report), however i am looking users information before generating main report (data from sub-report to main report).
I think I could have not explained in my question, though I will explain my scenario here:
(1) We have existing more than 400 reports all together, and more than 15 different departments, so 400 mutiply 15 = 6000 reports are in the system (Business Object XI)
(2) Each department has 400 reports in the respective folders which shows their own data
(3) Users are been set up with access to their own departments folder, however the reports are exclusively made for their departments only.
(4) I think, instead of maintaining 6000 reports department wise, why can not we maintain 400 reports which identifies user & users department before generating main report.
(5) So I want to know users department before generating main report (which is already exists in the system)
Hope this will clarify more than before.
many thanks
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
This is why you need to use Business Views - that's exactly what they're for. When you create a Business View you attach filters to it based on User Groups in BusinessObjects Enterprise XI R2.  So, if you have a user group for the Finance Department you could create a filter where users of the Finance Department only see records where the table.department = "Finance".

Creating Business Views properly is something that takes practice and understanding of the feature.  At minimum, I would highly recommend you review any available documentation. Here's a link to an excerpt from 'Crystal Reports 2008: The Complete Reference":

If you have an opportunity to do so, I would highly recommend taking a class on the feature.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Are these reports written in Crystal or BO?

My method would work with the subreport, however in view of thenumber of reports, I agree the effort would be tremendous.  I believe rhinok has suggested a viable solution.

Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
They're written in Crystal Reports and published in BusinessObjects Enterprise XI R2.  Unfortunately, the effort will still be tremendous - you should definitely consider this a project and devote resources, estimate time and expenses, etc... if you decide to do this.  This isn't something you can "fix" via forums, unfortunately.  It really has to do with how you've designed and deployed your Enterprise reporting system.
sanjeev410Author Commented:
Hi Experts,
thank you.
I am ready to pay :)
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.