This isn't really a blog, its more of a holding page for my domain (seems a shame not to have a page), if I know you then add me on either LinkedIn or Facebook (links are on the right), however if I don't know you then I won't add you!

Wednesday, 12 June 2013

Can I use "user" as a parameter name in SSRS (when using Oracle)

The answer is no!

Although SSRS will allow you to create a parameter called user, if using Oracle SQL it will not let you reference the parameter in a dataset, the warning “ORA-01745:invalid host/bind variable name” will be displayed whne running your dataset. 

I spent ages checking through my dataset until I realised that user must be a reserved term within the dataset that cannot coexist as a parameter name, again as per my posts hopefully this helps someone else out!

Tuesday, 11 June 2013

Crystal Reports - IF IN Expression

Carrying out development between different tools such as SQL, SSRS expressions and Crystal reports often ends up in headaches about the syntax and functions available (as there are big similarities).  When writing a Crystal Expression to include an IF statement that looks at a range of values (in a similar way to an IN clause in SQL) there are a few things to keep in mind.

For example
IF {MY_ELEMENT.STREAM} = "SA" THEN "Shop closed" ELSE “Shop open”
This expression will only read “shop closed” if the stream field is equals to “SA”, however if I have more than one value that equates to shop closed then I need to think about the construction of my expression.

I could write a simple OR in, however this becomes unwieldy the more values that will display as shop closed.

I could write a case statement within my SQL, however it could be that I am using a snapshot at a given moment in time which requires me to include the logic natively into my report. 

Using the IN string function, a series of values can be included within the IF statement with minimum bulk to the expression, however it operates slightly differently to the way it is used in SQL.

IF {MY_ELEMENT.STREAM} in("SA""SU”) THEN "Shop closed" ELSE “Shop open”

The separate values are stored within the brackets and separately quoted, however they are not separated by commas as in SQL.

Hope someone finds this useful, I struggled to find anything online about doing this in Crystal!