Using Special File in Command
Using Special File in Command
(OP)
I'm using Crystal XI and writing my on Command. I need to use one of the Special Fields of Crystal as a value. For example:
SELECT TABLE.FIELD1, TABLE.FIELD2 ... WHERE TABLE.FILED1 = FileAuthor (File Author is one the Special Fields)
Maybe I don't don't have correct syntax?
If I don't write my own query and use Select Expert, I can set TABLE.FILED1 = FileAuthor, without any problem.
SELECT TABLE.FIELD1, TABLE.FIELD2 ... WHERE TABLE.FILED1 = FileAuthor (File Author is one the Special Fields)
Maybe I don't don't have correct syntax?
If I don't write my own query and use Select Expert, I can set TABLE.FILED1 = FileAuthor, without any problem.
RE: Using Special File in Command
The approach would be to write the Command excluding reference to the FileAuthor, then add it to the Record Selection formula. This means that it would be applied locally (ie approriate records excluded after the the dataset is returned from the query).
I suspect that even if you use the standard "drag & drop" approach to building the report, and use the FileAuthor in the Record Selection formula, that will not be passed into the Where clause of the SQL Statement so I see no disadvantage in this approach.
Hope this helps.
Regards,
Pete.
RE: Using Special File in Command
So further research seems to show that FileAuthor cannot be used in a query (Not sure how that would work anyhow since it is part of the summary information of the report).
RE: Using Special File in Command
RE: Using Special File in Command
Did you try leaving it out of the Command and adding it to the Record Selection formula like I suggested?
RE: Using Special File in Command
There is a way around this if you don't need any subreports in your report, you could do this:
1. Create a command for your report that just pulls in the current date from the database. You need to have at least one record in a main report for a subreport to run.
2. Suppress all sections in the main report except the report header. You may also want to keep the page header and footer sections, but you may not need them.
3. Create a formula in the main report that returns FileAuthor. I'll call this "{@Author}".
4. Create a subreport that uses your command. In the command, create the parameter for the author. Change it slightly to use the author - it will look something like this:
CODE
You'll need the quotes around it for it to work correctly.
5. In the main report, create a subreport link
- From the left column select {@Author}
- In the drop-down at the bottom left, select the parameter from the command - in this case {?FileAuthor}.
- Save the link.
This will set the value of the parameter in the command based on the value of the {@Author} formula in the main report.
6. Subreports don't allow you to set page headers or footers. So, if you need page headers in the subreport and you're grouping your data, go to the Options tab of the Group Expert for the outer-most group and turn on "Repeat Group Header on Each Page". Create a separate group header section for that group and put your page header there. In order to prevent this from appearing between groups if you have multiple groups on a page, you go to the Section Expert for this header section and use a formula something like this:
CODE
For more information about working with commands, please see https://blogs.sap.com/2015/04/01/best-practices-wh...
-Dell
Associate Director, Data & Analytics
Protiviti
www.protiviti.com