I’ve adjusted the control to a date editor, however, I am getting a blank report.
Have you tried other dates in the custom field (like far in the future, and far in the past)? Same thing if it is blank?
Can you post the query expression from the RDL?
Yes, still getting blanks. Below is the query expression.
="SELECT
T1.Ordernum,
T1.AuthCode,
T1.CardNumber,
T1.PNRef,
T1.ResponseMsg,
T1.Result,
T1.TranDate,
T1.TranTotal,
T1.Calc_CardTypeDesc,
T1.Calc_CustomerID,
T1.Calc_CustomerName,
T1.Calc_Expiration,
T1.Calc_UnMaskedCard,
T1.ReferencePNRef,
T1.TranType,
T1.TranTypeDesc,
T1.Calc_EffAmount,
T1.CurrencyCode,
T1.DocTranTotal,
T1.Calc_DocEffAmount,
T2.Date01
FROM CreditTran_" + Parameters!TableGuid.Value + " T1"
- " JOIN CallContextBPMData_" + Parameters!TableGuid.Value + " T2
ON T1.TranDate <= T2.Date01"
The FROM part of the expression should be:
FROM CreditTran_" + Parameters!TableGuid.Value + " T1"
+ " JOIN CallContextBPMData_" + Parameters!TableGuid.Value + " T2
ON T1.TranDate <= T2.Date01"
I think you have an * where a + should be
Try changing " JOIN CallContextBPMData_" to " INNER JOIN CallContextBPMData_".
If you get the same thing (no results), then try
" LEFT JOIN CallContextBPMData_"
That should make it so that the BPM data doesn’t limit the data used by the RDL. The report should look like it normally would (just using the Trans after Date parameter). If that renders okay, add field for T2.Date01 to the report body, to see what appears when it runs.
Looks like the Left Join was the key! Your help on this is very much appreciated!
The LEFT JOIN won’t limit the records based on your extra date parameter.
Show the Date01 field on the report to make sure your value is being passed to the report.
It looks like the Date01 value is not being passed to the report as it is blank after adding a column for the Date01 in the report to view.
Make sure that after you added the new control for the Date Editor, that you set the binding.
When I bind Date01 to the date filter the report does not run/return results.
When I bind Date01 to the date filter the report does not run/return results.
Is there a reason I cannot use T1.TranDate as the date parameter instead of T2.Date01?
T1.TranDate holds the data you want to compare against your parameter. The parameter is passed to the report as T2.Date01.
I am getting no results for the Date01 field still. Don’t I need to add T2.Date01 to the rdd? Reference below current customization and rdl query expression.
You don’t need to add anything to the RDD, because the CallContextBPMData table is inherent to all RDD’s.
We don’t use CC’s so I did a test with another form - the Material Transaction Detail. While this has a start and End date, I can add a date field and limit the report to only show records between the native Start date and My date.
Then in the Report’s RDL I changed the query for the main table (PartTran in my case)
And added the Query field to the dataset’s properties.
I added a field to the header of my report just to show that the value from the Form was coming through.
I then uploaded the report
Now when run this with the following:
My report is limited to transactions in the range of 1/1/20 - 5/1/20, even though the “To:” date is 12/1/20.
If I set my custom date to after 12/1/2020, then the report includes Material Transactions prior to 12/1/20 (the native “To:” date) and it is 39 pages long…
Add the field to the header to make sure the CallContextBPMData value is coming through.
Perfect, thank you for your detailed response.
Is there perhaps some trick in 2021.1.11 when trying to Bind to the Character fields in CallContextBPMData? I am simply just trying to display the bound information in the page header.
I added an epiUltraCombo dropdown with a list of values onto the form, pick my value, preview the report, and I do not see the field value on the report…
PS: Aha, CCRpt is just the standard RDD report with Table datasources. I think this may vary significantly because in my case, I am using an RDD with a BAQ as a datasource. My guess is all the parameters go to the BAQ. Then the link is broken between BAQ and the SSRS. Or, there is another path for variables between BAQ and SSRS, or not. Essentially, what if I wanted to show in my SSRS Page header, any of the parameters on the report form? If that is possible, then the Character0x fields can work. But I am going on a limb to assume that the process is slightly different or the params table link is “different”.