Search code examples
sql-serverjoincrystal-reports

Dynamic Parameter showing too many values

I am currently creating a simple report in Crystal Reports with two tables:

{Table1.group_name_id} --> {Table2.technical_group_id}

Table 1 holds all of the groups; ID's, Names etc
Table 2 holds only the technical groups ID

With these two tables linked it means the only records that will return are those where technical groups are involved, perfect! But now I want to add a dynamic parameter to return the Technical Group Names for the End User to select.

Because {Table2} only holds one field (the ID) which links to {Table1}'s ID, I have to perform the Parameter selection on {Table1}'s name field.

But this is pulling back all of {Table1}'s names and is discounting the Join on {Table2} even with an enforced join present.

Is there a way to force it to only pull back {Table1}'s names as long as it matches the JOIN between {Table1} and {Table2}?

Thanks in advance!

Edit (additional information)

Table Link

Solution

  • As I thought - the problem was Crystal Reports was not recognising the JOIN when displaying the parameter values.

    After some reading, I found out that the JOINS are only recognised when the query is passed to the database (logically). So when selecting a Parameter, it doesn't recognise the JOINS.

    I got around this by creating a custom SQL command, forcing it to only pull back the groups in the second table.