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
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
Create 2 subreports. One for Contract and one for Count. Place each subreport in it's own details section.
Hth,
Spykair
Hth,
Spykair
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
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
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
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
Does the SP only return the 2 fields above ?
In your example how do you define what starts a new row ?
Gary
ASKER
My SP is below
@startdate smalldatetime,
@enddate smalldatetime
AS
SELECT A.contractid,A.OrdersRecei ved, 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.jo bid 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.jobi d 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
@startdate smalldatetime,
@enddate smalldatetime
AS
SELECT A.contractid,A.OrdersRecei
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.jo
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.jobi
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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!