Post reply

Cascading Parameters

  • March 7, 2006 at 9:28 am

    #167998

    I am re-creating Access reports in Reporting Services (2005), and a couple of the forms allow the user to select a value, and then select multiple values from another list based on that parm.

    One example allows the user to select a customer, then select one or more sales orders for that customer in one text box, and any products the customer has purchased in another text box.

    It appears that I need to use "cascading parameters" for this, but I have only been working in RS for about two weeks so I don't really know what I'm doing. If anybody has any ideas or helpful links, I would appreciate the help.

    Thanks in advance

  • March 7, 2006 at 9:44 am

    #625233

    Hello Chris,

    Please go through this link

    http://msdn2.microsoft.com/en-us/library/ms155917.aspx

    Thanks and have a great day!!!


    Lucky

  • March 8, 2006 at 11:09 am

    #625492

    Thanks for the help, Lucky, but now I have a trickier problem (I think).

    I am using SPs to pass the parameters to each dataset for this scenario, and it goes something like this:

    The user selects a customer (@Customer_ID int) which is passed to a Sales Order and a Product lookup dataset. These two datasets produce multi-valued parameters (@SalesOrderNumbers nvarchar(200), @MaterialCodes nvarchar(200)) that are passed into the SP that the report is based on, which looks something like the following:

    CREATE PROC getCustomerProducts (@Start_Year int,@End_Year int,

    @Start_Month int, @End_Month int, @Customer_ID int, @SalesOrderNumbers nvarchar(200), @MaterialCodes nvarchar(200))

    SELECT MA.MaterialDescription, CU.CustomerName, CAL.MonthNbr, CAL.YearNbr,

    SUM(TIK.UnitsShipped*SOMP.MaterialPrice) AS MaterialDollars

    FROM App_Material MA, App_SalesOrder SO,

    App_Customer CU, App_Calendar CAL, App_MaterialType MT,

    App_Location LO, App_SalesOrder_MaterialPricing SOMP, V_Ticket TIK

    WHERE INNER JOIN statements

    ANDCU.CustomerID = @Customer_ID

    ANDYearNbr >=@Start_Year

    ANDYearNbr =@Start_Month

    ANDMonthNbr <=@End_Month

    ANDSO.SalesOrderID IN (@SalesOrderNumbers)

    ANDMA.MaterialID IN (@MaterialCodes)

    GROUP BY CU.CustomerName, MA.MaterialDescription, CAL.YearNbr, CAL.MonthNbr

    ORDER BY MA.MaterialDescription, YearNbr, MonthNbr

    It works fine if I only pass one value for the multi-valued parameters, but it seems to have datatype issues if I pass more than one. Does anybody out there have any clue what the deal with passing multi-valued parms for these SPs.

  • March 8, 2006 at 12:57 pm

    #625513

    Never mind that last question, I managed to find an answer at the following site for those who care:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136846&SiteId=1

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply