Basing a report on a Query (CR ver 7) from Crystal SQL designer - limited sorting / grouping fields 
Author Message
 Basing a report on a Query (CR ver 7) from Crystal SQL designer - limited sorting / grouping fields

I have a report that has fairly complex SQL query requirements (I need 2
subqueries with parameters in each). I've found the only way to do this
is to use the Cystal SQL Deigner and have the parameters for the
subqueries specified in there (Crystal Report designer won't allow a
parameter to be used in the edited SQL for a report).

The query is fine in the SQL designer, and I can I base a new report on
it by using the "Query" option when asked to supply a data source.
However, when I get to the point where I want to specify sorting and
grouping, I find that only half of the fields are available for sorting
/ grouping! In fact, of the 4 tables that I'm navigating, only the
fields from ONE table are shown as available for sort/group, even though
fields from the other tables are on the report. If I ignore it in the
wizard, and see the (ungrouped & unsorted) report, the values are there
for the other fields, but it I try to add a group, I can still only see
fields from a single table (the one at the far 'many' end of the query -
which is useless when you think about it - I want to group on the 'one'
end)

This is a major problem for me, since I need to group on those other
fields. I've tried rearranging the SQL in the SQL designer, and the
order of the fields/tables in the SELECT and FROM clauses is irrelevant
- I still get the fields from only a single table (the same one every
time).

The online help and the manual offer no help here - can anyone help
please? Otherwise I'm doomed to continue using a dummy database table to
hold my subquery parameters, which was one reason I upgraded to v7.

Thanks in advance
Steve



Sun, 21 Oct 2001 03:00:00 GMT  
 Basing a report on a Query (CR ver 7) from Crystal SQL designer - limited sorting / grouping fields


Fri, 19 Jun 1992 00:00:00 GMT  
 Basing a report on a Query (CR ver 7) from Crystal SQL designer - limited sorting / grouping fields

Never mind - solved it myself!

For the benefit of others, it seems that when you use SQL Designer to
access tables with varchar() fields, if the varchar is set to 255 (the
max for SQL Server), the report designer won't let you sort on it. It
can only sort/group on fields that are 254 characters and below. I've
changed my varchar fields to 254 characters max and it's fine.

Pretty stupid considering it quite happily sorts on these very same
fields if you access them via a direct data source like ODBC, OLE DB,
ADO etc.

Steve

Quote:
> -----Original Message-----
> From:      Streeting, Steven
> Posted At: 05 May 1999 10:07
> Posted To: crystal
> Conversation:      Basing a report on a Query (CR ver 7) from
> Crystal SQL designer - limited sorting / grouping fields
> Subject:   Basing a report on a Query (CR ver 7) from
> Crystal SQL designer - limited sorting / grouping fields

> I have a report that has fairly complex SQL query
> requirements (I need 2 subqueries with parameters in each).
> I've found the only way to do this is to use the Cystal SQL
> Deigner and have the parameters for the subqueries specified
> in there (Crystal report designer won't allow a parameter to
> be used in the edited SQL for a report).

> The query is fine in the SQL designer, and I can I base a new
> report on it by using the "Query" option when asked to supply
> a data source. However, when I get to the point where I want
> to specify sorting and grouping, I find that only half of the
> fields are available for sorting / grouping! In fact, of the
> 4 tables that I'm navigating, only the fields from ONE table
> are shown as available for sort/group, even though fields
> from the other tables are on the report. If I ignore it in
> the wizard, and see the (ungrouped & unsorted) report, the
> values are there for the other fields, but it I try to add a
> group, I can still only see fields from a single table (the
> one at the far 'many' end of the query - which is useless
> when you think about it - I want to group on the 'one' end)

> This is a major problem for me, since I need to group on
> those other fields. I've tried rearranging the SQL in the SQL
> designer, and the order of the fields/tables in the SELECT
> and FROM clauses is irrelevant - I still get the fields from
> only a single table (the same one every time).

> The online help and the manual offer no help here - can
> anyone help please? Otherwise I'm doomed to continue using a
> dummy database table to hold my subquery parameters, which
> was one reason I upgraded to v7.

> Thanks in advance
> Steve



Sun, 21 Oct 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. report based on query created in Crystal SQL Designer***

2. Update Crystal SQL Designer-based report

3. Crystal Report based on an SQL query

4. parameter query for CR Report Designer Component

5. Crystal report based on Crystal query, doesnt work

6. CR 7.0 SQL Query Designer

7. Recompiling Crystal Reports from Ver 9 to Ver 8

8. crystal reports 6 query designer xbase error could not open the file

9. Using Sub Query in Sql Query - Crystal Report 5.0

10. Q: Pass a SQL query from VB to crystal report sub-report

11. Crystal Report Designer with SQL Server 2k

12. CR SQL designer bug??? with SQL subquery

 

 
Powered by phpBB® Forum Software