Make a joined sort of two different (but same type) fields

[thebigbadwolf]

Good morning and happy new year to all

I have create a report with two subreports in. I need to know if is possible to sort this mixed list by column #1 (Date) which is really two different dates.

If is imposible to do it using subreports, I need to know if is possible to do it even without subreports, (when the two dates are in the main report.

Thank you in advance

 

[Madawc]

It's not clear what you're asking. If you are wanting a single sort for data from two different subreports, that's not really possible. Small amounts of data can be passed back using Shared Variables, but that's not feasible with large amounts of data.

An SQL Command would allow you to collect records from two or more different sorts and assign dates from different fields to a single Sorting date. That depends on your database being SQL, and you being able to write and test in SQL.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[IanWaterman]

YOu need to give more info as its not clear what you are trying to achieve.

can you post some sample data as it currently is and how you would want it to be displayed.

Why are you currently using subreports. With out knowing your data structure its difficult to assess whether SRs are needed or not.

Ian

 

[thebigbadwolf]

Thanks for your time.

I have uploaded the report to check

 

[IanWaterman]

Sorry I can not download report.

Can you describe situation please.

Ian

 

[thebigbadwolf]

I have two fields: a table1's date1 with values 05/01/2013, 31/12/2012, 12/01/2013 and a table2's date2 with values 08/01/2013, 10/01/2013.

I need to have a mixed sort like: 31/12/2012, 05/01/2013, 08/01/2013, 10/01/2013, 12/01/2013

Is it possible?

If yes, is it possible even if the table1 is in subreport1 and the table2 is in subreport2?

If is not possible from subreports, can you tell me a way to do it also without them?

 

[Madawc]

I think you need to use the SQL method I suggested earlier, if you can.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[IanWaterman]

What is the relationship between the two tables. If none, you can use a Command to union the two tables and then report off that, this will eliminate need for subreports and enable a sort on the common date field.

Command will look like

Select datefield, list of required fields
from table1
Union all
Select datefield, list of required fields
from table2

The other fieldds will need to be same data type and relevant to each other from both tables.

Ian

 

[thebigbadwolf]

You mean that the work must be done out of crystal first? So there is no way to make a join in the report?

 

[IanWaterman]

A command is embedded within Crystal. This is used for reporting instead of a conventional report where tables are related by joins.

Without seeing a sample of your data before and after I can't see any alternative to a Union join of the tables.

Ian

 

[thebigbadwolf]

Can you be more specific about the formula because I am not familiar with this?

 

[IanWaterman]

A command is a data object, when you create a new report instead of selecting tables select command in the data expert window.

Enter your bespoke SQl and then run report from this data set. You can add Commamnd paramters which can be embedded in the SQL statement. DO NOt use normal report parameters as they will be very slow to execute.

Ian

 

[BettyJ]

Hoping this link will help you.

http://www.hexcentral.com/articles/crystal-sql-commands.htm

The command can be used just like any table in your database, and can be linked to tables.

 

[thebigbadwolf]

The example uses two tables but with same named fields. In my case the names are different like

SELECT ISIT_ISSUE_DATE, ISIT_ITEM
FROM ISSUE_ITEM
UNION SELECT RCIT_LAST_UPDATE, RCIT_ITEM
FROM RECEIPT_ITEM
ORDER BY ???????

so the order must be with which field name?

 

[IanWaterman]

The data set will result in these column headers ISIT_ISSUE_DATE, ISIT_ITEM so sort by these.

No real need to sort in command as Crystal can do that locally.

Ian

 

[thebigbadwolf]

What i need is a sort of date which have values of ISIT_ISSUE_DATE and RCIT_LAST_UPDATE. Is it possible? And if it is, can you sent me an example of this?

 

[IanWaterman]

The fields will be merged together with a single heading in the result set with title ISIT_ISSUE_DATE if you want to be able to identify the two data sets in the final query change to something like


SELECT 'ISIT' as TYPE, ISIT_ISSUE_DATE, ISIT_ITEM
FROM ISSUE_ITEM
UNION
SELECT 'RCIT' as TYPE, RCIT_LAST_UPDATE, RCIT_ITEM
FROM RECEIPT_ITEM
ORDER BY ISIT_ISSUE_DATE

Ian

 

[thebigbadwolf]

Thank you!!!

 

[thebigbadwolf]

Is this possible to be done using folmulas only? I am asking because I have an application that using crystal reports using a local tool but the SQL commands are not recognised

 

[IanWaterman]

What version are you using. some Early versions did not have a command but you could go to show SQL and type in your own SQL there.

Ian