How to write follwoing query ? 1

[shadabrauf]

I need to write following query in a report. How to write
This report has only one column and I need to show data based on following query.

select sum(balanceYtd) from temp where temp.accountno >='1000' and temp.accountNo <='2000';


Can some one let me know How do i write this query in crystal report.

shadab

 

[mrstnturner]

just parameter that actual field and it will give you only the information you tell it to pull!

 

[shadabrauf]

My requirement is I need to hardcode the parameters.

Could you please explain this little bit in detail , I am new in crystal report.

Thanks.

 

[Turkbear]

Hi,
Use a Record selection formula of
temp.accountno >='1000' and temp.accountNo <='2000';





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[shadabrauf]

Thanks

I did the same, After that my SQL query is showing following

SELECT "GLSUMMARY"."ACCOUNTNO", (sum(BalanceYtd))
FROM "RWC"."GLSUMMARY" "GLSUMMARY"
WHERE ("GLSUMMARY"."ACCOUNTNO">='1000' AND "GLSUMMARY"."ACCOUNTNO"<='2000')

I am getting error "Not a Sigle Group Function"

Please let me know what to do.

 

[Turkbear]

Hi,
In order to sum, you need to Group the data..
If More than 1 ACCOUNTNO:
Create a Group in Crystal on "GLSUMMARY"."ACCOUNTNO" and place the BalanceYtd in the detail of that group..Insert a Summary ( SUM) of that field in either the GH or GF -- you can 'hide' the actual details and just show the Summary)
If Just 1 :
then place the BalanceYtd in the details and create a GrandTotal using the SUM function..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[shadabrauf]

Is there any way to write following query

SELECT (sum(BalanceYtd))
FROM "RWC"."GLSUMMARY" "GLSUMMARY"
WHERE ("GLSUMMARY"."ACCOUNTNO">='1000' AND "GLSUMMARY"."ACCOUNTNO"<='2000')

Your help is appreciated.

Thanks

 

[shadabrauf]

Please let me know if possible, I am waiting for your suggestion

Thanks

 

[Turkbear]

Hi,
Did you try my idea?








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[shadabrauf]

I am new in crystal report, It is taking time to try that idea.

I am looking in some documents to apply your idea, I am getting error.

Please suggest if I can do

SELECT (sum(BalanceYtd))
FROM "RWC"."GLSUMMARY" "GLSUMMARY"
WHERE ("GLSUMMARY"."ACCOUNTNO">='1000' AND "GLSUMMARY"."ACCOUNTNO"<='2000')


In the mean time I am trying your idea.

thanks

 

[Turkbear]

Hi,
What database?
Yor last posting should work ( does in Oracle)

For your original one try:

SELECT "GLSUMMARY"."ACCOUNTNO", (sum(BalanceYtd))
 FROM   "RWC"."GLSUMMARY" "GLSUMMARY"
 WHERE  ("GLSUMMARY"."ACCOUNTNO">='1000' AND "GLSUMMARY"."ACCOUNTNO"<='2000')
[COLOR=red]
GROUP BY "GLSUMMARY"."ACCOUNTNO"
[/color]

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

What version of CR are you using? Where are you creating this query? In "Add Command"? Or?

-LB

 

[shadabrauf]

My database is Oracle.
And i am using Crystal Report 10.

Could u please let me know steps how to add the "GROUP BY "GLSUMMARY"."ACCOUNTNO" in crystal report.

I am trying to use "Group Selection Formula". but hetting error.

It is urgent for me please suggest .

shadab

 

[Turkbear]

Hi,
You really need to look into Crystal's help...

Do this:

In Crystal connect to your datasource..

In the report, insert a group on "GLSUMMARY"."ACCOUNTNO"
In the details for that group place the BalanceYtd field.

Now, right-click the field and select Insert..Summary..and use the SUM choice..This will place the sum in the Group footer..








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

If you want to use a SQL Query as your datasource, use "Add Command" in the database expert and then enter something like:

SELECT "GLSUMMARY"."ACCOUNTNO",
(select(sum(A."BalanceYtd")
from "GLSUMMARY" A
where A."ACCOUNTNO" = "GLSUMMARY"."ACCOUNTNO")
FROM "RWC"."GLSUMMARY" "GLSUMMARY"
WHERE "GLSUMMARY"."ACCOUNTNO">='1000' AND "GLSUMMARY"."ACCOUNTNO"<='2000'

Or, instead, you could do as previously suggested, and add a record selection formula:

{GLSUMMARY.ACCOUNTNO} IN '1000' TO '2000'

Then add {GLSUMMARY.BalanceYtd} to your detail section and insert a summary on it.

Both solutions assume you are trying to sum "BalanceYtd" within specific account numbers. If instead you want to summarize across these account numbers, please explain and provide some sample data.

-LB

 

[shadabrauf]

Hi

It is really great to know Add Command...It worked. Thanks alot and it is wonderfull, now I have full command over it.

I have another issue I need to have a report parameter as "Date" and that parameter I want to define on report level and want to pass the same parameter in each query (means in each command). Please suggest how to do this

Thanks

 

[lbass]

Please start on a new thread on this topic, where you indicate that you are using a command in CR 10 and want to incorporate a parameter. I'm unsure how you would do this in CR 10, as I've never used it. In CR 11, you can create a parameter in the same screen as the command and build it in, but I'm not sure whether that capacity exists in CR 10, or what the workaround might be if it doesn't. Sorry.

-LB