SSRS Count Records expression with date less than group by date

Question

I want to count records that are not marked as completed with a Received date less than the Received date of the row (group by Received date "Details") This will be the Start of Day column showing how many records are in queue.

I have a tablix in VS 2017 SSDT.

Tablix is grouped by Received Date

COLUMNS
Received Date (group by Details) another column same field (Textbox5)
Start of Day        
New Tasks       
Completed

I'm having an issue with the code logic for Start of Day column field.

I want to count records that are not marked as completed with a Received date less than the Received date of the row (group by Received date "Details")

This code works for New Tasks column. =COUNT(IIF(Fields!Received.Value < Fields!Received.Value AND Fields!Completed.Value = "NO", 1,0),"Details")

When I attempt the Start of Day expression I get errors. Textbox5 is the same dataset field used in group by field (Received). I added it to test different approach.

=COUNT(IIF(Fields!Received.Value < ReportItems!Textbox5.Value AND Fields!Completed.Value = "NO", 1,0),"Details")

Error: rsAggregateReportItemlnBody aggregate functions can be used only on report items contained in page headers and footers.

Sample data and expected output for Start of Day Column: it should count records in the group by row if they were in queue prior to start of day (yesterday).

your question will be easier to understand if you can show some sample data as it comes from your dataset and expected output from the sample. Please edit your question and add some more details . — Alan Schofield

niktrs niktrs · Accepted Answer · 2019-02-21T08:15:55

You can use running value to calculate the total. To get the prior date total you subtract the group total value

Start Of Day

=  RunningValue( 1, SUM, "Tablix1")- SUM( 1)

New Task

= SUM(1) or COUNT(1)

Complete

=  RunningValue( Iif(Fields!CompYN.Value = "YES",1,0), SUM, "Tablix1")- SUM( Iif(Fields!CompYN.Value = "YES",1,0))

SSRS Count Records expression with date less than group by date

2 Answers