Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SQL help for a report ...
Microsoft Dynamics GP (Archived)

SQL help for a report with parameters needed

(0) ShareShare
ReportReport
Posted on by Trancefrank 955

HI I am converting a Crystal Reports, to SSRS using SQL Server 2005. We are using Great Plains and these are reports in SSRS that have/are being converted from Crystal Reports.

Custnmbr is in the table Customerbudget and this is the parameter, but we only have customer name as the parameter, in all of our reports, this is the standard. I need to have a join that will take the Customername from the user and locate the correct number keeping this SQL working correctly as it is. AS I am new to SQL I am not certain how to add in this join to the existing and working SQL:

SELECT Sum("SOP10200"."XTNDPRCE") XTNDPRCE, Sum("SOP10200"."QUANTITY") QUANTITY,
DATEADD(M,DATEDIFF(M,0,SOP10100.DOCDATE),0) ID, "IV40600"."UserCatLongDescr", "IV00101"."ITEMNMBR",
"IV00101"."ITEMDESC",AVG(CustomerBudget.BudgetAmount) as Budget

FROM (((("dbo"."SOP10100" "SOP10100" INNER JOIN "dbo"."SOP10200" "SOP10200"
ON "SOP10100"."SOPNUMBE"="SOP10200"."SOPNUMBE")
INNER JOIN
"dbo"."CustomerBudget" "CustomerBudget" ON "SOP10100"."CUSTNMBR"="CustomerBudget"."CUSTNMBR")
LEFT OUTER JOIN "dbo"."SOP10106" "SOP10106"
ON ("SOP10100"."SOPTYPE"="SOP10106"."SOPTYPE")
AND ("SOP10100"."SOPNUMBE"="SOP10106"."SOPNUMBE"))
INNER JOIN "dbo"."IV00101" "IV00101"
ON "SOP10200"."ITEMNMBR"="IV00101"."ITEMNMBR")
INNER JOIN "dbo"."IV40600" "IV40600"
ON ("IV00101"."ITMGEDSC"="IV40600"."USCATVAL")
AND ("CustomerBudget"."USCATVAL"="IV40600"."USCATVAL")

WHERE "CustomerBudget"."CUSTNMBR"=@CUSTNMBR
AND "SOP10100"."SOPTYPE"=1 and (SOP10100.DOCDATE between @FromDate and @ToDate)

group by DATEADD(M,DATEDIFF(M,0,SOP10100.DOCDATE),0), "IV40600"."UserCatLongDescr", "IV00101"."ITEMNMBR",
"IV00101"."ITEMDESC"
Order by iv40600.UserCatLongDescr, iv00101.ITEMDESC

*This post is locked for comments

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    Re: SQL help for a report with parameters needed

    You could add another join at the end:

    INNER JOIN dbo.RM00101 on RM00101.CUSTNMBR = CustomerBudget.CUSTNMBR

    and change the first line of your WHERE clause to:

    WHERE RM00101.CUSTNAME = @CustomerName

    For what it's worth, I would be concerned when doing this because it's entirely possible to have 2 or more customers with an identical name.  In which case your report would give you the results for all of them added together.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: SQL help for a report with parameters needed

    SELECT Sum("SOP10200"."XTNDPRCE") XTNDPRCE, Sum("SOP10200"."QUANTITY") QUANTITY,
    DATEADD(M,DATEDIFF(M,0,SOP10100.DOCDATE),0) ID, "IV40600"."UserCatLongDescr", "IV00101"."ITEMNMBR",
    "IV00101"."ITEMDESC",AVG(CustomerBudget.BudgetAmount) as Budget

    FROM (((("dbo"."SOP10100" "SOP10100" INNER JOIN "dbo"."SOP10200" "SOP10200"
    ON "SOP10100"."SOPNUMBE"="SOP10200"."SOPNUMBE")
    INNER JOIN
    "dbo"."CustomerBudget" "CustomerBudget" ON "SOP10100"."CUSTNMBR"="CustomerBudget"."CUSTNMBR")
    LEFT OUTER JOIN "dbo"."SOP10106" "SOP10106"
    ON ("SOP10100"."SOPTYPE"="SOP10106"."SOPTYPE")
    AND ("SOP10100"."SOPNUMBE"="SOP10106"."SOPNUMBE"))
    INNER JOIN "dbo"."IV00101" "IV00101"
    ON "SOP10200"."ITEMNMBR"="IV00101"."ITEMNMBR")
    INNER JOIN "dbo"."IV40600" "IV40600"
    ON ("IV00101"."ITMGEDSC"="IV40600"."USCATVAL")
    AND ("CustomerBudget"."USCATVAL"="IV40600"."USCATVAL")
    INNER JOIN RM00101 ON RM00101.CUSTNMBR=”CustomerBudget”.”CUSTNMBR”

    WHERE RM00101.CUSTNAME=@CUSTNAME


    AND "SOP10100"."SOPTYPE"=1 and (SOP10100.DOCDATE between @FromDate and @ToDate)

    group by DATEADD(M,DATEDIFF(M,0,SOP10100.DOCDATE),0), "IV40600"."UserCatLongDescr", "IV00101"."ITEMNMBR",
    "IV00101"."ITEMDESC"
    Order by iv40600.UserCatLongDescr, iv00101.ITEMDESC
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: SQL help for a report with parameters needed

    If I understand correctly what you are trying to do is have the report user choose the customer by name, but then have the customernumber passed to the report as the parameter. The best way to do this is to create a seperate dataset that would look something like this:

     select custname,custnmbr from rm00101

     Then when you setup your parameter for CustomerID choose the option to select default values from Query and select this new dataset. For the value field choose custnmbr and for the label value choose custname.

    Hope this helps.

    Kevin

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans