Selecting Records in SSRS Report Based on Parameter for Distinct Field Data

I am attempting to create an SSRS BAQ report where the end user selects a specific date range to pull in sales orders that have been invoiced. Out of all the records returned in that date range, each record has a field entitled ShipTo_City. We would then like the end user to be able to select ShipTo Cities based on a DISTINCT list of Ship To Cities within that date range. I have achieved this in the SSRS Report Developer in Microsoft Visual Studio and works perfectly, but when I try to implement it within Epicor, I get the following error message. Obviously, I have missed a step, or what I am attempting to do is not possible with SSRS.

“Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: This report requires a default or user-defined value for the report parameter ‘ShipToCity’. To run or subscribe to this report, you must provide a parameter value. —> Microsoft.ReportingServices.Diagnostics.Utilities.ReportParameterValueNotSetException: This report requires a default or user-defined value for the report parameter ‘ShipToCity’. To run or subscribe to this report, you must provide a parameter value.”

Following are screenshots illustrating the way I have the parameter and dataset configured:

image

So you want to add the ShipTo_City as a BAQ Report filter, but limit the selectable cities to ones on invoices that are in that date range. Correct?

Any reason why they can’t be allowed to select a city that wouldn’t have an invoice within that date range?

You can think of the BAQ Rpt Options and Filters as being AND’d together. Only invoices that are in that date range AND have the shipto of a city selected, will be sent to the SSRS.

You can’t use SSRS Parameters because Epicor is rendering the PDF and not using the SSRS viewer.

You either have to put the parameter in the BAQ and filter the dataset to be rendered by SSRS or you have to use a standard SSRS report and link to the URL instead of using a BAQ report.

You can combine BAQ reports with standard SSRS subreports if you wanted to pull additional information from another DB based on a BAQ dataset :grinning:

1 Like

Yes Calvin, that is correct. However, the ShipTo_City field is not available under the Filters tab but under the options tab ShipTo_City is available. That being said, the end user has no way of knowing what cities are going to appear on the report based on their date range. On the options tab they would need to know what cities to populate in to the field as a list. It would be a shot in the dark to guess which ones unless they ran the report for a date range in its entirety, looked at all of the cities that appear on the report and then run it again populating the option field ‘ShipTo_City’ on the ROI with the list of cities they want. There is also the element of them mistyping the name of the city…

Thanks John, that makes sense. It’s not the like the parameter field that is available in Crystal Reports. I was hoping to be able to adapt it in SSRS…

It takes a little bit of extra work, but you can add the ShipTo_City to the filter. It requires adding the field in your BAQ as a Like column, and creating a Context Menu entry.

image

Running the report shows:

image

With the filter showing:

image

This isn’t super elegnat, as searching for the city by clicking the City button basically just shows all the possible ShipTo’s. It doesn’t limit the search results to ShipTo’s with Invoices that fall in the range on the first sheet. But if you can filter the search window by city, and then select any shipto with the matching cities.

It’s actually using the City of the selected ShipTo. So you don’t have to select every shipto for the city you want.

For example, the image below shows several ShipTo’s with the city Dover. The first is even in a different state. Selecting just that first ShipTo for DOVER (the one for Dover DE),

image

will make the BAQ retrun any invoice with a ShipTo city of Dover. See the Report below. None of those were the DE dove.

Adding to my previous post …

You might be able to make a BAQ to be used as a Search BAQ, so when searching for the cities, you provide dates, and it only returns Shiptos from invoices in that date range.

Thanks for these tips Calvin. I will give it a shot and see if I can make it work for us. I appreciate everyone’s feedback.

Cheers!

Here’s a link to the tricks you have to do to get non-key fields to show in a BAQ Report Filter