Skip to Content
avatar image
Former Member

Subreport parameter passed from main Report keeps prompting for values

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jul 29, 2015 at 11:24 AM

    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:


    Add comment
    10|10000 characters needed characters exceeded

  • Jul 29, 2015 at 06:50 AM

    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:



    Add comment
    10|10000 characters needed characters exceeded

    • 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