Add Shared Variable as User Input Parameter

[pto160]

I have a shared variable DateVar called ReturnDate in a subreport called Shipped Qty. This is an inventory forecasting report and the return date is the Out of Stock date. I would like to add this as a user input parameter. So someone puts in 7/31/09 and the report would show all items that have a return date before this.
How do you add this as a parameter? This date var is a calculated formula and is not a field in the database. Can this be done?

 

[pto160]

I am using Crystal Reports X1.

 

[chelseatech]

Shared variables are calculated "WhilePrintingRecords" so can't be used in record selection which is in the previous pass through the database.

Sounds like the report has a complicated structure and you might need to provide more information for us to help you.

You can pass a parameter from the main report to the subreport, if you think that would work for you. Create a copy of the parameter in the subreport, and then back in the main report, link the parameters together.

If the subreport is just locating and calculating a single value, it might be simpler to use a SQL expression to extract that. There is an example on how to do this in our newsletter.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[pto160]

Thanks. The shared date Variable is ReturnDate. The subreport and main report is Out of Stock Date, which I link using the subreport links. How do I create the parameter in the subreport equal or less than the user parameter date. I have tried to create the parameter under formulas:

Shared DateVar ReturnDate;
ReturnDate<={?Out of Stock Date}.

This turns it into a boolean.

I have tried Shared DateVar ReturnDate;
ReturnDate:={?Out of Stock Date} but it does not work.

 

[pto160]

I have tried everything under the sun to add this calculated shared date var as a user input parameter without any success. Do I have to use a sequel command or stored procedure to make this work? I think this could be beyond the capability of Crystal Reports since Crystals does not write anything back to the database.
I am attaching the report via a rapid share link.
The Out of Stock Field report formula is in a subreport called Shipped Qty. It is under a formula as Out of Stock Date. The parameter is anything equal to or less than 7/31/09. This means that records that have an out of stock date that exceeds 7/31/09 will not show up on the report. I hope I can get this figured out.

 

[pto160]

The parameter is anything equal to or less than 7/31/09 is an example. The out of stock date parameter is entered by the user.

 

[lbass]

Your file cannot be downloaded. You should describe the setup of the main report and the subreport and explain where the subreport is located in the main report. I

I cannot follow what you are trying to do. If the parameter is entered by the user, why are you referring to wanting to add the return date as a parameter? Do you mean you want only certain return dates to appear based on a parameter? Why wouldn't you set up the parameter within the subreport that is generating the return dates?

-LB

 

[pto160]

I should have issued instructions to download the file. Sorry about that. If you want to save the file.
Click the link<Free User<Advanced Download
Settings<Download<Save File
The download is available under Free User. The File type does not show anything until it is saved on your computer.

Yes, I only want to certain return dates based on what the user inputs into the parameter box. So if the user puts in 7/31/09, the report will only show items that have a date that is equal to or less than 7/31/09. I have tried to create the parameter in the subreport but I have had no success. The reason is because it is a calculated formula and is not a database field. It has to pull the information from the database first like Item qty in order to calculate the out of stock date.
I have tried:

Shared DateVar ReturnDate;
ReturnDate>={?Out of Stock Date}. It does not work. Creates a boolean and does not filter out the results.
I am about to give up. It is in no books or forum as far as I can tell. It looks like the only way this will work is through suppressing the results in Section Expert, which I want to avoid doing.

Subreport is called shipped qty and is GF3c on the main report. The main report showing the items and item qty is also GF3C.
In the subreport the return date shared datevar is under formula workshop called Out of Stock Date. It is in section RFA.

 

[lbass]

You are omitting the formula. Show us the content of the formula that populates ReturnDate so we can see what you mean.

-LB

 

[pto160]

The formula is as follows:
DateVar ReturnDate;
if {@Monthly Forecast}=-1 then ReturnDate:= Date (2099,12 ,31 );
if {@Monthly Forecast} <> -1 then
(Shared dateTimeVar Shared_Out_of_Stock_Date :=CurrentDateTime + {@Monthly Forecast} ;
ReturnDate:=Date( CurrentDateTime + {@Monthly Forecast}));
ReturnDate

The first part is basically saying that if the Monthly @Forecast = -1. (The monthly forecast is Net Stock/Qty Sales)then the date is 2099,12,31. This prevents the report from erroring out, else it uses the out of stock date=(current date+monthly forecast.

 

[lbass]

How can {@Monthly Forecast} be a formula: Net Stock/Qty Sales and then be added to a datetime? Please show the actual content of {@Monthly Forecast} and of any nested formulas.

-LB

 

[pto160]

Monthly forecast is a numbervar.

Monthly Forecast Formula is:

NumberVar Comparsion:= {@Sum Of Qty Shipped};
Shared numberVar Forecast_Days_Stock;
NumberVar ReturnVar;
if Comparsion <> 0 then
ReturnVar:=(Forecast_Days_Stock / Sum ({@Qty Shipped Avg}) * 30) else
ReturnVar:=-1;
ReturnVar

The forecast days stock shared variable is assigned in another subreport called Sales Order. It is
Shared numberVar Forecast_Days_Stock :={@Net Stock Order}

Net Stock Order formula is
{?Net Stock 4}-Sum ({@Sales Order})

There are 3 subreports. There is another subreport called Purchase Orders that use the Shared_Out_Of_Stock date var.

 

[lbass]

Sorry, but this is too complex for me to follow and give you any decent advice. Note Chelseatech's early comments--you can't use shared variables in a selection formula. You could potentially use shared variables to suppress sections in the main report or in another subreport, but only if the subreport passing the variable is in a section above the one you want to suppress.

-LB

 

[pto160]

I appreciate you looking into this. I sat down with a Crystal Reports developer for 4 hours and we tried the section expert and the report was not processing properly. It would display results that were not equal to the parameter. The solution maybe is to write back to the database and since the report is not on a local server but remote access server, we may have to request the ERP software people to allow us permission to write back to the database by creating new tables, which I was trying to avoid doing. Thanks once again.

 

[IdoMillet]

Do you have Crystal 2008? It provides "Saved Data" selection formula expression that may allow you to use shared variables.

In any case, instead of writing back to the database, a much cleaner solution is to create a view, stored procedure, or command that provides the result of the formula as if it was raw data.

- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[pto160]

No, I am using Crystal Report X1. I probably will call Business Objects to see if is possible to use record selection shared variables with saved data.

So using a stored procedure requires me to use sequel commands? I think this is the best option as using the suppression technique is not giving me the desired result.