Referencing Special Fields in Crystal Report Add Command Function

I would like to reference CurrentCEUserName in a Crystal Reports SQL Command.  Is it possible?

IE 
 
Select *
 
From USER
 
Where NETWORK_ID = CurrentCEUserName

Open in new window

Mike DeFeliceAsked:
Who is Participating?
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
  1. The container report won't have anything in it. It's used only to capture the user logged in with the CurrentCEUserName.  Basically, it's nothing more than an almost completely empty report.  Create a Formula field called CurrentCEUserName and in the formula editor type the Crystal keyword CurrentCEUserName.  This ensures the special field will be available to you.
  2. Create a subreport which uses the SQL Command and insert it into the Report Header or Footer of the Container report. This ensures that the sureport will only be run once, which is the same thing that would happen if it was a standalone report.
  3. Create a NETWORK_ID parameter within the SQL Command for the CurrentCEUserName.  You'll be prompted to validate the query when you complete it, so just put in any CE User Name in order to validate the Command.
  4. Back in the main report, right-click the subreport you just created and change links.   You'll want to link the CurrentCEUserName field to the SQL Command parameter you just created.  Please note, you'll see two choices - do not select the one labeld ?PM-......Select the actual Parameter you created, which should be something like ?NETWORK_ID
  5. Run the report
What you've essentially done is build a Crystal Reports front-end report that captures the current CE User name and lets you pass it into the SQL query used by another report.  Once again,the subreport will only run once, so you don't need to worry about it running multiple times.

~Kurt

SELECT
  *
FROM
  USER
WHERE
  NETWORK_ID = '{?NETWORK_ID}'

Open in new window

0
 
mlmccCommented:
Looks good.  Assuming they are logged in and the networkid field has the ce names.

mlmcc
0
 
Mike DeFeliceAuthor Commented:
But I am confused by the syntax...  When I run it I get a syntax error.  It does not seem to recognise the special field.  I am getting an error that there is incorrect syntax near the word USER





0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Mike DeFeliceAuthor Commented:
Shoot I just screwed that up...

Sorry... The error I am getting is invalid column name  'CurrentCEUser'

0
 
mlmccCommented:
Choose the field from the special field list in the formula editor

mlmcc
0
 
mlmccCommented:
Where are you adding the formula?

mlmcc
0
 
Mike DeFeliceAuthor Commented:
in the database expert under add command
0
 
mlmccCommented:
Are you selecting it from the special fields or typing it in?
I don't think those fields are available to a COMMAND.

Try adding the command without the filter then use the SELECT EXPERT to add it

mlmcc
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
You can't use Crystal Reports special fields in a SQL Command.  Commands are run in entirety before any Crystal Reports functions are available for use.  If you really need to do this, I'd recommend building a container report that has nothing but the parameters and special fields you need, then create your Command in a subreport and pass in the special fields from the container to the sub.

~Kurt
0
 
Mike DeFeliceAuthor Commented:
We are implementing this in a live environment and I think that will cause some speed issues because crystal seems to rewrite each subreport page.  Is there a way to stop it from doing that?
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
What do you mean by "crystal seems to rewrite each subreport page"? I ask, because I don't know anything about your environment (is it webified, are you calling reports from an Enterprise portal or via .ASP, for example).

In general, a subreport that hits the database will query the database once for every instance of the subreport.  So,
  • if you have a subreport in a report header or footer the subreport will only run once
  • if you have a subreport in a page header or footer, the subreport will run once/page
  • if you have a subreport in a group header or footer and there are 300 groups, the subreport will run 300 times
  • if you have a subreport in a detail section and there are 50k records, the subreport will run 50k times
So, at a high level, if you create a container report, which is pretty much empty and dosn't contain a query of it's own and embed a subreport in a single report header or footer, you'll only run the subreport once.

~Kurt
0
 
mlmccCommented:
0
 
Mike DeFeliceAuthor Commented:
I am a bit dense so I need to ask the question:
which one is the subreport?  Is it the report with the parameters or the report with all my data?  If it is subreport contains the parameters I am golden because I can just have that run once.  If not... I think I am in trouble.

Kurt -

To give you some background I have some very efficient stored procedures that I run for my non-Crystal Reports reports.  I want to utilize these for my Crystal Reports but they prompt you for a user name before running.  I want the stored procedure to get these without any user input.  I was hoping I could reference it in a command, however I do not not think that is going to work.  To answer you environment question we are uploading to Business Objects.

Any Ideas?
0
 
mlmccCommented:
To avoid the prompt you have to pass the login id and password to the report.  Is it possible the user doesn't have permission to use that SP?

mlmcc
0
 
Mike DeFeliceAuthor Commented:
The username is required for a variable I declare.Not a part of a security.  I want the procedure to only pull back data for a specific user.  not the whole population.
0
 
mlmccCommented:
I understand the requirement for the parameter but Crystal requires you to log into the database thus you can pass a valid login id and password to the report for using the stored procedure.

mlmcc
0
 
Mike DeFeliceAuthor Commented:
The login and password are working.  The issue is with passing the parameter automatically to the stored procedure.
0
 
Mike DeFeliceAuthor Commented:
That worked great.

Thanks everyone for your help.

Mike
0
 
Mike DeFeliceAuthor Commented:
Really steped out the solution well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.