When using Crystal Reports, you can create some parameters that are automatically replaced with a drop-down of selections. All these parameters begin with an @ or %. They are useful when the report requires users to select an event or a function or an order status. If you use these parameters, you do not have to hard code these values into Crystal. They also change as the values are changed in the system.
Use any of the following special lookup fields in a custom Crystal report in order to populate controls on the Custom Dialog window.
| ACCOUNT | FISCALPERIOD | LANGUAGE | PUBLICATION |
| ACCOUNTSTATUS | FISCALYEAR | MARKETSEGMENT | REASON |
| ACCTTYPE | FUNCTION | MEETING | RECEIVABLETRANSACTIONT |
| AFFILLIATION | FUNCTIONCATEGORY | MEMBERSHIPACCOUNT | REGION |
| ARCONTROL | FUNCTIONCLASS | MGMTRPTCODE | REGISTRATIONACCOUNT |
| BOOKINGLINK | FUNCTIONUSAGE | MTGFREQUENCY | RESOURCECODE |
| COUNTRY | GLBUDGETCODE | MTGLOCATION | SALESACCOUNT |
| CUSTOMLIST | GLTRANSANALYSISCODE1 | MTGREGION | SALESREPACCOUNT |
| DEPARTMENT | GLTRANSANALYSISCODE2 | NEWRESTYPE | SPEAKERACCOUNT |
| DIARYENTRYTYPE | GLTRANSANALYSISCODE3 | NOTECLASS | SECTION |
| DISTRIBUTIONLIST | HEADING | ORDER | SHIPPINGCLASS |
| EVENT | HOSTCITY | ORDERSTATUS | SPACE |
| EVENTCATEGORY | HOTELACCOUNTS | ORDERSTATUSCODE | STATE |
| EVENTCLASS | INVCLASS | PERSONNELACCOUNT | SUPPLIERACCOUNT |
| EVENTINDICATOR | INVENTORYITEM | POSITION | TERMS |
| EVENTRANK | INVMAJOR | PRICELIST | TOURACCOUNT |
| EVENTSTATUS | INVMINOR | PRODUCTCATEGORY | USER |
| EVENTSTATUSFROM | INVPRODGROUP | PRODUCTS | USERGROUPS |
| EVENTSTATUSTO | ISSUESTATUS | PROMOTION | VISITORACCOUNT |
| EVENTTYPE | ISSUETYPE | PUBLICACCOUNT |
Within the Crystal report, the following parameter options are also taken into consideration when generating the Custom Dialog Window:
- Prompt Text - Control Label is generated by the entry in this field.
- Allow Multiple Values - Control type (list box or multi-select box) is handled by the entry in this field. (Does not apply to event IDs)
Active parameters including @Organization, @Account, @Invoice, @Order, @Event and @Bulletin are handled automatically from select windows and automatically set the parameter value equal to that of the selected item(s).
%CUSTOMLIST is unique parameter that allows you to load a validation table into a report drop down. You use %CUSTOMLIST_ValidationTableId. The ValidationTableId is the ID column on the Custom Field Validation Tables screen. You can use the same one multiple times by appending _AddtionalText to the parameter. Radio buttons are loaded as single select drop-downs. The Generic Report Prompt uses the Reports Parameter description. If you leave it blank, it uses the validation table description.
Comments
26 comments
Is there a more comprehensive list of the active parameters anywhere? We only have a PDF from 2008
0 upvotes
The best place to start with active parameters is the report list for the screen you ultimately want the report to be on. This will tell you the list of active parameters available for the report list. Please see https://supportcenter.ungerboeck.com/entries/23758396 for how to find this information. For finding which report list belongs to which screen, please see https://supportcenter.ungerboeck.com/entries/23888768
This information is more accurate than a list as parameters available are different between versions of the software.
0 upvotes
For some reason I'm not getting my parameters to display as a dropdown. Its just a free form field. I'm looking at other examples in reports that work, so I'm pretty sure I have it correct. I also have it setup correctly in the Select Expert window. Any ideas? Even if I try creating a normal dynamic parameter and select a data source, it still does not display as a dropdown. I have to key in my values.
0 upvotes
Lori- Are you using Active Parameters (start with @) or Special Lookup Fields (start with %)? For Active Parameters, you will not receive a drop-down. For Special Lookup fields, you should receive a field that will have a dropdown or a lookup button, but only if you are using the Ungerboeck Custom Report Dialog. Also, you must be running a report for data related to the lookup field you are using. For example, if you are running a report on an Event's registrants and use the %EventStatus Special Lookup, you may not receive any data in the dropdown for the lookup.
Does this help with what you are trying to accomplish?
0 upvotes
The Ungerboeck Custom Report Dialog...is that the wdCustomDialog that is assigned in the Report Prompt within Report Master? I am trying the Special Lookup Fields that start with %. For example I just created a new parameter called %EventCategory. Only change I made in the dialog box was to allow Multiple. I went into the Select Expert panel and assigned {EV200_EVENT_MASTER.EV200_EVT_CATEGORY} = {?%EventCategory}. When I refreshed the report, I do not get a dropdown to select values. I have a field box that allows me to key in a value. I see these Special Lookup Fields working for other reports USI created, so not sure why its not working for mine.
0 upvotes
Lori- Yes, I am referring to wdCustomDialog. It is what will fill out those Special Lookup fields in Ungerboeck for you. As long as the parameter is valid for your report data, it should create a selection box for you in Ungerboeck. If you are already filtering the report by another parameter like @Event or @Order, it may not fill in the Special Lookup field as there will only be one valid value. You will need to logout of and back into Ungerboeck to refresh the report data once you've modified the report.
0 upvotes
I'm using the %InventoryItem special lookup field. Is there a way to limit the list returned and to show the item code? We want only inventory items that begin with 31, 34, 35 or 38. Having the item code would help in making sure the correct item is selected - quite a few of the descriptions are very similar.
0 upvotes
Tonya-
Unfortunately, there is no way to limit the results. Some special lookup fields automatically limit the results, such as %Function. But for %InventoryItem, there is no way to limit what is returned. In cases like this, if there are not many options, we usually create a list within Crystal Reports and hard code the options in the list. If you hard code a list and be sure to enter the correct code, Crystal Reports has an option for Parameters named "Prompt with Description Only". If you make sure this is set to false, the codes will show.
0 upvotes
Hi Emily,
I see that %Function is not included in the table above but you make mention of it in your last comment. I presume that this can be used in conjunction with the @Event parameter? I too would like to limit the results.
Is there a way I can get a list of level 2 only functions to prompt for a selected event?
0 upvotes
Hi Sean!
You're correct, there are some missing parameters on that list. The best place to find a comprehensive list is actually in the Parameters window of the Create/Edit EZWriter Data Source screen. Everything listed there that starts with ! also is a valid % Crystal Parameter.
When using %Function from the Event report list, it does limit to only the functions on the event. As for limiting to level 2, the way we've tackled this in the past is to run the report from the function screen and put a filter in on the function level. The report returns blank if anything other than a level 2 is selected.
If you want to get really complex, you might be able to accomplish this with a Crystal Dynamic parameter. You'd have to create a custom SQL Command in your data source that only returns level 2 functions and use it as the source of a parameter.
1 upvotes
Thanks Emily. The last suggestion is what I was contemplating. Do you think a Table-valued Function would be a valid option?
0 upvotes
I think a table-valued function might be a bit of overkill. If you're going to reuse it in more than one report, you could create a view that only contains level 2 functions.
0 upvotes
Would be great to have %INVOICE available as a special lookup field!
0 upvotes
Has there been any changed to this list since this was orginally posted?
Ideally I am hoping theres a %pavilion lookup availble for when running a report on an event.
0 upvotes
Hi Kieron,
I spoke with our solutions architect and there hasn't been any change on this one. If you're looking for additional functionality, please feel free to post in the product idea forums so that our team can consider it for future releases.
Thanks!
Ryan
0 upvotes
Hi,
In the Accounts table there also the contacts in. Is there a way to limit the Account lookup field in the prompt for just class: O ?
thanks,
Bunyemin
0 upvotes
Hi Bunyemin,
Currently the %Accounts field returns both Accounts and Contacts and does not filter by class. We do have module specific variations like %SalesAccount, but these too return all values. If you would to request the additional functionality, please feel free to post in the product idea forums so that our team can consider it for future releases.
Regards,
Rob
0 upvotes
Hi Rob,
Thank you for your comment.
It's a bit strange that this topic isn't issued or solved trough out the years.It's actually also annoying when the prompt doesn't limit the results to just the account the report calls.
If it would help I will post it.
Regards,
Bunyemin
0 upvotes
Bunyemin,
If you are trying to limit the Account parameter to just the account selected in a grid, try using @Account instead of %Account.
Regards,
Rob
0 upvotes
Hi Rob,
For the main lookup fields i'm just using the @ but the result is still not limited.
I also changed the order in the selection record formulas but it's hopeless. Our customers are really frustrated about this.
Regards,
Bunyemin
0 upvotes
Hi Bunyemin,
I've reached out to the Customer Success Manager for the organization you are working with. She will follow up with the team there to give further assistance.
Thanks!
Maggie
0 upvotes
Hello,
I'm trying to add the Special Lookup field of %EVENTCATEGORY. Are there any guides on exactly how to do this in crystal?
I have added a new Parameter, named it %EVENTCATEGORY, type Sring, List of values Static, Value field none, description Field, none. On the value options I changed Allow multiple values to True. Then I went to the select expert, new tab, selected EV200_EVENT_MASTER.EV200_EVT_CATEGORY, set "is equal to". However, when I run the report I get an empty drop down for %EVENTCATEGORY. When I set the Select Expert to "is one of" I can browse and select my categories. When I save that report and run it the prompt does give me a drop down but when I select my categories I get 0 results (which is incorrect).
Can anyone let me know what I am doing wrong?
Thank you,
Michelle
0 upvotes
Hi Michelle,
Our reporting team took a quick look at this and couldn't recreate the issue. Someone will be reaching out to you soon to look more closely at what you have set up and get you going on the right path!
Thanks,
Maggie
0 upvotes
Hi Michelle,
I'm not from UB but I don't think that they will ever contact you again about your issue. I'm also still waiting.
Maybe you should select in the parameter Value field the event_catagorie field and in your selection criteria = equal to. Then you should select in your prompt also the categories and then it passess it to your selection criteria.
Hope this will work.
Greetings,
Bunyemin Alkan
0 upvotes
Hi Bunyemin,
To follow up on this request for your organization, we had advised a training session for crystal report with the team. I will bring the topic again on the governance meeting.
Kind regards
0 upvotes
Hi All,
Ungerboeck was able to hep me. I had an extra space in my parameter name that was throwing the Record Selection formula off. I also needed to clear my parameter default value.
Best,
Michelle
0 upvotes
Please sign in to leave a comment.