?
Solved

Displaying results horizontally

Posted on 2005-05-16
9
Medium Priority
?
224 Views
Last Modified: 2015-11-23
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
Comment
Question by:polynominal
9 Comments
 
LVL 9

Expert Comment

by:cyberdevil67
ID: 14009770
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
 
LVL 10

Expert Comment

by:Spykair
ID: 14009888
Create 2 subreports. One for Contract and one for Count. Place each subreport in it's own details section.

Hth,
Spykair
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 14009918
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:polynominal
ID: 14010116
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
 
LVL 19

Expert Comment

by:GJParker
ID: 14010344
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
 

Author Comment

by:polynominal
ID: 14011127
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
 
LVL 5

Expert Comment

by:SaxonWica
ID: 14015271
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
 
LVL 8

Accepted Solution

by:
PATKIRSCH earned 750 total points
ID: 14016441
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
 

Expert Comment

by:jrchn55
ID: 41299153
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question