PurchaseOrderLaser.rdl Customisation – SSRS


Introduction

I find it quite bizarre, but the purchase order report issued as standard, does not include freight. we process freight as a landed cost, but it is charged from the goods vendor as standard …… this is a real headache. If we do not process it as a landed cost, the freight is dealt with as a miscellaneous cost and not rolled into the item cost on receipt therefore we do not have a truly representative item cost rolling into the COGs.

As such our purchasing team have asked me to add the landed cost on the po to the report issued to vendors. This might sound easy, but there is a couple of things to bear in mind :-

  1. The po report is generated from an Infor SP, so you can’t directly change that else you will risk loosing your changes when applying any patches or upgrades
  2. During our Infor training we were taught to ex-gen the SPs, but according to our Infor account manager you are technically not allowed to do this due to copyright (Based on our previous account managers advise I think this is incorrect but for the purpose of this exercise we are going to accomplish the desired result without an ex-gen)
  3. If you do choose to ex-gen the SP, you will have to re-do it on patch deployment or upgrade anyway.

Just remember if you are customizing reports to make a copy of the RDL (we prefix ours with PBTI_) , then amend your background task definitions accordingly – and ensure you deploy them to the correct location!

Adding a Data Source

The first thing to bear in mind is we run multiple sites on a single server, but we only run 1 version of each report and make the data within them dynamic. As such I am using en expression on my connection string to determine the database to use. At present the connection string caters for 2 key databases, I will be replacing this with a switch. It works as follows :-

=“Data Source=<DBSERVER>;Initial Catalog=” & iif(Parameters!BG_SITEID.Value = “UKWL”, “<DB1>”,“<DB2>”)

SSRS_DataSource_Po

Data Set

The data set itself is relatively straight forward as shown here :-

SSRS_DataSet_PoLandedCost

You will note though that there is a potential issue, where as it filters on the parameter @PoNum, this is bound to the report parameter [@pStartPoNum]. This means that if you run a batch of PO’s they will all show the landed cost for the first PO only – I will show you how I overcame this later.

Field Expression

This is more or less a copy-paste job from Infors PO Value field, as I wanted it to remain the same format. The expression is as follows :-

=IIf (IsNothing(Fields!std_symbol.Value),trim(Format(Round(Sum(Fields!Freight.Value, “PoFreight”),Fields!TotalCurrencyPlaces.Value),Fields!TotalCurrencyFormat.Value)),Fields!std_symbol.Value & (” “) & trim(Format(Round(Sum(Fields!Freight.Value, “PoFreight”),Fields!TotalCurrencyPlaces.Value),Fields!TotalCurrencyFormat.Value)))

If a batch of POs are requested

On the visibility of the text boxes I have added the following expression which calculates whether more than 1 po has been requested. If it has it hides the fields :-

=RIGHT(Parameters!pEndPoNum.Value,8) – RIGHT(Parameters!pStartPoNum.Value,8) > 0

 

 

 

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.