Displaying results horizontally

Posted on 2005-05-16
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


Question by:polynominal
    LVL 9

    Expert Comment

    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.

    LVL 10

    Expert Comment

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

    LVL 100

    Expert Comment

    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


    Author Comment


    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

    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


    LVL 19

    Expert Comment

    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 ?



    Author Comment

    My SP is below

    @startdate smalldatetime,
    @enddate smalldatetime
    SELECT A.contractid,A.OrdersReceived, B.orderscancelled
    (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
    (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


    LVL 5

    Expert Comment

    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.
    LVL 8

    Accepted Solution

    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


    Expert Comment

    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

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    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. …
    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now