?
Solved

Referencing Special Fields in Crystal Report Add Command Function

Posted on 2009-04-28
19
Medium Priority
?
2,078 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Mike DeFelice
  • 9
  • 7
  • 3
19 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 24251395
Looks good.  Assuming they are logged in and the networkid field has the ce names.

mlmcc
0
 

Author Comment

by:Mike DeFelice
ID: 24251581
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
 

Author Comment

by:Mike DeFelice
ID: 24251603
Shoot I just screwed that up...

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

0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 101

Expert Comment

by:mlmcc
ID: 24251684
Choose the field from the special field list in the formula editor

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24251700
Where are you adding the formula?

mlmcc
0
 

Author Comment

by:Mike DeFelice
ID: 24251844
in the database expert under add command
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 24253337
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 24253660
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
 

Author Comment

by:Mike DeFelice
ID: 24253707
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 24255655
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 24256520
0
 

Author Comment

by:Mike DeFelice
ID: 24259905
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 24261553
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
 

Author Comment

by:Mike DeFelice
ID: 24261766
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 24262370
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
 

Author Comment

by:Mike DeFelice
ID: 24262551
The login and password are working.  The issue is with passing the parameter automatically to the stored procedure.
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 2000 total points
ID: 24264058
  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
 

Author Comment

by:Mike DeFelice
ID: 24295137
That worked great.

Thanks everyone for your help.

Mike
0
 

Author Closing Comment

by:Mike DeFelice
ID: 31575485
Really steped out the solution well.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question