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
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
crgary_tx
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
mlmcc
ASKER
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?
Can you post a link to it?
mlmcc
mlmcc
ASKER
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
Dynamic-Cascading-Prompting.pdf
ASKER
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!
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!
This is close to what I suggested. Guess you are on a right track. Let me know if you succeed.