Solved

When I use 'add command' in my sub report, my dynamic parameter list shrinks.

Posted on 2008-10-20
11
261 Views
Last Modified: 2011-10-19
I added the following command as a left outter join to my sub report:

SELECT Min([Date]) "Date", [customer id] "Customer Id"
        FROM   dbo.Customers_with_Dates_in_future
        GROUP BY [customer id]

Inside my subreport I have a dynamic parameter which gets its values from a table (not the command).  Before I added the command, the list of values (as presented when you refresh the report) was over 300.  Now that I have the add command in the sub report, it only shows me like 15 values.

Has anyone else had this happen before?  If so any ideas what would cause it?
0
Comment
Question by:BostonMA
  • 6
  • 5
11 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 22762218
What is the full SQL?

It sounds like the new part is limiting the records for some reason?

mlmcc
0
 
LVL 4

Author Comment

by:BostonMA
ID: 22763535
That is the full sql of the command.  I really dont think its limiting the records because when i browse the field which the parameter is based on, I still see all the values...


Any other things you think it could be?

0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 22763926
You said you added it as a LEFT OUYER JOIN.  If that is the case there must be more.

With that SQL you are limiting the dates to the MIN date for a customer thus you won't see all the dates.

mlmcc
0
 
LVL 4

Author Comment

by:BostonMA
ID: 22763951

The table linking  looks like this:

                            (This is a left outer join to the manager table.)
                          -----------------Sql Command I pasted above.
                          |
Manager Table--------------------Last Sale View
                          |
                          ------------------ Many other misc views

In total there are 6 views all left outter j oined to the manager table.  

There really isnt any more sql in that 'add command' .  Does this help?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22764132
WHat is driving the parameter list?

I believe it is the full SQL for the report which would include all the tables.

How is the COMMAND joined - on what field(s)

mlmcc
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Author Comment

by:BostonMA
ID: 22765832
The parameter list is driven by a field on one of my views.  

The 'add command' which i posted above is joined to the main customer table through a manager id.  When i link the 'add command' to the manager table it only gives me a choice of Inner Join or Left Outter Join.  I've tried them both and in each case the parameter list is shrunk.  Remeber, no matter which way I join the 'add command' to the manager table, I can still go to the field which is driving the parameter list and 'browse it' and I see the complete list of values.



A field on one of my other 6 views is driving the parameter list.

What do you mean when you say 'I believe it is the full SQL for the report which would include all the tables.
'

My guess is that it has something to do with the message that gets displayed when i link the add command which I'm uploading, although nothing really makes sense as to what exactly it would be..
warning-1.jpg
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22770366
When you browse a field you see all the data for that field in the database

When you get prompted for the values in the parameter prompts Crystal actually runs the SQL and displays the values that were returned.

mlmcc
0
 
LVL 4

Author Comment

by:BostonMA
ID: 22770530
Do you have any suggestions on how I can get all the values to display?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22771765
What are you trying to accomplish with the Command?

mlmcc
0
 
LVL 4

Author Comment

by:BostonMA
ID: 22832252
I never really resolved this issue, but I circumvented it by getting the dba to create a view.

0
 
LVL 4

Author Closing Comment

by:BostonMA
ID: 31508012
Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now