Using Arrays to display data from database field

[makk07]

hi everyone,

I am using crystal reports XI and I am displaying the usergroup name from usergroup field in the database using arrays.

I created two formulas in subreport:
formula 1: To Initialize arrays
formula 2: To store values into an array

And in main report,I have created another formula(@Display Group Name) to retrieve the values and I put the formula in detail section.

Also my report is grouped on liaison name field and security type id field.

The problem is when there is more than one security type id for any particular liaison name I am getting repeated usergroup name on my report which I don't want.

Here is my code:
For the subreport:

Formula 1: (Ini Vars)//Initializing arrays to display usergroup name (Placed in RHa and suppress the section)

shared stringvar array gName;
shared numbervar array gID;
shared numbervar gNameSize := 0;

redim gName[995];
redim gID[995];
""

Formula 2: (Pop vars) //populating variables to display usergroup name (Placde in GF1 and suppress the section )

shared stringvar array gName;
shared numbervar array gID;
shared numbervar gNameSize;

local numbervar i;


gNameSize := gNameSize + 1;
i := gNameSize;

if i <= 995 then
(
gName := {usergroup.usergroup_name};
gID := {usergroup.usergroup_id};
)

** Group By usergroup.usergroup_id

For the Main Report:

Create a formula by name @Display Group Name (placed in a detail section)

//This formula is to display UserGroup Name

shared stringvar array gName;
shared numbervar array gID;
shared numbervar gNameSize;


local numbervar i;
local stringvar tString;

For i := 1 to gNameSize step 1 do
(
if gID = {vtsm_audit_log.group_id} then tString := gName
);

tString;


Please help me out.I got stuck,please help!

Thanks in advance
makk07

 

[IanWaterman]

Looks like you are duplicating names in the array.

Try modifying formula 2

Formula 2: (Pop vars) //populating variables to display usergroup name (Placde in GF1 and suppress the section )

shared stringvar array gName;
shared numbervar array gID;
shared numbervar gNameSize;

local numbervar i;


gNameSize := gNameSize + 1;
i := gNameSize;

if i <= 995 and not({usergroup.usergroup_name} in gName) then
(
gName := {usergroup.usergroup_name};
gID := {usergroup.usergroup_id};
)

Ian

 

[makk07]

thanks for your help.
I tried doing that but still its not working, do you have anyother suggestion.

thanks a lot again.please let me know if you have any other idea.

 

[IanWaterman]

Try putting a formula in subreport which displays each element of the array (as they are added) and see when an an element is duplicating.

That may give you some clues as to why its happening.

Ian

 

[makk07]

Please Ignore the earlier code.Sorry to bother you.

Here is the updated code in bottom,Please go through it.I really appreciate your help,also I am new to reporting field and I am facing difficulty.

Using Arrays I am trying to display usergroup name and security type description from diff. database tables.

1.For the usergroup name the link in the array formula is:
{usergroup.usergroup_id}={vtsm_liaison_group.usergroup_id}

2.For security type desc the link the array formula is:
{security_type.security_type_id}={vtsm_liaison_security_type.security_type_id}

3. I have a group in main report on vtsm_liaison_security_type.security_type_id

4. In the main report I am displaying usergroup name,security type description,security type id etc.

The problem is when I have more than one security_type_id
then usergroup name is getting repeated(I mean for every security_type_id its showing the same usergroup name)

In my main report,I am having two subreports. One for the purpose of displaying usergroup name and the other for displaying the security type description.

For the first Subreport: I am using “usergroup” table.

Formula 1Ini Vars
//Initializing arrays to display usergroup name

shared stringvar array gName;
shared numbervar array gID;
shared numbervar gNameSize := 0;

redim gName[995];
redim gID[995];
""

Formula 2Pop Vars
//populating variables to display usergroup name

shared stringvar array gName;
shared numbervar array gID;
shared numbervar gNameSize;

local numbervar i;


gNameSize := gNameSize + 1;
i := gNameSize;

if i <= 995 then
(
gName := {usergroup.usergroup_name};
gID := {usergroup.usergroup_id};

)

For the Main report: I am using 2 tables(1. vtsm_liaison_group & 2. vtsm_liaison_security_type)

@Display Group Name //This formula is to display UserGroup Name(I placed in Detail section)

shared stringvar array gName;
shared numbervar array gID;
shared numbervar gNameSize;


local numbervar i;
local stringvar tString;

For i := 1 to gNameSize step 1 do
(
if gID = {vtsm_liaison_group.usergroup_id} then tString := gName
);

tString;
------------------------------------------

For the second subreport:I am using “security_type” table

Formula 1Ini Vars Sec
//Initializing arrays to display security type description

shared stringvar array secTypeDesc;
shared numbervar array secTypeID;
shared numbervar secTypeDescSize := 0;

redim secTypeDesc[995];
redim secTypeID[995];
""

Formula 2Pop vars sec

//populating array variables to display security type description

shared stringvar array secTypeDesc;
shared numbervar array secTypeID;
shared numbervar secTypeDescSize;

local numbervar i;


secTypeDescSize := secTypeDescSize + 1;
i := secTypeDescSize;

if i <= 995 then
(
secTypeDesc := {security_type.security_type_desc};
secTypeID := {security_type.security_type_id};
)

For the Main report:

@Display sectype desc //This formula to display security type description ( I placed in Group Footer section)

shared stringvar array secTypeDesc;
shared numbervar array secTypeID;
shared numbervar secTypeDescSize;


local numbervar i;
local stringvar tString;

For i := 1 to secTypeDescSize step 1 do
(
if secTypeID = {vtsm_liaison_security_type.security_type_id} then tString := secTypeDesc
);

tString;

I really appreciate your help,your suggestions you gave me so far.

Thank you very much
makk07