SSRS Help - Add Project to Aged Receivables

jnbadger · July 28, 2020, 5:31pm

I am needing help adding Project ID to the Aged Receivables report. I’ve tried a few things so far:

Edit the data definition to include (un-exclude) the Project ID column from the InvcDtl table. Report runs w/o error, but the field shows up blank.
Because of that, I want to try getting the Project ID from the OrderDtl table. I’m thinking join OrderDtl with InvcDtl, on the Company, OrderNum, and OrderLine columns. I tried doing this by editing the data definition and syncing. When I click “sync dataset,” I get an error about the sync being skipped because the query expression is too complex.
That has led me to editing the query expression in SSRS manually, which I don’t have much experience with. Here is the original query expression: ArAging_OriginalQuery.txt (2.3 KB)

I tried adding this to the FROM clause, but getting an error:

LEFT OUTER JOIN OrderDtl_" +Parameters!TableGuid.Value + " T9
ON T4.Company = T9.Company AND T4.OrderNum = T9.OrderNum AND T4.OrderLine = T9.OrderLine

Thank you for reading and help in advance!!

Mark_Wonsil · July 28, 2020, 5:36pm

I think you’ll find this is the problem. Search for sync dataset in the group and you’ll find that you don’t use it for non-BAQ reports. You may have to throw out everything with the sync and start over.

ckrusen · July 28, 2020, 5:38pm

Ouch … You almost never want to do that. If you’re lucky, the failure to complete means your RDL wasn’t updated.

One issue might be that you have line breaks in the expression’s strings:

edit

Maybe that’s not an issue anymore. As the original query expression does have line breaks in the middle of the strings.

jnbadger · July 28, 2020, 5:47pm

I’ve had success with it before, but yes lesson learned I started a new copy after that step.

jnbadger · July 28, 2020, 5:50pm

Yep, started a new copy after the “sync dataset” failed

In the query expression you showed, that is actually the one that is working, and I’m trying to add to that. All I did was copy and paste from Report Builder into Notepad.

jkane · July 28, 2020, 5:50pm

Scratch all of what I said before.

The AR aging report buckets multiple records. Where are you trying to display the project number?

ckrusen · July 28, 2020, 5:56pm

I may get chatsized for suggesting this…

But when I’ve run into troubles adding tables to RDD, and trying to update the RDL’s query, I’ve decided to just make a subreport that connects directly to the DB. Add parameters to it, and use them for selecting the appropriate records from the DB.

Couple of down sides …

this method will always use the current value from the DB. An archived RDD based report saves the data from the time it was run. If you re-printed the report (from the archive), the sub report would fetch the current data. Where as the RDD with the tables added, would have saved the values in those _GUID tables.
If the DB gets moved to a different server, you’d need to update the sub-reports connection string.

jnbadger · July 28, 2020, 6:04pm

I need it to display for each invoice record on the report.

jnbadger · July 28, 2020, 6:09pm

Hmm, I haven’t done subreports before… I’ve been having trouble connecting to our DB directly; need to get with my IT guy to resolve. I actually have another project on my plate where I need to add a subreport, and I have one of your posts on the forum saved for that

But I will give it a shot if it comes to that! Thank you!!

So with subreports, it is true that you can pass values from the subreport to the main report? Because I will need to add the Project ID to the lines next to each invoice number on the AR Aging.

jkane · July 28, 2020, 6:11pm

Where in the RDD did you find the Project Number? I’m looking at the ARAgng RDD.

ckrusen · July 28, 2020, 6:14pm

Not really “passing” values from the subreport, but rather think of the subreport (which would consist of one field), as a field in your report. You won’t be able to “reference” it in the parent report (like use a number in a calculation, or hide/show a row based on the subreport’s contents).

But if you need to just display the info, then it works in a pinch.

Like I said, not the best solution, but it could satisfy your users until you get the “proper” one working.

jkane · July 28, 2020, 6:16pm

Please try pasting this into your rdl and see if it works.

 ="SELECT T1.Calc_CustName as ARDtls_Calc_CustName,
 T2.Calc_LegalNumber,
 T1.Calc_MulChildren as ARDtls_Calc_MulChildren,
 T1.Calc_ParentCustID as ARDtls_Calc_ParentCustID,
 T1.Calc_RptTitle2,
 T1.Calc_Summary,
  T2.InvoiceDate,
 T2.InvoiceNum,
 T2.PONum,
 T2.Calc_AgeBaseAll,
 T2.Calc_AgeCurAll,
 T2.Calc_AgeInvAmt1,
 T2.Calc_AgeInvAmt2,
 T2.Calc_AgeInvAmt3,
 T2.Calc_AgeInvAmt4,
 T2.Calc_AgeInvAmt5,
 T2.Calc_AgeInvAmt6,
 T2.Calc_AgeLbl1,
 T2.Calc_AgeLbl2,
 T2.Calc_AgeLbl3,
 T2.Calc_AgeLbl4,
 T2.Calc_AgeLbl5,
 T2.Calc_AgeLbl6,
 T2.Calc_BlockedFinChrg,
 T2.Calc_BlockedRemLetters,
 T2.Calc_Company,
 T2.Calc_ContPer,
 T2.Calc_ContPh,
 T2.Calc_crMemo,
 T2.Calc_curDesc,
 T2.Calc_CurDueDate,
 T2.Calc_CurrCode,
 T2.Calc_CustID,
 T2.Calc_CustName as Calc_CustName,
 CAST( T2.Calc_Index as nvarchar ) as Calc_Index,
 CAST( T2.Calc_MulChildren as nvarchar ) as Calc_MulChildren,
 T2.Calc_ParentCustID as Calc_ParentCustID,
 T2.Calc_PoDNNbr,
 T2.Calc_RptARAcctID,
 T2.Calc_RptUserID,
 T2.Calc_TierLevelNum,
 T2.Calc_TopCustID,
 CAST( T2.Calc_BasCurrency as nvarchar ) as Calc_BasCurrency,
  T3.TierLevelNum,
 T3.Calc_ARTotal1,
 T3.Calc_ARTotal2,
 T3.Calc_ARTotal3,
 T3.Calc_ARTotal4,
 T3.Calc_ARTotal5,
 T3.Calc_ARTotal6,
 T3.Calc_LevelCustID,
 T3.Calc_LevelDesc,
 T4.Calc_DecimalsGeneral,
  T5.InvoiceLine,
  T5.ProdCode,
 T5.ProjectID,
  T8.RetAmt
  FROM ARPrnt_" +Parameters!TableGuid.Value  + " T1 
  join Company_" +Parameters!TableGuid.Value  + " T4 
    on T1.Calc_Company=T4.Company 
 LEFT OUTER JOIN RlsHead_" +Parameters!TableGuid.Value  + " T3
   ON T1.Calc_Company = T3.Calc_Company AND T1.Calc_CustID = T3.Calc_CustID
  LEFT OUTER JOIN ARDtls_" +Parameters!TableGuid.Value  + " T2
   ON T1.Calc_Company = T2.Calc_Company AND T1.Calc_ParentCustID = T2.Calc_ParentCustID AND T1.Calc_CustID = T2.Calc_CustID and T1.Calc_RptARAcctID = T2.Calc_RptARAcctID  
 LEFT OUTER JOIN (SELECT T4.Company, T4.InvoiceNum, T4.ProdCode, T4.InvoiceLine, T4.ProjectID FROM InvcDtl_" +Parameters!TableGuid.Value  + " T4
 	WHERE (T4.InvoiceLine = (SELECT MIN(T6.InvoiceLine) FROM InvcDtl_" +Parameters!TableGuid.Value  + " T6
  WHERE T6.Company = T4.Company AND T6.InvoiceNum = T4.InvoiceNum))) T5
 	ON T5.Company = T2.Company AND T5.InvoiceNum = T2.InvoiceNum 	
 LEFT OUTER JOIN (SELECT T7.Company, T7.InvoiceNum, SUM(T7.DocMiscAmt) AS RetAmt FROM InvcMisc_" +Parameters!TableGuid.Value  + " T7 GROUP BY T7.Company, T7.InvoiceNum) T8
 		ON T8.Company = T2.Company AND T8.InvoiceNum = T2.InvoiceNum"

jnbadger · July 28, 2020, 6:22pm

Where in the RDD did you find the Project Number?

It was under the InvcDtl table. I’m now realizing this is not in the system RDD. I am working off of one revision from the standard report. The person that built this version must have added the InvcDtl table:

ckrusen · July 28, 2020, 6:23pm

The ArAgng RDD doesn’t have the InvcDtl table in it

. Are you possibly starting with an already modified RDD?

jkane · July 28, 2020, 6:23pm

It does not look like you need to add the invoice detail table based off of the original query you posted. If you try my query, I think it will work using the original RDD.

jnbadger · July 28, 2020, 6:25pm

Thank you for that explanation!! I could see that as a decent option, as the user is just trying to avoid looking up the project ID in the system.

ckrusen · July 28, 2020, 6:29pm

John - please use the formatting commands (place 3 ticks - the character on the tilde key) before and after your code (each set of three on their own line).

Else the text gets “tweaked”. Your first quote character was converted to a fancy quote -

Yields:

="SELECT T1.Calc_CustName, 
T2.CalcLegalNumber "

jkane · July 28, 2020, 6:31pm

Thanks. Do I need to call out that it is SQL (like I just learned to do with C#)? or is just the 3 ticks enough?

ckrusen · July 28, 2020, 6:38pm

I usually just use the three ticks. worst case, it doesn’t do the high lighting properly. Like in my example, that is meant to be one string. But because of the line break it doesn’t treat it as such. I could have used the text (where you’ve used the SQL or C# before)

using the ```text modifier, it does no syntax highlighting, but preserves line breaks and non-fancy quotes:

="SELECT T1.Calc_CustName, 
T2.CalcLegalNumber "

And to be technical, that is VB code, not SQL. The resulting text from the expression is a SQL expression. But the code itself is not.

jnbadger · July 28, 2020, 6:38pm

I copied the base report, then replace the ARDTLS data set query expression with your code. Before trying to add the Project ID field, I ran the report and got an error.

It’s one of those generic looking errors that says, “Query execution failed for dataset ‘ARDTLS’…” I don’t have access or know how to retrieve the rest of the error details from the report server.