Noetix: Improving Performance of the GMF_SLA_Cost_Subledger Template at R12

Looking at the source code behind this view it quickly becomes apparent that, with nine UNIONS each of them with a full GROUP BY, unless you have a very finely tuned database you are going to get performance problems when accessing this view. This is especially true if you are querying it using something like the Production_Batch_Number (only populated by Query position 9).Probably the quickest (and easiest) way to improve performance is to cut out...

Read More

SSRS: Changing US-format Date/Time Pickers (SharePoint Integrated Mode)

If you have, like us, configured your servers and clients for a non-US locale and then been surprised to see that when running SQL Server Reporting Services (SSRS) all the date/time pickers seem still, somehow, to be stuck in US Date/Time format the fix is actually incredibly easy.Basically the issue is that SharePoint uses neither the Locale of the Server or the Locale of the User but instead uses the SharePoint culture to determine the format that...

Read More

Oracle PL/SQL: Querying Inventory Quantities in R12.

As you have probably gathered from my other posts the company I work for uses NoetixViews for reporting against Oracle e-Business Suite however it looks like, at Release 12, Oracle has started providing some reasonably useful API's to get inventory quantities.Take a look at the INV_Quantity_Tree_Pub package (under the APPS user) in your database. The "query_quantities" API has the following parameters;P_API_VERSION_NUMBER    NUMBER    IN    P_INIT_MSG_LST    VARCHAR2   ...

Read More

Open-Source Replacement For Closed-Source Software

It's not often case that I'm forwarded something interested to read at work in a technical email but this time was a definite exception. The following URL cropped up in a conversation around Drupal;http://www.opensource-it.com/open_source_alternatives_proprietary_enterprise_softwareWhat it basically is is a website which allows you to search for a piece of closed-source Software, for example Microsoft Project and see that the Open-source equivalents...

Read More

SSRS: Scheduling/Subscribing To An Existing Report

This is a fairly simple blog post with details of how to schedule an existing report so that it will run automatically and send you the report. The process below describes using Reporting Services 2008R2 running in Sharepoint Integrated mode - standalone more is similar but not identical.It's worth noting that in order to schedule a report to run it's vitally important that the critical parameters for the report (i.e. reporting period) have default...

Read More

SSRS: Poor Support For Troubleshooting Of Dataset Errors

With apologies this post is slightly more of a rant than an actual solution to a problem!We have just completed our migration from Business objects (6.5 - ancient) to SSRS for reporting against our businesses most important system; Oracle e-Business Suite. As you can imagine this has been a major piece of work (300+ reports) and a major amount of time.It's actually a tribute to SSRS that we have managed this in about 8 months with a team of FIVE...

Read More

Configuring GMail Access (with Push Notification) on an iPhone/iPad/iPod

The first thing you're probably thinking is "why not just use the standard Gmail account type?" and it's a perfectly valid question. I recommend using exchange because it gives you Push Email - this means when you get email it "instantly" appears on your device rather than your device checking for email at regular intervals (called Polling Email). Depending on your volume of emails having Push Email could save you considerable amounts of battery...

Read More

Oracle PL/SQL: Using DBMS_LDAP To Query Active Directory

It's always useful to be able to retrieve details from Active Directory when working within an Oracle Database (I'll do a separate post later on how we use LDAP records to update our employee email addresses stored in Oracle).Oracle have created a package called DBMS_LDAP, I have to say when I first found it I thought it was new but it has apparently existed in one form or another since Oracle 9i. It is fairly self explanatory to use, the main (useful)...

Read More

SSRS: Far Future Date Issues For Date/Time Parameters

It's probably easiest just to show an example of problem (the quick solution is at the end).Create a new report following the instructions here only use the following SQL;SELECT TO_DATE(:Data_Value)FROM DUALThis assumes you are using an Oracle database to connect to. It's possible that this could be just an Oracle issue but it's pretty unlikely.Once the report is completed run it with the parameter '01-JAN-2070';2070 Parameter Passed ThroughIn the...

Read More

PL/SQL: When Were Your Tables Last Analysed?

As I'm sure any DBA will tell you one sure-fire way to have terrible-performing queries is not to update the statistical information Oracle is using to work out how to efficiently execute your query. Luckily Oracle keeps track of when the information was last updated for each column in the DBA_Table_Columns view.Here is a simple query that will give you a list of tables that have not been analysed in the last week;SELECT dtc.OWNER, dtc.TABLE_NAME, dtc.LAST_ANALYZED  FROM dba_tab_columns dtc WHERE dtc.OWNER NOT IN ('SYS', 'SYSTEM')  ...

Read More

SQL Server: Fixing Integer Division Division Issues

This was an interesting one I wasn't expecting to stumble upon this morning.At the moment I'm writing some cost monitoring SQL that looks at the Execution logs on a reporting services instance and works out, at a rate per hour, how much time and money the business is spending just waiting for reports to execute (either fetching the data, process the data, or rendering) - this is on the assumption that when we add processors/ memory to a box it's...

Read More