total count by excluding one condition of the main report

[gagani]

I have joined some tables with orderid as the common element in them and I filtered my report based on the condition of cancellation of orderstatus in 'order' table.

There is another table called 'persons' with personid and orderid. I am in need of the total count of personids.

If I join persons table with order table. Then I am getting the count of personids when orderstatus has got cancelled.

I do not want the count of personids when orderstatus got cancelled, but I want the count of personids irrespective of orderstatus.

Entire report is based on the cancellation status.
But i want one particular formula which should give the total count of personids without considering the orderstatus of cancelling.

can anyone help with it.

 

[RTag]

I guess you want to get the total number of the persons in the database. This can be done with SQLExpression.

Report automation for Crystal reports and SSRS

http://www.R-Tag.com

 

[gagani]

I am writing the sql query:

select count(distinct(reporders.repids)) from personorders with(nolock), orders with(nolock)
where
personorders.inactive =0 and
personorders.orderid = orders.orderid

The above query should perform at group level and record level
for the input parameters of 'fromdate' and 'todate'

How do I write the sqlexpression for it.

 

[RTag]

SQLExpression can be used in case you want to retrieve a value from the database in a separate call. This probably is not the case here.
I assume you have one record per per person and order in personorders table.

SELECT count(distinct(personorders.personid)) AS Persons,
SUM(CASE WHEN personorders.inactive =0 THEN 1 ELSE 0 END) AS InactiveOrders
FROM personorders



Report automation for Crystal reports and SSRS

http://www.R-Tag.com

 

[lbass]

You can't use parameters in a SQL expression, and since a SQL expression directly accesses the database and would not be subject to selection criteria within the report, a SQL expression wouldn't work in this case. Also note that subqueries like this MUST be enclosed in parens in the SQL expression editor.

You could instead write a command, where you can create the parameters within the command and then use them in the where clause.

-LB

 

[Madawc]

I'd have thought it would work with a running total and a formula to exclude some of them. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.


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

 

[SmpTraining]

I would use a subreport in the report header section.

The subreport would simply include the PERSONS table and a count of the personids. Use a shared variable to pass the value back to the main report and use this wherever you need it in the main report.

You will need two formulas, one to assign the count to the shared variable and another to display/use the value.

@AssignPersonCount
shared numberVar personCount :=0;
personCount := count({table.personids})

@DisplayPersonCount
shared numberVar personCount;

You will want to format the subreport such that it doesn't display anything in the main report.


Steve Phillips, Crystal Reports Trainer & Consultant

 

[gagani]

I used the subreport and got the value.
Now i want that value in subreport to use in the formula of main report.

In subreport, I wrote
whileprintingrecords;
shared numbervar personcount:= DistinctCount ({@personcount});

In mainreport, I wrote
whileprintingrecords;
shared numbervar personcount;
personcount

and placed the mainformula underneath the section of where subreport is placed.

But it's returning 0. It's not returning the value of subreport.

Could anyone help

 

[SmpTraining]

What you've done sounds fine. So to troubleshoot:

1. Check the spellings carefully, its best to copy and paste the shared numeberVar portions.
2. You are counting a formula @personcount. Should this not be the database field instead?
3. Copy the 'display' formula into the subreport and display it in the report footer section - is the value showing okay there?

These steps should resolve it.

Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk

 

[gagani]

this is formula i took for the person count and put a distinct count at the record level.
@personcount is basically,

if({persons.inactive})= false and
{ORDERS.SOLDDATE} in {?FromDate} to {?ToDate}
then {persons.IDS} else tonumber({@null})
then i took a distinct count at the record level.
then
In subreport, I wrote
whileprintingrecords;
shared numbervar personcount:= DistinctCount ({@personcount});


I could see the value of subreport in main report.
But I could not see the same value at @mainformula in mainreport.

 

[SmpTraining]

For a moment, lets forget the main report entirely.

If you just consider the subreport can you check whether a formula containing...

DistinctCount ({@personcount});

...displays the correct amount when displayed in the Report Footer section of the subreport?

Where in the subreport have you placed the formula containing the following code?

whileprintingrecords;shared numbervar personcount:= DistinctCount ({@personcount});

Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk

 

[gagani]

It's working fine without any changes.

I used the value of subreport in the formula of mainreport.

Now I am having trouble with hiding the subreport.

When I used the checkbox of suppress in the format editor and tried to preview, it is throwing division by zero error.

Before suppression, I could see the report properly.

 

[SmpTraining]

If you suppress a subreport in that way it doesn't run at all and therefore you have no value returned.

You need to follow these steps:

1. Right-click the subreport and format it to suppress when blank
2. Format the section the subreport appears in to 'Suppress when blank'
3. Go into the subreport and make sure all sections are suppressed

This combination will make sure the subreport is blank, the section it appears in doesn't display but the subreport still runs.

Steve Phillips, Crystal Reports Trainer & Consultant

http://www.smptraining.co.uk

 

[gagani]

Sorry, I didn't get the points 1 and 2.

In the common tab, I could see a checkbox with name 'suppress'. In the subreport tab, I could see a checkbox with 'suppress blank subreport' . I just clicked both, but still the same problem.

I didnt see anything like suppress when blank.

 

[gagani]

sorry and thank you. i just have check the subreport when blank and suppress the sections in subreport.

thanks very much