How to avoid hard coding array values in a formula

[bluecjh]

If I wanted to avoid hard coding
a list of values into an array (for example)
used in a crystal formula,

how can I reference say a text file or perhaps a table for the list of values?

In my case I want to compare a variable to see if
it's value is included in the list, but i don't like
hard coding all the values.

thanks


Chris

 

[Madawc]

You could use your Visual Linking Expert - make a 'left outer' link from the value to the table, adding that table as an 'alias' if you are already using it for something else.
Then do a formula field to chec, something like

If isnull({alias.text}) or {alias.text} = ""
then "* No Text *"
else {alias.text}

It helps to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5.

Madawc Williams (East Anglia, UK)

 

[bluecjh]

Version 10
Will ponder your suggestion Madawc

Chris

 

[bluecjh]

Don't think I can use this concept,
in my case i have two date fields on my report.
I want to show the number of working days
between these two dates
(I know there is a dll available for a working days ufl
but i am interested in the concept/problem)

I have a simple formula to create this third
'working days' field but I couldn't think of
a clever way to retrieve the public holiday dates
without hard coding them.

this extra table (of dates) wouldn't have a relationship to the
other tables in the report and I can't see how to
incorporate it, unless i've missunderstood you
your fix is not applicable Madawc. Thanks

Chris

 

[rosemaryl]

Dear bluecjh,

If the holidays are (or could be) defined in a table within your database then you could easily use a sql expression to get the number of holidays between your two date fields and exlude weekends using standard crystal datediff formula.

If you cannot add a table of dates which is easiest because you can then define in a columns like: Workday, Holiday, and so on for information that you need.

something like (example is MS SQL Syntax):

(Select Isnull(Sum(H.Holiday),0)
from Holiday H
where H.Date >= convert(varchar(10), ReportTable."FirstDate", 101) 
    and H.Date < (convert(varchar(10),ReportTable."SecondDate" +1, 101)  
)

There are other options, like creating a udf (User Defined Function) that when passed the First Date and Second Date return a list of dates separated by commas. You could then simply call the udf in a sql expression:

(dbo.GetHols(ReportTable."FirstDate",ReportTable."SecondDate"))

Then use that as the array ...

Madawcs solution could also be used, but if the data is stored in another database or in an excel spreadsheet then you could link it using a subreport, set the selection/linking criteria to be your two date fields, and using a shared variable, sum the holidays in the subreport and return back to the main report for use in a calculation....

You have given so little info on what you have that it makes it hard to come up with an optimal solution.

Regards,
ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[bluecjh]

Ok I follow.

Unfortunately I have been passed a C Report
linked to an access database. The report was very simple
straight data with minimal selection criteria.
It had 2 date columns, I was asked to add the total number of working days on the end.

I already had a Public holiday table in an sql server
database and so I wanted to utilise this table
in a formula to derive this final column.

I hadn't used Crystal 'sql expressions' before but I found when I attempted to dabble that in linking to a second data source (sql server) I was precluded from using 'sql expressions'. So I didn't pursue that possibility.

would you therefor go for Madawcs's solution?



Chris

 

[bluecjh]

Rosemary,
I tested your first suggestion using crsytal expression
and a table of holiday dates, I got all my data from one source (sql server), everything went fine.

your second suggestion i'm lost on:

(Select Isnull(Sum(H.Holiday),0) from Holiday H
where H.Date >= convert(varchar(10), reortTable."FirstDate", 101) 
    and H.Date < (convert(varchar(10),ReportTable."SecondDate" +1, 101)

IsNull(sum(h.holiday,0)?
can't sum a date field?
can you elaborate, many thanks

Chris

 

[bluecjh]

Rosemary,
are you saying that within the 'report's' main
query, to reuest this data and to concatenate
all the dates so as to make an array?

Happy xmas
I must go, till the new year ...
thanks

Chris

 

[rosemaryl]

Dear Chris,

In my example I noted:

Workday, Holiday, and so on for information that you need.

So my example sql expression was to show if you had a table of dates and if within that table of dates there was a column called Holiday and when it was a holiday it was populated with a 1 and if it is not a holiday it was populated with a 0, then one could sum the column for dates that were between the firstdate passed and the seconddate passed.

Can you not add a table of dates to the Access database? If not, then hard-coding or a subreport would be the way to go.

Look, if you have a table of dates in sql and that part is working then all you have to do is create a shared number variable in the main report, initiate it and set the value to 0. In a subreport, use the firstdate and seconddate passed and linked to the subreport to get the holidays that fall between, put that in a shared number variable declared with the same name as in the subreport. Now, you can size the subreport very tiny so it doesn't show, and then call the value in the shared variable in the main report in the calculation to determine business days ...

This will be much slower then being able to select from a table, but avoids hardcoding.

My recommendation is to add the sql dates table you have to access thereby getting rid of the need to do a subreport or hardcoding ....

Regards,
ro




Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[scottostanek]

Chris, responding to the older bit of the thread:

I hadn't used Crystal 'sql expressions' before but I found when I attempted to dabble that in linking to a second data source (sql server) I was precluded from using 'sql expressions'. So I didn't pursue that possibility

Have you tried making a dummy access table that queries the holiday info out of your sql server table? I have had luck doing so with Excel data sources mixed with Progress data sources, the dummy table lets the final report use one type of data source so the sql option is back in. I think Access has something like that, a virtual table link or somesuch.

Scotto the Unwise

 

[bluecjh]

Rosemary, Scotto and Madwc,

Thanks for all your suggestions I now have half a dozen,
I am grateful as I was interested to know the different
approaches to this issue.

Initially I think I will try creating an ODBC
connection to SQL server from access and then via
an 'sql expression' use the public holiday count
to my working days formula.

Happy new year.


Chris