web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Using parameter to det...
Microsoft Dynamics GP (Archived)

Using parameter to determine content of column is SRSS

(0) ShareShare
ReportReport
Posted on by Spectrum IT 120

I am trying to create a dynamic month over month sales report. Thanks to the Victoria Yudin View I have the query component resolved.  Based on this query I can easily create a static report that has Month, calculated %, month, calculated % and so forth.

What I am trying to do is give the users the ability to define the two time periods to compare them. For example they could choose July 2015 and September 2015 and the calculated field will show them the percentage change between those two time periods.

It will not allow me to use a field as a variable (that would have been too easy) so I can not see a way to dynamically fill the two data columns.

Any thoughts would be much appreciated.

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Victoria Yudin Profile Picture
    Victoria Yudin 22,769 on at

    Performance-wise, the best approach for this would be to change the SQL view you have into a stored procedure with the parameters you want. Then update the SSRS report to point to the stored procedure and it will automatically recognize the parameters from the SQL stored procedure.

  • Spectrum IT Profile Picture
    Spectrum IT 120 on at

    thank you for the suggestion. Based on what you said I modified the view and saved it as a stored procedure. When I set the variables in MS and execute the query from there it produces the desired results. However when I use the stored procedure from an SRSS report I get an error saying to enable remote errors for more info. When it is enabled I still get the same error.

    Interestingly enough it works when I remove the declare statement and cut/paste the query into the SRSS report.

    Thanks again for your assistance!

    CREATE PROCEDURE SP_VIEW_SALES_BY_CUSTOMER_MONTH_2

    AS

    declare @Period1 varchar(30),

    @Period2 varchar(30)

    select

    d.CUSTNMBR Customer_ID,

    d.CUSTNAME Customer_Name,

    sum(case when month(d.DOCDATE) = @Period1

     then d.SALES else 0 end) SalesPeriod1,

    sum(case when month(d.DOCDATE) = @Period2

     then d.SALES else 0 end) SalesPeriod2,

    sum(d.SALES) Yearly_Total

    from

    (select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME,

    case s.SOPTYPE

      when 3 then s.DOCAMNT

      when 4 then s.DOCAMNT*-1

      end SALES

    from SOP30200 s

    left outer join RM00101 c

      on s.CUSTNMBR = c.CUSTNMBR

    where

     s.VOIDSTTS = 0

      and s.SOPTYPE in (3,4)

      and year(s.DOCDATE) = 2015

    ) d

    group by d.CUSTNMBR, d.CUSTNAME

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,769 on at

    Glad you got it working.

    For the future, I typically create the stored procedure with the following syntax, where the parameters are declared before the 'AS':

    CREATE PROCEDURE SP_VIEW_SALES_BY_CUSTOMER_MONTH_2

    @Period1 varchar(30), @Period2 varchar(30)

    AS

    .......

    Then I set it up in SSRS and it recognizes the parameters.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans