none
Recursive hierarchy with detail at each group

    Question

  • I'm trying to create a report in SSRS that shows the open orders for each customer, using their customer number as a parameter. Here is a picture of the hierarchy of the groups:

    CustomerNumber - Customer information

    BillToNumber - Billing information

    ShipToNumber - Shipping Information

    OrderNumber - Order header information (Order Date, Credit terms, etc)

    LineNumber - Order line detail information (part number, price, quantity, etc)

    I tried setting this up as a table, but it wants to put all the groups together, so that the detail for the parent groups repeats with every single line number. I tried inserting subreports, but that causes the header information to repeat over and over as well. What's the best way to accomplish this type of report where you need details with every group?

    Friday, October 18, 2013 2:59 PM

Answers

  • Thanks! That article looks similar to what I need, but it doesn't end up bringing the groups together in the end. I just found this article and am currently testing it out. Looks like it'll solve the issue!

    http://www.sqlcircuit.com/2012/03/ssrs-how-to-show-tablix-inside-tablix.html

    • Marked as answer by april4181 Friday, October 18, 2013 6:10 PM
    Friday, October 18, 2013 6:09 PM

All replies

  • Hi,

    Is it statement like report?

    Thanks.


    Remember to mark as an answer if this post has helped you.

    Friday, October 18, 2013 3:48 PM
  • I'm not sure I totally understand your question, but yes this is for a report on all of the open orders for a customer that I'm trying to create with Report Builder 3.0. It's currently coming from one dataset and this is my query: 

    select SoldTo, [cust_ad_name],[cust_ad_line],[cust_ad_city],[cust_ad_state],[cust_ad_zip],[cust_ad_country],
    BillTo, [bill_ad_name],[bill_ad_line],[bill_ad_city],[bill_ad_state],[bill_ad_zip],[bill_ad_country],
    ShipTo, [ship_ad_name],[ship_ad_line],[ship_ad_city],[ship_ad_state],[ship_ad_zip],[ship_ad_country],
    SONbr, BO, St, Conf, LoadNbr,
    LDST, PuchaseOrder, ShowCode, Channel, Disc, Salespsn, AdminCode,
    EnteredBy, RplOrd, InsOrd, SchedulingComments, ShipVia, FreightList, FreightTerms,
    CreditTerms, TraxNbr, OrderSite, OrderType, Factory, Bundle, BundleReadyDate, BundleATSDate, Container, Remarks,
    OrdDate, ReqDate, PromDate, DueDate, ATSFlag, ATSHdr, ATSWeek, ReadyDate, ShipDate,
    OrdStat, StatusMsgs, StatusUpdated,
    Trl1Desc, Trl1Amt, Trl2Desc, Trl2Amt,Trl3Desc, Trl3Amt,
    Ln, ItemNo, ItemDesc, SP, QtyOrder, QtyAlloc, QtyOpen, ShipCube, Price, ExtPrice,
    AtsSeq, AtsDetail, LineATSSupply, StatusInfo, ATSRule
    from tbl_rpt_OpenOrderDataset 

    I really really really appreciate your help!

    Friday, October 18, 2013 3:52 PM
  • I'm trying to create a report in SSRS that shows the open orders for each customer, using their customer number as a parameter. Here is a picture of the hierarchy of the groups:

    CustomerNumber - Customer information

    BillToNumber - Billing information

    ShipToNumber - Shipping Information

    OrderNumber - Order header information (Order Date, Credit terms, etc)

    LineNumber - Order line detail information (part number, price, quantity, etc)

    I tried setting this up as a table, but it wants to put all the groups together, so that the detail for the parent groups repeats with every single line number. I tried inserting subreports, but that causes the header information to repeat over and over as well. What's the best way to accomplish this type of report where you need details with every group?

    Hi,

    You can accomplish your requirement using List , try this link for an example .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Friday, October 18, 2013 5:13 PM
  • Can you draw how you see your report on paper?

    Here is a link on a sample of a statement like report.

    http://jesborland.wordpress.com/2010/11/17/the-power-of-reporting-services-%E2%80%93-lists/


    Remember to mark as an answer if this post has helped you.

    Friday, October 18, 2013 5:32 PM
  • Thanks! That article looks similar to what I need, but it doesn't end up bringing the groups together in the end. I just found this article and am currently testing it out. Looks like it'll solve the issue!

    http://www.sqlcircuit.com/2012/03/ssrs-how-to-show-tablix-inside-tablix.html

    • Marked as answer by april4181 Friday, October 18, 2013 6:10 PM
    Friday, October 18, 2013 6:09 PM