Howe to shove a group if there is no data in it...

[rune]

I have grouped my data by half hour, and the report is a summary report (details suppressed). If there is no data in a period the report skips the group. I want to present all time periods, if there is no data in a group I’d like a message saying “no activity in this period”. The solution, I think; if there is no activity I have to make up some fake data, linked to the time period.

The users exports the report to excel and can fill out the missing lines themselves, but this makes the reports time consuming and not optimal.


Rune Flaathen

 

[kenhamady]

Crystal can't show a group without data.
So, I don't think there is a way to force this within CR, at least not a way that would carry into export. There are some techniques to get the data to display on the report, however they are pretty convoluted and won't export. Do they need to export or are they just doing that to fill in the blanks?

The only sure way I can think of is to ensure that every half hour has at least one record included in the report, even if it is a dummy. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[rune]

I can make "dummies" but how do I get the "dummy data" to relate to my "date/time" field so it will print in the period whit no activity?

If there is no way of doing this in CR I have to solve the it in Excel, so do you think I should focus on Excel or do you se a way to solve it with CR?

Thank you for the quick reply

Rune

 

[kenhamady]

If you can show these groups would you still need to export to Excel? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[rune]

Yes, we send this reports to our customers, who paste the information into their systems, they have macros who gets messed up if a period is missing. The reason this is a problem is that I'm replacing old reports made from an old system. My challenge is to make the new reports look the same as the old, when exported to Excel.

The person responsible for sending the report to our customer can of course fit and shrink the reports themselves, but I wane save them as much work I can. Anyway; the new reports are faster to run and better then the old ones in every way. This is just to make it perfect, so if your advise is to let it be, I'll do that.

 

[kenhamady]

You may have to accpet what you have.

The only way to make this work when going to Excel is to have dummy records. If you want to do dummy records you have to add them to the table and then make sure they meet the criteria of the report each time.

What groups does the report currently have?
What is your selection formula? Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[bardley]

Sorry, I may be way off here, but I have a report in which I have two report headers--one that prints if I have data for the report and one that prints if I don't. I also conditionally suppress the group headers and other stuff in the report if there is no data (with the same formula, below). Can't this idea be applied to groups?

I suppress everything using isnull Count (fieldname), so couldn't you use isnull count ({fieldname},{groupname}) to suppress empty groups? Brad Gunsalus
Cymtec Systems, Inc.
bgunsalus@cymtec.com

 

[IdoMillet]

There may be two possible approaches to produce what you need:

Approach #1:
Create a time_slot table with a record for each half hour.
Each of these records will have the "label" for the half hour you want to use as the Group Field as well as
FROM and TO fields.

For example 12:30 12:30 1:00
1:00 1:00 1:30
etc...

Do an OUTER JOIN between that table and your transaction data. Base the join on the transaction time falling between the FROM and TO fields of the time_slot table.
______________________________________________
Approach #2

Use a UNION to add all possible half hour records
with blank data in them (except for the field used for the half hour grouping).

Cheers,
- Ido

 

[bardley]

um, hi.
I applied the idea I posted above to one of my reports, and it works beautifully for me. Three things--

1. I think it's necessary to create a group summary with a count of the records (at any rate, it's a good idea)

2. you must insert the same suppression formula into every section you don't want to print for that group - including groups inside the every-half-hour one

3. you must use isnull, and not count(blah) = 0; if there are no records, the summary is not created (for that group), and is null.

Good Luck! #-) Brad Gunsalus
Cymtec Systems, Inc.
bgunsalus@cymtec.com

 

[kenhamady]

The challenge with using the left-outer join is that if you put any criteria on the outer table, it will nullify the outer join. This makes a report like this that is run for 'a defined period' difficult to create.

In other words, it is easy to maintain the outer join if there are NO matches at all in the outer table. You can use IsNull(). However, if there are matches for all times, but none are in the criteria period, things get more complicated. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[DonMc67]

Rune,

I'm assuming you are using an aggregate function (ie. sum, count, etc.) for activity in the time period. There are many options for looking for null values, have you considered an if then statement containing your aggregate?

if isnull(summaryfield) = true then
"No Activity in the time period" else
summaryfield

I do stuff like this quite a bit, I don't know if that's what you are looking for, but it's simple

 

[rnd]

You can try an isnull function in a "if..else..formula" in the place of the direct database field.
The formula may be something like this..
IF ISNULL ({YourDateTimeFieldName}) then "No Data......"
ELSE {YourDateTimeFieldName}
Note: If data type is different (date/time, text, number) you may have to type cast the data through "ToText" or other appropriate function.

Good Luck.

 

[smv929]

Ken,
I want to show all hours in a report. I have a dummy table (HOURS) that has one field Time_Stamp. It has 12 records like 010101, 020101, 030101 -> 240101. I have a formula (@TimeCov) that returns the hour. I group by this formula. I join the Hours table with the real data table (Pk_Line).

This works until I add a record selection criteria to show only a specified date from Pk_line. If that date doesn't have all the hours, then it again omits hours.

I tried appending a UNION SELECT into the Database menu's Show Query box but I get errors--Crystal sytax errors. Below is the statement. How do I get the dummy date column in the second select statement. Or is this not a good approach?

SELECT
PK_KIT_LINE."DATE_STAMP", PK_KIT_LINE."TIME_STAMP"
FROM
"ASAPDBA"."PK_KIT_LINE" PK_KIT_LINE
UNION
SELECT
HOURS."TIME_STAMP"
FROM
"ASAPDBA"."HOURS" HOURS

Thanks

 

[smv929]

I tried creating a view like...

Select <real query here>
union
select ' ' kit_number, '010101' as TIME_COMPLETED,'301' ZONE_NUM, ' ' DATE_COMPLETED, 'W' lnitem_status from dual
union
select ' ' kit_number, '020101' as TIME_COMPLETED,'301' ZONE_NUM, ' ' DATE_COMPLETED, 'W' lnitem_status from dual

The two Union statements append dummy records having hour 1 and hour 2. I did this for 24 hours. The problem is I want the report to have a parameter to choose zone_num and date_completed fields. In the dummy records, you can see that I've hard coded numbers for these--just for testing. This obviously won't work if you don't choose zone 301 and date 20020114 when selecting parameter values to run the report. Is there a way I can use this union approach in a view and somehow avoid hard-coding values for zone_num and date_num? Thanks. (This problem should be solved by Crystal software by now!!)

 

[kenhamady]

Your view could append the records from the hours table, and the values for the other fields could be a value to ignore in your report.

But you have to make sure that your WHERE clause is only applied to the real records, not the appended records. To do this using a parameter might be easier using a stored procedure with a parameter rather than a view. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com