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

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?
LVL 4
BostonMAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
What is the full SQL?

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

mlmcc
0
BostonMAAuthor Commented:
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
mlmccCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

BostonMAAuthor Commented:

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
mlmccCommented:
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
BostonMAAuthor Commented:
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
mlmccCommented:
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
BostonMAAuthor Commented:
Do you have any suggestions on how I can get all the values to display?
0
mlmccCommented:
What are you trying to accomplish with the Command?

mlmcc
0
BostonMAAuthor Commented:
I never really resolved this issue, but I circumvented it by getting the dba to create a view.

0
BostonMAAuthor Commented:
Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

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.