How to select codes by selected group 1

[Cpreston]

Hi

I have 2 parameters set, Groups and code.

Each Group consists of specific codes.

When I select the group I need and then have to trawl through all the codes to find the one I require.

What I need is when I select a group it then only displays the codes that are in that group (each product code is part of a specific product group)

As anyone any ideas how I can do this please. I am using a cross tab report as it displays Jan to Feb figures over the various years.

Thanks

 

[hilfy]

Assuming that your groups and codes are in the database, you need what is called a "Cascading Dynamic Prompt". How are you running the report? If you're running it through BusinessObjects or Crystal Reports Server, you will want to use the Business View Manager (BVM) to create this instead of doing it directly in the report. If not, you'll do it in the report.

I will generally separate the query for the prompt from the query for the data in the report - it's more efficient that way. So, assuming you're building this in the report, here's what I would do:

If you have the group name and codes all in the same table, add that table to the report - if it's already included in the report, add another copy of it. Crystal will show a warning and ask if you want to "alias" the table. It will then add the table with "_1" on the end of the table name. If you have to have multiple tables to get this information, you'll probably need to use a "Command", which is just a SQL Select statement, which will have logic like this:

Select
g.GroupIDField,
g.GroupNameField,
c.CodeIDField,
c.CodeNameField
from GroupTable as g
left join GroupCodeTable as c
on g.GroupIDField = c.GroupIDField
order by g.GroupNameField, c.CodeNameField

DO NOT link this command or the aliased table to ANY other tables in the report. Crystal will throw a warning saying this is generally not supported, but it will work in this case. DO NOT use fields form the command or aliased table in the report or formulas - use them to configure the parameter ONLY!

Now you'll create a single new parameter. Make it dynamic under "List of Values" in the top right of the parameter screen. Leave "Choose a Data Source" set to "New". Click in the first row of the "Value" column in the table and select the GroupIDField. Click in the Description column next to the value that you just added and select the GroupNameField. If you need to use the group as a separate parameter (only necessary if a single code can belong to multiple groups, click in the Parameters column to create a separate parameter for the group. In the row below the one you just edited, add the value and description for the code parameter and create the parameter. By selecting a specific row in this table you can edit the properties of the parameter itself. I will usually set the sort order to "Ascending by Description" and "Show Value or Description" to "Show Description".

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Cpreston]

well I had to play a bit to get it working but eventually got it working after 3 versions of the report I got it right.

Thanks for the advice and help and guidance