Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
176 views
in Technique[技术] by (71.8m points)

reporting services - Get previous first value from parameter

I have a parameter date from dataset date: Sample of data

Key Month Year Row num
20210131 January 2021 3
20201231 December 2020 2
20201130 November 2020 1
question from:https://stackoverflow.com/questions/66049447/get-previous-first-value-from-parameter

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

I would suggest that you extend the dataset query to include the previous value against each row.

Assuming you dataset query is called DataSet1 and uses a table called 'myTable' then something like this (I changed the sample column name from Key to KeyID to avoid keyword problems).

    SELECT *
        , LAG(KeyID) OVER(ORDER BY RowNum) as PreviousMonthKeyID
        FROM myTable

NOTE: This assumes row number is always in the correct order, if not then you could change this to order by KeyID.

You can then use a LOOKUP() to find the correct value, so if your parameter is called selectedDate and it's value is set to the KeyID column, use something like

=LOOKUP(Parameters!selectDate.Value, Fields!KeyID.Value, Fields!PreviousMonthKey.Value, "DataSet1")

This reads "get the value in selectdDate, find this value in the column KeyID in the dataset called DataSet1 and return the value found in the column called PreviousMonthKeyID"


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...