BAQ Parameters Not Working on SSRS Report

[CaudilC (Connor Caudill)]

Hi, I’m trying to run a report on a custom table that keeps track of attendance records for employees. The way the system is set up, records are disabled after a year of being added, so running this report on specific days is important. To do this I wanted to set up a parameter that would allow the person running the report to input a date, and the report would run to show all records that are valid a year before that input date. The way filters and options are set up in the BAQ Report Designer, it seemed impossible to fill that requirement, so I wanted to put those parameters on the BAQ itself and have those pushed to be report parameters. I tried to follow what was done here

Passing Report Parameters to a BAQ Report

Ahhh… I misunderstood. You parameter is truly an input and needs to be used inside the BAQ. Not as a “filter” on the results of the BAQ. If you made the BAQ Report first, there’s a chance there’s a conflict in the Report Style’s Data Definition. When you make a BAQ Report, that BAQReport ID becomes both the ReportID AND the Data Definition used in Report Style [image] A “blank” RDD will be created using that ReportID (E10H-Test in my example). I can add the BAQ and criteria sets and ever…

but ran into an error that I could not understand.

This error confuses me because I did input the parameters. Maybe someone else has done this before and can answer what went wrong.

[ckrusen (Calvin Krusen)]

@CaudilC

I wanted to set up a parameter that would allow the person running the report to input a date, and the report would run to show all records that are valid a year before that input date. The way filters and options are set up in the BAQ Report Designer, it seemed impossible to fill that requirement,

The easy solution is to make a Calc column in the BAQ that is the record date + 1 year (use the DATEDIFF(,,) function. Then have the BAQ Report option filed be on the calc column.

A record with a date of 1/15/2020 would make the calc field be 1/15/2021. If the user enters 1/1/2021, 1/15/2021 is > 1/1/2021

[hkeric.wci (Haso Keric)]

As of 10.2.300+

The BAQ Report Designer is considered legacy and should not be used going forward, it has been replaced with Dynamic Reports… so you would create the BAQ still and use Report Style and Report Data Definition to link the BAQ and that allows you to set input fields to BAQ Params.

Or you use the Legacy and use filters.

The Help Section is under this category:

[CaudilC (Connor Caudill)]

I tried this solution and ran into an issue, while your idea did partially work, there are always records being added to this table. So, entering a date sufficiently far back will also show records past that date that were added, since those dates will also be greater than the date entered as an option field. For example, entering a date like 6/1/2020 will show a ‘future’ record like 6/19/20, which we also don’t want.

[ckrusen (Calvin Krusen)]

You could filter out “future dates” in the RDL. I’m pretty sure the Option field’s value is in the RptParam dataset.

[CaudilC (Connor Caudill)]

I looked into this as well, the option fields are in that dataset, but the date field I am comparing them against are within the ReportResult dataset, and Report Builder throws an error when I try to compare values from two different datasets.

[ckrusen (Calvin Krusen)]

Filter out “future dates” in the tablix, not the query expression.

[CaudilC (Connor Caudill)]

When I put this filter in the tablix itself, if I go through the categories and add the values that way, it complains about having aggregate functions in FilterExpressions, but if I simply put values like [Option_2] and [Date01] it complains again about having two different datasets being compared. I’ve also tried mix-matching them so one is the normal field and the other is the ‘aggregate function’

[ckrusen (Calvin Krusen)]

Just so I’m clear … The goal is:

• A BAQ Report with a date field as in input (call it inDate).
• The BAQ results include a date field (call it recDate)
• The report should only show records where:
  • recDate >= (inDate - 1yr) AND recDate <= inDate

[CaudilC (Connor Caudill)]

Yes that is correct

[ckrusen (Calvin Krusen)]

You can edit the query expression for the BAQReportResult to join the Rpt Parameter table, like:

="SELECT T1.[PartTran_TranNum],T1.[PartTran_TranDate],T1.[PartTran_TranType],T1.[PartTran_PartNum],T1.[Calculated_TranDateYearPrior], T2.Option01 "
+ " FROM dbo.[BAQReportResult_" + Parameters!TableGuid.Value + "] AS T1"
+ " JOIN dbo.[BAQReportParameter_" + Parameters!TableGuid.Value + "] AS T2 ON 1=1"

edit

Just to be clear. I had to:

1. Add the "AS T1" to the query’s original FROM clause
2. Add "T1." in front of the original field names in the SELECT clause
3. Add the the JOIN ... AS T2 ON 1=1 clause
4. Add ", T2.Option01 " (don’t forget the comma, and trailing space) to the SELECT clause
5. Add the field to the

Now you can refer to BAQReportResults.Option01 in the tablix’s filter.

[CaudilC (Connor Caudill)]

Thank you so much for the patient help. Yes that ended up working, I could access the Option field and used it to filter the results and everything matched what we have in our database for a similar query.