Can I show all fields when the fields have no data? 1

[RachaelD]

I am doing a report where I need all of the fields to show, even if they have no data.

This is a timekeeping report, and I have an advanced graph showing On change of Time_Keeping.Full_Name and Time_Keeping.Short_Description show value Sum of Time_Keeping.Weekly_Total.

If a person doesn't report any time at all, they don't show up on the graph. Is there any way to make all of the names show?

 

[BOjdh2n]

Try changing your Report Options to convert database NULL values to default. You can find the option under the reporting tab in the individual report options or Crystal Report options.

 

[dgillz]

If the person has no time, there is no data to report on. So you need a table that has all employees in it, then left outer join it to the time table.

This will create a report that shows ALL employees, and their time if they have it or a blank if they don't have any time.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[RachaelD]

Converting database NULL values to default didn't work, but thanks anyway.

I can't join 2 Remedy tables, the security settings prohibit it. I may be able to talk to a programmer to create a join for me in the database that is a left outer join. Thanks!

 

[dgillz]

What security settings are you talking about? The join takes place in Crystal, not in Remedy.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[RachaelD]

Remedy security doesn't allow Crystal to join it's tables. If I did a join in Crystal it would have to have one Remedy table and one (other database) table, or have the Remedy developers do a join for me so that Crystal views it as one table.

 

[dgillz]

What error message are you getting in crystal?



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[RachaelD]

I'm not getting any error message, I just need all fields to show.

If I join 2 Remedy tables I get no data - no error message comes up. remedysupport.com says they don't allow reporting software to join tables.

 

[dgillz]

I doubt this very much. Certainly they could make this very tough on you if they choose to do so. Even so you could do this with subreports.

What is the data type of Remedy? MS SQL?

Please explain the two tables that I still believe you can join, include the pertinent field names, data types and lengths. Also indicate which table it is that includes all employees (employee master?) and which one contains the time.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[RachaelD]

I can do subreports, but I don't like to because of the slowness, and I still can't get them all to show in one graph.

Remedy uses an Oracle backend. I guarantee I cannot link tables. I have talked with many coworkers who are reporting analysts and with the developers and it won't work. Remedy Support confirms this. It's a security setting, which is no problem, the developers are willing to do table joins for me within Remedy.

The timekeeping table has all of the information I need, names and hours, etc. I just need all of the fields to show. I can't link it to the master employee list, because I only need certain groups of employees to ALL print, not the entire database of names.

I have a plan though now, thanks.

 

[Turkbear]

Hi,
As has been mentioned, if the timekeeping table has no entry for employees that have no time, you will not be able to show those employees (you can only show what is there, obviously).

If, however, they are there with a 0 for time, then you should be able to show them as long as you do not restrict your record selection to those where time > 0.

As to the Joining issue, if Remedy thinks it is a security issue then I would find another vendor since joining ( in and of itself) does not affect security ( at least not with properly designed Oracle databases) - It may well be a performance issue, but even that can be handled...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[RachaelD]

Well, I am a government employee, and we have spent lots of money on Remedy, so the vendor isn't an option I can change.

Thank you for your help.

 

[BOjdh2n]

In the past I have had similar issues with trend reports. The fact is that Crystal Reports can not report against data that does not exist in the database. You can trick the report by left outer joining your transaction Table to a “Calendar Table” (As suggested Above) but you can not filter data within the transaction table without recreating the original problem. The true fix for this issue involves a daily, weekly, or monthly ETL (Extract Translate and Load) of the production database into a Data warehouse. Your ETL process should identify those periods with no aggregate information and insert a 0 for that period. Your other option is to export the data to Excel and then update each missing record by hand. Then using the excel document, create another report. You can also try using the “Colander Table” option, but as apposed to using record selection, use record and section suppression to “Hide” the unwanted data. Use caution with this method, because the report will retrieve every records for the database. I wish there were a better answer and that Crystal Would have an option that would insert a 0 aggregate for missing periods, but I am not aware of such a feature.

 

[RachaelD]

Thanks BOjdg2n for a very helpful post. I sent it to my Remedy developers in hopes it will help them find a solution.

 

[gortuk]

The limitations on joining tables are a function of the Remedy ODBC driver, not the underlying Oracle database. If you can set up a connection to the Oracle database itself (ie not the AR System ODBC driver) then you can join tables as much as you want.

The latest versions of Remedy contain views which display most of the information found in the Remedy forms, so these are the most useful for reporting. However, if you use these views may need to write a few formulas to convert the data from Oracle into a more user-friendly format. For example, date-time fields are stored in Epoch time so you need to convert them if reporting off of Oracle. Same thing with certain picklists.

 

[RachaelD]

I am aware that I could report from the Oracle database, but we aren't willing to do that because of the multitude of security issues we'd have with it. In addition, the Oracle database calls the tables and fields "T023, T050", etc. We have decided as an organization to have the developers create joins themselves for me (similar to a view) on the database side.

Thank you.

 

[gortuk]

Rachael - with regards to the issue about the Oracle database calling the tables and fields T023, T050, etc - the views I was talking about are actually given readable names and contain recognizable field names as well.

Even though Oracle won't work for you in this case, I still wanted to mention those views in case anyone else is reading this and finds that useful.

 

[dgillz]

Rachael,

What "security issues" are you talking about?

I am amazed as to what will work for private industry that will not work for the govt.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[RachaelD]

We support Public Safety, Health, Assessment and Taxation, etc - therefore we need to keep file and database security tight on every level. Each employee has certain security clearances for the specific job they do. It weakens security when everyone is given rights to everything, so giving someone in Infrastructure admin rights to an Oracle server doesn't usually make business sense and disturbs the security structure.