PDA

View Full Version : Ask Variables on a report


ABC123

gregtcm
06-12-2007, 07:03 AM
Very basic stuff this (I hope!) but can't find any documentation that helps.

I am using ask variables to filter reports e.g. dates from and to but I can't find a way to get the user input of the date range ask variables to display on the report.

In alpha 4 I used to use script dialog boxes for user imput and these variables were available when creating a report.

any quick fixes?

Stan Mathews
06-12-2007, 09:14 AM
Very basic stuff this (I hope!) but can't find any documentation that helps.

I am using ask variables to filter reports e.g. dates from and to but I can't find a way to get the user input of the date range ask variables to display on the report.

In alpha 4 I used to use script dialog boxes for user imput and these variables were available when creating a report.

any quick fixes?

The usual way is to put two global variables on a form. Put a button on the form that prints the report. Have the report filter reference the variables.

You could have the report filter reference the ask variables directly.


By following a simple naming convention, you can control the order in which the ASK variable prompts appear on the dialog box. Alpha Five uses the portion of the ASK variable after the prefix to form the prompt used in the input dialog box.

The following expression was placed in the Filter Expression field on the Filter/Order tab of the Customer Information Properties dialog box.

lastname > var->askC_Lastname .and. Firstname > var->askC_Firstname


The expression produces the following pop-up dialog box when the Customer Information form is opened. [Picture]


The dialog box that Alpha Five displays prompts for the firstname field before the lastname field (since that is their alphabetical sort order). However, by renaming the ASK variables as follows, you can ensure that the dialog will prompt for the lastname field before the firstname field.

lastname > var->askC_1_Lastname .and. Firstname > var->askC_2_Firstname



thus you could have a report filter


lastname > var->askC_1_Lastname .and. Firstname > var->askC_2_Firstname

gregtcm
06-12-2007, 12:28 PM
Thanks Stan,

I have been trying to do this but to no avail, no the answer must be simple but I need running through the exact way to do this. I can already use Ask Variables but although they filter the report I am printing they will not show on the report editor to place on the form for printing.

The form and report I am setting up are for site visits to a property we maintain with dates of each visit

I have created a Global variable called Date_From

I have set up a form that has a variable field on it called Date_From that uses a calender box to enable a user to enter a date (e.g 01/05/2007) NB UK so that is 1st May 07! then I save the record I am viewing. I have a button on the form that previews a report with site_name as the main Group and all dated visits to that site are the detail. I have set the filter in the report to see the global variable and only child records with that date should show.

I then placed a calc field on the report Header i.e. month(Var->Date_From) to show the month of the dates filtered. e.g. Sites visited in the month of Month(Var->Date_From) using the expression builder.

So far no records are being selected for preview??? but ask variables work.

There must be an easier way to do this? Getting Ask Variables to actaully appear on a printed report would be the easiest for me to work with at the moment. All I want is a way a user can specify a date Oor two) on a form and then print a report that uses that date as a filter and also shows that date (or calc field with expression result using that date) on the printed report.

I really miss Alpha 4 scripts!

Stan Mathews
06-12-2007, 12:35 PM
Check this thread (http://msgboard.alphasoftware.com/alphaforum/showthread.php?t=31923) for the examples from Tom Cone at the end.

Tom Henkel
07-12-2007, 03:31 PM
try putting the ask variable into a calc field, then place the calc field onto the report. We do this all the time.

You can use the calc field as part of the filter instead of the variable.

Tom

Tom Henkel
07-12-2007, 03:51 PM
We also declare, then set variables through an x-basic dialog. Attached is the dialog box, and what follows is the code. We use this to filter the reports. I pass the global date variables into the report where they are also defined, then create calc fields using those global variables. The filter in the report is based on these calc fields. I don't know your experience level with x-basic, so don't take this wrong. The code may seem a bit overwhelming, but it is actually pretty straightforward, and it will accomplish what you need.


'Date Created: 16-Apr-2007 03:38:47 PM
'Last Updated: 17-Apr-2007 02:17:38 PM
'Created By :
'Updated By :
'Create an XDialog dialog box to prompt for parameters.
DIM SHARED report_sel as C
DIM GLOBAL Start_date as D
DIM GLOBAL end_date as D
DIM SHARED Vendor as C
DIM GLOBAL Vend_no as C
DIM SHARED varC_result as C
report_sel=""
Start_date={}
end_date={}
Vendor=""
varC_result=""
again:
Vend_no=""
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Post TANF Wrap Around System Reports",<<%dlg%
{frame=1,1 Reports}
{region}Please select a report to print:{endregion}{sp}
{region}(report_sel:Monthly Paid Detail);
(report_sel:Total Paid Summary);
(report_sel:Vendor Payment Summary);
{endregion};
;
{frame=1,1 Dates}
{region2}
Please Enter Begin Date:| [%DATE;P=popup.calendar(dtoc(Start_date));I=popup.calendar%.20Start_date!Start_date_*];
Please Enter End Date:| [%DATE;P=popup.calendar(dtoc(end_date));I=popup.calendar%.20end_date!end_date_*];
{endregion2};

{condition=(report_sel="Vendor Payment Summary")}
{frame=1,1 Vendor Information}
{region3}For One Vendor or All Vendors:{endregion}
{region}(Vendor:All Vendors);
(Vendor:One Vendor);
;
{condition=(Vendor="One Vendor")}
Enter Vendor Number:| [.10Vend_no]
{endregion3};
;
{condition=.t.}
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%,<<%code%
if left(a_dlg_button,11) = "Start_date_" then
if a_dlg_button = "Start_date_killfocus" then
Start_date = ctod(dtoc(Start_date))
end if
a_dlg_button = ""
end if

if left(a_dlg_button,9) = "end_date_" then
if a_dlg_button = "end_date_killfocus" then
end_date = ctod(dtoc(end_date))
end if
a_dlg_button = ""
end if
%code%)
if varC_result= "CANCEL"
ui_msg_box("Post TANF Wrap Around System","Action Cancelled at user request")
end
end if
if start_date = {} .or. end_date = {}
ui_msg_box("Post TANF Wrap Around System","Dates cannot be blank"+chr(13)+chr(13)+"Please Enter dates")
goto again
end if
if end_date < start_date
ui_msg_box("Post TANF Wrap Around System","End Date cannot be earlier than Start Date"+chr(13)+chr(13)+"Please Re-enter dates")
end_date={}
goto again
end if
if vendor = "One Vendor" .and. Vend_no=""
ui_msg_box("Post TANF Wrap Around System","Vendor Number is missing"+chr(13)+chr(13)+"Please Enter Vendor Number")
goto again
end if
debugcheck()
if vendor = "One Vendor" .and. Vend_no<>""
dim Global Vendor_num as N
vendor_num = val(vend_no)
if .not.exist(vendor_num,"vendor","Vendor_No")
ui_msg_box("Post TANF Wrap Around System","Vendor Number "+vend_no+" is invalid"+chr(13)+chr(13)+"Please Enter a valid Vendor Number")
goto again
end if
end if
'now that we've got everything, let's print the report
'variables: "rep_select"= selected report
' "Start_date"= begin date for report
' "End_date" = ending date for report
' "Vendor_num" = Vendor Number for Individual Vendor Summary
if Vendor = "One Vendor"
query.filter = replace_parameters("Vendor->vendor_no = [varN->Vendor_num]",local_variables())
else
query.filter = ""
end if
query.order = ""
if level = "A"
:Report.Preview(report_sel,query.filter,query.order)
else
:Report.Print(report_sel,query.filter,query.order)
end if
end

Tom