Variable SQL Where clause

[CGantz]

Hello,

Is it possible to code my sql statement so that depending on a selected parameter. Here's my problem: given a selected business I use different fields to extract data from my system. How do I code this?

Thanks,

Cole

 

[rosemaryl]

Dear CGantz,

If it were me, I would write a stored procedure.

Crystal will automatically see and create your parameter. You can edit the parameter to add default values that you want your users to choose from.

That way you can return the result set with the field names always the same even though the data is different. You could create formulas for the column headings so that if Parameter is X heading is Y.

This allows you to have one report to meet many needs.

The other option is to place the fields that pertain to a particular parameter in it's own section. So, if you have 5 parameters you would have 5 details and then you would use suppression formulas on each section based on for which parameter that section would show:

If {?Parameter} <> a

and so on for each section.

So in essence your SQL statement will pull all the fields you possibly need. If you need to also limit data based on the parameter that is easily done in the select statement:

If {?Parameter} = a then {Table.DateField} in xrange to yrange
else if...


I personally like the Stored Procedure from a performance standpoint but either option will work.

Hope that helps, if you need more info, let me know. Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CGantz]

Thanks Rosemary,

I'm not familar with stored procedures. How do I go about creating one?

Cole

 

[rosemaryl]

Dear CGantz,

There is some excellent help in the SQL Books On Line (BOL) on creating stored procedures. Also, if serious about learning, I would suggest you get a book on the subject.

Basically you check to see if the procedure exists, if so drop and then create procedure providing name.

Supply any parameters,
declare any variables and set,
Perform select(s) based on criteria if desired,
return data.

In Crystal make sure that in the Data Explorer Options you have Stored Procedure selected. Navigate to your database and stored procedure an add as you would any other table.

When you select Close in the explorer if there are any parameters in the SP - you will be immediately prompted to provide a value. Supply anything the first time.

Modify the parameter (Inser/Field Object/Parameters) to include your default values.

Refresh report, choose value from now available defaults and you are in business.

You can do anything allowed in a stored procedure, even update a table as long as the end result of the sp is that it returns a result set.

Here is an example:

/*First check to see if procedure exists if so drop */

if exists (select * from sysobjects where id = object_id('dbo.sp_MyProcedure')
drop procedure dbo.sp_MyProcedure
GO

CREATE PROCEDURE dbo.sp_MyProcedure /*Create the Procedure*/

@MyParam varchar (20) /* parameter to be populated when run - When testing provide a value when you execute the stored procedure, example sp_exec dbo.sp_MyProcedure 'String1' */

As

If @Myparam = &quot;String1&quot;
Begin
Select Field1, Field2, Field3 /* Last field selected end with no comma */
From MyTable Where MyTable.Blah = &quot;Blah&quot;
END
Else

If @Myparam = &quot;String2&quot;
Begin
Select Field1, Field2, Field3
From MyTable Where MyTable.Blah = &quot;Bleh&quot;
End
Else

If @Myparam = &quot;String3&quot;
Begin
Select Field1, Field2, Field3
From MyTable Where MyTable.Blah = &quot;Bluh&quot;
End
Else

If @Myparam = &quot;String4&quot;
Begin
Select Field1, Field2, Field3
From MyTable Where MyTable.Blah = &quot;Blih&quot;
End
Else

If @Myparam = &quot;String5&quot;
Begin
Select Field1, Field2, Field3
From MyTable Where MyTable.Blah = &quot;Bloh&quot;
End

go
grant execute on Database.dbo.sp_MyProcedure to Public
go

/*Code Ends - Don't forget to comment out grant to public - after first run*/

Hope that was helpful, let me know if you need more assistance.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CGantz]

Hi Rosemary,

Thanks for the information. I'm looking Books Online over right now. My report combines data from multiple databases. Is it better to create a Visual Basic program to extract the data to send to Crystal? From what I'm gathering the stored procedure in SQL Server only can reference the table it is stored in.

Thanks for your help,

Cole

 

[CGantz]

I found the answer to my problem. I can create the formula for my record selection by going to the &quot;Record&quot; menu selecting &quot;Edit Selection Formula&quot; then selecting &quot;Record&quot;. This bring up a formula editor dialog. As long as my parameter is defined I can manually type it in the formula. This is how my formula looks:

if {?Business} = 'unit1' then
{FILE01.TMPFLD1} in [&quot;A1&quot;, &quot;A2&quot;] and
not ({FILE01.TMPFLD3} in [&quot;C3&quot;, &quot;C4&quot;])
else
if {?Business} = 'unit2' then
{FILE01.TMPFLD1} in [&quot;A2&quot;, &quot;A3&quot;] and
{FILE01.TMPFLD5} in [&quot;D2&quot;, &quot;D4&quot;]

It seems to work okay.

Thanks,

Cole