CODEWITHSUNDEEP
sqlvb.netvisual-studio-2010crystal-reports
user3522506
user3522506

Reputation: 55

How to display blanks in Crystal Reports? (when there is / are no data)

  1. I have a date field Table1.Dates with the dates of the whole month of September:
  2. I have a field Table2.Sales with sales data in SOME dates in September
  3. I have a field Table3.Expenses with the expenses data in SOME dates in September.

Question -- :

How do I display a blank (or maybe "$0.00") where there are no data of sales / expenses in that day?

DATE ~ SALES ~ EXPENSES
-----------------------

 09/01/2014 ~ **$0.00** . . ~ $500.00

 09/02/2014 ~ $320.00  ~ **$0.00**

 09/03/2014 ~ **$0.00**  . . ~ **$0.00**

...

 09/30/2014 ~ $50.00 ~ $24.00

I have tried --:

Querying them to join their dates but instead of displaying a blank where there is no datum, it displays the next datum where it exists regardless of grouping them by Table1.Dates.

Any help regarding this? I'll try more ways and do further Googling.

~ UPDATE ~ (from the first solution provided)

enter image description here

The picture above is the actual results of precisely following the first solution's suggestion. It is the actual fields and data of Table1.

Although it does show the $0.00, the dates from Table1.Dates need yet to be displayed regardless of what data are in Table2 & Table3.

Upvotes: 0

Views: 799

Answers (1)

Siva
Siva

Reputation: 9101

you can doe something like below:

  1. First instead of inner join on Table1.Dates use Outer Join... since this table has all dates for the September month.

  2. Group the report using Table1.Dates.

  3. For Sales and Expences write below formula:

    if Table1.Dates=Table2.Dates //Assuming Table two also consists of Date field
Then "$"+ToText(Sales)
else "$"+ToText(0)

repeat same formula for Expences.

Let me know how it goes

Upvotes: 1

Related Questions