Help to Translate Crystal Rpt "Command" into Record Selection Criteria

[kwtx04rm]

Can anyone help in translating a Crystal Report "Command" into Crystal Report "Record Selection Criteria"? My SQL knowledge is limited, but I can determine tables and fields the command uses.

There are 7 tables as follows (in parentheses is the non alias table name):
amportfolio p (AMPORTFOLIO)
ammodel m (AMMODEL)
ammodel m1 (AMMODEL_1)
amcomputer c (AMCOMPUTER)
amasset a (AMASSET)
amastprojdesc pd (AMASTPROJDESC)
amproject pr (AMPROJECT)

And this is what I've been able to translate into Crystal Report record selection criteria:
{AMMODEL_1.NAME} IN ["HOSTS"] AND
{AMPORTFOLIO.SEASSIGNMENT} IN [0,1] AND
{AMPORTFOLIO.BOFA_MGMTGROUP} <> 5

The problem I am having is figuring out the rest of the Crystal Report record selection criteia...especially the part where the Command has nested Select statements. Below is what displays when editing the command:

SELECT a.serialno, c.tcpiphostname, m1.name as parent_model, c.computertype, p.bofa_mgmtgroup, m.name as model, p.lparentid as parent_portfolio
FROM amportfolio p
LEFT OUTER JOIN ammodel m ON p.lmodelid = m.lmodelid
LEFT OUTER JOIN ammodel m1 ON m.lparentid = m1.lmodelid
LEFT OUTER JOIN amcomputer c ON p.lportfolioitemid = c.litemid
LEFT OUTER JOIN amasset a on p.lastid = a.lastid
WHERE M1.NAME = 'HOSTS'
AND P.SEASSIGNMENT IN (0,1)
AND P.BOFA_MGMTGROUP <> 5
AND (P.lparentid NOT IN
(SELECT p.lportfolioitemid
FROM amportfolio p
LEFT OUTER JOIN ammodel m ON p.lmodelid = m.lmodelid
LEFT OUTER JOIN ammodel m1 ON m.lparentid = m1.lmodelid
WHERE m1.name = 'SERVER') OR P.LPARENTID = 0)
AND LPORTFOLIOITEMID NOT IN
(SELECT P.LPORTFOLIOITEMID
FROM amportfolio p
LEFT OUTER JOIN ammodel m ON p.lmodelid = m.lmodelid
LEFT OUTER JOIN ammodel m1 ON m.lparentid = m1.lmodelid
LEFT OUTER JOIN amastprojdesc pd on p.lastid = pd.lastid
LEFT OUTER JOIN amproject pr on pd.lprojid = pr.lprojid
WHERE m1.name = 'HOSTS'
AND PR.BOFA_SOURCE = 2
AND PR.CODE = '32')

Many thanks if you can assist!!!

 

[synapsevampire]

Why are you moving away fom a commandto using the Crystal GUI?

Consoierusing a SP or a View, and please remember to post your software versions (Crystal and the database).

-k

 

[kwtx04rm]

Hi synapsevampire:

Currently using CR 10 Professional...will be upgrading to CR XI Developer. Datasources used are DB2 and SQL Server databses.

Currently my skill level with SQL is minimal - as you can see I was able to decipher part of the command into Crystal syntax and I am better able to create or modify reports with the Crystal GUI. I plan to become more proficient with SQL going forward, but for now I need help to fully translate the command into Crystal record selection. Also, there are other reports that were developed by others which are being transitioned to me and they use "Commands".

Regards.

 

[synapsevampire]

Use a subreport or MS Access.

-k

 

[lbass]

I answered your other post. I think it is a misstep to try to convert a command back to using the CR GUI--there is a reason that the original developer used a command, and the same goal cannot always be accomplished within CR. Even if it can, your report will likely be less efficient and with unnecessary complexity.

-LB

 

[kwtx04rm]

synapsevampire and lbass

Appreciate the feedback...thank you.

 

[synapsevampire]

I agree with LB, until youmust change something that impacts that, leave it.

In the interim, forge a relationship with the DBAs there,and if they want touse realSL, which I'm a big fan of, ask them to create Views or SPs.

-k