Parameter based on another parameter

[piovesan]

Hi;
I have had to create two parameters in a report because I am querying two similar tables (one which is sort of an archive of the other but we still want the old data to show up along with the new data from the active table). So basically, I have two parameters, each selecting the same field values in two different tables....To the user, it looks like they are selecting their parameter choice twice...
Is there a way to have the value of one parameter simply "pick up" the value that was entered into the first parameter?
Thanks!
(I am using Crystal Report Developer 11.5)

 

[lbass]

Just use one parameter and set both fields to it, using an "or" statement:

(
{tableA.field} = {?Parameter} or
{tableB.field} = {?Parameter}
)

However, you might be better off creating a command to use as your sole datasource, where you "merge" the two tables using a union all statement:

select 'A' as whichone, tableA.field1, tableA.field2
from tableA
union all
select 'B' as whichone, tableB.field1, tableB.field2
from tableB

{Command.field1} would then contain both field1 fields, etc. You could use {command.whichone} to distinguish the two.

-LB

 

[piovesan]

This question is related to an earlier one I wrote yesterday... I thought my problem was with the parameters but even without the parameters, I seem to have a problem selecting records from two tables with an “or” statement…
Attached is the SQL from the report I am trying to build.
As you can see, there are several joins, and the relationship between the Parcel table and the Parcel_Modified table is basically this, a record related to the acquisition of land is inserted into the Parcel table. When that land gets modified (subdivided), that original record gets copied into the Parcel_Modified table, and the new land records (say the two new parcels that were created through the subdivision of land) gets input into the Parcel table. The original and the two new records are all related through a field in the Acquisition_Disposal table called orig_asset.
The report I am trying to create needs to show all parcels of land acquired as they were originally acquired…. Therefore, if a parcel was modified, the record that needs to display is NOT the two new records that are in the Parcel table, but the original record that is now in the Parcel_Modified table. This is why that orig_asset field is so important.
I also need to build in a parameter (or just create two reports, one for each type) that displays land for Business Parks, or all non-business park lands….this is a field called Main Class from either the HRM_Parcel table or the HRM_Parcel_Modified table.
So, even without using parameters (lets just say I was going to build two report, one for Business Parks, and the other for Non, the filter query would be like this would it not??:
" ACQUISITION_DISPOSAL"."TRANS_TYPE"=1 AND NOT ("ACQUISITION_DISPOSAL"."ACQDISTYPE"='EXCHANGE' OR " ACQUISITION_DISPOSAL"."ACQDISTYPE"='SUBDIVISION') AND ("ACQUISITION_DISPOSAL"."ACQDISDATE">=TO_DATE ('01-11-1979 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND " ACQUISITION_DISPOSAL"."ACQDISDATE"<TO_DATE ('31-12-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND ("PARCEL"."MAIN_CLASS"<>'BUSINESS PARK' OR " PARCEL_MODIFIED"."MAIN_CLASS"<>'BUSINESS PARK')

However, it is the end section in that filter that does not seem to work! With this statement above, I get ONLY the records in the Parcel table that do not have a main class of Business Park. I don’t get any records at all from the Parcel_Modified table. And if I reverse the order if that part if the statement, I get only records from the Parcel_Modified table, and none from the Parcel table….. If I delete just that part of the filter, everything works fine.... I just get all of the Main Class types of land..... I just want to be able to seperate them out either by a parameter choice selection, or using two reports....
There is probably something really obvious and stupid I am doing, but I can’t see it!!
Help??

 

[piovesan]

oops... I meant to put that last part as a new question.... sorry!!

 

[lbass]

Did you try the add command->union all statement suggestion? I really think you should go that way. If you want help with it, please copy your SQL query into the thread (database->show SQL query).

-LB

 

[piovesan]

OK... I will try anything now... I am attempting to attach the document again with the SQL statement...
Thanks!

 

[piovesan]

Mediafire does not seem to be working.... try this attachment....

 

[lbass]

Okay, I guess I'm wondering why you are not also connecting parcel_modified to aliases of the same tables that parcel is connected to? Don't you want the same selection criteria to apply?

Assuming this is the case, I think you should try the following command as your sole datasource for the report:


SELECT DISTINCT
'Parcel' "Type",
"ACQUISITION_DISPOSAL"."ACQDISTYPE",
"ACQUISITION_DISPOSAL"."ACQDISDATE",
"ACQUISITION_DISPOSAL"."ACQ_PURP",
"ACQUISITION_DISPOSAL"."ORIG_ASSET",
"ACQUISITION_DISPOSAL"."ACQDISP_ID",
"PARCEL"."ASSET_TYPE",
"ACQUISITION_DISPOSAL"."TRANS_TYPE",
"CIVIC_ADDRESS"."CIV_ID",
"CIVIC_ADDRESS"."STR_NAME",
"CIVIC_ADDRESS"."GSA_NAME",
"PR_REALESTATE_COMMENTS"."RE_LOCATE",
"PARCEL"."PID",
"ACQUISITION_DISPOSAL"."ACQDISCOST",
"PR_CLIENT"."CLIENTCODE",
"PR_CLIENT"."CLIENTNAME",
"PARCEL"."MAIN_CLASS",
"PARCEL"."HECTARES",
"PID_DIST"."DIST_ID",
"PARCEL"."ASSET_ID",
"CIVIC_ADDRESS"."CIV_NUM"

FROM
(((("SDEADM"."ACQUISITION_DISPOSAL""ACQUISITION_DISPOSAL"

LEFT OUTER JOIN "SDEADM"."PARCEL""PARCEL"ON
"ACQUISITION_DISPOSAL"."ORIG_ASSET"="PARCEL"."ASSET_ID" and
"ACQUISITION_DISPOSAL"."TRANS_TYPE"=1 AND
NOT ("ACQUISITION_DISPOSAL"."ACQDISTYPE"='EXCHANGE' OR
"ACQUISITION_DISPOSAL"."ACQDISTYPE"='SUBDIVISION'
) AND
(
"ACQUISITION_DISPOSAL"."ACQDISDATE">=TO_DATE ('01-11-1979 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"ACQUISITION_DISPOSAL"."ACQDISDATE"<TO_DATE ('31-12-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
) and
"PARCEL"."MAIN_CLASS"<>'{?Class}'
)

LEFT OUTER JOIN "SDEADM"."CIVIC_ADDRESS""CIVIC_ADDRESS"ON
"PARCEL"."PID"="CIVIC_ADDRESS"."PID"
)

LEFT OUTER JOIN "SDEADM"."PR_CLIENT""PR_CLIENT"ON
("PARCEL"."ASSET_ID"="PR_CLIENT"."ASSET_ID") AND
("PARCEL"."PID"="PR_CLIENT"."PID")
)

LEFT OUTER JOIN "SDEADM"."PR_REALESTATE_COMMENTS""PR_REALESTATE_COMMENTS"ON
("PARCEL"."ASSET_ID"="PR_REALESTATE_COMMENTS"."ASSET_ID") AND
("PARCEL"."PID"="PR_REALESTATE_COMMENTS"."PID")
)

LEFT OUTER JOIN "SDEADM"."PID_DIST""PID_DIST"ON
"PARCEL"."PID"="PID_DIST"."PID"

union

SELECT DISTINCT
'Parcel Modified' "Type",
"ACQUISITION_DISPOSAL"."ACQDISTYPE",
"ACQUISITION_DISPOSAL"."ACQDISDATE",
"ACQUISITION_DISPOSAL"."ACQ_PURP",
"ACQUISITION_DISPOSAL"."ORIG_ASSET",
"ACQUISITION_DISPOSAL"."ACQDISP_ID",
"PARCEL_MODIFIED"."ASSET_TYPE",
"ACQUISITION_DISPOSAL"."TRANS_TYPE",
"CIVIC_ADDRESS"."CIV_ID",
"CIVIC_ADDRESS"."STR_NAME",
"CIVIC_ADDRESS"."GSA_NAME",
"PR_REALESTATE_COMMENTS"."RE_LOCATE",
"PARCEL_MODIFIED"."PID",
"ACQUISITION_DISPOSAL"."ACQDISCOST",
"PR_CLIENT"."CLIENTCODE",
"PR_CLIENT"."CLIENTNAME",
"PARCEL_MODIFIED"."MAIN_CLASS",
"PARCEL_MODIFIED"."HECTARES",
"PID_DIST"."DIST_ID",
"PARCEL_MODIFIED"."ASSET_ID",
"CIVIC_ADDRESS"."CIV_NUM"

FROM
(((("SDEADM"."ACQUISITION_DISPOSAL""ACQUISITION_DISPOSAL"

LEFT OUTER JOIN "SDEADM"."PARCEL_MODIFIED""PARCEL_MODIFIED"ON
"ACQUISITION_DISPOSAL"."ORIG_ASSET"="PARCEL_MODIFIED"."ASSET_ID" and
"ACQUISITION_DISPOSAL"."TRANS_TYPE"=1 AND
NOT ("ACQUISITION_DISPOSAL"."ACQDISTYPE"='EXCHANGE' OR
"ACQUISITION_DISPOSAL"."ACQDISTYPE"='SUBDIVISION'
) AND
(
"ACQUISITION_DISPOSAL"."ACQDISDATE">=TO_DATE ('01-11-1979 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"ACQUISITION_DISPOSAL"."ACQDISDATE"<TO_DATE ('31-12-2011 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
) and
"PARCEL_MODIFIED"."MAIN_CLASS"<>'{?Class}'
)

LEFT OUTER JOIN "SDEADM"."CIVIC_ADDRESS""CIVIC_ADDRESS"ON
"PARCEL_MODIFIED"."PID"="CIVIC_ADDRESS"."PID"
)

LEFT OUTER JOIN "SDEADM"."PR_CLIENT""PR_CLIENT"ON
("PARCEL_MODIFIED"."ASSET_ID"="PR_CLIENT"."ASSET_ID") AND
("PARCEL_MODIFIED"."PID"="PR_CLIENT"."PID")
)

LEFT OUTER JOIN "SDEADM"."PR_REALESTATE_COMMENTS""PR_REALESTATE_COMMENTS"ON
("PARCEL_MODIFIED"."ASSET_ID"="PR_REALESTATE_COMMENTS"."ASSET_ID") AND
("PARCEL_MODIFIED"."PID"="PR_REALESTATE_COMMENTS"."PID")
)

LEFT OUTER JOIN "SDEADM"."PID_DIST""PID_DIST"ON
"PARCEL_MODIFIED"."PID"="PID_DIST"."PID"

ORDER BY 4,2

This assumes that the same fields are in each table. This causes fields from corresponding tables to merge into one command field. You can distinguish them, if necessary, by referencing the field {command.type}.

I also moved the "where" conditions into the From clause so that you can preserve the left joins. I replaced "Business Park" with a parameter {?Class}--you should create this in the command area on the right hand side (not in the main report).

-LB

 

[piovesan]

Hi again! I am trying this... I just have a question... what does the first part in each section of your statement do? 'Parcel' "Type" and 'Parcel_Modified' "Type"

And also, what does the order by 4,2 do?
Thanks!!

 

[lbass]

I created the Type field so you could distinguish which rows were generated by which table.

In union statements, you can't add an "order by" clause must be provided at the end of the last union statement. The 4 and 2 reference the ordinal number of the field you are ordering by in the select clause--which I now see, should have been changed to 5 and 3, after I inserted the "Type" field, so please change that to:

order by 5,3

-LB

 

[piovesan]

Thanks lbass! I am still working on this one and am getting really close to what I want.... I need help with one other thing though.... Here is the Command I have created so far:
SELECT DISTINCT
'Parcel' "Type",
"LND_ACQUISITION_DISPOSAL"."ACQDISTYPE", "LND_ACQUISITION_DISPOSAL"."ACQDISDATE", "LND_ACQUISITION_DISPOSAL"."ACQ_PURP", "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET", "LND_ACQUISITION_DISPOSAL"."ACQDISP_ID",
"LND_HRM_PARCEL"."ASSET_TYPE",
"LND_ACQUISITION_DISPOSAL"."TRANS_TYPE",
"LND_CIVIC_ADDRESS"."CIV_ID",
"LND_CIVIC_ADDRESS"."STR_NAME",
"LND_CIVIC_ADDRESS"."GSA_NAME",
"LND_PR_REALESTATE_COMMENTS"."RE_LOCATE",
"LND_HRM_PARCEL"."PID",
"LND_ACQUISITION_DISPOSAL"."ACQDISCOST",
"LND_PR_CLIENT"."CLIENTCODE",
"LND_PR_CLIENT"."CLIENTNAME",
"LND_HRM_PARCEL"."MAIN_CLASS",
"LND_HRM_PARCEL"."HECTARES",
"PID_DIST"."DIST_ID",
"LND_HRM_PARCEL"."ASSET_ID",
"LND_CIVIC_ADDRESS"."CIV_NUM",
"LND_HRM_PARCEL"."MOD_TYPE",
"LND_ASSET_XREF"."MODASSETID",
"LND_ASSET_XREF"."HRMASSETID"

FROM ((((("SDEADM"."LND_ACQUISITION_DISPOSAL" "LND_ACQUISITION_DISPOSAL" LEFT OUTER JOIN "SDEADM"."LND_HRM_PARCEL" "LND_HRM_PARCEL" ON "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET"="LND_HRM_PARCEL"."ASSET_ID")

LEFT OUTER JOIN "SDEADM"."LND_CIVIC_ADDRESS" "LND_CIVIC_ADDRESS" ON "LND_HRM_PARCEL"."PID"="LND_CIVIC_ADDRESS"."PID")

LEFT OUTER JOIN "SDEADM"."LND_ASSET_XREF" "LND_ASSET_XREF" ON "LND_HRM_PARCEL"."ASSET_ID"="LND_ASSET_XREF"."HRMASSETID")

LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT" ON ("LND_HRM_PARCEL"."ASSET_ID"="LND_PR_CLIENT"."ASSET_ID") AND ("LND_HRM_PARCEL"."PID"="LND_PR_CLIENT"."PID"))

LEFT OUTER JOIN "SDEADM"."LND_PR_REALESTATE_COMMENTS" "LND_PR_REALESTATE_COMMENTS" ON ("LND_HRM_PARCEL"."ASSET_ID"="LND_PR_REALESTATE_COMMENTS"."ASSET_ID") AND ("LND_HRM_PARCEL"."PID"="LND_PR_REALESTATE_COMMENTS"."PID"))

LEFT OUTER JOIN "SDEADM"."PID_DIST" "PID_DIST" ON "LND_HRM_PARCEL"."PID"="PID_DIST"."PID"

WHERE (("LND_ACQUISITION_DISPOSAL"."TRANS_TYPE" = 1)
AND NOT ("LND_ACQUISITION_DISPOSAL"."ACQDISTYPE"='EXCHANGE' OR "LND_ACQUISITION_DISPOSAL"."ACQDISTYPE"='SUBDIVISION'))

UNION

SELECT DISTINCT
'Parcel_Modified' "Type",
"LND_ACQUISITION_DISPOSAL"."ACQDISTYPE", "LND_ACQUISITION_DISPOSAL"."ACQDISDATE", "LND_ACQUISITION_DISPOSAL"."ACQ_PURP", "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET", "LND_ACQUISITION_DISPOSAL"."ACQDISP_ID",
"LND_HRM_PARCEL_MODIFIED"."ASSET_TYPE",
"LND_ACQUISITION_DISPOSAL"."TRANS_TYPE",
"LND_CIVIC_ADDRESS"."CIV_ID",
"LND_CIVIC_ADDRESS"."STR_NAME",
"LND_CIVIC_ADDRESS"."GSA_NAME",
"LND_PR_REALESTATE_COMMENTS"."RE_LOCATE",
"LND_HRM_PARCEL_MODIFIED"."PID",
"LND_ACQUISITION_DISPOSAL"."ACQDISCOST",
"LND_PR_CLIENT"."CLIENTCODE",
"LND_PR_CLIENT"."CLIENTNAME",
"LND_HRM_PARCEL_MODIFIED"."MAIN_CLASS",
"LND_HRM_PARCEL_MODIFIED"."HECTARES",
"PID_DIST"."DIST_ID",
"LND_HRM_PARCEL_MODIFIED"."ASSET_ID",
"LND_CIVIC_ADDRESS"."CIV_NUM",
"LND_HRM_PARCEL_MODIFIED"."MODIFY_TYPE",
"LND_ASSET_XREF"."MODASSETID",
"LND_ASSET_XREF"."HRMASSETID"

FROM ((((("SDEADM"."LND_ACQUISITION_DISPOSAL" "LND_ACQUISITION_DISPOSAL" LEFT OUTER JOIN "SDEADM"."LND_HRM_PARCEL_MODIFIED" "LND_HRM_PARCEL_MODIFIED" ON "LND_ACQUISITION_DISPOSAL"."ORIG_ASSET"="LND_HRM_PARCEL_MODIFIED"."ASSET_ID")

LEFT OUTER JOIN "SDEADM"."LND_CIVIC_ADDRESS" "LND_CIVIC_ADDRESS" ON "LND_HRM_PARCEL_MODIFIED"."PID"="LND_CIVIC_ADDRESS"."PID")

LEFT OUTER JOIN "SDEADM"."LND_ASSET_XREF" "LND_ASSET_XREF" ON "LND_HRM_PARCEL_MODIFIED"."ASSET_ID"="LND_ASSET_XREF"."MODASSETID")

LEFT OUTER JOIN "SDEADM"."LND_PR_CLIENT" "LND_PR_CLIENT" ON ("LND_HRM_PARCEL_MODIFIED"."ASSET_ID"="LND_PR_CLIENT"."ASSET_ID") AND ("LND_HRM_PARCEL_MODIFIED"."PID"="LND_PR_CLIENT"."PID"))

LEFT OUTER JOIN "SDEADM"."LND_PR_REALESTATE_COMMENTS" "LND_PR_REALESTATE_COMMENTS" ON ("LND_HRM_PARCEL_MODIFIED"."ASSET_ID"="LND_PR_REALESTATE_COMMENTS"."ASSET_ID") AND ("LND_HRM_PARCEL_MODIFIED"."PID"="LND_PR_REALESTATE_COMMENTS"."PID"))

LEFT OUTER JOIN "SDEADM"."PID_DIST" "PID_DIST" ON "LND_HRM_PARCEL_MODIFIED"."PID"="PID_DIST"."PID"

WHERE (("LND_ACQUISITION_DISPOSAL"."TRANS_TYPE" = 1)
AND NOT ("LND_ACQUISITION_DISPOSAL"."ACQDISTYPE"='EXCHANGE' OR "LND_ACQUISITION_DISPOSAL"."ACQDISTYPE"='SUBDIVISION'))

In the report, I have created two sections for details. Section A is to display the records from the Type = parcel and only those from the parcel items that have the Asset_ID equal to the Orig_Asset…. So, in the section expert, I have the supress set up to suppress Section A for the following:
{Command.Type} = "Parcel_Modified"
or {Command.ASSET_ID} <> {Command.ORIG_ASSET}

This works… it displays what I want……..in a way… I will explain below….

Section B of the details is the opposite… I want the parcel_modified records only, and only where the Asset_ID is equal to the Orig_Asset. So, again, I set up the following supression for Section B:
{Command.Type} = "Parcel"
or {Command.ASSET_ID} <> {Command.ORIG_ASSET}

This also works…. In a way….
The only problem with the two sections, is that because there can be more than one address for each Asset_ID, I am getting multiple records…… for example, in Section B, I have an Asset_ID 14652 showing up 5 times because there are 5 different Civ_IDs related to it… so that would so up as 5 different unique records in the union. What I tried to do, was create a MAXCIV formula like this:
if not isnull ({Command.CIV_ID}) then
Cstr (maximum ({Command.CIV_NUM}, {Command.ORIG_ASSET}), 0 , "")
else "no location entered - click globe for map"

thinking that would solve my problem by displaying just the maximum civic address for the Asset_ID. But it just displays the 5 records, all with the maximun civic……… so, I obviously haven’t adjusted the “display” of information with this formula….

Any ideas on how to just DISPLAY the maximum record??

Thanks!!

 

[lbass]

Try group selection. Go to report->selection formula->GROUP and enter:

isnull(maximum ({Command.CIV_NUM}, {Command.ORIG_ASSET})) or
{Command.CIV_NUM}=maximum ({Command.CIV_NUM}, {Command.ORIG_ASSET})

You can add a formula to the asset group section like this:
if isnull(maximum ({Command.CIV_NUM}, {Command.ORIG_ASSET})) then
"no location entered - click globe for map" else
totext(maximum ({Command.CIV_NUM}, {Command.ORIG_ASSET}),0,"")

-LB

 

[piovesan]

Do I have to take all my fields out of the details sections (section a and b) and put them into two Groups?? Is that the idea?

 

[lbass]

No. Why not just try the suggestion and see what happens?

-LB