BusinessObjects 4.2 sp09 - Crystal Reports 2016 - Dynamic Parameters

[111378]

Hello,

We have some existing Crystal Reports into which we're trying to add dynamic parameters so they can be scheduled ad hoc. We run into issues when trying to save to the repository after adding the Dyn Parameters. Any ideas of what we could be doing wrong? Is there another workflow that I'm not aware of?

BOBJ 4.2 sp09 and Crystal Reports 2016

Thanks,

Mike

[DellSC · Accepted solution]

In BOBJ, Crystal dynamic parameters are managed through the Business View Manager (BVM) that is available in the Client Tools install. It has it's own security schema that is not available through the normal BOBJ security - you run the BVM to get to it, although you'll use user groups from BOBJ to assign security. I recommend having a specific user group for your report developers who can modify dynamic params. You'll probably need to log in with Administrator rights the first time in order to set this up.

1. Run the BVM.

2. Right-click on the "Dynamic Cascading Prompts" folder and select "Edit Rights". You'll see something like this:

3. Add the user group for your report developers and grant access to View and Edit, then click on OK.

Also, when working with Crystal dynamic parameters and BOBJ, it's a best practice to create the foundations for them in the BVM instead of creating them in Crystal and them publishing them. This way you can do things like create a single Connection that all of the business views that drive the dynamic parameters instead of having duplicate Connections - one for each parameter.

The easiest way to get the hang of this is to publish a single dynamic parameter from Crystal and then use that connection and the rest of the objects as a foundation for any other dynamic parameters you need. Once you have published the first set, I would do the following:

1. Run the BVM.

2. Find the for the param you just saved It will be named in the format <parameter name>_DC. Rename it so that it is named for the database you're connecting to, but keep the "_DC" on the name so that you know this is the connection to use. Also, edit it and set the user name password for the connection (assuming you're not using SSO to the database). You'll use this single connection for creating the objects needed for reports based on this database.

3. Find the Data Foundation you published - it will be named with the format <parameter name>_DF. This is like the data foundation in the IDT. It contains tables and links between them. When you create parameters by publishing them from Crystal, this will contain ALL of the table and links from the report. This is not necessarily the most efficient way to get the data for the lookup. So, in this case, delete all of the tables and links that are not required for getting the lookup data. In the example below, because I want the Customer Name, I would delete the Orders, Orders_Detail, and Product tables, leaving just the Customer table where the name is. Or, if I just want those customers who have orders, I could a left inner join to orders, which would filter out those customers who don't have orders. I could also create a filter that would exclude inactive customers from the list.

When creating your own data foundations, just add the table(s) that are needed for the lookup. Or you can create and use Commands (SQL Select statements) in your data foundations - just don't include parameters in the Command if you do this. Save the DF when you're finished editing it.

Data Foundations can potentially contain the tables for multiple parameters if, for example, you have a table or set of interconnected tables that can provide lookups for multiple parameters.

4. Now look for the Business Elements (name ends with "_BE") for the published parameter. This is the list of fields that are available for use by the business views used to create dynamic params. You can set up a single set of BE to cover all possible parameters from the DF or you can create a separate BE for each param.

5. Next up is the Business View (name ends with "_BV"), which is a collection of fields from one or more Business Elements. Each param will have its own Business View.

6. Finally, we have a List of Values (LOV). This is the object that you published that has no suffix, just the parameter name. LOVs are available in Crystal for creating parameters. I will usually right-click on the LOV and schedule it once as a way of verifying that I have everything set up correctly.

If it the schedule runs correctly, there will be a success message in the Schedule Status box in the LOV properties screen. If there's an error, the status will show the error. Be sure the clear the instance after it's successful run. This way the LOV will run to provide up-to-date data for the parameter.

7. You'll also see objects with a suffix of "- Prompt Group" in the BVM. These are created when an LOV is used to create a dynamic param in Crystal which is then saved to the repository when the report is published. These are also available in the Repository Explorer in Crystal for use in dynamic params. I will usually build a simple report on the table that has the param data and create a two Prompt Groups - one for single-select parameters and another for multi-select parameters - so that there isn't a proliferation of prompt groups when multiple reports use the dynamic parameter.

There is also a very good section in the BVM help titled "Best practices for prompting", that will walk you through all of this as well.

-Dell

[111378 · Accepted solution]

Thank you so much for the detailed and thoughtful answer. I was oblivious to the BVM and its functions. I will attempt these workflows.

Thanks again,

Mike