Sorting on report

[strom99]

Is it possible to have the viewer select how the report is sorted at the prompt screen?

Thanks

 

[bburnell]

I'm not sure what you mean. Do you mean the CR viewer form in VB? Please give more details so I can (try) and help!
Brett Please visit my websites!

http://smsforce.virtualave.net

http://www.b2ksolutions.net

http://www.goodguyz.com

 

[strom99]

I am using version 8 and would like to somehow try and let the user sort the data when they open the report from the viewer [CR8 VB]. Could this be done by a parameter?? Hope this is enough info.

Thanks for the help

 

[bburnell]

I got this out of Crystal Reports help...


To define sort order using parameter fields
To set the sort order using parameter fields, you need to first create a formula that includes a parameter field and then sort based on that formula. For example, assume that you have a customer list report based on the Customer table. For each customer, you show the Customer Name, City, Region, Country, and Phone Number. You want to be able to sort the report by Country, by Region, or by City, depending on your needs at the time.

Create a parameter field and call it SortField.
In the Prompting Text edit box, enter a prompt similar to this:
Type R to sort by Region or C to sort by City; otherwise, data will be sorted by Country.

Select String from the Value Type drop-down list.
You may want to limit the number of characters the user can type to one. To do this, select the Length Limit check box, and type the numeral 1 into the Max Length text box. Now the parameter field will only accept single-character values. The field will accept "C" as a value, but not "City."
Create a formula similar to this and call it Sort:
If {?SortField} = "C" Then
{customer.CITY}
Else
If {?SortField} = "R" Then
{customer.REGION}
Else
{customer.COUNTRY}

This formula prompts for a value for the parameter field {?SortField}. If you enter "C", the formula will sort by the City field. If you enter "R" it will sort by the Region field. If you enter anything else, or do not enter anything at all, the formula will sort by the Country field.

For more information see If statements.

Place the formula in the Report Header section of the report and select Suppress in the Section Expert so that it does not print.
Click Sort Records.
Choose your formula.
Now when you run the report, the program will prompt you for a sort field, the formula will return a value based on your selection, and the sort facility will use that value as your sort field.


I hope that is what you need!
Brett Please visit my websites!

http://smsforce.virtualave.net

http://www.b2ksolutions.net

http://www.goodguyz.com

 

[MalcolmW]

You could also get it sorted by your database, which would be faster than sorting it at the client with SCR.
CREATE PROCEDURE spTestSQLServer
@SortFieldName varchar(50)
AS
EXEC ('SELECT * FROM Table ORDER BY ' + @SortFieldName)
Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.

 

[Ramjet]

Malcom,

Your example doesn't work for me?

SQL Server7
SCR8

In Crystal I get an ODBC Error saying incorrect Syntax Near
BY. Also I though you didn't use ' in stored procedures for
select statements?
This is exactly what I needed so all I need is to figure out
why SCR doesn't like my(your) stored procedure

Thanks

 

[MalcolmW]

can you execute it from Query Analyzer?
just type in
spTestSQLServer 'SomeFieldName'
and execute the query.

I checked it again, and it works for me with SQL Server v7 and SCR v7.
The single quotes in the sp are defining a string that will be executed, that's all. Malcolm

 

[kenhamady]

if you are launching the report from a VB App you can simply tell the report what sort fields to use in your VB Code. The syntax depends on which of the VB techniques you are using. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

Ken,
An excellent alternative - it would do the sorting at the client though, not on the server, so there would be a performance hit. Malcolm

 

[Ramjet]

Perhaps I am misunderstanding what you are trying to say.
So I have the store procedure below and it works IF the two variables I have are hardcoded in the SP. But How do I get those variables from SCR to the SP... More importantly how do I get them from a web form to SCR to the SP???????????


So Reading your post I thought this would work

CREATE PROCEDURE PolicyRpt AS
@StartDate varchar(8)
@EndDate varchar(8)

SELECT PolicyID as PolicyID, AgencyID as AgencyID, FROM PLMASTER WHERE PostDate BETWEEN @StartDate AND @EndDate ORDER BY LocZipCode


When I hardcode the variables and execute from a sql window it works.
So where @StartDate & @ EndDate get populated by SCR is where I need help??

Thanks For your help I have learned much from this forum

 

[MalcolmW]

You just need to make those variables into parameters. That is a subtle but key difference. Malcolm