Check Register Report

(William) #1

We have an report style called CashPro Import. This have some info but they want to add the address for the customer. Problem is, every time I add it in I get an error telling me to trace down the error.

Here is the original:

=“SELECT T1.BankAcctID,T1.Company,T1.[Description],T1.Calc_BaseCurrencySymbol,T1.Calc_CurrencyCount, T2.BankAcctID as CheckHed_BankAcctID,T2.CheckAmt,T2.CheckDate,T2.CheckNum,T2.Company as CheckHed_Company,T2.CurrencyCode,T2.DocCheckAmt,T2.HeadNum,T2.ManualPrint,T2.Name,T2.VendorNum,T2.Voided,T2.Calc_DocCheckAmt,T2.Calc_VendorID,T2.Calc_VoidManualCheck,T2.Calc_VendorCurrency,T2.CurrencyCode_CurrDesc,T2.CurrencyCode_CurrencyID,T2.CurrencyCode_CurrName,T2.CurrencyCode_CurrSymbol,CAST( T2.CurrencyCode_DecimalsGeneral as nvarchar ) as CurrencyCode_DecimalsGeneral,T2.CurrencyCode_DocumentDesc,T2.Calc_DspBankCheckAmt
FROM BankAcct_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN CheckHed_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.BankAcctID = T2.BankAcctID"

I was thinking I just needed to add in T2.Address1, T2.Address2, etc. But that errors out. Ideas on what I am missing here?

(William) #2


(William) #3

I activated remote errors and noticed it said Invalid Column Name State. I was trying to reference CheckHed_State. After I fixed that I got the following error:

The Value expression for the text box ‘CheckHed_State’ refers to the field ‘CheckHed_State’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.

Same case??

(William) #4

Figured this one out, didn’t change the name in the table. So now back to Invalid Column. I am referencing the T2, which is CheckHed and CheckHed has the address info in it. But for some reason, this query will not let it in.

(al maragni (USA - NY/NJ)) #5

I think you’ll need to confirm
(1) the fields are in the DATASET generated out of the ReportDataDefinition (if this is NOT a BAQRPT)
(2) the fields are LISTED in the FIELDS section of the DATASET PROPERTIES / FIELDS in the SSRS report.

(William) #6

Thanks @amaragni It is mentioned in the Fields, and in the Query. Not sure about the ReportDataDefinition, as I am not sure where that is.

(William) #7

@amaragni I found the Report Data Definitions and the field sI am wanting are excluded. Can these be removed from the exclusion list? If so, I am unable to figure it out.

(al maragni (USA - NY/NJ)) #8

yes - just UNCHECK the EXCLUSIONS box . the fields will be put into the data set.
then you’ll need to setup the fields onthe SSRS DATA SET FIELDS as i stated b4

(al maragni (USA - NY/NJ)) #9

sorry … also note: i think the system disallows changes to base forms. so if the dataset is a standard set - you will need to duplicate/copy it before you can make changes to the excludsion list.

(William) #10

@amaragni Thanks for the input! After I copied the report data definition I remembered seeing it in the Report Style. So I changed the one i needed to the copied definition and everything is working. Once again, Thank you for the help.