Subreport parameter passed from main Report keeps prompting for values
I'm facing the next problem: I need to create a report which, based on the BO User Name who executes it, makes up a list of valid values allowed for that specific user. There is a SQL Server 2008 table which stores the valid values for each user. I thought of creating a Command (SQL Query) to inquire on that table, based on the CurrentCEUserName() function. However, as far as I know, it is not possible to use variables or BO functions within the Command definition, but only Parameters.
The approach I followed is like this:
I constructed a simple report (main report) which contains a subreport in it. In the main Report, I created a Formula Field which gets the result of calling the CurrentCEUserName() function. Then, I intended to pass this value to the Subreport (which I placed in the Main Report's Report Footer)
Within the Subreport (Test3.rpt) I created a connection to a SQL Server 2008 DB, in order to retrieve the tables I need to display in the report. I created a Command too (Query_Names_For_User). This Command would filter the data which is related to the UserID I sent as parameter from within the Main Report:
When the query gets executed, I'd get some data like this:
So, when user AFSITAdmin signs in, the Command should get the first 3 rows
In turn, I would use this data set ('Query1', 'Query2' and 'Query3') so that the user could choose one or more of these values for running the subreport.
Then, the Subreport's parameters would look like this:
The Query_Name parameter is based on the QueryDescription Command's column:
Then in the Select Expert Section I filter the data based on the Query_Name Parameter:
When I execute the report, it asks for the UserID parameter :
I think this should not happen since this parameter was passed using the User_ID Formula Field I created in the main report.
Anyway, if proceed and go ahead and type a valid User ID , like AFSITAdmin, then, the Command gets executed accordingly and the valid rows for that user get displayed on the Prompt so that the user can choose one or more values from the valid ones for him/her
If I select valid values then the report shows the data properly filtered:
However, it is not right that the report asks for the UserID parameter since it was passed down from the Main Report into the SubReport. Besides, it would be no logical that the users were prompted to enter their own UserIDs in order to have their data available to be filtered (moreover, a user could enter other user's UserID and handle data he/she should not have access to).
I'm using Crystal Reports 2011, SP10, Patch1.
Any comment or help would be greatly appreciated
I did the last part of my scenario, which includes to pass a parameter which is based on a user selection which is, in turn, based on the only records over which this user has access to (for that matter, this data could be a Cost Centers List, Items, Accounts or any other data upon which, a user could have a restricted access).
- Added a Subreport in the Main Report's Footer
- I created a Command within the Subreport as the Datasource for it .I created a Parameter called P_QueryName in the same Command definition. This parameter will receive the value stored in the Main Report's P_QueryName Parameter so that the Command will filter only those values (Cost Centers, Items, Accounts or so) chosen by the user. Remember that (thanks to the Data Foundation filter created in the Main Report) , these values, were taken from the list of valid values for the user who is running the report.
- I linked the P_QueryName Report Parameter, to the P_QueryName Subreport Parameter
This way, the Main Reports allowed me for
- Applying the Row Level Security for a specific user
- To create a Parameter based on the data allowed to the user
- To pass this parameter to a Subreport so that a Command in it can make use of such parameter
Now, when I filter any data on the Main Report, it gets properly reflected on the Subreport
Abhilash, thanks a bunch for your help, we appreciate it a lot