SSRS Report Help

utaylor · February 22, 2024, 3:09pm

I was getting there @jkane hhaah! I was just trying to teach and build up to that.

jkane · February 22, 2024, 3:10pm

mgordon · February 22, 2024, 3:18pm

So with this code, will I end up having “SELECT” in the query twice? Once in the parentheses with the new dataset query we had created and then again after the parentheses where the main dataset query would be starting? Also, how does the SplitQ function work?

utaylor · February 22, 2024, 3:19pm

Yo,

Here’s the outcome and here’s the query in my dataset.

For this test I put "item1~item2~item3~item4 in the quotedtl.quotecomment field as your UD field.

utaylor · February 22, 2024, 3:21pm

So now, @mgordon , like @jkane was saying, since we can write the query to get the results we want, we can join those results back to the main query in the main dataset by using a CTE. I won’t get into CTE’s right now, but you can google them.

I’ll now put this logic in the main dataset to get what you want.

I also have never done what @jkane is suggesting using a canned report so @jkane if you want to tag team this and finish this up with the CTE that’d be cool!

RIP Carl Weathers https://youtu.be/zWhwUGzw3G4?t=59

utaylor · February 22, 2024, 3:41pm

@mgordon

Here’s the quote form as you wanted it and I’ll give you the query in the main dataset that @jkane was advising you to manipulate/transform. @jkane if I did it wrong please let me know.

Again @mgordon, you cannot copy and paste things into the canned report text query so you’ll need to figure it all out yourself in terms of formatting, but a potential solution has been provided.

You’ll also need to figure out your grouping because QuoteQty is in there as a joined table. But this gets you more or less there.

=“with test as (SELECT T1.Calc_TaxMethod, T1.DocQuoteAmt,T1.DocTotalPotential, T1.DocTotalGrossValue, T1.Calc_LineMiscTotal, T1.Calc_HeadMiscTotal, T1.Calc_TotalTaxAmt, T1.DateQuoted,T1.ExpirationDate,T1.QuoteComment,T1.QuoteNum,T1.Reference,T1.Calc_CompanyAddr,T1.Calc_CompFax,T1.Calc_CompPhone,T1.Calc_CurSymbol,T1.Calc_CustContact,T1.Calc_CustContactEMail,T1.Calc_CustFax,T1.Calc_CustPartOpts,T1.Calc_CustPhone,T1.Calc_EMail,T1.Calc_Message1,T1.Calc_Message2,T1.Calc_QuoteAddr,T1.Currency_CurrDesc,T1.Customer_Name,T1.Calc_HasHeadMisc,T2.SellingExpectedUM_UOMSymbol,T2.Company,T2.DiscountPercent,T2.DocDiscount,T2.DisplaySeq,T2.DrawNum,T2.KitParentLine,T2.KitFlag,T2.KitPricing,T2.KitPrintCompsInv,T2.KitShipComplete,T2.LeadTime,T2.PartNum,T2.QuoteComment as QuoteDtl_QuoteComment,T2.QuoteLine,T2.QuoteNum as QuoteDtl_QuoteNum,T2.RevisionNum,T2.XPartNum,T2.XRevisionNum,T2.Calc_LineDesc,T2.Calc_HasMisc, T2.Calc_Duration, T2.Calc_Modifier, T2.Calc_Mate, T2.Calc_Labor, T2.Calc_Misc, T2.ContractNum, T2.RenewalNbr, T2.SellingExpectedQty, T2.DocExpUnitPrice, T2.DocExtPriceDtl, T3.DocUnitPrice,T3.PricePerCode,T3.QtyNum,T3.SalesUM,T3.SellingQuantity,T3.UnitPrice,T3.Calc_NetPrice,T3.Calc_UMDescription
FROM QuoteHed_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN QuoteDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.QuoteNum = T2.QuoteNum
LEFT OUTER JOIN QuoteQty_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.QuoteNum = T3.QuoteNum AND T2.QuoteLine = T3.QuoteLine), ConfiguratorInputs as (SELECT T1.Company,T1.QuoteNum,T2.QuoteLine,T2.QuoteComment as QuoteDtl_QuoteComment, value FROM QuoteHed_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN QuoteDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.QuoteNum = T2.QuoteNum
LEFT OUTER JOIN QuoteQty_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.QuoteNum = T3.QuoteNum AND T2.QuoteLine = T3.QuoteLine CROSS APPLY STRING_SPLIT(T2.QuoteComment, ‘~’)) SELECT * FROM test left outer join ConfiguratorInputs c on test.company = c.company and test.quotenum = c.quotenum and test.quoteline = c.quoteline"

mgordon · February 22, 2024, 3:45pm

Thanks for the help @utaylor !!! I’m currently working on getting a CTE into my main dataset query and really appreciate all the helpful tips! I’ll keep you all posted on how it goes!

utaylor · February 22, 2024, 3:46pm

Please do!

utaylor · February 22, 2024, 3:48pm

Honestly though @mgordon I am trying to reference a VB function that is found in many of the epicor reports that splits things like this, for example the company address in your quote form… That’s a split function.

If we copy and paste that function and create our own that assigns the iterative output to a variable, we can then print that variable.

What I am saying is, pass your UD field to the split function and then have the split function append that value as well as a carriage return at the end to a string variable until it has finished splitting the UD field.

What you’re left with is a variable with string value and carriage returns that you can then honor in your report output. That way you’re not manipulating datasets for the sake of formatting.

utaylor · February 22, 2024, 3:50pm

See this, this is how that company address for your company on the default on your quote form is being created.

jkane · February 22, 2024, 3:50pm

Actually, @utaylor , I was advocating doing it another way. Hold on.

utaylor · February 22, 2024, 4:08pm

here @mgordon, put this code in the report.

then in your line group, make a new text box with a placeholder expression of this:

=Code.ConcatenateWithParagraphs(Fields!QuoteDtl_QuoteComment.Value)

Public Function ConcatenateWithParagraphs(ByVal strSplit As String) As String
    Dim strArr() As String
    Dim i As Integer
    Dim sReturn As String = ""

    strArr = strSplit.Split("~")

    For i = 0 To strArr.Length - 1
        If i = 0 Then
            sReturn += strArr(i)
        Else
            sReturn += vbCrLf & vbCrLf & strArr(i)
        End If
    Next

    Return sReturn
End Function

jkane · February 22, 2024, 4:22pm

Can’t guarantee this will work as I cannot test it ( v8), but the concept works as I have done it multiple times before. If there are any errors, I can help address.

=";WITH SplitQ (Company, QuoteNum, QuoteLine, Items, Ordinal)
AS
(
	SELECT T2.Company, T2.QuoteNum, T2.QuoteLine, STRING_SPLIT(T2.QuoteComment, '~', 1)
	FROM QuoteDtl_" + Parameters!TableGuid.Value + " T2
)

SELECT T1.Calc_TaxMethod,
 T1.DocQuoteAmt,
T1.DocTotalPotential,
T1.DocTotalGrossValue,
T1.Calc_LineMiscTotal,
T1.Calc_HeadMiscTotal, 
T1.Calc_TotalTaxAmt, 
T1.DateQuoted,
T1.ExpirationDate,
T1.QuoteComment,
T1.QuoteNum,
T1.Reference,
T1.Calc_CompanyAddr,
T1.Calc_CompFax,
T1.Calc_CompPhone,
T1.Calc_CurSymbol,
T1.Calc_CustContact,
T1.Calc_CustContactEMail,
T1.Calc_CustFax,
T1.Calc_CustPartOpts,
T1.Calc_CustPhone,
T1.Calc_EMail,
T1.Calc_Message1,
T1.Calc_Message2,
T1.Calc_QuoteAddr,
T1.Currency_CurrDesc,
T1.Customer_Name,
T1.Calc_HasHeadMisc,
T2.SellingExpectedUM_UOMSymbol,
T2.Company,
T2.DiscountPercent,
T2.DocDiscount,
T2.DisplaySeq,
T2.DrawNum,
T2.KitParentLine,
T2.KitFlag,
T2.KitPricing,
T2.KitPrintCompsInv,
T2.KitShipComplete,
T2.LeadTime,
T2.PartNum,
T2.QuoteComment as QuoteDtl_QuoteComment,
T2.QuoteLine,
T2.QuoteNum as QuoteDtl_QuoteNum,
T2.RevisionNum,
T2.XPartNum,
T2.XRevisionNum,
T2.Calc_LineDesc,
T2.Calc_HasMisc, 
T2.Calc_Duration,
 T2.Calc_Modifier, 
T2.Calc_Mate,
 T2.Calc_Labor,
 T2.Calc_Misc,
 T2.ContractNum,
 T2.RenewalNbr, 
T2.SellingExpectedQty,
T2.DocExpUnitPrice,
 T2.DocExtPriceDtl,
 T3.DocUnitPrice,
T3.PricePerCode,
T3.QtyNum,
T3.SalesUM,
T3.SellingQuantity,
T3.UnitPrice,
T3.Calc_NetPrice,
T3.Calc_UMDescription,
SplitQ.Company AS SplitQ_Company,
SplitQ.QuoteNum AS SplitQ_QuoteNum,
SplitQ.QuoteLine AS SplitQ_QuoteLine,
SplitQ.Items,
SplitQ.Ordinal

FROM QuoteHed_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN QuoteDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.QuoteNum = T2.QuoteNum
LEFT OUTER JOIN QuoteQty_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.QuoteNum = T3.QuoteNum AND T2.QuoteLine = T3.QuoteLine
LEFT OUTER JOIN SplitQ
ON T2.Company = SplitQ.Company AND T2.QuoteNum = SplitQ.QuoteNum AND T2.QuoteLine = SplitQ.QuoteLine"

utaylor · February 22, 2024, 4:53pm

yeah that’s a lot cleaner

I didn’t really look at what I was doing

mgordon · February 22, 2024, 5:01pm

Tried this out and only edit I had to make was to change the STRING_SPLIT portion and put it after a CROSS APPLY underneath the FROM in my CTE. Before that, I was getting an error saying STRING_SPLIT was not a built-in function.

However, it’s still just repeating the first element in the big string. Not sure if my moving it down is what’s causing this, but I don’t know how the SPLIT_STRING was not working before.

utaylor · February 22, 2024, 5:10pm

Do this instead in your code and it will give you bullets like you want:

Public Function SplitNextLine(ByVal strSplit As String) As String
    Dim strArr() As String
    Dim i As Integer
    Dim sReturn As String = ""

    strArr = strSplit.Split("~")

    For i = 0 To strArr.Length - 1
        If i = 0 Then
            sReturn += Chr(149) & " " & strArr(i) ' Add bullet point before the first item
        Else
            sReturn += vbCrLf & Chr(149) & " " & strArr(i) ' Add bullet point before subsequent items
        End If
    Next

    Return sReturn
End Function

jkane · February 22, 2024, 5:21pm

I was not sure if the Ordinal would work or not as that is SQL Server 2022.

Try this.

=";WITH SplitQ (Company, QuoteNum, QuoteLine, Items)
AS
(
	SELECT T2.Company, T2.QuoteNum, T2.QuoteLine, STRING_SPLIT(T2.QuoteComment, '~')
	FROM QuoteDtl_" + Parameters!TableGuid.Value + " T2
)

SELECT T1.Calc_TaxMethod,
 T1.DocQuoteAmt,
T1.DocTotalPotential,
T1.DocTotalGrossValue,
T1.Calc_LineMiscTotal,
T1.Calc_HeadMiscTotal, 
T1.Calc_TotalTaxAmt, 
T1.DateQuoted,
T1.ExpirationDate,
T1.QuoteComment,
T1.QuoteNum,
T1.Reference,
T1.Calc_CompanyAddr,
T1.Calc_CompFax,
T1.Calc_CompPhone,
T1.Calc_CurSymbol,
T1.Calc_CustContact,
T1.Calc_CustContactEMail,
T1.Calc_CustFax,
T1.Calc_CustPartOpts,
T1.Calc_CustPhone,
T1.Calc_EMail,
T1.Calc_Message1,
T1.Calc_Message2,
T1.Calc_QuoteAddr,
T1.Currency_CurrDesc,
T1.Customer_Name,
T1.Calc_HasHeadMisc,
T2.SellingExpectedUM_UOMSymbol,
T2.Company,
T2.DiscountPercent,
T2.DocDiscount,
T2.DisplaySeq,
T2.DrawNum,
T2.KitParentLine,
T2.KitFlag,
T2.KitPricing,
T2.KitPrintCompsInv,
T2.KitShipComplete,
T2.LeadTime,
T2.PartNum,
T2.QuoteComment as QuoteDtl_QuoteComment,
T2.QuoteLine,
T2.QuoteNum as QuoteDtl_QuoteNum,
T2.RevisionNum,
T2.XPartNum,
T2.XRevisionNum,
T2.Calc_LineDesc,
T2.Calc_HasMisc, 
T2.Calc_Duration,
 T2.Calc_Modifier, 
T2.Calc_Mate,
 T2.Calc_Labor,
 T2.Calc_Misc,
 T2.ContractNum,
 T2.RenewalNbr, 
T2.SellingExpectedQty,
T2.DocExpUnitPrice,
 T2.DocExtPriceDtl,
 T3.DocUnitPrice,
T3.PricePerCode,
T3.QtyNum,
T3.SalesUM,
T3.SellingQuantity,
T3.UnitPrice,
T3.Calc_NetPrice,
T3.Calc_UMDescription,
SplitQ.Company AS SplitQ_Company,
SplitQ.QuoteNum AS SplitQ_QuoteNum,
SplitQ.QuoteLine AS SplitQ_QuoteLine,
SplitQ.Items

FROM QuoteHed_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN QuoteDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.QuoteNum = T2.QuoteNum
LEFT OUTER JOIN QuoteQty_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.QuoteNum = T3.QuoteNum AND T2.QuoteLine = T3.QuoteLine
LEFT OUTER JOIN SplitQ
ON T2.Company = SplitQ.Company AND T2.QuoteNum = SplitQ.QuoteNum AND T2.QuoteLine = SplitQ.QuoteLine"

mgordon · February 22, 2024, 5:34pm

Unfortunately still getting this pesky error:

Thinking @utaylor 's suggestion with the coded bullet points may be the best option for this. Now for the fun part… getting it to cooperate with the other scenarios we use that report for…

utaylor · February 22, 2024, 5:36pm

You’re fine Matthew, just a hidden expression on the row for whatever scenario you know you want.

Show the row when X = true, else don’t.

mgordon · February 22, 2024, 5:38pm

Yeah, there were just a lot of IIF statements that I had used to sort through different scenarios haha. Thanks for all the help and information!

Topic		Replies	Views
Using Split in SSRS Epicor ERP 10 reports	37	6642	October 22, 2020
BAQ: Calculated Field Concatenate Rows to single field Experts' Corner	132	56926	June 14, 2023
SSRS Report Inconsistencies Epicor ERP 10 epicor , ssrs , reports	13	1074	January 6, 2021
Crystal report formula help Epicor ERP 9	21	1880	January 6, 2017
SSRS Conditional value Off Topic ssrs	14	3351	September 10, 2019

SSRS Report Help

Related Topics