Is it possible to create dynamic sqls as source for List of Values.

[Former Member]

Hi,

I have a requirement wherein there are three drop-downs(independent of one another), and based on the selection of any of these dropdowns I would like to populate my fourth drop-down.

Ex: 1st Dropdown: Instrument Type

      2nd Dropdown: Instrument Class

      3rd Dropdown: Trading Type

4th Dropdown : Positions (with multi-select) could be populated by selecting any of the above three dropdowns. Initially it will show all positions in the list but when any of the above dropdowns are selected, it will refresh the list based on that particular selection.

The three dropdowns comes from different tables. Is it possible to create a LoV with the above scenario? If so, could you please brief me the process.

Thanks,

Sathish.

[JWiseman · Accepted solution]

hello Sathish,

there is a potential to build this out by customizing the webelements functions.  i use the word potential as this isn't the classic dcp model that the cascading controls in webelements use.

before you consider this path though, you'd need to be running businessobjects enterprise or crystal reports server and the individual controls are limited to 1000 values (due to the array limit of 1000 values in crystal.)

-jamie

[Former Member · Accepted solution]

Hi Jamie,

Sounds really exciting. I have never used/heard that such third party controls exist for CR. Just to confirm, are these webelements reliable for production, because once I migrate a report with this functionality, we would like to ensure nothing untoward happens in production, basically no side-effects. Ours is a critical production system, where the BO interface is used almost throughout the day by BAs all over the world, so I would like to ensure if the webelements are stable for production. Has this interface being certified by SAP for acting as a interface layer for use in Crystal reports? Because I would have to speak to my managers before thinking of implementing it in our production environment. We are using BO 3.x and Crystal Reports 2008 in our environment.

Do you have any documentation for the webelements, wherein it provides me a sample for creating dynamic controls and report jumping etc?

The features seem to be full of value and really the ones which give maximum benefit out of Crystal reports. Just by reading the feature set, this should be integrated by SAP into their Crystal reports pack, not sure why are not they doing it.

Anyway, thanks a ton for showing another way to potentially achieve solve my issue.

Crystal reports, is no doubt the finest enterprise reporting tool in the market, but always felt its UI layer lacked the punch, it should be more user friendly and should give developers more power to use it. Simple, example would be a drop-down set should have options like selecting primary source, secondary and so on. So, if primary source is NULL (in my case Instrument Type) try to fetch data based on secondary source (Instrument Class) else go to the third type.

Just have a Source field: and have comma separated sources for the control to fetch from.

Thanks,

Sathish.

[JWiseman · Accepted solution]

hi Sathish,

to answer your questions on webelements; no they are not certified by sap to act as an interface level. webelements are controls (javascript and html) that sit on top of a feature which is supported: pass-through html. webelements are not officially supported themselves. given that, when you use the functions you are basically creating another report object. this new report is the same as any other report in the way that it must honour database and enterprise securities. like any report, the more objects you add to it can affect report performance to some extent...e.g. if you add many arrays to a report you might see some performance decrease.

to use this function library, you must ensure that pass-through html is activated properly on your boe system. this allows reports that include embedded html and javascript to run properly without having that code print as text. to activate pass-through html you must make changes to a web.xml file. if you were to patch your boe system, you'd want to ensure that this web.xml change was re-added to your system to avoid any delays in the webelements reports rendering the controls properly...when pass-through html is not activated, the controls show up as text on the report. the steps to activate pass-through are in the webelements user guide in the main download.

to use webelements you create a crystal report and then add in the controls that you want using crystal's custom functions feature..i.e. you add the appropriate custom function to a formula. the end outcome is an opendocument url that targets another report. all of the code the renders the html / javascript is on the report object itself...you can right click on any of the reports in the report designer (preview mode) to copy and paste the code into a text editor to have a look at it.

in the main download there is a fairly large user guide as well as some sample reports. in the sample reports there is a dcp report that may be beneficial for you to look at. the basic work-flow of these dcp's is similar to those you find in the crystal reports prompt dialogue...you start at prompt 1 > go to the lower level prompt 2 > go to the lowest level prompt 3. however, you can customize the functions that are in the webelements library...here's the catch...you'd want to find someone that work with javascript as you'd need to customize the webuilder function as well as the weselectcascade function.

one other option is to activate pass-through html on your boe system and create your own controls as well...you may find this easier than trying to go through all of the webelements control code and editing it to work for you. i.e. if you can find javascript code that does that you want, it may be easier.

i hope that this helps,

jamie

[Former Member · Accepted solution]

Thanks Jamie for the explanation. Will look into this.

[Former Member]

Dont know if this would work but, create a three independent parameter in the main report of Crystal and then pass the values in the subreport custom sql to fetch you 4 prompt value "Position". There might be a chance that you achieve what you are looking for, but not in a way to want.

[ido_millet]

One of the 3rd-party Crystal Reports Viewers listed at http://kenhamady.com/bookmarks.html has an additional implementation of dynamic and cascading parameters that uses other Crystal reports as parameter dialogs.

So your report can use a parameter that is actually another Crystal report, and that report in turn can have it's own 3 dynamic parameters.  This solves not only your scenario, but also other scenarios, such as the need to use different data sources, or the need to have type-in parameters at the top of the cascade.

[Former Member]

Thanks for the response but I'm not sure it would work. Are you suggesting create a command prompt in the sub-report with custom sql to take the i/p(s) (either the Instrument Class/Type or Trading Type) and have the i/p drop-down in the sub-report take them as input?

Hmm..Let me give that a try.

[Former Member]

Hii,

If you can use crystal report for enterprise then your requirement will be applied on universe level and create report on it ... http://scn.sap.com/docs/DOC-21803

Regards,

Dhaval

[Former Member]

Hi Dhaval,

Not sure what do you mean by Universe level. I use Crystal reports 2008 for development, Business View Manager for creating the prompts and we have business users use the Infoview for running the reports on demand and we also have some periodic scheduling of the reports. I create stored procedures in Oracle database. Don't use Universe connections at all, the requirement I have is for UI display to enable easy selection/restriction for users.

Thanks,

Sathish.

[DellSC]

Unfortunately, there is no way to do this as you describe in Crystal.  Do the first three relate to each other in any way?  Are these mutually exclusive?  I have some thoughts rumbling around in the back of my head about how you might do this using cascading dynamic prompts.

-Dell

[DellSC]

If a user can select Instrument Type OR Instrument Class OR Trading Type, but not any combination of the above, you can create a command to pull the data for the prompts.  However, you would have to re-do how you have the prompts set up.  The logic for the command would look something like this:

(

Select

  'Instrument Type' as param_type,

  instrument_type_code as code,

  instrument_type_description as description,

  position_code,

  position_description

from instrument_types as i

  inner join position as p on i.linking_field = p.linking_field

UNION ALL

Select

  'Instrument_Class' as param_type,

  instrument_class_code as code,

  instrument_class_description as description,

  position_code,

  position_description

from instrument_class as i

  inner join position as p on i.linking_field=p.linking_field

UNION ALL

Select

  'Trading_Type' as param_type,

  trading_type_code as code,

  trading_type_descrption as description,

  position_code,

  position_description

from trading_type as t

  inner join position as p on t.linking_field = p.linking_field

)

order by 1, 3, 5

This type of situation is the one time I will recommend using a command in report with other tables or commands because you will NOT link this command to any of the other data in your report - its sole purpose is to provide data for the parameters.

You would then set up a dynamic cascading prompt that has 3 parameters:

- The first param displays the param_type field for the user to select.

- The second param displays the code/description aliased fields which will be automatically filtered based on what was selected in the first part.

- The third param displays the position code/description based on what's selected in the first two parameters.

You might even be able to set this up so that users can select multiple entries in the first param so that they can combine instrument type, instrument class, and trading type.

-Dell

[Former Member]

Thanks Dell, will look into the idea you have proposed. Seems to be ingenious one to solve the issue.

Are you suggesting me to create Command Object in my report with the above Union clauses and put this in a main report and this main report's sole purpose would be to provide for user selection and I can have my original reports implemented as sub-reports. Right?

If so, can I create the dynamic cascading prompts at the Crystal report side itself? Till now, I have used Business View Manager for creating dynamic prompts and only used Crystal side for Static prompts.

Please clarify my points, before I proceed with your nice proposal for the solution.

[DellSC]

No.  You would include both the data for the report and this command in the main report.  However, you would NOT link them together - Crystal will tell you it's generally not supported, but it does work.  The command would be used in the parameters only.  The rest of the data for the report would be used in the report only.  I've used this technique successfully in a number of reports.

-Dell