asked on

Crystal Reports dynamic params with Stored procedure

Hi Experts,

Currently , I am using SSRS reports which has 3 params (states,districts and Cities) based on user selection(can have multiple values), passed into a stored procedure.
The stored procedure has a temp table which is populated after performing calculations on some columns based on joining many different tables. It returns the temp table output.
Now we are moving to Crysral reports 11 . What is best approach in reusing this SP in CR ? From what I have read here, CR works differently from SSRS in that it calls SP and then allow CR engine to perform operation? where as in SSRS, I select from 3 params and hit View report button which run the SP with params passed into

Raghavendra Hullur

Hi,

There are 2 ways in which you can create dynamic parameters(cascading in this case I think based state, district and city combination).

One way is to use Business View Manager (provided as a separate tool from SAP) and create List of Values, store on crystal server repository and use the same in Crystal Reports as a report level filter since you are using a stored proc as source instead of a command object.

Other way would be to create a command object with a select statement select the combination of state, district and city to create 3 select objects to use them to create a dynamic filter combination and use those as report level filters.

I hope that makes sense.

Do let me know if further info is required and also share a copy of the report with some saved sample data if you are allowed to do so.

-Raghavendra

G Ram

ASKER

Hello Raghavendra,

I have to check if we have Business View Manager and if it is indeed , then .. Is the LOV based off set of Sprocs which is fired based on the selection of country and then state .... ?

Command object is the one I was reading about and I tried but I was not able to make it dynamic and I thought it was because CR fires all the referenced Sprocs when the report is loaded and here I am looking for a dynamic LOV based on user selection. Can I have the steps ?

Thanks,

Mike McCracken

To the best of my knowledge Command parameters cannot be dynamic.

I also don't believe you can pass multiple values in a parameter since Crystal considers the multiple value parameters to be an array. It has to be done through a formula and a subreport.

mlmcc

G Ram

ASKER

Thanks Mike !

It has to be done through a formula and a subreport.

Could you please point out through a simple example ,being a newbie in CR and coming from a SSRS background ?

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.