Parameter field that will select specific records to run rpt against

[kapaa]

Not sure if this is possible with CR8 pulling from a SQL db, maybe I need CR9 or 10, if so say the word. When running certain reports there are 25 departments that might want to see data specific to their department only. As to editing reports to always add new departments I wanted something more dynamic where a parameter field that will always select only specific records based on a formula. I thought I could created Formula field that says:

if {contact_id} = 625 and {contact_org_id} = 10320467 then {contact_last_name}

The above will select only company depts from the 17,000 contact database but from here there's no way use this in a parameter field.

I was told I need to 'turn it around' by creating a new parameter field (like depts), on the select values page select the table 'contact', select 'contact_id', and then add all id's as default value. From here I guess I create a formula field that somehow uses my parameter 'depts'


This seems to make sense some sense but two things confuse me, how to create the formula in the formula field and how will this possibly work as a parameter field that will give the user a drop down box with 20 or so depts to select from? Again, if this is easier to do with ver 9 or 10 let me know.

 

[dgillz]

Your formula above will never limit the report's recordset, it will just selectively display the name (or not) on every record in the database.

If you put this formula, with a slight change, in the record selection formula, then it would limit the recordset. The record selection formula would be:
{contact_id} = 625 and {contact_org_id} = 10320467

Notice this formula will evaluate to true or false. All selection formulas must evaluate to true or false.

So then add your parameter field and replace the hard codes values of 625 or 10320467 with the parameter. I have no idea which one of these is a department so I cannot help you any more.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[IanWaterman]

Sounds like you need Crystal XI. This has dynamic and cascading parameters.

Parameter 1 is dynamically linked to your depts, thus if new depts are added or renamed the parameter list automatically updates. (older versions of CR meant that report had to be updated and parameter list manually refreshed)

Paramter 2 is also dynamic with a cascading link to parameter 1, such that if Dept 1 is selected only Contacts within Dept 1 are shown.

Hope that is what you meant, if yes then you need to upgrade.

Ian