We have an ssrs baq report with two sub reports connected to sql view data sets. We are trying to pass date ranges to the two sub reports to filter the sql view data sets.
How do we reference these baq report parameters to filter the sql view data sets?
Pretty sure you could do it two ways.
Reference first row in dataset
in your parameter expression just write
=First(Fields!Option01.Value, "BAQReportParameter")
or
Adding the values into the BAQReportResult DataSet
SELECT T1.blah, T1.blahtwo, (SELECT S1.ParameterYouWantToAdd FROM BAQReportParameter_"
+ Parameters!TableGuid.Value + ") as ParameterYouWantToAdd
FROM BAQReportResult_" + Parameters!TableGuid.Value + " T1
then add the field into the dataset, then pass to the subreport as normal
I have been working with Aaron on this issue. To define this more precisely we are attaching a dataset from a SQL view to this BAQ SSRS Report that we are trying to filter using the BAQ Report Parameters we are setting up in the Option Fields,
So that when we run this report from E10 we can enter different dates for the BAQ report and the SQL View dataset(s), other view datasets to be added if we can get this to work.
We tried several examples by @josecgomez and others to get the filter working in the SQL View via SSRS Query Designer using a Where statement. Never got a filtered result from the SQL View dataset in the report.
We tried a hard date in the SQL statement for the view and the result in the SSRS Query Designer and get the correct result,
But if we run the report via the test report with these hard dates in the SQL View dataset Where statement we get the entire dataset in our result not the filtered result in the SSRS Query Designer.
Not sure what we are doing wrong.
We were able to display the ReportParam_Date03 on the report so we know they are getting through.
If you can print the field then you are getting it , all you need to do is pass down those fields into the subreport
Create parameters in the subreport that gets pushed from the first report and filter the dataset by the parameter.
Just to make sure we are on the same page. I went into the Subreport Properties and added the parameter shown below with “StartDate” being tied to the expression below. “EndDate” is tied to a similar expression but to Date04 instead.
I then went into my subreport’s Dataset Properties and removed the Where statement from the query and added this filter. OrderHed_Date01 is the date field in the subreport that we are trying to filter using the inputs from Date03 and Date04.
After that I ran into this:
So I went into the Report Parameter Properties and added default values to the “StartDate” and “EndDate” parameters.
And tested the report again and got this:
Did you link the parameters from the parent report to the sub report? In the sub report properties?
So really the problem is nothing with the subreport, is more just getting the Parameters you pass down into the SQL Statement of the subreport
If you have verified that the Parameter shows in the subreport, then you should be able to filter by adding the parameters in your Subreport’s dataset
Then should be able to reference them in your subquery’s query expression with @ signs
I am pretty baffled at this point because it should work. This is the code in my dataset query and the query parameters I added.
And I have the parent report parameters set up this way with both the default and available values set to Date03 for the @StartDate parameter and Date04 for @EndDate.
I am now consistently getting the error message: “Error: Subreport could not be shown.” when I test my report.
Do you need to create parameters EndDate and StartDate in the parent report?
Why not add a dataset to the subreport, which is identical to the BAQReportParamter of the parent. Then set the defaults of the subreport’s parameters like you were doing on the parent.
Don’t forget pass the GUID to the subreport for use in its BAQReportParamter query expression.
Ignore the above (most of it)
Your main report needs only one parameter: TableGuid
Your subreport should have two: FromDate, ToDate. Neither should have any defaults
The subreport’s dataset query should reference those parameters. Like:
The subreport object in the main report, should have the two parameters:
=First(Fields!Option01.Value, "BAQReportParameter")
I have tried your suggestion, but now when I run my test report I keep getting this error.
Just to reiterate, I am using a sql view as my subreport not another BAQ report incase that helps narrow down some possible solutions.
If you hard code values in the subreports parameters properties (replace the =First(...) expression with a fixed value), do you get the same error?
And if you run the subreport by itself, it asks for the sub’s parameter s, and runs okay?
Yes
Remove the parameters from the WHERE clause in in the subreport, and add fields to display the parameters (also in the subreport). Then try running the parent report again.
The subreport will contain all the data from the view, but more importantly, need to check if the values from the parent report are getting through to the sub.
And I think you should be passing the BAQReportParameter.Option__ fields. Not the .Date__ fields to the subreport.
Remove the default value from the parameter. you shouldn’t have to do anything else with the parameter once you give it a name and data type.
This is all you should have to do:
Add the parameter in the subreport, and then pass the value from the main report, right click the subreport object in the main, pass the field to the parameter there and you should be all good.
I have gone back in my subreport and only added the parameters “StartDate” which is tied to Date03 in my main report and “EndDate” which is tied to Date04. When I run the report I get this new message that I haven’t seen before.
Does your subreport’s dataset still contain these? Believe these need to be there for the query to pull the values from the report
No I removed that because it was referring to the old parameters that I removed.
Add those back in to the dataset. Believe your issues were related to the default value you set on the parameters
I am sorry I am very new to Epicor and I really appreciate your help and patience.
This is where the @StartDate and @EndDate parameters populated before:
I feel like I am going in circles because if I add them back to my dataset then they auto populate back in that Parameter folder and I am running into the same errors.
I am wondering if an RDD approach would be easier for this scenario?
