Running Value CountDistinct and Logic Expression?

Question

I have original data like this.

Original Data

I need to create two report with it, this is the first report :

First Report

The running value can be achieved with this expression RunningValue(Fields!City.Value+Fields!Month.Value,CountDistinct,"Region")

The second report i need is this:

Second Report

What can i do to add logic to the running value so it can avoid numbering row with Sum(Amount) zero ?

Answer 1

I'm not sure you can do this using RunningValue, other people may know of a way.

What I did was move the logic to the query.

I reproduced some data to match your final report numbers (your sample data does not match the sample report output).

Here's the sample data I used.

DECLARE @t TABLE(Region varchar(10), City varchar(10), MonthID int, Amount int)
INSERT INTO @t VALUES
('Asia', 'Tokyo', 4, 1000),
('Asia', 'Tokyo', 4, 500),
('Asia', 'Tokyo', 5, 2000),
('Asia', 'Tokyo', 5, -2000),
('Asia', 'Tokyo', 6, 1000),
('Asia', 'Tokyo', 6, -500),
('Asia', 'Bangkok', 4, 500),
('Asia', 'Bangkok', 4, 500),
('Asia', 'Bangkok', 5, 3000),
('Asia', 'Bangkok', 5, -500),
('Asia', 'Bangkok', 6, -750),
('Asia', 'Bangkok', 6, 750)

SELECT 
        *
        , ROW_NUMBER() OVER(PARTITION BY Region, City ORDER BY MonthID) as RowN1
        , ROW_NUMBER() OVER(PARTITION BY (CASE Amount WHEN 0 THEN 0 ELSE 1 END), Region, City ORDER BY MonthID) as RowN2
    FROM 
        (
        SELECT 
                Region, City, MonthID
                , SUM(Amount) AS Amount
            FROM @t
            GROUP BY Region, City, MonthID
        ) x
        ORDER BY Region, City DESC, MonthID

I used the ROW_NUMBER function to assign a row numbers for both reports.

The first one "RowN1" is a simple row number within city

The second one "RowN2" does the same thing but it partitions any zero values so they are not in the same partition as the other data.

This gives us the following dataset

Now you can use a simple table to display the result in your first report using RowN1

In your second report use RowN2 with the expression

=IIF(Fields!Amount.Value=0, Nothing, Fields!RowN2.Value)

This simply forces a blank to be displayed if the amount is zero.

I did this and got the following results.

Note: I used a month number in the data just to make sorting easier, in the report I used =MonthName(Fields!MonthID.Value) to show the actual name.