Dynamic Parameter Record Selection 1

[Zbrojovka]

I have the following report scenario:

I am grouped by ivmain.officer_ID then ivmain.Date_occu.

My report links to the Employee table: "ivmain"."officer_id"="emmain"."empl_id" so I can resolve the actual employee's First and Last Name.

I have set up a dynamic parameter in which ivmain.officer_ID is selected first and described by emmain.emLname.
The hierarchical connection then allows the user to select the First name from emmain.emFname based on their first selection. This is set to allow multiple values.

I have the following selection criteria set currently:

{systab1.code_key} = "DIVS" and
{emmain.emfname} startswith ({?Driver - emfname}) and
{ivmain.officer_id} = {?Driver - officer_id}

This will not return any results. Now I can of course take the ivmain.officer_id out of the equation and just dynamically set parameters based on employee last name, first name, but I only want the last names of people entered into the ivmain table to appear in my parameter list, then link so a selection can be made for the first name.


I've tried the above selection criteria as {ivmain.officer_id} startswith {?Driver - officer_id}, but of course this will not work because I'm not dealing with a string field for this piece.

The dynamic parameters work perfect, I just need help with the selection formula since I'm pulling Number and String fields.

Thanks In Advance!!

 

[lbass]

If you use an equal join between the lvmain and emmain tables and also check "enforce both" in the link options, you should only see names in the parameter list that correspond to IDs in the lvmain table.

-LB

 

[Zbrojovka]

Thanks lbass,

I've actually got the parameter section working just fine. What do I need to correct my selection formula so the ivmain.officer_id and emmain.emFname are queried correctly and displayed?

 

[lbass]

I would try the following, with the higher order parameter first (this is a cascading parameter, correct?):

{systab1.code_key} = "DIVS" and
{emmain.emLname} startswith ({?Driver - emLname}) and //or however that looks as the highest one
{emmain.emfname} startswith ({?Driver - emfname})

-LB

 

[Zbrojovka]

Yes correct, this is a cascading parameter.

Your select formula would also be correct, except my problem is I'm using ivmain.officer_id as the first parameter in the cascade and it is a Number field. I just have the description linked to emmain.emLname. So I'm confused on how to write the select formula to look at the first Number parameter (Officer ID) and then the String parameter (First Name) next, especially when allowing multiple values.

 

[lbass]

But you don't need to use the officer ID, if you use the linking I suggested earlier. Is there some other reason you want to use the officer ID?

Actually, since you are using a dynamic parm to populate the pick lists, you can set up the record selection with = signs instead of using "startswith".

-LB

 

[Zbrojovka]

I have to use officer_id because that is the primary selection. I'm only using anything from the emmain table as a reference for descriptions and it is linked on the system ID.

Since the officer_id is a number field I can't say:

{ivmain.officer_id} = ({?Driver - emlname}).

I should be able to use {ivmain.officer_id} = ({?Driver - officer_id}) since the parameter is in essence being selected by the ID. It displays the description because of the dynamic link, but it is using the ID. It is all breaking because I'm trying to select a number field and a string field in the same statement. I don't want to break these up because that would break my Cascading parameter.

 

[lbass]

I guess I don't really see why you have to use officer ID. If you are displaying the Lname field as the description field, it implies a one-to-one relationship between the two fields, so as long as the two tables are linked together as suggested, why wouldn't that work? It would help to see a sample of how these fields would display if placed in the detail section.

-LB

 

[Zbrojovka]

Well, if you are getting confused that means I definitely am.

Here's the SQL query which works perfect and returns the right data. I've tried to simplify things by just focusing on the officer_id with the dynamic parameter defined by the emmain table. But even the SQL query works correctly when adding the second parameter.

SELECT "ivmain"."date_occu", "ivmain"."carid1", "emmain"."emlname",
"emmain"."emfname", "systab1"."code_key", "ivmain"."ivmainid",
"ivmain"."hour_occu", "ivmain"."preventabl", "ivmain"."streetnbr",
"ivmain"."street", "ivmain"."city", "ivmain"."state", "ivmain"."emshift",
"ivmain"."dtudftext1", "ivmain"."officer_id", "ivmain"."notes",
"ivmain"."reason"
FROM ("?Server?"."dbo"."ivmain" "ivmain" INNER JOIN
"?Server?"."dbo"."emmain" "emmain" ON
"ivmain"."officer_id"="emmain"."empl_id") INNER JOIN
"?Server?"."dbo"."systab1" "systab1" ON
"emmain"."emdivision"="systab1"."code_agcy"
WHERE "systab1"."code_key"='DIVS' AND "ivmain"."officer_id"=77
ORDER BY "ivmain"."officer_id"

All I would add for the second parameter for the first name would be AND "emmain"."emfname"='JOHN'


I've linked the tables as you suggested by enforcing both ways, but this makes no difference. Even if I leave the officer_id out all together and create my dynamic parameter with the employee lname, fname; I don't get the desired list of names in my parameter list.
The only way I've been able to get the correct list is to utilize the ivmain.officer_id field.

I keep going back to if I can get my list correct why can't I have a formula that plays nice on the selection side?

 

[Zbrojovka]

Anyone have any ideas on this one? I'm still stumped with it. Considering just staying with Static, String, Multiple Values. I was just trying to save the users some time.

 

[IanWaterman]

Have you checked to see if query returns data

SELECT "ivmain"."date_occu", "ivmain"."carid1", "emmain"."emlname",
"emmain"."emfname", "systab1"."code_key", "ivmain"."ivmainid",
"ivmain"."hour_occu", "ivmain"."preventabl", "ivmain"."streetnbr",
"ivmain"."street", "ivmain"."city", "ivmain"."state", "ivmain"."emshift",
"ivmain"."dtudftext1", "ivmain"."officer_id", "ivmain"."notes",
"ivmain"."reason"
FROM ("?Server?"."dbo"."ivmain" "ivmain" INNER JOIN
"?Server?"."dbo"."emmain" "emmain" ON
"ivmain"."officer_id"="emmain"."empl_id") INNER JOIN
"?Server?"."dbo"."systab1" "systab1" ON
"emmain"."emdivision"="systab1"."code_agcy"
WHERE "systab1"."code_key"='DIVS' AND "ivmain"."officer_id"=77
AND "emmain"."emfname"='JOHN'
ORDER BY "ivmain"."officer_id"

It could be that none of the records in your database meet this criteria.

Ian

 

[Zbrojovka]

Yes, the queries return the exact data in SQL. For some reason the select criteria I've specified doesn't play nice in Crystal, even though the query generated by Crystal works just fine.

Shouldn't this work?

{systab1.code_key} = "DIVS" and
{emmain.emfname} = {?Driver} and
{ivmain.officer_id} = {?Driver - officer_id}


Where emfname is String value and officer_id is a Number.

 

[IanWaterman]

Run the report with the 3 params you have and then check to see what SQL is generated.

Is it the same as above.

Ian

 

[Zbrojovka]

Yep, exact same statement pasted above.

 

[IanWaterman]

Now I am confused, if the parameters generate the correct query and that query brings back data. I can not see why Crystal is not displaying anything.

When the report runs does it give you a record count in bottom right?

Are you sure Crystal is pointing at the a same database as when you test the query in SQL Server.

Ian

 

[Zbrojovka]

Yep, and now we have the problem. Why is the query generated displaying data and Crystal is not?

I am pointing at the correct data source yes and the record count ='s a big fat 0.

This is the entire problem actually. Ol' faithful SQL is right and Crystal is bugging out. Crystal very rarely lets me down...especially with something SO simple. I been over the report forward and backwards checking links, checking groups, data, etc, etc.

I think I'm going to let this one go. The users can continue entering Static values just fine.

Many thanks to lbass and IanWaterman for responding to my post and spending the time to give it some thought.

 

[lbass]

When you set this up, let's say you name it officer-fname. Then you choose 'dynamic' and then add the officer ID field and click on create parameter on the right. It will autmatically change to number type. Then you also add the emfname in the same screen (2nd row) and then click on create parameter again. The datatype will appear as string. You should set each parameter up to allow multiple values.

You should then add a record selection formula that looks like this:

{lvmain.officer ID} = {?officer-fname - officer ID} and
{emmain.emfname} = {?officer-fname - emfname} and
{systab1.code_key} = "DIVS"

-LB