By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 738 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Query in report does not pull all records.

P: 46
Okay. . . . I have a query from a view that should pull up multiple records. When I run it in sql server, it pulls up all records. But when I create the report for it, it only pulls up the first record of each described category of the query. For example
The query is:

select '200701','200712',eid,name,conftype,rate, rotation,confattendance, confoffered,sum(finalvalue)FinalValue from ViewConfAttnRate where period between '200701' and '200712' group by eid,name,conftype,rate, rotation,confattendance, confoffered order by name
the following gets returned in sql server:
eid name conftype rate rotation confattended offered finalvalue
9503 deleted for privacy 4 0.3 1 3 12 0.833333333
9503 deleted for privacy 41 0.3 1 4 10 1.333333333
9503 deleted for privacy 4 0.3 2 1 13 0.256410256
9503 deleted for privacy 4 0.3 2 7 12 1.944444444
9503 deleted for privacy 41 0.3 2 8 16 1.666666667
9503 deleted for privacy 4 0.3 3 4 13 1.025641026
9503 deleted for privacy 41 0.3 3 4 16 0.833333333
9503 deleted for privacy 39 1 5 5 5
1

But when I create my report, using the same query, the following report displays:
9503 deleted for privacy 41 0.3 1 4 10 1.333333333
9503 deleted for privacy 4 0.3 1 3 12 0.833333333
9503 deleted for privacy 39 1 5 5 5 1

How do I get my report to combine the totals of the records in the first set, rather than just returning only one. By the way - the the final value field is the calculation = confattended/(Offered * rate)
Dec 24 '07 #1
Share this Question
Share on Google+
1 Reply


puppydogbuddy
Expert 100+
P: 1,923
Okay. . . . I have a query from a view that should pull up multiple records. When I run it in sql server, it pulls up all records. But when I create the report for it, it only pulls up the first record of each described category of the query. For example
The query is:

select '200701','200712',eid,name,conftype,rate, rotation,confattendance, confoffered,sum(finalvalue)FinalValue from ViewConfAttnRate where period between '200701' and '200712' group by eid,name,conftype,rate, rotation,confattendance, confoffered order by name
the following gets returned in sql server:
eid name conftype rate rotation confattended offered finalvalue
9503 deleted for privacy 4 0.3 1 3 12 0.833333333
9503 deleted for privacy 41 0.3 1 4 10 1.333333333
9503 deleted for privacy 4 0.3 2 1 13 0.256410256
9503 deleted for privacy 4 0.3 2 7 12 1.944444444
9503 deleted for privacy 41 0.3 2 8 16 1.666666667
9503 deleted for privacy 4 0.3 3 4 13 1.025641026
9503 deleted for privacy 41 0.3 3 4 16 0.833333333
9503 deleted for privacy 39 1 5 5 5
1

But when I create my report, using the same query, the following report displays:
9503 deleted for privacy 41 0.3 1 4 10 1.333333333
9503 deleted for privacy 4 0.3 1 3 12 0.833333333
9503 deleted for privacy 39 1 5 5 5 1

How do I get my report to combine the totals of the records in the first set, rather than just returning only one. By the way - the the final value field is the calculation = confattended/(Offered * rate)
Be sure your report has the same grouping levels (no detail secton) as your query.
change calculation in the group footer as follows:
= Sum(confattended/(Offered * rate))
Dec 24 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.