Application

SQL Agent – Environment specific workflow

SQL Agent – Environment specific workflow

I like to keep my SQL Agent jobs in sync between environments, however not all jobs need to be run in all environments. To handle this, I’ve been using some TSQL to force a failure and end the job at a point. Here’s the overall job Within that, the TSQL for the ‘–Finish here if DEV–‘ step is To control the workflow, I’m setting the on success and on failure as follows:

read more

Controlling Chart axis in SSRS

Controlling Chart axis in SSRS

Had a tricky one today where the business wanted more axes to show on an SSRS report. Seems fair enough, cause as this example shows, a single line is pretty bland. After a bit of reading, I found a few clues over here. The concept is to convert a number to scientific notation, then use the first digit as a multiplier against 10 to the power of the exponent. This generates a nice round number, which is also divisible by 5. Here’s the code, I just pass the range of values on the chart (the calc is also used for the max of the chart), and the function returns an appropriate interval Here’s how my chart looks wtih the interval calcs: But… well that doesn’t look great either, because 600, 1200, 1800… and the maximum isn’t shown. So then the code ‘evolved’ again… End result:

read more

SSRS Missing Functionality

SSRS Missing Functionality

Just putting this out there cause surely I can’t be the only one missing this… If you install SSRS on SQL Server 2016 Standard Edition, quite a few of the menu items will be missing. Items such as: Mobile Reports KPI Reports Branding The solution.. install SSRS on Enterprise Edition.

read more

SSIS Framework Selection

SSIS Framework Selection

After 16 years of BI development in the IBM Cognos stack, I’ve recently jumped ship over to Microsoft and have faced a pretty steep learning curve as I plan to build a robust data warehouse and reporting solution in three months! To jump start this activity I’ve been reviewing SSIS Frameworks, which will provide the ‘belts and braces’ around ETL code without my having to invest months building this logic around logging, notification, error detection / recovery and job flow from scratch.

read more

Auto-Generated prompts in Cognos 10 BI

Auto-Generated prompts in Cognos 10 BI

I had an issue with Cognos 10 today whereby the automatically generated prompt control for a BI report was showing a drop down list, rather than a type in prompt. The reason this was an issue is that I wanted to set up a schedule of this report, with this particular prompt being for ‘Client Code’. As we have many thousands of clients, there seems to be a limitation with auto-generated drop down prompts whereby they only showing the first thousand or so records. After some experimenting, I was able to find that coalescing the column with a null string would force the auto-generated prompt to be a type-in box, thus allowing the client code to be entered. Here is the code originally used by my filter: #/* Strategic Client */# [Engagement Revenue].[Strategic Client Level Dimension].[cons_client_cd] = ?pStrategicClient2? and here is the modified code that uses a type-in prompt box: #/* Strategic Client */# coalesce ([Engagement Revenue].[Strategic Client Level Dimension].[cons_client_cd], ”) = ?pStrategicClient? Below is a picture showing both these prompt behaviours: Note: I’ve since discovered you can also set the prompt behaviour in the Framework Package

read more