Report Design: two commands in one report

Print Page | Close Window

two commands in one report

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12091
Printed Date: 05 May 2024 at 4:00am

Topic: two commands in one report

Posted By: rozpalacz
Subject: two commands in one report
Date Posted: 17 Jan 2011 at 10:21pm

I have I command_1 tht returns data and simultaneously inserting data to global temporary table (GTT).
I need to execute command_1 first (get data, and put data to GTT) and next execute command_2 that will return data from GTT. I have to do this on one page.

Can I anyway set priority which command should execute first, wait for finish, and execute second command?

Replies:

Posted By: lockwelle
Date Posted: 18 Jan 2011 at 3:34am

not that I know of. can't you do this via a stored proc, or just select the data that you need.

It is my belief that the command was not meant to be used to gather the data for a report, but rather to allow for dynamic parameter retrieval. With that said, I highly doubt that what you want is available. I don't know of any options to allow that either.

Posted By: rozpalacz
Date Posted: 18 Jan 2011 at 9:08pm

Works fine if I have two subraports. One with command1 and second with command2. First subraport gets data and inser new data in GTT via procedure with autonomical transaction. Second subraport takes data from GTT. But how get the same on one raport?

Posted By: DBlank
Date Posted: 19 Jan 2011 at 4:14am

Why not change your command to use a second query in itself?

Posted By: rozpalacz
Date Posted: 19 Jan 2011 at 7:52pm

use a second query in itself

How to do this?
First query (command1) must execute first. Second query (command 2) must execute last.

I have a report for same companies. First command (procedure) return sth like this for two companies:

|  NUMBER  |  COMPANY  |  DAY  |  MONTH  |  YEAR  |  COMPANY  |  DAY  |  MONTH  |  YEAR  |

and second command return exactly this:

|  NUMBER  |  COMPANY  |  DAY  |  MONTH  |  YEAR  |

Command 1 looks like:

select 
  a.number,
  a.company, a.day, a.month, a.year,
  b.company, b.day, b.month, b.year,
from table( REP_PCK.rep(sysdate, 'IBM') ) a
join table( REP_PCK.rep(sysdate, 'SONY') ) b
on (a.number = b.number)

Command 2 looks like:

select number, 'SUM FOR ALL' company, day, month, year
from rap_gtt

I can not do this like this:

select 
  a.number,
  a.company, a.day, a.month, a.year,
  b.company, b.day, b.month, b.year,
  c.company, c.day, c.month, c.year,
from table( REP_PCK.rep(sysdate, 'IBM') ) a
join table( REP_PCK.rep(sysdate, 'SONY') ) b on (a.number = b.number)
join rap_gtt c on (a.number = c.number)

cause I will get no data or data that was in rap_gtt before I execute query.
Ok, I can use two queries but how to set that command 1 should execute first, command 2 has to wait for command 1 and after this just execute.

Posted By: DBlank
Date Posted: 20 Jan 2011 at 4:04am

Rather than what you are currently doing can you tell me what the data is and what you want to get to. I am not sure why you are splitting the two companies into seperate columns rather than just selecting the data or doing a union.

Posted By: rozpalacz
Date Posted: 20 Jan 2011 at 5:13am

I use second command to get summary of all companies.
Are You using Oracle? I can send You an example what I want to get, why and how - PL\SQL procedure, rpt file and example tables.

I just want to know, how execute command_1 (query) that will return data for each company, wait for finish, and next execute command_2 (query) to get summary of all companies. Thats all.

Posted By: DBlank
Date Posted: 20 Jan 2011 at 5:39am

Sorry, can't accept files.

Why do you need to get the summaries using a command?

That is what crystal does well so use crystal to do the summarizing for you.

Posted By: hilfy
Date Posted: 20 Jan 2011 at 9:25am

Make a single command that is an anonymous PL/SQL block that will run the first query and then the second query. That way you know that the first part will always run first.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Print Page | Close Window