Hi,

I have the table that has MasterID and ToCompany columns. The requirements for the reports are:

1. Show only the items for a specified MasterID. The parameter must be set programmatically in code, the user can not change it on the report for security reasons. Also preferably the parameter should not be displayed on the report.
2. Optionally filter items by ToCompany. The list of companies should be filtered by specified MasterID.

I started with meeting the first requirement and have no problem handling it by itself. I set up the parameter MasterID, set the parameter in code, and then display the report. Since the parameter has been set already, there’s no prompt displayed to the user, the report is displayed to a user showing the records filtered by the specified MasterID. So far this is exactly what I need. Also, the sql query has the where clause for the MasterID. Note the parameter could be either static or dynamic, works the same way. I went with the static one for performance reasons. I have MasterID, I don’t need to retrieve the list of MasterIDs from the database.

Now I need to extend the functionality of the report to meet the second requirement. Here’s what I have tried:

1. I added the second parameter ToCompany independent of the MasterID. It’s dynamic, optional. The first requirement is still met, everything works as described above. The user only prompted for the second parameter ToCompany. The report shows the records based on the user selection. The sql query has the where clause for the MasterID and optionally for ToCompany. Everything works as I needed except for one thing. ToCompany parameter displays the list of all companies for all MasterIDs completely disregarding the first parameter. I have tried this approach because I thought that after MasterID has been set, the sql query is re-generated right away and then the report would display the list for ToCompany based on the updated sql query. Clearly, that was the incorrect assumption.

2. I removed all the parameters and created new cascading dynamic parameters MasterID-ToCompany. I set MasterID in code, and then display the report.  However the user is still prompted for MasterID, MasterID that was set in code is not selected, the user has to select MasterID from the list. After the user selected MasterID from the list, ToCompany parameter displays the filtered list based on the picked MasterID. The report shows the records based on the parameters. So now the first requirement is not met. First issue is even though MasterID is set in code, it’s not selected in the list. Second, the user sees the complete list of MasterIDs and can selected other ones which is not acceptable at all.

3. I’ve tried to play around with parameter options, but that did not work. I could not get better results then my first two attempts.

Please let me know if you have some ideas. Thanks in advance.

Hi lockwelle,

First let me clarify, because I think I misled you into thinking that I have one static MasterID. I don’t. When I said I set up the parameter MasterID as static, I meant the option of report’s parameter (List of Values: static or dynamic). In fact, the user selects the master from the list to view it’s items. When the user wants to generate the report, the code grabs MasterID of currently selected master and supplies it to the report.

If I understood correctly, you’re suggesting to set up the command parameter for MasterID and hardcode the value for it. As you can see now, it’s not an option because I don’t have one static MasterID. And as far as I know, you can’t set the command’s parameters dynamically. Please let me know if you know the way to do that.

Actually the report initially was set up with the command object. But I have to get away from it for some reasons. One of them has to do with the parameters. Again I could not use the command parameters, because I could not set them dynamically. I set up the report parameters. But I could not find a way to pass them to the command. The sql query would be exactly as specified in the command. It would not get updated with the where clause for the parameters. So the report would retrieve all the records from the database and then filter them based on the specified parameters.

Thanks for the suggestion.