[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1222
  • Last Modified:

Crystal Reports Formula

I need to create a formula which give me the result of customers who "did not" pay. That is I need to display Payment = null in the last MTD.

I have in my database - charges and payments.

I tried doing this:

{ViewTenantStatement1.TransactionDate} in MonthToDate and
{ViewTenantStatement1.IsCurrent} and
{ViewTenantStatement1.TransactionType_ID} <> 2

The result are simply displaying all the charges for that month - and ignoring payments (transaction type =2). I need something like:
{ViewTenantStatement1.TransactionDate} in MonthToDate and
{ViewTenantStatement1.IsCurrent} and
{ViewTenantStatement1.TransactionType_ID} is  2 but is NULL. .  .
0
Starquest321
Asked:
Starquest321
  • 15
  • 6
  • 6
  • +2
4 Solutions
 
peter57rCommented:
It's not clear to me what the data looks like.

Are you saying that there IS a record with transactiontype_id of 2 for every month but the payment amount on that record is null if there is no payment   or are you saying that there is NO record with tranactiontype_id of 2 for months where there is no payment.
0
 
AsishRajCommented:
If i understand you correctly than i guess this is what you looking for

{ViewTenantStatement1.TransactionDate} in MonthToDate and
{ViewTenantStatement1.IsCurrent} and
{ViewTenantStatement1.TransactionType_ID} = 2 AND IsNull({ViewTenantStatement1.TransactionType_ID}

Open in new window


What you need to do is use the IsNULL function to filter the records and display as you want
0
 
Starquest321Author Commented:
I get a "("
is missing

:(
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
AsishRajCommented:
sorry missing ending bracket

{ViewTenantStatement1.TransactionDate} in MonthToDate and
{ViewTenantStatement1.IsCurrent} and
{ViewTenantStatement1.TransactionType_ID} = 2 AND IsNull({ViewTenantStatement1.TransactionType_ID})
                                            

Open in new window

0
 
peter57rCommented:
{ViewTenantStatement1.TransactionType_ID} = 2
AND IsNull({ViewTenantStatement1.TransactionType_ID})

Cannot possibly be true.

Please answer the question I posted before.
0
 
Starquest321Author Commented:
Formula worked - but generating 0 records. . .

My logic works: Give me all the tenant that are current, monthtodate AND did not pay (meaning transaction type = 2 but is null...

What am I missing?
0
 
peter57rCommented:
'Formula worked -'  
as I have just said , it cannot work.  It is wrong.  Just because it doesn't give an error doesn't mean it is correct.
0
 
AsishRajCommented:
I think i have 2 agree with you peter57r. it doesnt actully make any logic.

{ViewTenantStatement1.TransactionType_ID} = 2
AND IsNull({ViewTenantStatement1.TransactionType_ID})

will always return zero records.

@Starquest321, maybe if you can explain a bit better as to what you are after then we can help you out a bit better
0
 
Starquest321Author Commented:
It's not clear to me what the data looks like.

Are you saying that there IS a record with transactiontype_id of 2 for every month but the payment amount on that record is null if there is no payment   or are you saying that there is NO record with tranactiontype_id of 2 for months where there is no payment.

I am saying option 2:

"you saying that there is NO record with tranactiontype_id of 2 for months where there is no payment."
0
 
AsishRajCommented:
if its the second one then it will work. but u do need to have an idea as to what the data must look like.
0
 
Starquest321Author Commented:
Asishraj:

I think peter is right . .

{ViewTenantStatement1.TransactionType_ID} = 2
AND IsNull({ViewTenantStatement1.TransactionType_ID})

Cannot possibly be true.
0
 
AsishRajCommented:
thats what i mentioned in ID: 38333871
0
 
Starquest321Author Commented:
Can I attach the report with the data? Will that help?
0
 
Starquest321Author Commented:
What do you mean data "looks like" . . . how can I help progress the solution?
0
 
AsishRajCommented:
sure u can attach the report with data. i can look into it as soon as i hv a spare moment
0
 
mlmccCommented:
I think what you want is SQL like this.

SELECT  VT.Tenant
FROM ViewTenantStatement1  VT
WHERE
NOT ( VT.Tenant IN (Select VTS.Tenant
FROM ViewTenantStatement1 VTS
VTS.TransactionDate in MonthToDate and
VTS.IsCurrent and
VTS.TransactionType_ID = 2 ))

If so you will have to build the query in the database or in a Crystal Command.  I don't believe you can do it in the select expert.

mlmcc
0
 
Starquest321Author Commented:
Here is the report with the data. . .
tbalancenonpay-lakevillage.rpt
0
 
mlmccCommented:
If I understand the report, the data you provided is only the tenant who are current.  There is no data on tenant that are behind.

I see the data is a lot more complex than what I thought so my idea won't be easy to implement unless you do it in the database

To get the records you want you will have to remove the filtering then use suppression.  I don't believe you can accomplish what you want through a selection filter with the current tables

mlmcc
0
 
Starquest321Author Commented:
Let me example: The dataset is ONLY the tenants that are current. FROM those I want to PICK up the tenants who who did not make payments MTD (assuming I run the report on the 10th of each month).

The result will be: Select from the tenants that are current (not historical) the tenants who did NOT pay yet this month.

Thus tenants that ARE behind simply have a balance that is shown in the report.
0
 
mlmccCommented:
How do you determine they did not pay?
Is it that there is no type 2 transaction?
If so you need some kind of subselect to choose all the tenant with a type 2 then exclude them from your result.

It could be done in Crystal using record suppression but you then need to choose all tenants who are current regardless of tranaction type value


If you are doing any opverall summaries then there will be an issue with excluding the records from the summaries.

mlmcc
0
 
Starquest321Author Commented:
mlmcc: Exactly! I am determining that tenants did not pay if there is NO type 2 transaction.  How do I put in that "subselect" statement? Can you post an example of the record supression you are speaking of?

I am not doing ANY  opverall summaries. What are they?
0
 
mlmccCommented:
Subselect - You can only do it in the database or use a Crystal Command.  I gave you an example above

To use suppression
Remove the Type selection in the selection formula

Create a formula COuntType2
If {ViewTenantStatement1.TransactionType_ID} = 2 then
     1
Else
      0

You can then suppress the group header, details, group footer with a formula
Sum({@CountType2},{Group field}) > 0

mlmcc
0
 
Starquest321Author Commented:
I am almost there:

I get an error in the formula:

Sum({@count2type},GroupName (GroupName ({ViewTenantStatement1.PropertyUnitID}) > 0)

It's saying missing )

I changed the formula name to count2type. . .
0
 
mlmccCommented:
Does your report have a group?

Sum({@count2type}, GroupName (GroupName ({ViewTenantStatement1.PropertyUnitID}) > 0)

GroupName (GroupName ({ViewTenantStatement1.PropertyUnitID} need to just be the field that the report is grouped on

mlmcc
0
 
Starquest321Author Commented:
I am a little new at this..but I think I do have a group. I attached the report above in the previous answer if you can quickly glance.

I base the group name on the PropertyUnitID. .

I am still missing a ) no matter what I do (or how many additional ones I add :(
0
 
Starquest321Author Commented:
Please please help . . :(
I really need that report. . .
For some reason the suppression formula is missing something small. . .
0
 
James0628Commented:
In your Sum function, just use the name of the field that you used for the group.  You don't need the GroupName function.  If your group is on ViewTenantStatement1.PropertyUnitID, just use

Sum({@count2type}, {ViewTenantStatement1.PropertyUnitID}) > 0


 FWIW, you were also trying to use GroupName on GroupName, like this:
GroupName (GroupName ({ViewTenantStatement1.PropertyUnitID}))

 I'm guessing that you can't use nested GroupName functions.  I've never tried it.  In any case, it's not necessary, and you don't need (or want) to use GroupName at all there.

 James
0
 
mlmccCommented:
Try

Sum({@count2type}, {ViewTenantStatement1.PropertyUnitID}) > 0

mlmcc
0
 
Starquest321Author Commented:
worked! Thanks all for the help!
0
 
Starquest321Author Commented:
Can I add one additional question (or do I have to open another one)
How can I make the Telephone numbers in the report be a href to the following:

tel://{phone}

?
0
 
James0628Commented:
Since that seems to be completely unrelated to this question, you should probably start a new question.

 James
0
 
Starquest321Author Commented:
I posted my question here - I would appreciate it if you can take a look:

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27845270.html
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 15
  • 6
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now