×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using Special File in Command

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.

RE: Using Special File in Command

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.

RE: Using Special File in Command

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

RE: Using Special File in Command

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

RE: Using Special File in Command

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?

RE: Using Special File in Command

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:

CODE

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

CODE

not OnFirstRecord and
not InRepeatedGroupHeader 

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close