Ask Golden

Golden has become BITadvisors!

Leslie Horner — Thur, 05 Mar 2009 14:29:42 GMT

Hello everyone! We are excited to announce that Golden Consulting has merged Massachusetts-based BI Technologies to become Business Intelligence Technology Advisors, Inc. We have launched a new website and a new calendar of BI events, and we hope you will take a look: http://www.BITadvisors.com.

Grouping by shared variable

Janice — Wed, 21 Jan 2009 20:27:41 GMT

I have a Crystal Report running against an Oracle database, which means I do not have the option of using stored procedures or SQL Commands.

The report has several levels of grouping.

Group 1 is the Reviewer, Group 2 the Patient Encounter, Group 3 the specific review tied to the encounter. I would like to further group the Patient Encounters by type of Insurance (Medicare vs. Other.) The only way to identify Medicare is to loop through multiple insurance details looking for a range of values in any one of those, and link that back to the encounter (Group #2).

I created a running total MCare, for "distinct EncNum", which is incremented if any of the insurance plans meet the criteria, and reset with a new encounter.

I created a formula InsType

shared stringvar InsType;
evaluateafter ({#MCare});
if {#MCare} = 1
then InsType := "MCare";
InsType

However, Crystal will not give me the option to group by InsType. I am looking for a solution to identify the account based on the details as Medicare or Other, and then use that to group and subtotal for Medicare vs. Non-Medicare accounts.

Any suggestions would be greatly appreciated.

Excel in Xcelsius

Bill — Wed, 21 Jan 2009 13:59:03 GMT

Hi,

I have a situation where I need to use one selector for three different data sources(all are in Excel). The problem is that two of these need to return more than one row. A VLOOKUP will not work because it will only return one row. A filter selector will work but what would I do with the other multiple selection? Any ideas? Is there a way to pull more than one row in Excel?

Thank,

Bill

Run time error

kenny_gua — Sat, 13 Dec 2008 07:03:46 GMT

Hi,

When I run the crystal report, getting following alert-error message.

'The String is non-numeric'

and then open the window of following script of Crystal report.

not({member.division} in ) and
Date (ToNumber ( Left ( {?StartDate}, 4 ) ) , ToNumber ( Mid ({?StartDate},5 ,2 )), ToNumber ( Mid ({?StartDate}, 7,2 ))) = {payhist.STARTDATE} and
{@Include the benefit in the report?} and
{workstat.BENELIG} = True and
(
( {workstat.BENELIG} = True and {@Coverage amount} > 0 )
or
{@Deduction amount - absolute value} > 0
)

Can you please help and resolve my issue. Thanks.

Formula help for prompt date

Pam Petronas — Thur, 13 Nov 2008 22:08:11 GMT

Hi,
My report has a Startdate prompt which lets you select 1. 'M' - last month or 2. you can enter any date as a parameter.
based on what is selected in the prompt I need to display the last month.
If the report is run today (based on current date) choosing 'M' it should show Oct date.
if you select to enter a value in the prompt like 9/10/2008 then it should show Aug date.
I have to write a formula to get it. The {?StartDate} is defined as a ''String"

If {?StartDate} = 'M' then
MonthName(month(DateAdd ('M', -1,CurrentDate)),True) & "-" & cstr(DateAdd ('M', -1,CurrentDate),"yyyy")
else
{?StartDate} (I don't know how to manipulate {?StartDate} to make it point to last point last month, in this case Aug 2008)

Thanks

LastFullWeek Last Year

Jose H Perez — Thur, 25 Sep 2008 16:40:07 GMT

Is there a way that I can create a formula that will look at same period last year using LastFullWeek or similar to gather information for that period ?

Example

Description Last Week Last Year same period
----------------------------------------------------------
Supplies 340.00 220.00

Thanks in advance
Jose

saving login info

glenn hazelton — Mon, 15 Sep 2008 17:03:09 GMT

is there a way to save the login/pw info in a rpt so that the when the report is opened in a viewer it is already connected to the database?

Recommended way to Increase Number of Jobs on Job Server

Stacy Lapidus — Mon, 08 Sep 2008 16:55:56 GMT

We would like to increase the number of simultaneous jobs that run on each processor. We have 4 processors and our maximum jobs are set to 20 (5 for each processor). This number was recommended in the BO sizing document. I noticied that the processors are running fairly low when we have 20 jobs running simultaneouly and there seems to be room for more jobs to run.

We have BOXI R2 software and would like to increase the number of simultaneous jobs. Is it best to increase this number on the existing job server or to create a new job server on the same physical hardware to run additional jobs?

Thanks for your help.

Calculating # of days between dates

Cynthia — Fri, 05 Sep 2008 15:51:46 GMT

I need to calculate the number of days between dates to get a consecutive number of days for an employee. It calculates the first group but does not continue to the next line. The formula is below. Can you tell how to fix this?

Shared NumberVar consecDays;
Shared NumberVar consecDaysMax;
if(Previous({TR_CC_HRIS_MAND_ABS_BY_SUPVID.TR_GEMS_ID}) = {TR_CC_HRIS_MAND_ABS_BY_SUPVID.TR_GEMS_ID} and
   DateDiff("d",Previous({TR_CC_HRIS_MAND_ABS_BY_SUPVID.RETURN_DT}), {TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT}) <= 1 or
   (DateDiff("d",Previous({TR_CC_HRIS_MAND_ABS_BY_SUPVID.RETURN_DT}), {TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT}) = 2 and
    DayofWeek(DateAdd("d",-1,{TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT})) in and
    DayofWeek(DateAdd("d",-2,{TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT})) in )) then
    consecDays:= consecDays + DateDiff("d",{TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT},{TR_CC_HRIS_MAND_ABS_BY_SUPVID.RETURN_DT})+1
else
    consecDays:= DateDiff("d",{TR_CC_HRIS_MAND_ABS_BY_SUPVID.BEGIN_DT},{TR_CC_HRIS_MAND_ABS_BY_SUPVID.RETURN_DT});
if(Previous({TR_CC_HRIS_MAND_ABS_BY_SUPVID.TR_GEMS_ID}) <> {TR_CC_HRIS_MAND_ABS_BY_SUPVID.TR_GEMS_ID}) then
    consecDaysMax := consecDays
else if(consecDaysMax < consecDays) then
    consecDaysMax := consecDays;

consecDays

The results look like:                                   but I want
HRSTAKEN    CONSCDAYS CONSCHRS     HRS TAKEN CONSC DAYS HRS
40                      6             48                40              6             48
8                       6             56                  8             7             56
etc.

Please help. Thank you.

Export to excel

Satish — Mon, 25 Aug 2008 08:46:31 GMT

I'm exporting my report (Crystal Reports X1) to excel using Microsoft Excel 97-2000(XLS) format. The problem is in excel it occupies more than row per record. I've to use can grow option for the detail fields as the text cannot be accomodated in one line . Also I cannot use the data only option while exporting the report to excel as the requirement states so. Is there anyway I can ensure there is noly one row for one record in excel using the Microsoft Excel 97-2000(XLS) format?

Default Value for Dynamic Parameter

Satish — Fri, 22 Aug 2008 07:26:12 GMT

I want the 'ALL' value I've added using a union query to the be the default value for dynamic parameters in my report instead of the blank value which crystal XI report adds as default. Is there anyway to achieve this?

Removing Discussion Panel

Bill — Wed, 20 Aug 2008 15:37:27 GMT

Hi,

I have a dashboard posted in Infoview on the bottom of the screen is a "Discussions" panel. How do keep this from appearing?

Thanks,

Bill

Need help to summarize data from different tables !!!

Vijay — Wed, 20 Aug 2008 07:33:38 GMT

Hi there,
I am a newbie to Crystal Reports. My client needs one of his reports to summarize data from different tables of the same database. I am in desparate need of help!!!!!!

So let me a give a scenario of what they want.
There are 4 tables

Products - This contains the details of all the products - the master data - Primary Key - Product Code
Repairs - table contains all the repairs of the various products - Primary key - Repair ID.
Credits - the varios credits that have been granted for various products - Primary Key - CreditNo
Spares - The various spare parts for the products - Primary Key - Sparecode

The Recall, Credits and Spares are linked to Products table via the Product Code field (Primary key - foreign key relationship).

Products
Product Code Product desc
0001                Pram
0002                crayons
00093              Walkers
0003                 Baby toys
00055               Chocolate

Repairs                                                           Credits                                                            Spares

Repair Id   Product Code Repair Desc            Credit NO   ProductCode Credit Amount              Sparecode ProductCode Amount
12334        0001                 xyz                         44332        0001            888                             322223       00001              8833
23435        0001                 xxh                         3333         00093            999                              223356       00001             2222
35667        0003                 fsd                         44444          0001          009                               33333        00055            22222

The report that is need should be like this

Product Desc     Recall              Credits                 Spares   Subtotal
Pram                  2                      2                         2
Baby Toys          1                      0                         0
Walkers             0                       1                        0
Baby oys           1                        0                         0
Chocolates       0                          0                       1
Crayons           0                             0                      0

Sub Total

The report should take every Products in the "Products" table and count the number of times its is occuring in the 3 other tables and display them in the corresponding columns. Moreover we should subtotal the columns and rows.

Till now I have used wizards and stuff like cross tabs to generate reports. But this report is driving me crazy. I would greatly appreciate if you explain your solutions in detail without missing even the basic instructions,,as I am new to this field.

P.S - I would prefer it if there was no coding involved,,

Thanks In advance........

Field Explorer Missing...

Todd — Fri, 15 Aug 2008 17:23:11 GMT

I am using Crystal Reports V10 and I can no longer get the Field Explorer to show up on my screen, therefore, I cannot figure out how to place new fields into existing reports. I click the Field Explorer button on the tool bar and also use the view pull down option, but the Field Explorer windows does not appear. Does anybody know how I can get Field Explorer back?

Exporting Xcelsius File

Danielle — Thur, 14 Aug 2008 19:34:13 GMT

I have an Xcelsius file that I'm trying to export to a PDF file but when I export the PDF is set up portrait style where my Xcelsius file is landscape. How do I change the PDF layout to landscape?

Or I'd like to export directly to Business Enterprise but that option is grayed out. Is that because of settings on our Enterprise server or because of the version of Xcelsius we have?

Thanks!