CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
damseldeebi
damseldeebi

Reputation: 137

SSRS : New calculated field based on where condition

I'm new to SSRS reporting and I'm need of help...

I have a dataset with fields "Process", "Level" and "Workweek"

Process Level  WW
------- -----  --
Test    Lvl_0   3
Test    Lvl_1  28
Test    Lvl_2  48
Samp    Lvl_0  10
Samp    Lvl_1  39
Samp    Lvl_2  51

What I want now is to create two more calculated fields called Start_WW and Pro_Start that has the values from WW field.

WW of Lvl_0 is considered to be the Process_Start.

the logic is something like

Process Level  WW Start_WW Pro_Start
------- -----  -- -------- ---------
Test    Lvl_0   3    0       3
Test    Lvl_1  28    3       3
Test    Lvl_2  48   28       3
Samp    Lvl_0  10    0      10
Samp    Lvl_1  39   10      10
Samp    Lvl_2  51   39      10

I know it is similar to SQL

Update Table SET Start_WW=(Select WW from Table where Level='Lvl_0') where Level='Lvl_1'

Update Table SET Proc_Start=(Select WW from Table where Level='Lvl_0')

I'm not sure how to write the expression for it. Pls help me.

Thanks in Advance!!

Upvotes: 0

Views: 290

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

If the Start_WW is simply the previous WW then you don't even need a calculated field; you can just use the Previous() function in the expression for the table's cell:

=IIF(Fields!Process.Value = Previous(Fields!Process.Value), Previous(Fields!WW.Value), 0)

For the Pro_Start, you can add this to your SQL:

SELECT MyTable.Process, MyTable.Level, MyTable.WW, Start.Pro_Start 
FROM MyTable 
INNER JOIN (
    SELECT Process, Level, MIN(WW) AS Pro_Start 
    FROM MyTable 
    GROUP BY Process, Level
) Start ON MyTable.Process = Start.Process AND MyTable.Level = Start.Level

With no access to the query, then you can get Pro_Start via a Lookup():

=Lookup(Fields!Process.Value, Fields!Process.Value, Fields!WW.Value, "MyDataset")

The Lookup() will return the first instance of the Process and give you the WW of that row.

Upvotes: 2

Related Questions