Talis Developer Network - Comments

bulk removal of interest codes

lcartwright — Mon, 30 Jan 2012 13:48:12 +0000

Hi
I would like to be able to run this delete on 1st Feb. My test runs on MIS confirm the expected number of rows and the correct Interest codes deleted. I've checked the results against some Decisions queries and it all LOOKS ok. I'd feel happier to have a confirmation that deleting these rows will not have an affect on another process or part of the DB.
many thanks
Lin

bulk removal of interest codes

lcartwright — Fri, 27 Jan 2012 14:11:04 +0000

I realise now that I probably shouldn't remove any from the grouping table as this contains all the codes configured in Alto. I had originally thought that this table was populated when a code was used by a borrower record. Derr!
So.. is it ok to just remove the relevant rows from the borrower grouping link table.
Lin

Ranking

Brian Crampton — Fri, 6 Jan 2012 17:10:56 +0000

Ranking works in Webi and changes the selected block (a table is a type of block as is a chart) immediately.

You need to have the block that you want to apply the ranking to highlighted in order to enable the ranking button otherwise it is greyed out e.g. if the report as a whole is selected.

Brian Crampton
Consultant, Capita

Ranking

JohnHudson — Fri, 6 Jan 2012 11:07:44 +0000

Should this work in the web interface? I always struggle to get any useful information out of Decisions and this approach looks ideal. However, when I do the "Rank" step, nothing appears to happen, although "sorts" in my memory seem to work immediately. I've even tried refreshing and I still don't get the ranking after clicking on this option.

Thanks.

--
John Hudson, Gateshead Council Libraries and Arts

Minutes in Decisions reports

Brian Crampton — Mon, 5 Dec 2011 09:19:43 +0000

Unfortunately there is not a minute function in the BusinessObjects list of functions so it would require adding minutes as an object to the Universe and using the Sybase functionality to extract that information. I can add it as a potential change in the next set of universes.

In the meanwhile there is a potential work around although it does rely on modifying an existing time object which then can't be used as a result object but can be used as a condition object.

I would suggest using the Year object as it is possible to extract the Year value from the full date time object using the BusinessObjects function if it is required.

If you select the Year objects from the due date set of date objects (or create date) and then check the SQL generated by clicking on the SQL button in the query panel.

Within the SQL you will see the line below in the SELECT section

datepart(yy,dbo_LOAN1.DUE_DATE)

Click on the custom SQL button and you can change the SQL to

datepart(mi,dbo_LOAN1.DUE_DATE)

where mi means minutes.

If you then save the change and run the query then the year object will contain the minute value. The heading will still say Year due but this can be modified.

Also bear in mind that if you subsequently change the query the manual change will be removed and will have to be done again.

In terms of grouping you can then create a variable for each half hour to use in reports e.g.

=If(([Year due] Between (0;29));"00-29";"30-59")

Now the wording the quotes will depend on the output you are looking for e.g. it might be better to have 00 and 30 rather than show a range.

Thanks

Brian Crampton
Developer, Capita

Invoice reporting in Decisions

lcartwright — Wed, 19 Oct 2011 16:54:12 +0100

Hello Brian
thanks very much for the help.
I've managed to get to the figures that we needed using your suggested method but using the date of payment object from the invoice creation details folder in the query filter panel. The invoice value is a variable that sums the item cost measure.

I think that library managers are needing to look closely at the volumes and type of work that staff are doing so being able to produce figures from the system to provide evidence is becoming more important.

It seems that we can get more out of the universe than you thought!!
thanks again
Lin

Invoice reporting in Decisions

Brian Crampton — Tue, 18 Oct 2011 15:35:12 +0100

Within the Orders universe invoice reporting is limited especially when considering the invoice line data and payments.

The Orders universe was created before the all the current invoice functionality was introduced and the original design of the universe makes it difficult to update in a way that works successfully.

However it should be possible to do something along the lines you mention. One issue is the Completed date. The current Orders universe does not have an object for the data completed. This is actually a status change date and the way it is stored in the database makes it more complicated to use in reporting than might seem when looking in Alto. It is only possible to report on the Invoice date and the create date.

The following is an an example report so I'm not sure if it matches the objects you want but it does give an indication.

1) From the Orders universe select the following objects in the results Objects panel:

2) Then select the Invoices | Invoice date reporting | Invoice date as a Query filter and set the date range as required. You may also want to limit the suppliers at this point as well.

3) Run the query. You will get a default block which I would then delete.

4) Create a variable, Total with the following formula to add the service charge and net value together.

=[Item net (base currency)]+[Service (base currency)]

5) Select the Supplier code (and Supplier name if you wish) object and the Total object from the report manager and drag into the report and create a block. I think that would give the breakdown you require.

6) If you add the Invoice number object into the block you can then do some checks to confirm that the invoice value in the report matches Alto.

Now there may be more issues based on other objects you may want but that would be the starting point. It seemed to work in my initial tests.

Thanks

Brian Crampton
Developer, Capita

Invoice reporting in Decisions

lcartwright — Mon, 10 Oct 2011 16:48:17 +0100

I have a request for a Decisions report on invoices but I am thinking that I can't actually provide the information requested.
It is on the face of it a simple request but how do I do it? To begin with there doesn't appear to be a way of selecting invoices with a status of completed.
Please tell me I am wrong!!
the actual request is as follows...
"For invoices completed between April 2010 and March 2011 for supplier codes BERTRAMEDI, ULVEREDI, MAGEDI provide the total monetary value of the invoices."

Thanks Brian. We'll have a

Rhion Pritchard — Tue, 27 Sep 2011 10:53:41 +0100

Thanks Brian. We'll have a go at it in the next few days.

Rhion Pritchard
Manager, TalNet

Borrower Anonymiser

runderwood — Fri, 9 Sep 2011 14:25:04 +0100

Hi Brian

Thank you for your comment and suggestion. I inserted your line into the Borrower Anonymiser script, tested on a small number and it looks to work. Am doing a proper test next week (once we have restored to the MIS) and should have a better idea on running time.

Many thanks once more.

Rach

res_add_itms and location email address

Brian Crampton — Thu, 8 Sep 2011 16:10:57 +0100

The issue in this case is that the relevant item details for each item including the location name are held in an array sorted by sequence, class etc and then the array is used to produce the output. The output is not produced as each item is processed.

This means that the location email address needs to be added to each row in the array, @sorted_data. The code then also needs to be extracted correctly from the array once sorted and about to be sent to the output file.

It is not a complicated process as such but requires a number of changes throughout the retrieve.pl, select.pl, main.pl and format.pl files.

There is an general article on the use of arrays in Capita scripts, Adding variables to MIS PERL scripts that use arrays. While it doesn't specifically mention the res_add_itms script it does give an overview of what would be required.

Thanks

Brian Crampton
Developer, Capita

Borrower anonymiser

Brian Crampton — Wed, 7 Sep 2011 11:44:21 +0100

Changes to scripts could potentially affect support not least in terms that the change would not be included in any updates of the standard script. However any script you write yourself would be the same.

To avoid confusion I would change the name of your version. It is also important that you fully test any changes on the MIS first so you are happy.

Now I haven't tested this but there is a section in the script

@tmp = &one_by_n_sql ("select LOAN_ID, ITEM_ID, BORROWER_ID, LOAN_TYPE,DUE_DATE,ACTION_ID from LOAN where LOAN_ID = $tmploanid");

&sql ($d, "exec CG_INS_LOAN_SP $tmp[0], $tmp[1], $tmp[2], 'F', 2, $tm
p[3], \'$tmp[4]\', \'$dt\', '', '$OPERATOR', $tmp[5]");

The second line inserts the discharge row in the LOAN table and the variable $temp[1] holds the ITEM_ID so a line like below after the CG_INS_LOAN_SP should do the job.

&sql ($d, "update ITEM set STATUS_ID=101 where ITEM_ID=$tmp[1]");

The STATUS_ID value would depend on your local configuration.

As I said I haven't tested this so it is only a guide.

Thanks

Brian Crampton
Developer, Capita

Borrower Anonymiser

runderwood — Wed, 7 Sep 2011 09:59:27 +0100

Hello Brian

Thank you for your comment.

We are concious about timings on our server, due to their age etc. So anything that puts less strain is a good thing.

Admittedly I have now got the basis for a script pulling the Barcodes over from the bor_anon_delete.rep file, but I am now struggling with the import to sql.

I did think about a code change in Borrower Anonymiser as it seems it would be a quick step, however I looked at the code within it and was a bit overwhelmed and really did not know where to start. Any pointers would be gratefully received. If we made a change to this script, would that affect any support that we may need in future?

Look forward to hearing from you.

Rach

Borrower anonymiser

Brian Crampton — Tue, 6 Sep 2011 11:40:54 +0100

It would probably be more straightforward to update the item within the borrower anonymiser script itself using a code cahnge rather than write a whole new script to extract barcodes and then update the database.

However there is the loa_long_odue script which can pick up loans longer than a specified number of days and you can change the status of the item using this script first.

Also bear in mind that any changes to values in the ITEM table inserts a row in the ITEM_UPDATE table. In addition to the performance issues in updating large numbers of items in a single update which isn;t relevant here, that processing a large number of item updates also has a knock on effect on the update_daily_access_points script performance as there is many more items to process.

Thanks

Brian Crampton
Developer, Capita

Retrieving Bib information from list of barcodes

Jamie — Wed, 10 Aug 2011 13:17:19 +0100

I have a list of 20,000+ barcodes and need the bibliographic information related to each one.

Does anyone know if this is this possible via Decisions or another script?

Thanks in advance

Jamie