Create Report in VS2013 Report Viewer with Multiple Report Tables that use the same Dataset

Question

I need to create a single report in VS2013 Report Viewer (.net 4.5 C#) winforms application that displays multiple reports tables. The report tables need to utilize the same SQL Store Procedure (to fill a dt named "DataSet"), simply varying the data returned per which items is selected from a listbox prompt in a prior screen. I am attempting to use the local reporting mode (.net assembly referenced in my winforms application). Below is an example of how the single report should appear and the code I’ve attempted to use to generate the report. I have two rdlc files, the initial file which contains the header and footer, is the one linked in the ItemsSoldMonthlyReport_Load method. The other rdlc is embedded in the details row of the first report file (in the report designer) as a subreport. The ItemsSoldMonthlyReport_Load method properly passes the itemType, pFromDate, and pThruDate parameters to the subreport method. The subreport method is launched per the number of items applied to the “SoldList” dataset, which is correct. Using the GetData method in the CC_SubreportProcessingEventHandler for each item in the itemType parameter the data for each table is successfully returned. The correct data is set to the "DataSet" DataTable for each report table. However, it seems the "DataSet" DataTable can only be set once as a ReportDataSource. No error occurs when I attempt to set the "DataSet" loaded with the second set of data as a ReportDataSource but the data of the second table (i.e.: Clothing) is not displayed in the final report. Instead the first datatable (i.e.: Toys) is displayed twice in the final report display. Is it possible to do what I am attempting and if so what approach should I take? I’ve tried a lot of things and researched a lot on this and cannot find an answer myself. If report viewer is absolutely incapable of generating such a report can anyone suggest an alternate that’s not too costly? Thanks in advance…

What the report should look like...

The code of my latest (and closest attempt) to create such a report...

namespace StoreProject_Forms
{
    public partial class rptItemsSoldReport : Form
    {
        int x = 0;
        public rptItemsSoldReport()
        {
            InitializeComponent();
        }

        public void CC_SubreportProcessingEventHandler(object sender, SubreportProcessingEventArgs e)
        {
            DataTable dt = new DataTable();
            var mainSource = ((LocalReport)sender).DataSources["SoldList"];
            var itemTypes = e.Parameters["itemType"].Values;
            DateTime sdate = e.Parameters["pFromDate"].Values;
            DateTime edate = e.Parameters["pThruDate"].Values;
            StoreProject1.StoreDataSetTableAdapters.spGetItemsSoldReportTableAdapter commDt = new StoreProject1.StoreDataSetTableAdapters.spGetItemsSoldReportTableAdapter();
            dt.Clear();
            dt = commDt.GetData(sdate, edate, itemTypes[x]);
            e.DataSources.Add(new ReportDataSource("DataSet1", dt));
            x++;
        }


        public void ItemsSoldMonthlyReport_Load(DateTime startDate, DateTime endDate, ListBox.SelectedObjectCollection itemTypesTxt)
        {
            startDate = new DateTime(startDate.Year, startDate.Month, 1);
            endDate = new DateTime(endDate.Year, endDate.Month, 1);
            System.Collections.Generic.List<ReportParameter> paramList = new System.Collections.Generic.List<ReportParameter>();
            string itemTypes = null;
            reportViewer1.LocalReport.DataSources.Clear();
            StoreProject1.StoreDataSetTableAdapters.spGetItemsSoldReportTableAdapter commDt = new StoreProject1.StoreDataSetTableAdapters.spGetItemsSoldReportTableAdapter();
            List<string> itemsA = new List<string>();
            Dictionary<string, int> allItemTypes = new Dictionary<string, int>();
            DataTable ct = new DataTable();
            foreach (KeyValuePair<string, int> item in itemTypesTxt)
            {
                itemsA.Add(item.Key);
                allItemTypes.Add(item.Key, item.Value);
            }
            this.reportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(CC_SubreportProcessingEventHandler);
            ReportParameter itemParam = new ReportParameter("itemType");
            ct = StoreProject1.ItemList.ConvertToDatatable(allItemTypes);
            string[] itemsArray = itemsA.ToArray();
            ReportDataSource _rsource = new ReportDataSource("SoldList", ct);
            reportViewer1.LocalReport.DataSources.Add(_rsource);

            itemParam.Values.AddRange(itemsArray);
            paramList.Add(itemParam);

            string sfdate = startDate.ToString("MM/yyyy");
            paramList.Add(new ReportParameter("pFromDate", sfdate, false));

            string stdate = endDate.ToString("MM/yyyy");
            paramList.Add(new ReportParameter("pThruDate", stdate, false));

            reportViewer1.LocalReport.ReportPath = @"C:/StoreProject1/ItemsSoldReport.rdlc";
            reportViewer1.LocalReport.SetParameters(paramList);
            this.reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
            this.reportViewer1.LocalReport.Refresh();
        }
    }
}

Answer 1

Sorry it's a bit long. For each Report we have multiple approaches. As mentioned in comment section, I generated the same (not exactly but the outcome is the same) report with list as step by step below:

1. Create a DataSet with one table have 7 Columns.

2. Start a new Report, add a List full space inside, then add 7 TextBoxes (which have data from Col1 to Col 7) inside that List.

3. Set Visibility of those TextBoxes to Show or hide based on an expression in order to hide them when we passed emty string data:

TextBox1: =IIF(Fields!Col1.Value="",TRUE,FALSE)

TextBox2: =IIF(Fields!Col2.Value="",TRUE,FALSE)

TextBox3 to 7: =IIF(Fields!Col3.Value & Fields!Col4.Value & Fields!Col5.Value & Fields!Col6.Value & Fields!Col7.Value = "",TRUE,FALSE)

4. Sample Data struct:

Something simple:

class clsDataStruct
{
    public class ItemType
    {
        public string Name { get; set; }
    }

    public class Item
    {
        public string Name { get; set; }
        public ItemType Type { get; set; }
        public int Cost { get; set; }
        public int Prize { get; set; }
    }

    public class BillByItem
    {
        public DateTime DateSold { get; set; }
        public Item Item { get; set; }
    }
}

5. A Form to view result:

Add a Form, a ReportViewer to that Form, set our previously created Report to that ReportViewer. Then inside Form's Load event, add some code to generate some random data (this is where you take your data instead) then generate our Report:

    //for data generation
    static Random rdn = new Random();

    //some list to hold data
    List<clsDataStruct.ItemType> lstItemType = new List<clsDataStruct.ItemType>();
    List<clsDataStruct.Item> lstItem = new List<clsDataStruct.Item>();
    List<clsDataStruct.BillByItem> lstBill = new List<clsDataStruct.BillByItem>();

    //this is what user choose to filter the report
    //user choose to report which item type
    List<clsDataStruct.ItemType> lstItemTypeByUser = new List<clsDataStruct.ItemType>();
    //date begin and end of report
    DateTime dteStart;
    DateTime dteEnd;

    private void Form1_Load(object sender, EventArgs e)
    {
        //create 3 ItemType
        for (int i = 1; i < 4; i++)
        {
            clsDataStruct.ItemType itt = new clsDataStruct.ItemType();
            itt.Name = "Item Type " + i.ToString();
            lstItemType.Add(itt);
        }

        //create 12 Item
        for (int i = 1; i < 13; i++)
        {
            clsDataStruct.Item item = new clsDataStruct.Item();
            item.Name = "Item " + i.ToString();
            item.Type = lstItemType[i % 3];
            item.Cost = rdn.Next(10);
            item.Prize = item.Cost + rdn.Next(5);
            lstItem.Add(item);
        }

        //create 30 BillByItem in next 3 month
        for (int i = 1; i < 31; i++)
        {
            clsDataStruct.BillByItem bill = new clsDataStruct.BillByItem();
            bill.DateSold = DateTime.Now.AddDays(rdn.Next(90));
            bill.Item = lstItem[rdn.Next(12)];
            lstBill.Add(bill);
        }
        //set the filters
        //add 2 random type to the filter
        lstItemTypeByUser.Add(lstItemType.Where(s => !lstItemTypeByUser.Contains(s)).ToList()[rdn.Next(lstItemType.Where(s => !lstItemTypeByUser.Contains(s)).ToList().Count)]);
        lstItemTypeByUser.Add(lstItemType.Where(s => !lstItemTypeByUser.Contains(s)).ToList()[rdn.Next(lstItemType.Where(s => !lstItemTypeByUser.Contains(s)).ToList().Count)]);
        //date start and end is one month from date we have data
        dteStart = DateTime.Now.AddDays(rdn.Next(60) - 30);
        dteEnd = DateTime.Now.AddMonths(3).AddDays(rdn.Next(60) - 30);

        this.reportViewer1.LocalReport.DataSources.Clear();
        dsReports.dtLyLichTrichNgangDataTable dtLyLichTrichNgang = new dsReports.dtLyLichTrichNgangDataTable();

        //Simple title, replace with yours
        dtLyLichTrichNgang.Rows.Add("ITEM INVENTORY SOLD " + dteStart.ToShortDateString() + " - " + dteEnd.ToShortDateString(), "", "", "", "", "", "");
        //empty row
        dtLyLichTrichNgang.Rows.Add(" ", "", "", "", "", "", "");

        DateTime dteFirstOfCycle;
        DateTime dteLastOfCycle;
        //cycle through months and fill data to datatable, maybe week or quarter
        for (dteFirstOfCycle = new DateTime(dteStart.Year, dteStart.Month, 1); dteFirstOfCycle < dteEnd; dteFirstOfCycle = dteFirstOfCycle.AddMonths(1))
        {
            dteLastOfCycle = dteFirstOfCycle.AddMonths(1).AddDays(-1);

            //take BillByItem in each month
            var billMonth = lstBill.Where(s => s.DateSold >= dteFirstOfCycle && s.DateSold <= dteLastOfCycle).OrderBy(s => s.DateSold);

            dtLyLichTrichNgang.Rows.Add("FROM " + dteFirstOfCycle.ToShortDateString() + " TO " + dteLastOfCycle.ToShortDateString(), "", "", "", "", "", "");
            //empty row
            dtLyLichTrichNgang.Rows.Add(" ", "", "", "", "", "", "");

            //cycle through each item type
            foreach (clsDataStruct.ItemType itt in lstItemTypeByUser)
                //have sold something
                if (billMonth.Where(s => s.Item.Type == itt).Count() != 0)
                {
                    //itemtype
                    dtLyLichTrichNgang.Rows.Add(itt.Name.ToUpper(), "", "", "", "", "", "");
                    //detail header
                    dtLyLichTrichNgang.Rows.Add("", "", "Name", "Cost", "Prize", "Profit", "Date Sold");
                    //cycle through each bill
                    foreach (clsDataStruct.BillByItem bill in billMonth)
                        dtLyLichTrichNgang.Rows.Add("", "", bill.Item.Name, bill.Item.Cost, bill.Item.Prize, bill.Item.Prize - bill.Item.Cost, bill.DateSold.ToShortDateString());
                    //total row
                    dtLyLichTrichNgang.Rows.Add("", "", "TOTAL", billMonth.Sum(s => s.Item.Cost), billMonth.Sum(s => s.Item.Prize), billMonth.Sum(s => s.Item.Prize - s.Item.Cost), "");
                }
                //sold nothing
                else
                {
                    dtLyLichTrichNgang.Rows.Add(itt.Name.ToUpper(), "", "", "", "", "", "");
                    dtLyLichTrichNgang.Rows.Add("Nothing sold", "", "", "", "", "", "");
                    //empty row
                    dtLyLichTrichNgang.Rows.Add(" ", "", "", "", "", "", "");
                }
            //empty row
            dtLyLichTrichNgang.Rows.Add(" ", "", "", "", "", "", "");
        }

        this.reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("dsLyLichTrichNgang", (DataTable)dtLyLichTrichNgang));
        this.reportViewer1.RefreshReport();
    }

6. Result:

With some tweak on TextBox's BorderStyle you can have your desired Report.