Report sharing with a value of 5000 RMB and 5000 RMB report sharing

Report sharing with a value of 5000 RMB and 5000 RMB report sharing

I chatted with a friend and found that he had made a great report recently. In his words, it should be at least RMB. I will share it with you and make progress together.

In the words of his friend Xiao Zhang, he recently received a demand from the company's finance department. He needed to collect statistics on the cost reduction information of each department through the cost reduction plan of the procurement and R & D departments, then IT would be too difficult for the finance department's sister-in-law to make statistics manually. If this is the case, ask the IT staff to use the report software FineReport.

Friend Xiao Zhang analyzed the data sources of the Plan and thought about the following logic:

① Information contained in a single price list table: price date, supplier, and leading department

② Information contained in the Single Table of the price list: Product NO., unit price, original unit price, effective date, and expiration date

③ Purchase order ticket header information: Ticket, Document No., supplier, and document date

④ Purchase order single information: product number, name, specification, purchase unit price, Tax Rate

As a result, Xiao Zhang thought of how to obtain the original unit price. The final report is as follows:

The logic of original unit price acquisition is to capture the original unit price information of the corresponding ticket when the receipt date is between the validity date and expiration date of the ticket.

 

The following is a function for getting the original unit price:

ALTER Function [dbo].[UF_GetLastPrice](@TL004 AS nvarchar(10),@date as nvarchar(8),@item as nvarchar(30))returns decimal(18,6)asbegin--set @TL004='09001'--set @date='20150116'--set @item='10102069620001';declare @price as decimal(18,6);declare @zdbm as nvarchar(20);with b as(select TM004,TM014,TM018,TL004,PURTL.UDF01 from PURTL inner join PURTM ON TL001=TM001 AND TL002=TM002where TM011='Y' AND TL004=@TL004  and   TM014<=@date and TM004=@item)select @price=isnull(t.TM018,0),@zdbm=t.UDF01 from (select b.UDF01,b.TM004,b.TM018,ROW_NUMBER() over(partition by b.TM004 order by b.TM014 desc )rnfrom  b ) twhere rn=1return  @priceend

　　

In this way, you can get the original unit price. You can use FineReport to implement the desired information.