Link to home
Start Free TrialLog in
Avatar of polynominal
polynominal

asked on

Displaying results horizontally

I have a stored proedure which gives me the following outcome

Contract       Count
TV AAP         2000
TV Cable       1000
TV Civils        3000

I want the results to run horizontally in the details section i.e.

TV AAP       TV Cable     TV Civils
2000           1000           3000  

How would I do that

Thanks

Poly
Avatar of cyberdevil67
cyberdevil67

Hi polynominal,

 Can't, details run in a vertical way. If there is a few records only you could setup this info in the page header, by dragging a text object onto the report and then dragging the database field underneath the text object.

Cheers!
Create 2 subreports. One for Contract and one for Count. Place each subreport in it's own details section.

Hth,
Spykair
Avatar of Mike McCracken
Will you always get them in the same order or will you only have the 3 records?

If so you could use a multiple column report

Open the report
Right Click in the left margin in the detail section
Select --> FORMAT SECTION
Ensure the DETAIL section is selected
Click the check box to FORMAT WITH MULTIPLE COLUMNS
A new TAB appears - Select it
Set up the columns and choose ACROSS THEN DOWN

mlmcc
Avatar of polynominal

ASKER

Hi

I am going to be adding more fields to this, so I will have 6 column heading and about 10 fields below it, there will only ever be 3 records on each line. I have tried what mlmcc sugeests but to no vail

i.e
TV AAP       TV Cable     TV Civils        In      Out    Static
2000           1000           3000  
2050            250            5000
                                                        200     50       40

and so forth  

Thanks for the answers so far

Poly




My first thoughts are that with the layout of your data this is not possible.

Does the SP only return the 2 fields above ?

In your example how do you define what starts a new row ?

Gary

My SP is below

@startdate smalldatetime,
@enddate smalldatetime
AS
SELECT A.contractid,A.OrdersReceived, B.orderscancelled
FROM
(SELECT     job.contractid, COUNT(job.jobid) OrdersReceived
FROM         job INNER JOIN
                      contract ON job.contractid = contract.contractid LEFT OUTER JOIN
                      vw_job_originaljobvalue ON job.jobid = vw_job_originaljobvalue.jobid LEFT OUTER JOIN
                      vw_job_jobscancelled ON job.jobid = vw_job_jobscancelled.jobid
WHERE     (job.contractid IN (5, 6, 7, 12, 13, 14)) AND  job.validated BETWEEN @startdate AND @enddate
                      AND (vw_job_jobscancelled.jobid IS NULL)
GROUP BY job.contractid) A
LEFT OUTER JOIN
(SELECT    job.contractid, COUNT(job.jobid) AS orderscancelled
FROM         job INNER JOIN
                      cancelreason ON job.jobid = cancelreason.jobid
WHERE     (job.contractid IN (5, 6, 7, 12, 13, 14)) AND (job.statusid = 4) AND cancelreason.whencancelled BETWEEN @startdate and @enddate
GROUP BY job.contractid) B
On A.contractid = B.contractid

At the moment this gives me
 
              OrdersReceived     OrdersCancelled
AAP               50                            40
Cable             25                            50
Civils              30                            35

I will be adding more subqueries to the SP when I figure out how to display it

I would like it to be displayed like
                                    AAP           Cable       Civils          Civils   Cable In    Asset Ass
Orders Received              50               25           30
Orders Cancelled                                                               40         50            35

The other queries will follow under the 6 headings below.

How can I eiher display it horizontally or vertically

Poly

 
Will it returns only 3 rows? If it is, you can group it by job.contractid. This is not the best way to do this, but it will show results as you wanted. Put the details you want to display at the group footer, using formulas. But since there's no unique ID except job.contractid, you have to summarize the fields.
ASKER CERTIFIED SOLUTION
Avatar of PATKIRSCH
PATKIRSCH
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello Patkrish

Is there a tutorial that will guide you to creating an if/then formula to the tutorial. I am new to the crystal reports interface and would really appreciate it if you can give some form of direction.

Thank You again

Jose Caballero