Running SQL Query in report

[WannaFly]

I have some SQL queries that basically sum up numbers from a number of different tables - I need to display these numbers on my report but can find no way to use pure SQL in the report. Is it even possible? Should i run it in my VB app and pass the number as a parameter?
Thanks for any help.
CR9 - MS SQL database

 

[synapsevampire]

CR9 has the Add Command function which allows for pasting in SQL, or you can pass the recordset to Crystal.

Your posts states that you create numbers, yet the latter question suggests passing a single number, which is it?

You might also consider creating Views to report against, that way you have a more centralized location, easing maintenance and reusability.

-k

 

[WannaFly]

synapsevampire,
Thanks - I had found the add command right as you posted this. That would work but apparently you cant have your crystal reports parameters in commands.

The numbers I am using are sums of dollars from receipts from different tables. - I just know i can query for it in VB and pass it as a parameter if i have to.

I have not used views at all - perhaps i will look into that.

Thanks

 

[WannaFly]

Here is a sample of my SQL:

select amount_paid from tickets
where receipt_number in 
(select receipt_number from receipts where register_number=@register
and cashier=@cashier
and receipt_date = @today);

I run the same basic query on multiple tables - the idea being that i need to find the amoutns paid on that register by that cashier and the only thing that links it is the receipt number.
Any ideas or suggestions would be great, i have a few reports to make with this stuff and it it making my head spin

 

[bdreed35]

You can use parameters in SQL Commands. The trick is you have to create them and add them from within the Add Command to Report dialog box.

Paste your SQL into the Add Command dialog box.
Create your parameters on the right side of the dialog box by clicking the Create button. The standard parameter dialog box will appear.
After you have created your 2 parameters, place your cursor in your SQL at the point that you want to insert the parameter and then go and double click the parameter name. It will add it to the SQL.

~Brian

 

[synapsevampire]

Brian is correct.

Also you could use derived tables instead of a nested query, which is the more modern approach:

select amount_paid from tickets,
(select receipt_number from receipts where register_number=@register
and cashier=@cashier
and receipt_date = @today) Recepits
where amount_paid.receipt_number = Receipts.receipt_number

Another option would be a Stored Procedure.

This is all dependent upon the type of database, which you didn't share.

-k

 

[WannaFly]

I cant seem to find howto add parameters from the SQL Command dialog - I do not have a "creat" button.

I'll look into the derived tables, but that still requires me to use the SQL Command with parameters.

I did state my db, it was at the very bottom, but nevertheless i'm using MS SQL server 2000.

Thanks for your help .

Maybe i will just write a stored procedure that has x amounts of outputs that are all the numbers i need. *shrug*

 

[bdreed35]

This probably should have been asked up front but what version of Crystal Reports are you using?

~Brian

 

[WannaFly]

Crysal Reports 9(CR9) I think at least - its the development interface that comes with Visual Studio .NET 2003.

 

[bdreed35]

I am not familiar with all the functionality that is provided with .NET studio. I will have to digress to someone who does. Sorry.

~Brian

 

[synapsevampire]

Brian: 1st post says CR 9, now they say .NET

Perhaps there's a difference?

-k

 

[bdreed35]

My apologies SV, I overlooked that.

~Brian

 

[WannaFly]

I am pretty sure CR version 9 is what comes with Visual Studio .NET, my version number for the DLL is 9.1.9800.0.... sorry if i confused anyone.

 

[synapsevampire]

No worries, Wannafly, you're probably correct.

But there should be a parameters button in the SQL area if memory serves.

-k

 

[WannaFly]

This is what my SQL Command area looks like

Picture

 

[WannaFly]

Well, unfortunately the easiest way that i know howto do this is to run my queries and just send the numbers as parameters. I cant figure out howto use the parameters in an SQL command.