• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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
0
polynominal
Asked:
polynominal
1 Solution
 
cyberdevil67Commented:
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!
0
 
SpykairCommented:
Create 2 subreports. One for Contract and one for Count. Place each subreport in it's own details section.

Hth,
Spykair
0
 
mlmccCommented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
polynominalAuthor Commented:
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




0
 
GJParkerCommented:
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

0
 
polynominalAuthor Commented:
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

 
0
 
SaxonWicaCommented:
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.
0
 
PATKIRSCHCommented:
actually, what you want to do is create an if/then formula for each column (alternately, you could use a cross tab) that will look like:

1st column: if {contract} = "TV AAP" then {count} else 0
2nd column : if {contract} = "TV Cable" then {count} else 0

and so on... then place these across your columns in the detail section

If you want to only see some group level totals, then also insert subtotals/grandtotals for each formuls and hide the detail section if you wish.

hope that helps :) Pat K

0
 
jrchn55Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now