Crystal Reports 2008 - Dynamic Cascading Parameter, with Command Statement

Hi guys,

Has anyone been able to use Dynamic Cascading Parameters with Command statement in CR2008?

The command statement is necessary due to performance of the query (we have tuned it as well as we can).

I have a command statement that basically does the following in the where clause

where table.mso = '{?MSOParam}'
and table.programmer = '{?ProgrammerParam}'
and table.service = '{?ServiceParam}'

I created a Business View and a cascading list of values for these three hierarchical values. The business view contains 3 columns (MSO, Programmer, Service) and all correct combinations.

Based on MSO chosen, list of Programmers appears.
Based on Programmer chosen, list of Services appears.

I want the selections from the cascading prompt to populate into my SQL Command.  

Has anyone been able to do this? So far I've only been able to filter the query based on Service. Unfortunately I don't know how to make the command use the options selected for MSO and Programmer.

Thanks
LVL 14
LinInDenverAsked:
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.

crgary_txCommented:
Can you turn the command object into a database view? If you can use the view, then you can have all your filters in record selection formula in crystal which supports dynamic and cascading parameters. I think crystal has limitations in accepting dynamic and cascading parametrs with command object/stored procedure as source..atleast with XI and before.
0
LinInDenverAuthor Commented:
Hi Gary,

Good thought - unfortunately we cannot turn it into a view - the performance of the database is really bad, and the parameters above (and others) are used at several spots to speed it up. It's on a production database, so  speed is important to prevent the report from taking down the whole system.
0
crgary_txCommented:
At times I have used stored procedures which accepts multivalued parameters. The stored procedure could accept comma seperated parameter values in a string format and a logic was built into the stored procedure to parse the string. On the report side, the main report was turned into a subreport which was wrapped in a blank 'container' report! The same parameters which were in the subreports  were also defined in the main report with the scope to accept multivalued parameter. This multivalued parameter can be linked to the subreport (stored procedure parameter) using formula: join({?parameter},',').

However in your case you want to use the dynamic and cascading parameter. I dont know if you can expand the above concept to handle dynamic and cascading parameters..

worth giving a try!  I cant think of any other methods to handle dynamic and cascading parameters in a command object..
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

mlmccCommented:
Commands do not support dynamic parameters.  

Is the filter that complex that it needs to be a command or are you trying to do calculations in the command?

mlmcc
0
LinInDenverAuthor Commented:
Hi mlmcc,

Unfortunately it is that complex :( This report is being converted from SSRS, and I guess cascading parameters work differently in that solution.

I've been working on this for 3 days, and I think there just isn't a good way to do it. In addition, I found a bug in Crystal where multiple values selected on cascading parameters doesn't even work properly.
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
mlmccCommented:
You can' t do a cascade with multiple since it doesn't know which one to select or how to tie them together.  That is as Crsyatl chose to implement.

mlmcc

0
LinInDenverAuthor Commented:
I found a document that states multiples are supported with cascading in XI, so I was hoping they'd be supported in 2008. I downloaded the document at work, and can send to you if you want a copy?
0
mlmccCommented:
Can you post a link to it?

mlmcc
0
LinInDenverAuthor Commented:
Sorry, i'm not sure what the original link was. I have attached the document here. It has references to multiple values on pages 8, 12/13 (figure 11)
Dynamic-Cascading-Prompting.pdf
0
LinInDenverAuthor Commented:
Hi guys - here is what I ended up doing.


Here is what I've come up with and it seems to be working.

1) I embedded my existing report as a sub report
2) In Main report, I created my dynamic cascading parameter, and clicked on each level to "Make Parameter".
3) I was then able to link all 6 related parameters (it is a 6 level cascade) from main into my sub report (where I kept my existing single parameters inside the command statement).

That part is now working great - unfortunately I'm missing items from my parameter list, so I've been working with MaxRecordSet in my registry but still don't have that part working. I'll keep playing with that part today and searching the forums!
0
gs79Commented:
This is close to what I suggested. Guess you are on a right track. Let me know if you succeed.
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
DB Reporting Tools

From novice to tech pro — start learning today.