March 23, 2016 at 9:08 am
I need to position a If than statement using an expression:
=Parameters!FundId.Value=Fields!Fund_Name.Value
I need =Parameters!FundId.Value if this equals Fields!Fund_Name.Value than show =Parameters!FundId.Value
any ideas?
March 23, 2016 at 9:16 am
I need =Parameters!FundId.Value if this equals Fields!Fund_Name.Value than show =Parameters!FundId.Value
=IIF(Fields!Fund_Name.Value = Parameters!FundID.Value,Parameters!FundID.Value,NULL)
?
March 23, 2016 at 9:20 am
The Value expression for the textrun ‘Fund_Name.Paragraphs[0].TextRuns[0]’ contains an error: [BC30822] 'NULL' is not declared. 'Null' constant is no longer supported; use 'System.DBNull' instead.
March 23, 2016 at 9:41 am
oh, use "" instead.
March 23, 2016 at 9:49 am
pietlinden (3/23/2016)
oh, use "" instead.
That works to display a Null however I now need it to remove the
record from the report all together it was doing this befor but then its
like its not joined in the SQL?
select top 100
pc.accountnbr 'Account',
qf.description 'Fund Name',
pc.checknbr 'Check Number',
pc.checkprintdate As StartD,
pc.checkprintdate As EndD,
p.fullname 'Pay Provider',
pc.amountpaid 'Paid AMT',
pc.paydiscount 'Intrest',
pc.advanceapplied 'Advance' ,
pc.checkamt 'Check AMT'
from paycheck pc
left join qfund qf on pc.fundid = qf.fundid
left join payment pm on pm.paymentid = pc.paymentid
left join provider p on pm.provid = p.provid
where
pc.checkprintdate Between @StartD And @EndD
March 23, 2016 at 9:56 am
pietlinden (3/23/2016)
oh, use "" instead.
That works to display a Null however I now need it to remove the
record from the report all together it was doing this befor but then its
like its not joined in the SQL?
select top 100
pc.accountnbr 'Account',
qf.description 'Fund Name',
pc.checknbr 'Check Number',
pc.checkprintdate As StartD,
pc.checkprintdate As EndD,
p.fullname 'Pay Provider',
pc.amountpaid 'Paid AMT',
pc.paydiscount 'Intrest',
pc.advanceapplied 'Advance' ,
pc.checkamt 'Check AMT'
from paycheck pc
left join qfund qf on pc.fundid = qf.fundid
left join payment pm on pm.paymentid = pc.paymentid
left join provider p on pm.provid = p.provid
where
pc.checkprintdate Between @StartD And @EndD
March 23, 2016 at 12:38 pm
Young Jedi,
A LEFT/RIGHT/OUTER join is "lossless". In other words, you'll get NULLs propagated on the "outside" table if you use an outer join. It's the way outer joins are supposed to work. If you want to eliminate the record entirely from the result set, then you need to change the join from LEFT JOIN to INNER JOIN. The best way to get your head around this is to play with some queries and a couple of really small tables (maybe 5 records in each table, at the most!).
Compare the output of your query to this (in SSMS, if you can):
SELECT TOP 100
pc.accountnbr 'Account',
qf.description 'Fund Name',
pc.checknbr 'Check Number',
pc.checkprintdate As StartD,
pc.checkprintdate As EndD,
p.fullname 'Pay Provider',
pc.amountpaid 'Paid AMT',
pc.paydiscount 'Intrest',
pc.advanceapplied 'Advance' ,
pc.checkamt 'Check AMT'
FROM paycheck pc
INNER JOIN qfund qf ON pc.fundid = qf.fundid
INNER JOIN payment pm ON pm.paymentid = pc.paymentid
INNER JOIN provider p ON pm.provid = p.provid
WHERE
pc.checkprintdate BETWEEN @StartD AND @EndD
You'll see when you run this that since INNER joins eliminate records on both sides of the join that have no matches, you should have far fewer records returned. The next big question is are there any tables for which you want to display ALL the records whether they have related records or not? Those tables go on the "left" side of the join.
Say you wanted to show all Paychecks (pc) whether they had Payments or not:
SELECT pc.PaycheckID
, pc.CheckDate
, pm.PaymentDate
, pm.PaymentAmount
FROM Paycheck pc LEFT JOIN Payment pm ON pc.PaymentID = pm.PaymentID;
If you used this
SELECT pc.PaycheckID
, pc.CheckDate
, pm.PaymentDate
, pm.PaymentAmount
FROM Paycheck pc INNER JOIN Payment pm ON pc.PaymentID = pm.PaymentID;
any Paychecks without Payments against them would be eliminated from the result.
Hope that makes sense. If you don't have rights to create queries against the SQL database, you can do this stuff in Access too, if you have it. (I know, "Access" is a dirty word around here, but the JOIN part of the language is identical to T-SQL, the "extensions" are very different... )
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply