Crystal Reports 2008 to extract data

[guesswhoami]

I'm using Crystal Reports 2008 to extract data from
/XYZ/ABC/1433 .rpt/

How do i extract data upto /XYZ/ABC/ and display that as a folder name

 

[lbass]

Please show samples that include the part "up to" /XYZ, etc. Is the "/XYZ" the same in all records?

-LB

 

[guesswhoami]

Thanks for the response.

I am working on a report in crystal 2008 where the Folderpath field needs to be split because it is separated by a '/'

For example the following data in the FolderPath field
/servername/Server Intelligence/Servers/CustomerDetail.rpt/ needs to be split and displayed as /servername/Server Intelligence/Servers
since CustomerDetail.rpt is a rpt file i do not want that to be displayed as folder path in my report.

few other examples are.
/servername/Users/ : This has to be displayed as it.

/Auditor/en/Average Number of Users Logged In.rpt/ has to be displayed as /Auditor/en/


Help Appreciated.

 

[hilfy]

The "InStrRev()" function gets you the position of the last instance of a specific character or string within another string. So, you should be able to create a formula that's somethign like this:

left({table.folderpath}, InStrRev({table.folderpath}, '/'))

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

If it would be correct to assume that you are trying to exclude that part of the folderpath that includes an extension using ".", then you could do this:

stringvar array x := split({table.folderpath},"/");
numbervar i;
numbervar j := ubound(x);
stringvar y := "";
for i := 1 to j do(
if instr(x,".")<> 0 then
y := y else
y := y + x+"/"
);
left(y,len-1)

-LB

 

[guesswhoami]

Thanks.

The formula is working as desired for some folder path but for some it does not .for ex consider this folder path in the table .
Admissions/CustomerDetails - Weekly

The way the it is being displayed on the report is Admissions/CustomerDetails - Weekly

But it should be displayed as Admissions since Admissions is the main folder and under this folder there are several rpt files for ex CustomerDetails - Weekly, CustomerDetails -Monthly, CustomerDetails - Quarterly etc.

The way i want to display it in the report is a follows:
Admissions
CustomerDetails - Weekly
CustomerDetails -Monthly
CustomerDetails - Quarterly

as opposed to

Admissions/CustomerDetails - Weekly
CustomerDetails - Weekly.

 

[lbass]

I mentioned that this assumed you were excluding array elements that included ".". You will have to establish the rules that distinguish the higher order level from the files, e.g., the files contain extensions with "." or with "-". Or are you saying it is always everything but the last component? If so, then try a variation on hilfy's suggestion:

stringvar x := {table.folderpath};
stringvar y := left(trim(x),len(trim(x))-1);
left(y,InStrRev(y,'/'))

-LB

 

[guesswhoami]

Thanks a lot LB, hilfy4`he following formula worked for me.

stringvar x := {table.folderpath};
stringvar y := left(trim(x),len(trim(x))-1);
left(y,InStrRev(y,'/'))

 

[guesswhoami]

Just noticed a problem in the report.
Using this formula to display the root folder i see a issue for some folders.

for example CustomerDetails report which is under Customer folder is displayed as follows:

/
CustomerDetails

/Customer
CustomerDetails

So i have the the report being displayed at the root folder as well as the main folder, which is not correct

It should be only under /Customer.


One more example:

/ 'Root Folder
Order Details/2010 'Report

Orders/Summary 'Actual Folder
Order Details/2010 'Report

This is happening only for certain reports

stringvar x := {table.folderpath};
stringvar y := left(trim(x),len(trim(x))-1);
left(y,InStrRev(y,'/'))

 

[lbass]

Can't follow this without seeing how the field is displaying before use of the formula in each case.

-LB

 

[guesswhoami]

Before the formula is used :

/Customer/
CustomerDetails.rpt

/Customer/CustomerDetails.rpt/
CustomerDetails.rpt



After the formula is used:

/
CustomerDetails.rpt

/Customer/
CustomerDetails.rpt

I want only this to be displayed
/Customer/
CustomerDetails.rpt

 

[lbass]

I'm sorry but I'm confused. Are these two different examples? Are you adding in returns for some reason--if so please, remove them? I can't tell what you are considering the initial field versus showing a header with part of the field and a details section. Maybe you can label this.

-LB

 

[guesswhoami]

The object of this report is to display the count of reports that are viewed or Scheduled.
There are 2 Groups in my report .one at the folder level(This is to display the folder and subfolder name) and second at the object level(this is to display the report name)

The issue here is that in some case the report (This is the same report but being display 2 times) is being displayed in the following way :

/ ----------------------FolderName
CustomerDetails ---------------------Object Name
CustomerSummaryReport ---------------------Object Name

and also as

/Customer/ ----------------------FolderName
CustomerDetails ---------------------Object Name
CustomerSummaryReport ---------------------Object Name


The way i want it to be displayed is irrespective of how many subfolders are there .If there are 10 subfolders then all those subfolder should be displayed in the folder group level (once) and the object name should be displayed in the object group level.

This should be the correct way :
/Customer/ ----------------------FolderName
CustomerDetails ---------------------Object Name
CustomerSummaryReport ---------------------Object Name

My question is why is it displayed 2 times once at / and the second time at the correct folder level

stringvar x := {table.folderpath};
stringvar y := left(trim(x),len(trim(x))-1);
left(y,InStrRev(y,'/'))

 

[lbass]

In what section are you placing the formula? The formula itself isn't causing the duplication.

-LB

 

[guesswhoami]

I am placing the formula in the group 1 section (folder level) since this is where i want the folder name to be displayed

Section 2 has object details, Count od scheduled and viewed and the most recent time stamp

 

[lbass]

What is your group structure? It looks like you have some outer group (not shown) that is forcing the inner group to repeat--maybe viewed vs scheduled? Please identify report sections by their actual name. "Section 2" could be GH1b, details, etc.

Also identify the fields/formulas you are placing in each section.

-LB

 

[guesswhoami]

There are only 2 groups.

Group #1 (folder )------ this is where the formula is placed)
Group #2 object, schedule Instance, Viewed Instance, date

When i use the following formula I get the root folder and all the objects under it (there is no subfolders being displayed and there is no duplication too)

stringvar x := {Command.DETAIL_TEXT};
stringvar y := right(trim(x),len(trim(x))-1);
left(y, InStr(y, '/') - 1)

This is how the shedule and view is obtained in the sql command
CASE
WHEN audit.event_id in ( 1000,1001) THEN 1
ELSE 0
END AS Schedule_Instance,
CASE WHEN audit.event_id IN (2000, 2001) THEN 1
else 0 END as View_Instance,

 

[lbass]

So you are saying your modified formula works for you and you are all set?

-LB

 

[guesswhoami]

No the formula is not working as desired since it is showing only the root folder .I want to display the entire folder path(including subfolders too).

The reason i tried that formula was becoz i was trying to play around to see how the duplicates could be removed.


So the issue still has not been resolved.

Using Formula 1 only main folder is displayed .There is no subfolders being displayed and there is no duplication too

Formula 1
stringvar x := {Command.DETAIL_TEXT};
stringvar y := right(trim(x),len(trim(x))-1);
left(y, InStr(y, '/') - 1)


Using Formula 2 there are duplicates being displayed .Objects are being displayed at root level(/) as well as at the folder-Subfolder level.

Formula 2
stringvar x := {Command.DETAIL_TEXT};
stringvar y := left(trim(x),len(trim(x))-1);
left(y,InStrRev(y,'/'))

 

[lbass]

You shouldn't be using the same variable names in different formulas unless you define them as local:

Formula 1
local stringvar x := {Command.DETAIL_TEXT};
local stringvar y := right(trim(x),len(trim(x))-1);
left(y, InStr(y, '/') - 1)

Formula 2
local stringvar x := {Command.DETAIL_TEXT};
local stringvar y := left(trim(x),len(trim(x))-1);
left(y,InStrRev(y,'/'))

However, I don't think the formulas are causing the duplication, and I can't really follow what you are doing to recommend other changes. Sorry.

-LB