Sort in sub-report

[#1]

I have a sub-report that is pulling either a yes answer or blank answer. Is there a way I can sort all the ones that are blank or all the ones that say 'yes' in my sub-report ?

so it looks like this:

value:

252     yes

210

148

156     yes

I would like to see all the 'yes' sorted together, etc....

[JWiseman · Accepted solution]

hi Curt,

the only way to sort on whileprintingrecords numbers that come from a subreport or elsewhere is to pass them to another subreport. this new subreport is then used as the display for all values.

this is a bit of work so you should probably look into Dell's suggestion of using a Command object to bring back all data into one main report.

however, if a command based report is not an option, have a look at the attached sample report. extract the contents and change the .txt extension to .rpt.  there are some instructions on the report in the Design mode on how to make this work.

in your case, you would use your existing subreport data as the new main report and then pass all of the "yes" values to the subreport. this new subreport would use the data that is currently in your main report. by passing those "yes" values to a subreport, you can then use those values in a sort. so essentially you are reversing your current report as you're switching the current main report with the current subreport. hopefully the sample will make more sense then what i've written above.

-jamie

Sorting on WPR Formula.txt.zip

[cothmer]

I will try your suggestions. I think we have decided to go a different direction for this report. Thanks for your replys.

[DellSC]

There is no way to sort a main report on the results of a subreport when the subreport is providing a value for the data to be sorted.

Is there a specific reason why you need a subreport here?  There may be ways around this limitation (such as using a Command...)

-Dell

[cothmer]

Well, I am trying to only pull where a patient has particular order but not the other order (the other order being that it was NOT placed at all, hence the blank rows. If I put all this in the main report then I get all other orders besides the one that was not placed. So, I have a formula in the sub that says

if {ORDER_PROC.PROC_CODE} = 'CON2' then "Yes" else "No". It doesn't print the 'No' but shows up blank (not sure why 'No' doesnt print) but it does print the 'Yes' and the blank rows. Does this make sense ? Not real familiar with commands however.

[abhilash_kumar]

Hi Curt,

In the Formula Editor, make sure 'Default Values for Nulls' is selected from the drop-down above.

This should make sure 'No' prints as well.

-Abhilash

[DellSC]

Using subreports like this will significantly slow down your report because Crystal will have to connect to the database each time the subreport runs (for every row above).

What do your tables and links look like?  Can you post the SQL from both the main report and the subreport?

I can think of several ways to get you the data you want without using a subreport - which one is best will depend on how your data is put together.

-Dell

[abhilash_kumar]

Hi Curt,

As long as the column that shows 'Yes' or blank is Not a formula that uses print time functions, you should be able to go to the Record Sort Expert and add that column to the sort list and sort in descending order.

-Abhilash