| Author |
Message |
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
   Posted: 26 May 2009 at 6:05am |
Sounds right.
For some reason I thought that the out of stock parameter was coming from the data. If it is something that the use can enter via a parameter, you don't need the shared variable. You can just link the subreport to the main reports parameter Out_Of_Stock_Date.
Sorry to lead you astray, again, I thought that the date was going to be generated from the data, not a parameter.
|
 IP Logged |
|
pto160
Newbie
Joined: 13 May 2009
Location: United States
Online Status: Offline
Posts: 16
|
Posted: 26 May 2009 at 4:34pm |
|
Thanks. In the subreport I tried to create the parameter by using the following formula.
Shared DateVar ReturnDate;
ReturnDate<={?Out of Stock Date}
This turn it into a boolean.
I tried
Shared DateVar ReturnDate;
ReturnDate:<={?Out of Stock Date}.
It says a boolean is required here.
So I am not sure how to write the user input parameter in the subreport?
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 27 May 2009 at 6:30am |
I am not sure where you are using this, but I am guessing that what you want is:
ReturnDate := {?Out of Stock Date}
This will assign the value of the field to the shared variable.
if you want to compare for the purposes of the selecting data:
if {some data field} >= ReturnDate then
true //or false which ever is correct
else
false //or true
This can shortened to just:
{some data field} <= ReturnDate
which can be used in filter or suppression formulas, but an assignment is the first example.
|
|
IP Logged |
|
pto160
Newbie
Joined: 13 May 2009
Location: United States
Online Status: Offline
Posts: 16
|
Posted: 27 May 2009 at 4:26pm |
|
Do I have to use the section expert if I try to suppress the results? It may look like I have to use a sequel command or stored procedure in order to record select the results properly. Suppressing the results means that everything is returned but we are essentially hiding the results that do not match the parameter. I tried doing that but it got very messy.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 28 May 2009 at 6:13am |
if you can filter the values in the SQL selection, all the better. Many who post, don't use stored procs or SQL, they just bind to the tables.
Given that you are going to use a SQL expression, you might not need to use the suppression, but it will all depend on what data is returned and what you want displayed.
|
|
IP Logged |
|
pto160
Newbie
Joined: 13 May 2009
Location: United States
Online Status: Offline
Posts: 16
|
Posted: 28 May 2009 at 4:19pm |
|
Thanks. I sat down with a Crystal Reports developer for 4 hours a year or two ago and we were using the section expert to suppress the results that did not match the parameter. The problem was that it would return only the correct results for about a page but would not return anything after that.
Then we started to add a copy of the subreport section and base the suppression on this subreport copy. Still did not work. I have not seen anything on this in any book or the business objects support section of their website.
Can shared variables be calculated using a stored procedure on the database? I have been told that shared variables are only evaluated "while printing records".
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 29 May 2009 at 6:07am |
I was taught by a Crystal Report tech support person...way long ago. If you want to suppress records that don't match the parameters, filter them out in the Report/Select Formulas/Records.
shared variables are calculated by the report. If you want to suppress something in a stored proc, just don't return it. You can use a stored proc to return multiple tables and then use the value for one of the tables as a shared variable, but why? Just use the value.
I do this in every report. My stored proc returns 2 tables (sometimes more, but usually 2). 1 is the data for the report, 1 is the parameters used to generate the report (my data comes from an XML file...ok, ado.net really). I will use the parameter information to, in some cases, to 'dynamically' structure/group my report.
As for suppressing a section, like a row of data or a footer, etc, if it is not always visible or always suppressed, then you need to use the Section Expert to enter the formula that will suppress it.
Long winded, but hopefully helpful to all.
|
|
IP Logged |
|
pto160
Newbie
Joined: 13 May 2009
Location: United States
Online Status: Offline
Posts: 16
|
Posted: 04 Nov 2009 at 9:42am |
|
I have researching this issue lately again. The best way is through a stored procedure, and maybe the only way that works properly. Can I just write the shared date variable formula as a stored procedure, and leave the rest of the report as is?
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 04 Nov 2009 at 11:45am |
the easiest solution is to gather all your data in the stored proc. I haven't ever mixed a stored proc and linking to tables in Database Expert in the same report. I would think that you could, but again, the simplest is to use the stored proc.
The down-side, is when you change the datasource, it will erase your report, but this can be averted. Add the stored proc as datasource and then replace each field as needed until the little checkmarks are all gone in the old datasource, then you can safely delete them as datasources.
HTH
|
|
IP Logged |
|
pto160
Newbie
Joined: 13 May 2009
Location: United States
Online Status: Offline
Posts: 16
|
Posted: 04 Nov 2009 at 12:33pm |
|
Thanks. Would it be necessary to have database administrator rights to execute the stored procedure? Do I have to write the stored procedure on the database, or can I use crystal reports to store and activate the stored procedure?
The problem is that the database is controlled by the ERP vendor. They currently allow us to retrieve the data from the database using crystals but I am not allowed to modify or make any changes to the database. I am a bit of a novice in how stored procedures operate. Could you recommend a book that tells you how to write stored procedures? Your help has been invaluable.
|
|
IP Logged |
|
|
|
