none
Error : "Detail member with inner members"

    Question

  • Hi,
    I am trying to create a report with sub report and i am getting the following error:
    "The tablix 'table1' has a detail member with inner members. Detail members can contain only static inner members."

    I am placing the main report with "table1 " and subreport in a list running using common parameters.. "Dealer, StartDate and Endate"

    Query for Main report:

    SELECT XASDM.SSDMSDC AS DEALER, XASDM.SDMNAM AS DEALERNAME, P1MST.PPMLOC AS LOCATION,
                   SUM(v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.Weight) AS POUNDS,
                   SUM(v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.BfatCompLbs) AS BFPOUNDS,
                   SUM(v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.ProteinCompLbs) AS PRTPOUNDS,
                   SUM(v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.SolidsNonfatCompLbs) AS SNFPOUNDS,
                   SUM(v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.OtherSolidsCompLbs) AS OTRSLDLBS
    FROM  P1MST INNER JOIN
                   v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch ON
                   P1MST.PPMNBR = v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.Producer INNER JOIN
                   XASDM ON v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.DestinationCode = XASDM.SSDMSDC
    WHERE (v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.PickupDate BETWEEN @startdate AND @enddate)
    GROUP BY XASDM.SSDMSDC, XASDM.SDMNAM, P1MST.PPMLOC
    ORDER BY DEALER, LOCATION.

    Query for Sub Report:

    SELECT P1MST.PPMSTE AS STATE, COUNT(DISTINCT P1MST.PPMNBR) AS [PROD. COUNT],
                   SUM(v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.Weight) AS POUNDS
    FROM  v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch INNER JOIN
                   P1MST ON v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.Producer = P1MST.PPMNBR
    WHERE (v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.PickupDate BETWEEN @STARTDATE AND @ENDDATE) AND
                   (v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.DestinationCode = @Dealer)
    GROUP BY P1MST.PPMSTE, v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.DestinationCode
    ORDER BY v_pp_ProducerLoadsAndLabsDeliveredBarCodeMatch.DestinationCode.


    Can anyone help me in this?

    Thanks in advance for all the active participation in this forum.

    Kevin.
    • Changed type Kvasire Wednesday, December 09, 2009 3:49 PM
    Wednesday, December 09, 2009 2:38 PM

Answers

  • You can only place a subreport in a row that is grouped.  Check the group expression for the detail row.  If this doesn't work, create a group above the detail row and place the subreport in a group header or footer row for that group.
    Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com
    • Marked as answer by Tony Tang_YJ Wednesday, December 16, 2009 11:25 AM
    Wednesday, December 09, 2009 4:38 PM
    Moderator

All replies

  • You can only place a subreport in a row that is grouped.  Check the group expression for the detail row.  If this doesn't work, create a group above the detail row and place the subreport in a group header or footer row for that group.
    Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com
    • Marked as answer by Tony Tang_YJ Wednesday, December 16, 2009 11:25 AM
    Wednesday, December 09, 2009 4:38 PM
    Moderator
  • -"You can only place a subreport in a row that is grouped."

    Good answer!  I was using a list in the header, and a tablix for repeating detail information, but encountered the same error ("The tablix 'table1' has a detail member with inner members. Detail members can contain only static inner members.")

    Moving the tablix to the group header fixed my issue.  Thanks!


    Monday, June 06, 2011 6:44 PM
  • Hi All,

    I am having the similar issue how can I move the tablix to the group header?

     

    Thursday, June 30, 2011 6:51 PM
  • -"You can only place a subreport in a row that is grouped."

    -"Moving the tablix to the group header ...

    Nice idea. Worked for me,too. Thanks! 

    Thursday, April 19, 2012 10:41 AM