Good morning,
I have created my first report that uses more than one BAQ. I created three BAQs that all get joined together on Company and Department. I created a RDD with all three BAQs, then created 2 relations to join the tables. Finally, I exported the RDL and made my report.
I have a department productivity report that looks at yesterday, MTD, and YTD for each department’s productivity rate. In our case this is being calculated from the LaborDtl table using LaborHours, and the PayrollDate. I am using the built-in Current date, to filter the LaborDtl tables on payroll date for CurrentDate -1 Day.
This works well. I am getting the right data, and it seems accurate enough. However, today is Monday, and that means that yesterday there was no data to show. So my report returns NaN in all the fields. This is correct, since we didn’t have any work yesterday, but now I can see that I can’t get the data for Friday unless I come in and run it Saturday (or setup some schedule or something.)
This leads me back to the point of the post. I am not using parameters right now, and I don’t have access to BAQReport Options or Filters, since I made my own RDD and Report Style.
How can I get my custom RDD to filter for a parameterized date range? So that on Monday my manger can run this report for last Friday (and Saturday if folks were working). Do I need to add a customization to the little form that pops up to print my report? Is there something I can do in the RDD?
Remember, I have three BAQs, and I want to filter them all by the same date range. The first BAQ pulls all my totals for just the last day’s worth of work by department. The other two BAQs do virtually the same thing for month to date, and year to date. The To date part being the date I want to parameterize.
Mr. Nate: I create Parameter in the BAQ and then create a calculated field that points to the Parameter. That way your date range and/or “Parameters” can be passed to the SSRS and then added.
I generally try to add my filter options to the BAQ as well. That way the dataset is filtered before SSRS get it.
I can’t use the BAQ Report Designer since I made my own RDD that combines more than one BAQ. So, I don’t have the ability to filters, or add options.
I could add a parameter to the BAQs, but I would have to add the same parameter in all three BAQs, and then the user would be asked for the date three times.
I see that the RDD has report criteria sets, criteria prompt, and criteria filter. But I can’t figure how they are used. Can I use these options to filter three BAQs for the same date range? The first BAQ should return all the data for the date given, the second BAQ will pull all the data from the first day of the month up through the date given, and the last BAQ will pull all the data from the first day of the year up through the date given.
You do have the ability! I just learned this recently. I am assuming you made your own RDD to be able to add multiple BAQs? In your RDD, you can create Criteria Sets. See below. Then you specify the criteria set you want to invoke in your report style.
I tried this approach. When I open by report to see the print preview button and choose a style, instead I get this error and this window:
Application Error
Exception caught in: Ice.Lib.EpiClientLib
Error Detail
============
Message: Control 'grdFilterList' of type EpiUltraGrid has an invalid binding to view FilterList1.
Program: Ice.Lib.EpiClientLib.dll
Method: EventSetup
Client Stack Trace
==================
at Ice.Lib.Framework.EpiUltraGrid.EventSetup()
at Ice.Lib.Framework.EpiUltraGrid.set_EpiTransaction(IEpiTransaction value)
at Ice.UI.Rpt.DynamicCriteriaReport.FilterListPanel.set_ReportTrans(Transaction value)
at Ice.UI.Rpt.DynamicCriteriaReport.FilterPanel.BuildFilterPanel(Int32 filterID, String viewName, RptCriteriaFilterRow filterRow, Transaction trans)
at Ice.UI.Rpt.DynamicCriteriaReport.FilterPanel.AddCustomSheetToDockManager(RptCriteriaFilterRow filterRow, Transaction trans)
at Ice.UI.Rpt.DynamicCriteriaReport.FilterPanel.BuildDynamicFilters(RptCriteriaDataDataSet dsReportCriteria, Transaction trans)
at Ice.UI.Rpt.DynamicCriteriaReport.DynamicCriteriaReportForm.BuildUIControls()
at Ice.UI.Rpt.DynamicCriteriaReport.DynamicCriteriaReportForm.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at Ice.Lib.Framework.EpiForm.WndProc(Message& message)
I am getting closer, but I don’t have any data fields in my BAQs’ outputs. I was filtering my BAQs in the BAQ using the Payroll date field, but I can’t filter by that in the RDD since payroll isn’t a field in my output.
Is there a way to filter a BAQ by parameters, using a prompt in the RDD?
I cant figure out how to add report criteria mappings like you have. I have added the report criteria set. and I added a criteria prompt. I don’t have an option to add the criteria mappings.
I think that was it. I replaced my parameters thinking they wouldn’t be usable. I still have to get it working but I think this is close. Thanks Kevin!
I am still not quite sure how this should be setup. I have three BAQs to filter. For one BAQ I want to filter on PayrollDate >= @StartDate and PayrollDate <= @EndDate. For the second BAQ, I want to filter by PayrollDate <= @EndDate (I already set the start date to the first day of the month.) I want to filter the third BAQ the same as the second, except that I already put in the first of the year as the start date.
So in BAQ 1 I will have 2 parameters, and BAQs 2 and 3 will only have 1 parameter.
I want to set the start and end date only once, and then have all three BAQs use those date values.
So in my RDD I need to setup a Report Criteria Set first. Then what? I can see my parameters from the BAQs under Criteria Mappings. Just not sure how to put all this together.
When I try to change them, the field just goes blank. I can’t type in a value there. All my parameters int he underlying BAQs have the same exact names (EndDate).
How can I make it so that I don’t have to supply the same parameter value three times?
