Having Query how can i build a report in Crystal Reports 7 very urgent

sathyguy
04-19-2004, 01:38 AM
Friends

I am having the "Union All" query which i used in the sql.
Now i want to create a report using that query.
I have to pass the user input from vb to crystal reports. the user inputs are specied as a variable in the below query.

fromprojcode,toprojcode,fromdate1,todate1 are all vb variables.

how can i generate the report using crystal report 7?
what i did was,i added 6 tables(which i used in the below query) in the report
after that in the visual basic i put the where condition part as a selection formula but its not working.


************

strqry="SELECT GINMast.GINDate as VCHDate,GINMast.ProjCode as PrjCode,GINDtl.GINNo as VCHNo," & _
"GINDtl.ItemCode as ItemCode,GINDtl.Del_Qty as QTY,GINDtl.Price as Price,GINDtl.Total as Total " & _
"From GINMast,GINDtl " & _
"Where " & _
"(GINMast.Projcode between '" & fromprojcode & "' and '" & toprojcode & "') and " & _
"(GINMast.GINDate between #" & fromdate1 & "# and #" & todate1 & "#) and " & _
"GINMast.GINNo = GINDtl.GINNo " & _
"Union All " & _
"SELECT GRNMast.GRNDate as VCHDate, GRNMast.ProjCode as PrjCode,GRNDtl.GRNNo as VCHNo, GRNDtl.ItemCode as ItemCode, " & _
"GRNDtl.Pur_Qty as QTY,GRNDtl.Price as Price, GRNDtl.Total as Total " & _
"From GRNMast,GRNDtl " & _
"Where " & _
"(GRNMast.Projcode between '" & fromprojcode & "' and '" & toprojcode & "') and " & _
"(GRNMast.GRNDate between #" & fromdate1 & "# and #" & todate1 & "#) and " & _
"GRNMast.GRNNo = GRNDtl.GRNNo " & _
"Union All " & _
"SELECT GINRHead.GINRDate as VCHDate,GINRHead.ProjCode as PrjCode,GINRSplit.GINRNo as VCHNo, GINRSplit.ItemCode as ItemCode, " & _
"GINRSplit.Ret_Qty as QTY, GINRSplit.Price as Price, GINRSplit.Total as Total " & _
"From GINRHead,GINRSplit " & _
"Where " & _
"(GINRHead.Projcode between '" & fromprojcode & "' and '" & toprojcode & "') and " & _
"(GINRHead.GINRDate between #" & fromdate1 & "# and #" & todate1 & "#) and " & _
"GINRHead.GINRNo = GINRSplit.GINRNo"

************

thanks
sathyguy

MKoslof
04-19-2004, 07:31 AM
OK, I see many potential errors with this:

A) at design time, are all these fields and table defined? Does the report have a reference to all these fields and tables?

B) Why are you doing a Union all and not a JOIN?

C) And if you must do a Union, don't specify UNION ALL, do a standard UNION. Why are you doing a UNION ALL call here?

sathyguy
04-19-2004, 07:41 AM
What i did is
1. In the report designer i Added 6 tables after that using the linking expert i gave a link(simple link)
2. Using the Insert database field i inserted the fields which i want.
after that i try to change the query in the "Show sql query" its changing but when i save its giving error.
3. so i tried to get all the records using the query in vb.
4. i checked this query is working.
5. now i dont know how to get all the fields in reports using this query.
6. i dont know how to use the "JOIN" so i used the union all.


what i need is when the user input the projcode and date then i have to get all the relevant records in all the 6 tables.

thanks
sathyguy

MKoslof
04-19-2004, 07:45 AM
after that i try to change the query in the "Show sql query" its changing but when i save its giving error.

OK, if in the query designer of the report you get an error...this should put up a flag in your mind :). Something is WRONG with this query. What is the error message. Try, breaking the query down in the query editor. Add the first table and save it..does it work. Add the second table...does it work, etc. Track down where in your query Crystal is throwing the error.

sathyguy
04-19-2004, 08:20 AM
can you tell my approach is correct or not.

1. the "show sql query" will be enabled only if i insert any database field.
so i inserted all the field which i selected in the sql statement. that is
24 fields, 8 fields from each table(pls check it in the query)
but what i need is only 8 fields.i mean the common fields satisfy the query.
2. after that i inserted the query inside the box of "show sql query" but without the where condition because im sending the values from vb to crystal reports.
3. after entering the query i saved the report then its telling me columns which you selected is not equal.
4. but the same query with the "WHERE" condition i tried in vb recordset.
its working fine.

so what might be wrong in my approach.
now im leaving tommorrow i will again come here to solve this problem.

thanks
sathyguy

MKoslof
04-19-2004, 08:51 AM
OK, you want to create a standard SQL query and save it within Crystal. Yes, you are correct, you don't need a where clause, you can pass that from VB at run-time to restrict the records.

You say you only need 8 fields from the combined tables. This is why you need a JOIN and not a UNION. Basically, how do these tables link and what fields do you want on the report. If you tell me the names of the tables, the fields on the report, and how they relate (table1.ID = table2.ID), etc. I will show how how to write the proper sql statement.

sathyguy
04-20-2004, 12:02 AM
ok
6 tables,i mean 3 pairs, one is master and other one is detail like that 3 pairs.

GINMast
GINDtl
GINRHead
GINRSplit
GRNMast
GRNDtl

The fields i need should have the alaises name like this. So in the report i need in this heading.

GINMast.GINDate as VCHDate
GINMast.ProjCode as PrjCode
GINDtl.GINNo as VCHNo
GINDtl.ItemCode as ItemCode
GINDtl.Del_Qty as QTY
GINDtl.Price as Price
GINDtl.Total as Total

GRNMast.GRNDate as VCHDate
GRNMast.ProjCode as PrjCode
GRNDtl.GRNNo as VCHNo
GRNDtl.ItemCode as ItemCode
GRNDtl.Pur_Qty as QTY
GRNDtl.Price as Price
GRNDtl.Total as Total

GINRHead.GINRDate as VCHDate
GINRHead.ProjCode as PrjCode
GINRSplit.GINRNo as VCHNo
GINRSplit.ItemCode as ItemCode
GINRSplit.Ret_Qty as QTY
GINRSplit.Price as Price
GINRSplit.Total as Total

So i need all the above fields in the report.
why i used the "UNION ALL" is
In first group(master,detail)there will be some 10 records
second group there will be some 5 records.
third some 2 records.
so when you execute the query i need to get all the 10+5+2 so totally 17 records i need.

Also, i need to group the ProjCode.

The relation will be like this

GINMast.GINNo = GINDtl.GINNo
GRNMast.GRNNo = GRNDtl.GRNNo
GINRHead.GINRNo = GINRSplit.GINRNo

The where condition i will put it in VB.
The user will enter the where condition in VB.
The where condition will be in each group

(GINMast.Projcode between '" & fromprojcode & "' and '" & toprojcode & "')
(GINMast.GINDate between #" & fromdate1 & "# and #" & todate1 & "#)

(GRNMast.Projcode between '" & fromprojcode & "' and '" & toprojcode & "')
(GRNMast.GRNDate between #" & fromdate1 & "# and #" & todate1 & "#)

(GINRHead.Projcode between '" & fromprojcode & "' and '" & toprojcode & "')
(GINRHead.GINRDate between #" & fromdate1 & "# and #" & todate1 & "#)

I need a report like this.

PrjCode(this one is group below this) : 101
S.No VCHDate VCHNo ItemCode QTY Price Total
1 2-2-2004 12 1001 5 10 50.00
2 5-5-2004 15 1002 4 12 48.00
total 98.00
PrjCode:102
S.No VCHDate VCHNo ItemCode QTY Price Total
1 2-2-2004 18 1005 5 60 300.00
2 5-5-2004 21 1006 5 12 60.00
total 360.00

Net Amount: 458.00

like the above i need the report.

Thanks
sathyguy

Kashif
04-20-2004, 05:01 AM
so i inserted all the field which i selected in the sql statement. that is
24 fields, 8 fields from each table(pls check it in the query)
but what i need is only 8 fields.i mean the common fields satisfy the query.


Use the Select Statment to filter your 24 Fileds like this

Use Brackets around your SQL Statment like this and give its a Alias Name For Example A, and Select all the Fileds in the Main Query(Above All the Quesried)
Select A.Filed1, A.Filed2
From
(Select f1 as Filed1, F2 as Field2... From Table
Union ALL
Select g1 as Filed1, g2 as Field2... From .....Others
Union ALL
Select h1 as Filed1, h2 as Field2......... From .....other_Table
) A

This is Just as Example. i have not use your Tables and Fileds.
use it your self and make the Query.
I have aslo use the Same and Make the Report using the Union ALL.

Kashif
04-20-2004, 05:26 AM
I have Copy your Query into Text files then Reformat it.
as your Requirment.
Use Alias in the Tabel name instead of Whole table name as i use in this query.

sathyguy
04-20-2004, 06:10 AM
kashif,

Thanks for your query but my problem is not in the query.
because i used my query in vb its working fine.
my problem is how can i use this query in reports.
now what i did is.
i copied your query in the "show sql query"
after that i inserted the database field once i save the file.its giving error.
ok now leave all that pls dont get angry can you tell me what to do next.
let me use your query.
now tell me what to do
what are all the tables i have to add in the visual linking expert.

MKoslof
04-20-2004, 06:23 AM
Why are you still stuck on this union. Why are you using the visual linking expert? If you are not going to take my advice (create a JOIN and build your own SQL statement without using the visual linking expert) how can I help you? :)

sathyguy
04-20-2004, 06:55 AM
i dont know how to write the join query.
i tried without using the visual linking expert.
i designed the query using crysal sql designer.
there it showed all the 8 fields
after that i opened the crystal report designer.
in the data tab i selected the query.
after that i selected the query which is designed ie., projwise.qry
in the nexttab(fields) i saw all the 8 fields.
when i clicked the nexttab(sort) i saw the projcode and the itemcode are missing.
this is the problem i face so, what to do now.
by the way pls dont get angry on me.

MKoslof
04-20-2004, 07:49 AM
First off, look at all the fields you are bringing in. Is the itemCode, Price and Total the same on all tables...it appears you have duplicate data here. If so, you don't need to include all the fields, only one of each. Secondly, if you want to build your relationship on the ItemCode field. If so, you don't need to include all three in your query. Since I assume this field LINKS your tables as a relationship, you only need to include it once, not three times. Tell me which of these fields are the SAME. You only want unique fields on your query.

sathyguy
04-20-2004, 08:08 AM
No No All are different values.
Have a look of this database then you will easily understand.

for GINNO the itemcode,price and value will be different from GINRNo and GRNNO

MKoslof
04-20-2004, 08:17 AM
I have a lot of work today, here is the concept to get you started. You are linking on GRNNO. You can do an "implied" INNER JOIN this way. Now, depending on what data you need to bring in from each table, you might need a RIGHT AND LEFT JOIN. But, as an initial query, try this. Now, you will have to list out all your fields, but follow this model, for three tables:



SELECT A.Field1, A.Field2, A.Field3, B.Field2, B.Field3, C.Field1, C.Field2
FROM A,B,C
WHERE A.GRNNO = B.GRNNO
AND A.GRNNO = C.GRNNO

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum