Crystal Reports- +ve's and -ve's

Crystal reports is a very popular Reporting tool. Below is an attempt to measure its effectiveness in reporting.

+ve's

1. The simplicity of the tool.The tool asks for a connection and once you provide it either to a DB or a cube the report can be filled up through drag and drop of the fileds.
2. The parameter feature can be used for Yes/No questions etc as it does not have any radio buttons kind of feature.
3. The supress feature can be used extensively to get various functionalities. It depends on how and where you use it.
4. The formula fields can be used in tandem with supress feature and parameters,this can make many complex things simpler.
5. There are feature like on demand subreports(hyperlink to other reports) or normal subreports as well.

-ve's

1. Deleting any unused parameters is a pain sometimes as they dont get deleted and cause problems later
2. Only one level of sub report nesting is available.(You cant have subreport within a subreport)
3. We can have only one query for a report.(We cant have separate queries for different measures as in BO)
4. Aliases can be added but they really act weirdly.(so avoid them)
5. Formatting the report takes double the report development time.
6. Tables cant be added to the report(Lines have to be drawn..!!!)
7. Export feature is good only for PDF format. For other formats you cant even compare the report and the exported copy.

T-SQL : Sweet and Sour

I have working on SQL server 2005 (t-sql) lately. This blog is a comparison between the pl/sql features available in t-sql,db2 v 8.2 and oracle 9i pl/sql.
This blog will be updated when anything new turns up.
So here goes...

# Feature Available in SQL server 2005? DB2 Oracle 1 Exception handling Yes. yes yes 2 Transaction Yes. yes yes 3 ELSIF construct No yes yes 4 IF exists () Yes. yes no 5 Max column size 32000 32000 4000 6 Implicit Cursor No Yes Yes 7 Recursive query Yes. Similar to db2 but only 100 literations yes yes : using connect byprior 8 Print Command Yes No Yes 9 Dynamic SQL Yes Yes Yes 10 Dynamic SQL STRING DATATYPE nvarchar and nchar varchar varchar 11 Variable and cursor name same yes yes no 12 Default value while variable declaration no yes yes 13 Sequences No Yes yes

Extracting parts from a Comma seperated string

One of the requirements in our project was to extract individual column names from a string containing comma seperated values i.e if the string has say:
v_column_string := ',country_code,country_name,country_descripton,'
I wanted to get each column name from this string. Here's how I wrote the query for that..


[CODE]
SELECT *
FROM(
SELECT
SUBSTR(v_column_string ,
INSTR (v_column_string , ',' , 1 , A.LVL)+ 1,
INSTR (v_column_string , ',' , 1 , A.LVL + 1 ) -
INSTR (v_column_string , ',' , 1, A.LVL)-1
) AS COLUMNS
FROM
( SELECT ROWNUM LVL FROM ALL_OBJECTS WHERE ROWNUM <= no_of_words)A

) B
WHERE B.COLUMNS IS NOT NULL

OUTPUT :

country_code
country_name
country_desc

where no_of_words is the number of words in the String(in this case it is 3). In case you don't know how many words might be there, you can go ahead and give the length of the string here(although this will be an overhead as many nulls will be returned , which is why i have a filter for not null).

Notice that I have given the string with a leading and ending comma i.e ',country_code,country_name,country_desc,'. This is basically to extract the first word and the last. If you don't want to give these two comma's you can tweak the query to handle it.

Hope you wil find this useful someday:)

CLOB Datatype

Ah!! The first Tech Post...
Some time back, i had tried out some things to get the features of the CLOB datatype in DB2.
I will share the details, so that you guys need not spend more time on it if in some point of time you need to find the features of CLOB.
Some of the features are w.r.t storing a procedure text in a clob variable.

Features Of CLOB Variables

No Parameter Supports
1 Size upto 2GB - 1 byte

2 Logging Yes

3 Reading Directly from disk. Buffer Pool Not Used.

4 Execute immediate
of DMLs specified in
CLOB Yes
[Code]
CREATE PROCEDURE GTT_CORE.CLOB_TEST
LANGUAGE SQL
BEGIN
DECLARE V_CLOB CLOB;

SET V_CLOB ='INSERT INTO GTT_CORE.DUMMY VALUES(1)';
EXECUTE IMMEDIATE V_CLOB;

END

5 Compilation of Procedure
as dynamic statement Yes
[Code]
BEGIN
DECLARE V_CLOB CLOB;

SET V_CLOB =
'Create procedure GTT_CORE.PROC
LANGUAGE SQL
BEGIN
DECLARE V_COUNT INTEGER;
SELECT COUNT(1) INTO V_COUNT FROM GTT_CORE.DUMMY;
INSERT INTO GTT_CORE.DUMMY VALUES (V_COUNT);
END';
EXECUTE IMMEDIATE V_CLOB;

END


6 Indentation of procedure Yes

7 Global Temporary Table

With a CLOB column NO. DB2 SQL error: SQLCODE: -350, SQLSTATE: 42962,SQLERRMC: V
Message: LOB, DATALINK, or structured type column ""V"" cannot be used in an index, a key, a unique constraint, a functional dependency, a generated column, or a declared temporary table.

[Code]
DROP PROCEDURE GTT_CORE.CLOB_TEST
GO
CREATE PROCEDURE GTT_CORE.CLOB_TEST
LANGUAGE SQL
BEGIN
DECLARE V_CLOB CLOB;
declare GLOBAL TEMPORARY TABLE DUMMY_CLOB (V CLOB) ON COMMIT PRESERVE ROWS NOT LOGGED WITH REPLACE;
SET V_CLOB =
'Create procedure GTT_CORE.PROC
LANGUAGE SQL
BEGIN
DECLARE V_COUNT INTEGER;';
SET V_CLOB = V_CLOB||'
SELECT COUNT(1)
INTO V_COUNT
FROM GTT_CORE.DUMMY;

INSERT INTO GTT_CORE.DUMMY VALUES (V_COUNT);
END';

insert into SESSION.DUMMY_CLOB values(v_clob);

END

8 Storage of CLOB in a table.
Is it with indentation?? Yes
[Code]
DROP PROCEDURE GTT_CORE.CLOB_TEST
GO
CREATE PROCEDURE GTT_CORE.CLOB_TEST
LANGUAGE SQL
BEGIN
DECLARE V_CLOB CLOB;

SET V_CLOB =
'Create procedure GTT_CORE.PROC
LANGUAGE SQL
BEGIN
DECLARE V_COUNT INTEGER;';
SET V_CLOB = V_CLOB||'
SELECT COUNT(1)
INTO V_COUNT
FROM GTT_CORE.DUMMY;

INSERT INTO GTT_CORE.DUMMY VALUES (V_COUNT);
END';

insert into gtt_core.dummy1 values(v_clob);


END


Hope it will be useful someday

-- Rahul Nair

Posting Guidelines

I have identified some posting guidelines to help us have better posts
Authors

1. Please Contribute through new posts. Don't Use comments section.
2. Reply to questions through Comments(responses) link.
3. Please Give appropriate labels

Some labels can be : Oracle, DB2, SQL server, DW, Java,SQL,PL/SQL
Labels can help to find postings fast if there are too many :)

Non Authors

Please Post Question in Response link

We can add to the above when need arises. But let us first kick this off to a good start.

Knowlege Pool

Welcome to Knowledege Pool Guys!!!
This is an Initiative for having a knowledge repository accessible, wherever you are.
Learn . Share and Question
So lets take it forward.....