CODEWITHSUNDEEP
sqlcrystal-reportsoracle10g
Amit Raj
Amit Raj

Reputation: 1378

Implement a SQL query in Crystal reports

I want to generate a report in Crystal Reports and I am very new to it. I am unaware of how to manipulate the Crystal Report. I made a SQL query which gives me all the required output from the Oracle(10g) database. But I need to convert it to be used inside Crystal Reports.

This is the SQL query which gives me required output:

SELECT 
    ins.ins_name,ins.ins,crdi.ct, crdi.bn,
    sum(DECODE(cardh.crd_st, 'PO', 1, 0)) POCount,
    sum(DECODE(cardh.crd_st, 'CN', 1, 0)) CNCount
FROM
    crdh, crdi, ins
WHERE 
    crdh.crd_st IN ('PO','CN') 
    and crdi.bn in (select unique bn from crdh)
    and crdh.bn = crdi.bn 
    and crdi.ins = ins.ins
GROUP BY 
    ins.ins, crdi.bn, ins.ins_name, crdi.ct
ORDER BY 
    ins.ins, crdi.bn;

When I implemented the above query I got the following error:

a) When I put the above query in Database|Show SQL Query..., the following part of the query is removed:

sum(DECODE(cardh.crd_st, 'PO', 1, 0)) POCount,
sum(DECODE(cardh.crd_st, 'CN', 1, 0)) CNCount
GROUP BY ins.ins, crdi.bn, ins.ins_name,crdi.ct

b) When I added a group for ins.ins, Crystal reports adds a lots of spaces in the report.

c) How can I print the value of POCount and CNCount in the crystal report?

I am also adding the screenshot of the output for better understanding.

Output of the above SQL query

There can be multiple BN for one INS and for one BN there are multiple CNs and POs. Like INS 3 has two BN ('123456','789012') and there are 3 POs and 0 CN in BN '123456', but there is only one CN in BN '789012'. I hope this is helpful to replier.

Please help me to get a report same as the output of the above mentioned query. Thanks in advance.

I got the following result :

enter image description here

Upvotes: 1

Views: 2158

Answers (1)

Siva
Siva

Reputation: 9101

Dont implement sum in query instead implement it in crystal. so change the query like this.

SELECT ins.ins_name,ins.ins,crdi.ct, crdi.bn,
      cardh.crd_st,cardh.crd_st
FROM crdh, crdi, ins

Add above query in crystal report command when you make a connection to the report.

Now for your report to display in the required format.

  1. Create a formula @PO

    if cardh.crd_st= 'PO' then 1 else 0

  2. Create formula for CN

    if cardh.crd_st='CN' then 1 else 0

  3. Place above formula in detail

  4. Create a group by ins.ins_name

  5. Place all your columns in group footer and at the same time take sum for the formulas @po and CN
  6. As you are using where condition in query to get that condition in CR. Implement your where clause in Select Expert ---> Record Selection Formula, If you are not comfortable then try implement the where clause in query itself.

Let me know how it goes.

Upvotes: 3

Related Questions