• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

Get count on filtered rows

Hi
I apply several filters to a datatable, then bind a datasource to it. This is a list of Policy renewals.
So my current code looks like
' stFilter contains all the filters
Me.BroomsDataset.Renewals.DefaultView.RowFilter = stFilter
' Set the sorting, then bind
Me.BroomsDataset.Renewals.DefaultView.Sort = "Rdat"
' Filters only apply when datasouce set
Me.RenewalsBindingSource.DataSource = Me.BroomsDataset.Renewals.DefaultView

Open in new window


Now I have to do one additional step. Add an extra column which counts the number of policies for a Client. Which I can do like this
For Each rowRenewal In Me.BroomsDataset.Renewals
     stCalc = "Count(PolicyRef)"
     stPolFilter = "ClientRef = '" & rowRenewal.ClientRef & "'"
     rowRenewal.PolicyCount = Me.BroomsDataset.Renewals.Compute(stCalc,stPolFilter)
Next
Me.BroomsDataset.Renewals.AcceptChanges()

Open in new window


However, this count will include all policies for a Client - I only want to count the filtered policies. For example, Client has 5 policies overall, 2 are in March. The filter is for March policies only, so only those two rows will be in RenewalsBindingSource.DataSource.
I'd like the PolicyCount to be 2 in that case, instead its 5.
What should I change to count only the filtered rows?
Thanks
0
jdhackett
Asked:
jdhackett
  • 2
  • 2
1 Solution
 
adriankohwsCommented:
Why not get it from the database what you what before you bind to your dataset, then it's easier. For example:

SELECT Policy_Holder_ID, Policy_Name, Policy_Date,
(COUNT(*) OVER (PARTITION BY (SELECT 1)))AS Found_Policies
FROM YOURTABLE WHERE
Your conidtion here
0
 
jdhackettAuthor Commented:
Not all the data is in SQL, so your idea will not work in this case.
What I want is to work with the dataset only, not changing how the underlying data is loaded.
0
 
adriankohwsCommented:
Yes. If you keep manipulating with the binding source and not updating into the database, of course not all data is there. Till today, I am just extremely curious on why lots of people are holding big chunk of binding source data without updating to the database. In event of many concurrent users using the system, might meet data concurrency issues.
0
 
vastoCommented:
Try this:

Dim dt as System.Data.DataTable = Me.BroomsDataset.Renewals.DefaultView.ToTable()

For Each rowRenewal In dt
     stCalc = "Count(PolicyRef)"
     stPolFilter = "ClientRef = '" & rowRenewal.ClientRef & "'"
     rowRenewal.PolicyCount = dt.Compute(stCalc,stPolFilter)
Next
dt.AcceptChanges()

I cannot check the syntax
0
 
jdhackettAuthor Commented:
I also discovered that setting the RowStateFilter will also work. Just run this line before applying the Filter.

Me.ArachasBrooms01DataSet.Renewals.DefaultView.RowStateFilter = DataViewRowState.CurrentRows
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now