Solved

Add Command - SQL query as Data Source for Report

Posted on 2008-10-06
5
662 Views
Last Modified: 2010-04-21
Hello,

I'm using the Add Command for an ODBC data source and building the query as follows:

select sum(Invoice_Detail.Amount) As Revenue
From Invoice_Detail
Inner Join Invoice_Header On Invoice_Detail.Document=Invoice_Header.Document
where Invoice_Detail.Job = 'REVENUE'

What I'd like to do is make the query dynamic and use a formula result that holds a shared variable from the main report instead of the Job value hard-coded above ('REVENUE').

Also, once I add this field onto my report, I can't figure out how to edit the query behind this field ??

Thanks,
JMO9966
0
Comment
Question by:JMO9966
  • 2
  • 2
5 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 22655083
2 ways, that I can think of.

Add the command without using the where clause.
When you add the command you can add a parameter to it.,
Then add the where clause using the parameter.

The other way would be to just add it as a command without the where clause then add a report parameter and use the select expertwith the parameter to filter the data.

mlmcc
0
 

Author Comment

by:JMO9966
ID: 22655156
Thanks again,

I tried adding a parameter to the sql query itself but it error'd when I hit OK.  I set it as a String type.

"To Few parameters, expected 1"

0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 22655437
After you add the parameter you have to add it to the query.

How are you adding it?

mlmcc
0
 
LVL 19

Accepted Solution

by:
GJParker earned 250 total points
ID: 22661032
I wouldn't use a command at all, create your subreport using tables and a normal paramter and then CR to sum the invoice_detail.amount

HTH
0
 

Author Closing Comment

by:JMO9966
ID: 31503646
Thanks guys!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now