Using Special File in Command

[VHDN]

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.

 

[pmax9999]

I don't believe you will be able to utilise Crystal Reports functionality at the point of the initial query.

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.

 

[kray4660]

I think there is a way to utilize FileAuthor in a query. But I do not recall how it is done (I believe I read about it years ago). Did you look at the query it generates when you do the drag and drop method?

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).

 

[VHDN]

When I drag and drop and view the query, it shows field1 = FileAuthor but when I try this in command, it doesn't like it.

 

[pmax9999]

Correct. It is a Crystal function, not a database function.

Did you try leaving it out of the Command and adding it to the Record Selection formula like I suggested?

 

[hilfy]

If you don't have a lot of data, pmax9999's answer about using the Select Expert will work. However, the filter won't be pushed down to the database for processing. Instead, Crystal will pull ALL of the data into memory and filter it there. If you have thousands of records, this can significantly slow down the report.

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:

...
WHERE TABLE.FILED1 = '{?FileAuthor}'

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:

not OnFirstRecord and
not InRepeatedGroupHeader

For more information about working with commands, please see

https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell

Associate Director, Data & Analytics
Protiviti

http://www.protiviti.com