Baq Report Designer Option Fields in Sql Statements

ckrusen · April 8, 2021, 3:03pm

Is this from the parent RDL?

And where did the OrderHistoryDataSet come from?

Edit

looking back, it looks like that is your sql view. If you can retrieve that info in the parent, then why do you need a subreport?

cfinley · April 8, 2021, 3:05pm

No worries. Everyone starts somewhere.

StartDate & EndDate need added to the subreport RDL. Just add the name, and datatype. Nothing else

aaronb · April 8, 2021, 3:08pm

@ckrusen because I have separate reports that I am trying to bring into this main report. Would it be easier to just remake the tables in this report and do the filtering that way?

cfinley · April 8, 2021, 3:13pm

Main report on the left, subreport on the right. Subreport can then use the parameters in the dataset
WHERE T1.FieldName = @StartDate

ckrusen · April 8, 2021, 3:25pm

If you need the info in different ways in the Parent and Sub, then a sub is appropriate.

I’d try each of the following. When it stops working will indicate what you need to fix.

The BAQ Report without the datasource or dataset for the view, and no subreport. Also, include fields in the [page header to display BAQReportParam.Date01 thru Date03, and Option01 through Option03 (you’ll need to use the First(...) function). This is basically the original BAQ Report, and shows the values the user enters when submitting the report.
The BAQ Report with the datasource and dataset for the view, but no subreport. Don’t put any parameters in the OrderHistory’s dataset’s query expression. This checks that the View is accessible.
As above, but with parameters added to the parent report. No defaults on the params. Add those params to the WHERE clause of the OrderHist dataset. Testing it should prompt the user for the parameters, and use them in the OrderHist query, thus limiting the records.
Add in the subreport, and place static values for the subs inputs.
As above, but with the sub inputs set to the params. Testing it should prompt you for the params (these are the parents).
If its working up to here, then set the defaults of the parents params.

One dumb thing it could be - which I doubt, but worth checking - is if you’re using the same parameter names in both the parent and sub. There could be some confusuon.

aaronb · April 8, 2021, 4:27pm

I have made it to #4. Where should I place the static values? What are the sub inputs?

ckrusen · April 8, 2021, 4:30pm

Before you do that, the subreport runs by itself, correct?

And it asks for the parameters (when tested in SSRS Report Builder)?

aaronb · April 8, 2021, 4:33pm

Yes it prompts me for a start date and end date and those are functioning correctly.

ckrusen · April 8, 2021, 4:49pm

Okay on to #4.

Insert your subreport into the parent. While editing the Parent, select the subreport and view it’s properties.

In the subs properties, set it like

But hardcode values into the Paramter values. Preferably the same ones you used when testing the subreport by itself.

Edit

Make sure your hardcoded expression returns the proper type.

Like =CDate("04/20/2021")

aaronb · April 8, 2021, 5:47pm

You want me to put this in the Subreport Properties or the Subreport’s Dataset Properties?

ckrusen · April 8, 2021, 5:48pm

The properties of the subreport object that was inserted into the main report.

If I may borrow Finley’s screenshot …

aaronb · April 8, 2021, 5:53pm

Okay I added that and tested the report in the BAQ Report Designer and getting this error again:

ckrusen · April 8, 2021, 5:56pm

In the Subreport Properties dialog (shown above), the dropdown under the Name column shows the parameters that the actual subreport RDL has defined?

aaronb · April 8, 2021, 5:57pm

Yes

ckrusen · April 8, 2021, 6:09pm

One last thing to try … In the subreport RDL, create defaults for the parameters, enter fixed dates, such that they don’t filter any data. Like the default for StartDate being 1/1/2000, and the one for end date being 12/31/2021. Test run the subreport by itself in Report Builder. Does it run without prompting or generating any errors?

If they don’t contain any confidential info, could you upload the two RDL’s (the parent and the sub)? I know I wouldn’t be able to run them, but it would be easier to look at, than trying to describe how they’re set up.

aaronb · April 8, 2021, 6:14pm

Setting the defaults to a specific date did work without any errors in the report builder.

ckrusen · April 8, 2021, 6:17pm

“did work” - did you mena just testing the Subreport in the report builder, right?

If so, try running the parent report. It will do one of the following:

Not run
Run but the subreport is wrong because it is using the defaults, and not the value passed from the main report.
Run as desired.

aaronb · April 8, 2021, 6:33pm

Yes just testing the subreport in the report builder worked. It gave the same error as before when I tried testing the parent report in the BAQ Report Designer.

ckrusen · April 8, 2021, 6:38pm

So the Parent runs when the subreport is removed. But doesn’t when the sub report is in the parent?

If it’s not too much trouble, can you change the names of the params in the subreport to SubStarDate and SubEndDate? Just so we’re sure which parameters the error is referring to.

Any chance you could upload (or direct message me) the two RDL files? I know I wouldn’t be able to run them, but it would give me a clearer picture.

aaronb · April 8, 2021, 6:52pm

I feel that the issue definitely lies in the parent report. What parameters should I have set up in the parent report?