I want to perform my sql statement as below to be in DataTable concept:-
select
ISNULL(SUM(child.initial_rc), 0), ISNULL(SUM(child.annual_rc), 0), ISNULL(SUM(child.initial_rcCoS), 0), ISNULL(SUM(child.annual_rcCoS), 0)
from TotalRevenueByMonth a
inner join RevenueByYear b
on a.totalcategory=b.totalcategory
and a.financialyear=b.financialyear
and a.accountmanagerid=b.accountmanagerid
and a.aregionid=b.aregionid
and a.avbuid=b.avbuid
where a.productfamily in('item a','item b')
and a.totalcategory=1 and a.financialyear=12 and a.accountmanagerid=1 and a.aregionid=10 and a.avbuid=1
I have transform it to be DataTable as below, but i having difficulty on
a.productfamily in('item a','item b', 'item c'). How to transform a.productfamily in('item a','item b', 'item c') into dataTable?
Maybe you want to add records into a table named TotalRevenueByMonth in the dataset. If so, you can split the items into a list. Then you can add the item into table in a for loop. You can also assign the value to the column of table directly.
How can i change to based on table TotalRevenueByMonthExt.
And also allow to have multi filter. E.g. allowed check on productfamily in ('itema','item b'...) and regionid in ('regionA','regionB'). How can i modify this portion of code as shown belown:-
micnie2020
Member
306 Points
523 Posts
DataSet Relations with filter using productfamily in('item a','item b', 'item c')
Apr 19, 2012 01:12 AM|LINK
Dear All,
I want to perform my sql statement as below to be in DataTable concept:-
select
ISNULL(SUM(child.initial_rc), 0), ISNULL(SUM(child.annual_rc), 0), ISNULL(SUM(child.initial_rcCoS), 0), ISNULL(SUM(child.annual_rcCoS), 0)
from TotalRevenueByMonth a
inner join RevenueByYear b
on a.totalcategory=b.totalcategory
and a.financialyear=b.financialyear
and a.accountmanagerid=b.accountmanagerid
and a.aregionid=b.aregionid
and a.avbuid=b.avbuid
where a.productfamily in('item a','item b')
and a.totalcategory=1 and a.financialyear=12 and a.accountmanagerid=1 and a.aregionid=10 and a.avbuid=1
I have transform it to be DataTable as below, but i having difficulty on a.productfamily in('item a','item b', 'item c'). How to transform a.productfamily in('item a','item b', 'item c') into dataTable?
Please Advise.
private void UpdateTotalRevenueByMonthRelation2(Boolean filterByAccountManager, Boolean filterByRegion, Boolean filterByBu, Boolean filterByProductFamily, string filterByProductFamily2)
{
// Remove current relation
this.RemoveDataRelation("TotalRevenueByMonth_Revenue");
// Set up relation cols
int index = 1;
int numberOfCols = 2;
numberOfCols += (filterByAccountManager) ? 1 : 0;
numberOfCols += (filterByRegion) ? 1 : 0;
numberOfCols += (filterByBu) ? 1 : 0;
// Select parent/child table columns
DataColumn[] parentCols = new DataColumn[numberOfCols];
parentCols[0] = this.dataSet1.TotalRevenueByMonth.totalcategoryColumn;
parentCols[1] = this.dataSet1.TotalRevenueByMonth.financialyearColumn;
DataColumn[] childCols = new DataColumn[numberOfCols];
childCols[0] = this.dataSet1.RevenueByYear.totalcategoryColumn;
childCols[1] = this.dataSet1.RevenueByYear.financialyearColumn;
if (filterByAccountManager)
{
index += 1;
parentCols[index] = this.dataSet1.TotalRevenueByMonth.accountmanageridColumn;
childCols[index] = this.dataSet1.RevenueByYear.accountmanageridColumn;
}
if (filterByRegion)
{
index += 1;
parentCols[index] = this.dataSet1.TotalRevenueByMonth.aregionidColumn;
childCols[index] = this.dataSet1.RevenueByYear.aregionidColumn;
}
if (filterByBu)
{
index += 1;
parentCols[index] = this.dataSet1.TotalRevenueByMonth.avbuidColumn;
childCols[index] = this.dataSet1.RevenueByYear.avbuidColumn;
}
// Create new relation
this.dataSet1.Relations.Add("TotalRevenueByMonth_Revenue", parentCols, childCols, false);
this.dataSet1.TotalRevenueByMonth.initial_rcColumn.Expression = "ISNULL(SUM(child.initial_rc), 0)";
this.dataSet1.TotalRevenueByMonth.annual_rcCoSColumn.Expression = "ISNULL(SUM(child.annual_rc), 0)";
//fields on revenuebyyear
this.dataSet1.TotalRevenueByMonth.initial_rcCoSColumn.Expression = "ISNULL(SUM(child.initial_rcCoS), 0)";
this.dataSet1.TotalRevenueByMonth.annual_rcCoSColumn.Expression = "ISNULL(SUM(child.annual_rcCoS), 0)";
//Filter productfamily in ('item a', 'item b' ...), but not works!
string[] segmentArray = filterByProductFamily2.TrimStart(',').Split(',');
List<string> segmentList = new List<string>(segmentArray.Length);
segmentList.AddRange(segmentArray);
this.dataSet1.TotalRevenueByMonth.Where(rs => segmentList.Contains(rs.productfamily.ToString()));
}
Please Advise.
Thank you.
Richey
Contributor
3816 Points
431 Posts
Re: DataSet Relations with filter using productfamily in('item a','item b', 'item c')
Apr 24, 2012 01:08 PM|LINK
Hi，
Maybe you want to add records into a table named TotalRevenueByMonth in the dataset. If so, you can split the items into a list. Then you can add the item into table in a for loop. You can also assign the value to the column of table directly.
micnie2020
Member
306 Points
523 Posts
Re: DataSet Relations with filter using productfamily in('item a','item b', 'item c')
Apr 25, 2012 08:58 AM|LINK
Hi,
Thank you for your kind reply.
The problem is TotalRevenueByMonth , totalcategory are the primary key. Not allow to duplicate.
I am creating extensions table, and regenerate the value as show below:-
for (int j = 0; j < productFamily.Count(); j++)
{
// Added by Micheale Multi-Select
for (int i = 1; i <= 10; i++)
{ DataSet1.TotalRevenueByMonthExtRow total2 = dataSet1.TotalRevenueByMonthExt.NewTotalRevenueByMonthExtRow();
total2.totalcategory = i;
total2.financialyear = year;
total2.financialmonth = month;
total2.accountmanagerid = (filterByAccountManager) ? accountManagerId :Guid.Empty;
total2.regionid = (filterByRegion) ? RegionId :Guid.Empty;
total2.buid = (filterByBu) ? BuId :Guid.Empty;
total2.productfamily = productFamily[j];
dataSet1.TotalRevenueByMonthExt.AddTotalRevenueByMonthExtRow(total2);
}
}
// Update totals matrices
this.UpdateRevenuesByMonthTotalMatrix(year, month, filterByAccountManager, accountManagerId,filterByBu, BuId, filterByRegion, RegionId, filterByProductFamily, productFamily);
How can i change to based on table TotalRevenueByMonthExt.
And also allow to have multi filter. E.g. allowed check on productfamily in ('itema','item b'...) and regionid in ('regionA','regionB'). How can i modify this portion of code as shown belown:-
DataSet1.TotalRevenueByMonthRow totalPendingNew = (DataSet1.TotalRevenueByMonthRow)dataSet1.TotalRevenueByMonth.FindBytotalcategory(Helpers.TotalCategory.PNEWBUSINESS);
//Function UpdateRevenuesByMonthTotalMatrix.
public void UpdateRevenuesByMonthTotalMatrix(int year, int month, Boolean filterByAccountManager, Guid accountManagerId,
Boolean filterByBu, Guid BuId, Boolean filterByRegion, Guid RegionId, Boolean filterByProductfamily, Guid[] productFamily)
{
// Function to update the month view matrix
// Total pending new business
DataSet1.TotalRevenueByMonthRow totalPendingNew = (DataSet1.TotalRevenueByMonthRow)dataSet1.TotalRevenueByMonth.FindBytotalcategory(Helpers.TotalCategory.PNEWBUSINESS);
// Total pending renewals
DataSet1.TotalRevenueByMonthRow totalPendingRenew = (DataSet1.TotalRevenueByMonthRow)dataSet1.TotalRevenueByMonth.FindBytotalcategory(Helpers.TotalCategory.PPREDICTABLERENEWAL);
// Total pending new product
DataSet1.TotalRevenueByMonthRow totalPendingNewProd = (DataSet1.TotalRevenueByMonthRow)dataSet1.TotalRevenueByMonth.FindBytotalcategory(Helpers.TotalCategory.PNEWPRODUCT);
:
:
}
Please advise.
Thank you.
Regards,
Micheale
micnie2020
Member
306 Points
523 Posts
Re: DataSet Relations with filter using productfamily in('item a','item b', 'item c')
Apr 27, 2012 11:19 AM|LINK
I manage to resolved this & another post in 1 solution:-
http://forums.asp.net/t/1797222.aspx/1?DataSet+Select+and+GroupBy
Thank you.