Query Question

[metalteck]

In MS Access, I know that one would be able to create a query, and if needed to, could run another query based on the first query.

I was wondering, I have a report that pulls my data. Is there a way I could either make crystal run another query based on the first report and another table, etc?

 

[synapsevampire]

Sounds very inefficient to me.

You can export a report and then report off of the export.

Again, it sounds kludgy, I suggest that you post technical information and seek architectural ideas:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[metalteck]

Currently, the report is run in crystal and then exported to Excel.
It it then imported to Access.
Then I set the partition and connect to Access via ODBC.

I'm trying to find an easier and simpler way to do this.
Any suggestions?

 

[synapsevampire]

What is the original datasource, and why not design the report to return what you need the first time?

Note that asking for suggestions but ignoring requests for technical information will take much longersince we'll have to slowly coax asic information from you, and peoplelike me will bug out of the thread because you're unwilling to put the effort into the solution.

-k

 

[metalteck]

Crystal V 8.5
The data is all stored on a db2 server and crystal is the reporter. The inital report is called Reason Code. And this report gathers all of the codes for patients throughout a time period. After this report is generated, it is exported to access.

The exported report and 2 others are used generate the Revenue Report. But before the exported report can be linked to crystal, the location needs to be set and connected via the ODBC drivers.

What I basically want to know is do I need to export the data to access or is there a way I can run a saved report as a part of a new report. I would show you the sql code, but it won't show it to me, and the output is irrelevant. I am just thinking about the process of the report.

 

[synapsevampire]

The output is the goal, thinking it irrelevent is part of the reason you are jumping through hoops here.

I suggest that you LINK (not import) the DB2 tables to an MS Access database, build out what you need using queries, and base the end report on the query.

Crystal may be capable of doing it all in one fell swoop, but since you think that the data and output have nothing to do with design, I can't help you with that.

There are subreports in Crystal, which might operate as your "saved report".

-k

 

[metalteck]

Ok, I've been examining the report and this is what I have so far...

The Report is a list of accounts which are sorted this way.

SELECT
TRN_FILE."TRN_NUM", TRN_FILE."TRN_PAT_ACCT", TRN_FILE."TRN_SVC_DT", TRN_FILE."TRN_SVC_CD", TRN_FILE."TRN_SVC_TYP_CD", TRN_FILE."TRN_MOD_CD", TRN_FILE."TRN_FAC_CD", TRN_FILE."TRN_INS_CD", TRN_FILE."TRN_PRV_CD", TRN_FILE."TRN_AMT", TRN_FILE."TRN_UNT", TRN_FILE."TRN_ENC_NUM", TRN_FILE."TRN_RMK", TRN_FILE."TRN_RMK2", TRN_FILE."TRN_MOD_CD2",
PALW_FILE."PALW_SVC_CD", PALW_FILE."PALW_INS_CD", PALW_FILE."PALW_MOD_CD", PALW_FILE."PALW_FAC_CD", PALW_FILE."PALW_PRV_CD", PALW_FILE."PALW_1_EFF_DT", PALW_FILE."PALW_1_PRC1", PALW_FILE."PALW_2_EFF_DT", PALW_FILE."PALW_2_PRC1", PALW_FILE."PALW_3_EFF_DT", PALW_FILE."PALW_3_PRC1",
CLT_FILE."CLT_CLT_CD", CLT_FILE."CLT_CLIN_NM",
PAT_FILE."PAT_ACCT", PAT_FILE."PAT_NM",
EOB_FILE."EOB_RCT_NUM", EOB_FILE."EOB_RCT_POST_DT", EOB_FILE."EOB_RCT_AMT", EOB_FILE."EOB_COPAY_AMT", EOB_FILE."EOB_COINS_AMT",
INS_FILE."INS_PAYOR_CD",
PRV_FILE."PRV_NM",
PINS_FILE."PINS_INS_CD", PINS_FILE."PINS_CNTRCT_NUM"
FROM
"DB2"."TRN_FILE" TRN_FILE INNER JOIN "DB2"."CLT_FILE" CLT_FILE ON
TRN_FILE."TRN_CLT_CD" = CLT_FILE."CLT_CLT_CD" INNER JOIN "DB2"."PAT_FILE" PAT_FILE ON
TRN_FILE."TRN_PAT_ACCT" = PAT_FILE."PAT_ACCT" LEFT JOIN "DB2"."EOB_FILE" EOB_FILE ON
TRN_FILE."TRN_NUM" = EOB_FILE."EOB_CHG_NUM" LEFT JOIN "DB2"."INS_FILE" INS_FILE ON
TRN_FILE."TRN_INS_CD" = INS_FILE."INS_CD" LEFT JOIN "DB2"."PRV_FILE" PRV_FILE ON
TRN_FILE."TRN_PRV_CD" = PRV_FILE."PRV_CD" LEFT JOIN "DB2"."PALW_FILE" PALW_FILE ON
TRN_FILE."TRN_SVC_CD" = PALW_FILE."PALW_SVC_CD" AND
TRN_FILE."TRN_INS_CD" = PALW_FILE."PALW_INS_CD" LEFT JOIN "DB2"."PINS_FILE" PINS_FILE ON
PAT_FILE."PAT_ACCT" = PINS_FILE."PINS_PAT_ACCT"
WHERE
(TRN_FILE."TRN_INS_CD" < 'PP1' OR
TRN_FILE."TRN_INS_CD" > 'PPP') AND
TRN_FILE."TRN_SVC_TYP_CD" = 'CHG' AND
(TRN_FILE."TRN_FAC_CD" LIKE '2M' OR
TRN_FILE."TRN_FAC_CD" LIKE 'L1' OR
TRN_FILE."TRN_FAC_CD" LIKE '24' OR
TRN_FILE."TRN_FAC_CD" LIKE '03')
ORDER BY
TRN_FILE."TRN_PAT_ACCT" ASC,
TRN_FILE."TRN_ENC_NUM" ASC,
TRN_FILE."TRN_NUM" ASC

After this information is compiled, it is exported to an access database, then exported into crystal as a separate table. The final report has the exported report, along with 2 other tables, to pull a summary of the information.

Is there a way I can avoid the step of having to export the data into access?

 

[Turkbear]

Hi,
One way is to use a subreport that is linked to the Main report by the needed fields..
Or, and perhaps this is a better way, create a Command Object with that first query and use that to link to the other tables..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[metalteck]

How do you create a command object?

 

[Turkbear]

Hi,
When you select a datasource, if it supports Command Objects, it will show that in addition to the Tables and views..


If is does have that ability, just paste your query into the text editor section..It appears that no parameters are involved, so you need not add any.

This then becomes another set of records to use in your report ( like you would any table or view)..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[metalteck]

I'm fairly new to crystal. Can you explain further...step by step?

 

[Turkbear]

Hi,
Try the help file, but specifics depend on your database and your level of Sql coding knowledge..

What I posted is pretty much it:
Choose Command Object from the available stuff in your data connection.
Paste the code you posted into the area that says
Enter SQL Query in the box below..
Click on OK

If the code is valid, the Edit box wil close and you will have a Command Object.

A report can use this just like a table...

Here is an excerpt from the XI help file

If the database you are using supports a query language such as SQL, you can write your own command which will be represented in Crystal Reports as a Table object. This allows database users complete control of the data processing that gets pushed down to the database server. A user who has experience with databases and the SQL language can write a highly optimized command that can considerably reduce the size of the set of data returned from the server.

You can write your own command by using the Add Command node in the Database Expert to create a virtual table which will represent the results of processing the command. Once the command has been created, you can store it in the BusinessObjects Enterprise Repository so it can be shared between many users.

Note:    Some of the Crystal Reports native database drivers do not support the Add Command feature:

DB2 Server 
Sybase Server 
Informix Online Server 
When you use a virtual table that was created as a Command, or when you use a Command from the BusinessObjects Enterprise Repository, Crystal Reports does not alter the syntax of the SQL submitted to the server (that is, it does not automatically add quoting or escape characters). This behavior also applies to parameters used in Commands. Therefore, you must add the quoting and escape characters that are necessary for your database driver.

Hope it helps..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[metalteck]

I'm trying to view the Add Command option, but it does not appear anywhere. I found a site that says I should have a repositry folder as well, but I don't. Is there anything I can do with that?

Also, according to the excerpt, if I did have the Add Command option, it would not work because I'm trying to pull information from a DB2 Server.

Any other options you think I should try?

 

[synapsevampire]

You might switch to ODBC if you need to use an Add Command.

The help doesn't say you can't use it with DB2, it says you cannot with the Crystal supplied native connection.

-k