Subreport parameter passed from main Report keeps prompting for values

[Former Member]

Hi all

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

[abhilash_kumar · Accepted solution]

Hi Gabriel,

What you're trying to do cannot be done using the CR Designer alone!

When you have a 'dynamic prompt' created off of a field in a 'Command SQL', the report first needs to run this SQL to get the List of Values for the prompt.

When the SQL itself is filtered via another prompt, this prompt needs to be answered before the list of values can be generated.

Unfortunately, the list of values cannot be generated by a 'static formula' linked to the subreport's datasource - that's just how CR works!

Odd enough, if you have a 'prompt' on the Main Report that is linked to the Subreport's command prompt this does work however, since you want the list of values to be generated using 'currentceusername', it really defeats the purpose of prompting the user for this ID.

The workaround involves using the Business View Manager. The only issue is that the report's datasource would also need to be a Business View:

1) In the Data Foundation, create a filter that looks like this:

{User_ID} = currentceusername

2) Create the LOV off of the Business Element

3) Use the Business View as the report's datasource and point the dynamic prompt to the LOV you created in the Business View Manager.

P.S: Read more about the BVM here:

http://help.sap.com/businessobject/product_guides/boexir3/en/xi3_bv_admin_en.pdf

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash

Your answer fit perfectly in the scenario I'm trying to implement.

I didn't know that filters can be implemented within a Data Foundation just like you suggested.

Although your suggestion was indeed very helpful on getting this doubt solved, I had to figure out some other issues which I'd like to point out, so that anyone having the same issue could have a more accurate solution.

I'm showing the next image which depicts the elements which I created on the Business View Manager tool

It is important to notice that the filter could be applied in the Business Element too, but I guess it all depends on specifically how you want to solve your own scenario.

Once I did this, I didn't get the correct results immediately, since my report kept displaying all the records regardless of the filter I just set up. Searching the web, I found this post:

http://www.tek-tips.com/viewthread.cfm?qid=1099357

So, besides creating the filter you still have to define the users who could be able to use this filter. Go to the filter definition in the Objects Explorer panel and right click on it.

Then select the users who could use the filter:

After that, I could see the correct report output in my report, showing just the records the Current CEUser  (the one running the report) has privileges to see:

Moreover, I created a List of Values within the Business View Manager, based on the same set of objects (Data Foundation, Data Element, Business View) in order to set a Prompt inside my report, so when it runs, the User can choose which data he'd like the report to display.

Then a Parameter must be created inside Crystal Reports , and then in the Select Expert Section, the actual filter based on this parameter can be done:

The Prompt, then, will only show data based on the data set the User is allowed to see:

So the reports displays like this:

I just have to do a next step and see if passing this Parameter down to a Subreport,, would help me to complete the whole scenario I have to work with.

Meanwhile, thanks a lot for your help Abhilash.

Best regards

[Former Member · Accepted solution]

Hi again.

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).

So I:

1. Added a Subreport in the Main Report's Footer
2. 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.
3. I linked the P_QueryName Report Parameter, to the P_QueryName Subreport Parameter

This way, the Main Reports allowed me for

1. Applying the Row Level Security for a specific user
2. To create a Parameter based on the data allowed to the user
3. 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

Best regards

Gabriel

[abhilash_kumar · Accepted solution]

Gabriel,

Thank you for the detailed solution! Would you be able to put this up as blog please?

-Abhilash

[former_member226669]

Hi Gabriel,

You have to link the formula created in main report to the Parameter in the sub report.

1) Right click the Subreport. Select Change Subreport Links...

2) Add the Main Report formula to the Field(s) to link to:

3) Select the Parameter P_UserID from the dropdown Subreport parameter field to use:

Thanks,

Nrupal

[Former Member]

Hi Nrupal

I had already done that as you can see in the very first image I placed in the original post.

What I needed was something more like the answer provided by Abhilash Kumar (watch below).Thanks anyway for the suggestion.

Best regards