Showing posts with label crystal. Show all posts
Showing posts with label crystal. Show all posts

Thursday, March 8, 2012

Cumalative %

Hi there, i am having some problems with a Crystal Report. I have created a report showing the duration of calls within a HEAT System. The calls are grouped into the below areas:

Under 4 Hours
Under 8 Hours
Under 2 Days
Under 3 Days

What is required is the following:

Duration of Call(Group) Number of Calls Cumalative %

Under 4 Hours 300 30% Calc(300/1000)
Under 8 Hours 300 60% Calc(600/1000)
Under 2 Days 100 70% Calc(700/1000)
Under 3 Days 300 100% Calc(1000/1000)

Total Calls 1000

I don't know how to achieve this in a Grouped report in Crystal - what would i use in a formula to do this?

Any help would be greatly received.

Regards

TimI think you want the PercentOfCount function, e.g.

PercentOfSum (call, inner, outer)

So if your report were grouped on, say,
1) Call centre
2) Duration

You would want: PercentOfSum(call, duration, centre)

Anyway, read the help on the PercentOf... functions.|||Sorry, I kept writing PercentOfSum instead of PercentOfCount

Friday, February 17, 2012

CSV output sql server 2000

Hi,
I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms
Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal
1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)
So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.
I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.
Thanks for your time and help
:)You can use the OSQL utility in order to create a file with or without
headers (you can control the behavior of the headers with the -h
switch). Another tool that you can use is DTS package and run it with
DTSRun utility. Also I would not run the tools in a stored procedure
with xp_cmdshell, because this is a huge security problem. By working
this way you give the user a way to run there own scripts and command
on the operating system level with the privileges of the account that
runs SQL Server's service. Since you are working with ASP anyway, it
is better to run the OSQL (or if you choose a different utility, then
the other utility) from the ASP code and not from a stored procedure.
Adi
Katie wrote:
> Hi,
> I have a bunch of stored procedures (some using temp tables) which give
> out tables which are then used in crystal to give out reports. These
> stored procs are run dynamically depending on values users select on
> forms
> Some people instead of reports want Comma Seperated Files (CSVs). I am
> trying to find a good way of outputting to csv with using either sql
> server or ASP. I tried two ways but none of them were ideal
> 1st method
> first way i tried was creating a record set in asp and then using the
> following to output the data in a comma delimited row and writing to
> file.
> Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> The problem with the above was with data sets greater than 10,000
> records the processing time increases exponentially because of memory
> usage ( i found other people with the same problem)
> So i tried the following
> 2nd method
> I used bcp using the master.dbo.xp_cmdshell command to output the
> csv. The problem with that is that the bcp executes the stored proc
> three times and also doesnot give out column headers. So i had to run
> the stored proc once in ASP and get the headers from the record set and
> store it to a text file and then run the stored proc in bcp and send
> the output to a csv file. Then merge the two files into a third file.
> The problem with this method is that it takes around 4 times as long to
> run as just the stored proceedure and also i have to create 3 files
> instead of one.
> I would appreciate any suggestions you have to output the csv from a
> stored proc in sql server. Note users have to do this dynamically when
> they submit a form so cant use the query analyzer or enterprise manager
> tools.
> Thanks for your time and help
> :)|||Hi,
Thanks for your response.
But isnt OSQL also a command line utitlity? how would i run that from
inside asp. Would i use some shell command utility in vbscript?
:)
Adi wrote:
> You can use the OSQL utility in order to create a file with or without
> headers (you can control the behavior of the headers with the -h
> switch). Another tool that you can use is DTS package and run it with
> DTSRun utility. Also I would not run the tools in a stored procedure
> with xp_cmdshell, because this is a huge security problem. By working
> this way you give the user a way to run there own scripts and command
> on the operating system level with the privileges of the account that
> runs SQL Server's service. Since you are working with ASP anyway, it
> is better to run the OSQL (or if you choose a different utility, then
> the other utility) from the ASP code and not from a stored procedure.
> Adi
>
> Katie wrote:
> > Hi,
> >
> > I have a bunch of stored procedures (some using temp tables) which give
> > out tables which are then used in crystal to give out reports. These
> > stored procs are run dynamically depending on values users select on
> > forms
> >
> > Some people instead of reports want Comma Seperated Files (CSVs). I am
> > trying to find a good way of outputting to csv with using either sql
> > server or ASP. I tried two ways but none of them were ideal
> >
> > 1st method
> > first way i tried was creating a record set in asp and then using the
> > following to output the data in a comma delimited row and writing to
> > file.
> > Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> > The problem with the above was with data sets greater than 10,000
> > records the processing time increases exponentially because of memory
> > usage ( i found other people with the same problem)
> >
> > So i tried the following
> > 2nd method
> > I used bcp using the master.dbo.xp_cmdshell command to output the
> > csv. The problem with that is that the bcp executes the stored proc
> > three times and also doesnot give out column headers. So i had to run
> > the stored proc once in ASP and get the headers from the record set and
> > store it to a text file and then run the stored proc in bcp and send
> > the output to a csv file. Then merge the two files into a third file.
> > The problem with this method is that it takes around 4 times as long to
> > run as just the stored proceedure and also i have to create 3 files
> > instead of one.
> >
> > I would appreciate any suggestions you have to output the csv from a
> > stored proc in sql server. Note users have to do this dynamically when
> > they submit a form so cant use the query analyzer or enterprise manager
> > tools.
> >
> > Thanks for your time and help
> > :)|||Hi Katie
I'm sorry, but I'm not an ASP developer. As far as I know, you can
run a command line utility from an ASP code with the WScript.Shell. I
admit that I don't know much more (for example if this also has some
kind of security implications, or if there are some limitation of using
the WScript.Shell object, etc') and it would be a good idea to get more
information about running a command line utility from the ASP code.
Adi
Katie wrote:
> Hi,
> Thanks for your response.
> But isnt OSQL also a command line utitlity? how would i run that from
> inside asp. Would i use some shell command utility in vbscript?
> :)
> Adi wrote:
> > You can use the OSQL utility in order to create a file with or without
> > headers (you can control the behavior of the headers with the -h
> > switch). Another tool that you can use is DTS package and run it with
> > DTSRun utility. Also I would not run the tools in a stored procedure
> > with xp_cmdshell, because this is a huge security problem. By working
> > this way you give the user a way to run there own scripts and command
> > on the operating system level with the privileges of the account that
> > runs SQL Server's service. Since you are working with ASP anyway, it
> > is better to run the OSQL (or if you choose a different utility, then
> > the other utility) from the ASP code and not from a stored procedure.
> >
> > Adi
> >
> >
> > Katie wrote:
> > > Hi,
> > >
> > > I have a bunch of stored procedures (some using temp tables) which give
> > > out tables which are then used in crystal to give out reports. These
> > > stored procs are run dynamically depending on values users select on
> > > forms
> > >
> > > Some people instead of reports want Comma Seperated Files (CSVs). I am
> > > trying to find a good way of outputting to csv with using either sql
> > > server or ASP. I tried two ways but none of them were ideal
> > >
> > > 1st method
> > > first way i tried was creating a record set in asp and then using the
> > > following to output the data in a comma delimited row and writing to
> > > file.
> > > Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> > > The problem with the above was with data sets greater than 10,000
> > > records the processing time increases exponentially because of memory
> > > usage ( i found other people with the same problem)
> > >
> > > So i tried the following
> > > 2nd method
> > > I used bcp using the master.dbo.xp_cmdshell command to output the
> > > csv. The problem with that is that the bcp executes the stored proc
> > > three times and also doesnot give out column headers. So i had to run
> > > the stored proc once in ASP and get the headers from the record set and
> > > store it to a text file and then run the stored proc in bcp and send
> > > the output to a csv file. Then merge the two files into a third file.
> > > The problem with this method is that it takes around 4 times as long to
> > > run as just the stored proceedure and also i have to create 3 files
> > > instead of one.
> > >
> > > I would appreciate any suggestions you have to output the csv from a
> > > stored proc in sql server. Note users have to do this dynamically when
> > > they submit a form so cant use the query analyzer or enterprise manager
> > > tools.
> > >
> > > Thanks for your time and help
> > > :)|||Hi,
I tried the OSQL and was able to output a text file but the problem
with that was that it was not formatted. It included blank spaces in
the column (wasnt trimmed) and also the column headers where followed
by a -- line
Unfortunately excel wouldnot import the OSql text file correctly
:(
Adi wrote:
> Hi Katie
> I'm sorry, but I'm not an ASP developer. As far as I know, you can
> run a command line utility from an ASP code with the WScript.Shell. I
> admit that I don't know much more (for example if this also has some
> kind of security implications, or if there are some limitation of using
> the WScript.Shell object, etc') and it would be a good idea to get more
> information about running a command line utility from the ASP code.
> Adi
>
> Katie wrote:
> > Hi,
> >
> > Thanks for your response.
> > But isnt OSQL also a command line utitlity? how would i run that from
> > inside asp. Would i use some shell command utility in vbscript?
> >
> > :)
> >
> > Adi wrote:
> > > You can use the OSQL utility in order to create a file with or without
> > > headers (you can control the behavior of the headers with the -h
> > > switch). Another tool that you can use is DTS package and run it with
> > > DTSRun utility. Also I would not run the tools in a stored procedure
> > > with xp_cmdshell, because this is a huge security problem. By working
> > > this way you give the user a way to run there own scripts and command
> > > on the operating system level with the privileges of the account that
> > > runs SQL Server's service. Since you are working with ASP anyway, it
> > > is better to run the OSQL (or if you choose a different utility, then
> > > the other utility) from the ASP code and not from a stored procedure.
> > >
> > > Adi
> > >
> > >
> > > Katie wrote:
> > > > Hi,
> > > >
> > > > I have a bunch of stored procedures (some using temp tables) which give
> > > > out tables which are then used in crystal to give out reports. These
> > > > stored procs are run dynamically depending on values users select on
> > > > forms
> > > >
> > > > Some people instead of reports want Comma Seperated Files (CSVs). I am
> > > > trying to find a good way of outputting to csv with using either sql
> > > > server or ASP. I tried two ways but none of them were ideal
> > > >
> > > > 1st method
> > > > first way i tried was creating a record set in asp and then using the
> > > > following to output the data in a comma delimited row and writing to
> > > > file.
> > > > Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> > > > The problem with the above was with data sets greater than 10,000
> > > > records the processing time increases exponentially because of memory
> > > > usage ( i found other people with the same problem)
> > > >
> > > > So i tried the following
> > > > 2nd method
> > > > I used bcp using the master.dbo.xp_cmdshell command to output the
> > > > csv. The problem with that is that the bcp executes the stored proc
> > > > three times and also doesnot give out column headers. So i had to run
> > > > the stored proc once in ASP and get the headers from the record set and
> > > > store it to a text file and then run the stored proc in bcp and send
> > > > the output to a csv file. Then merge the two files into a third file.
> > > > The problem with this method is that it takes around 4 times as long to
> > > > run as just the stored proceedure and also i have to create 3 files
> > > > instead of one.
> > > >
> > > > I would appreciate any suggestions you have to output the csv from a
> > > > stored proc in sql server. Note users have to do this dynamically when
> > > > they submit a form so cant use the query analyzer or enterprise manager
> > > > tools.
> > > >
> > > > Thanks for your time and help
> > > > :)

CSV output sql server 2000

Hi,
I have a bunch of stored procedures (some using temp tables) which give
out tables which are then used in crystal to give out reports. These
stored procs are run dynamically depending on values users select on
forms
Some people instead of reports want Comma Seperated Files (CSVs). I am
trying to find a good way of outputting to csv with using either sql
server or ASP. I tried two ways but none of them were ideal
1st method
first way i tried was creating a record set in asp and then using the
following to output the data in a comma delimited row and writing to
file.
Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
The problem with the above was with data sets greater than 10,000
records the processing time increases exponentially because of memory
usage ( i found other people with the same problem)
So i tried the following
2nd method
I used bcp using the master.dbo.xp_cmdshell command to output the
csv. The problem with that is that the bcp executes the stored proc
three times and also doesnot give out column headers. So i had to run
the stored proc once in ASP and get the headers from the record set and
store it to a text file and then run the stored proc in bcp and send
the output to a csv file. Then merge the two files into a third file.
The problem with this method is that it takes around 4 times as long to
run as just the stored proceedure and also i have to create 3 files
instead of one.
I would appreciate any suggestions you have to output the csv from a
stored proc in sql server. Note users have to do this dynamically when
they submit a form so cant use the query analyzer or enterprise manager
tools.
Thanks for your time and help
You can use the OSQL utility in order to create a file with or without
headers (you can control the behavior of the headers with the -h
switch). Another tool that you can use is DTS package and run it with
DTSRun utility. Also I would not run the tools in a stored procedure
with xp_cmdshell, because this is a huge security problem. By working
this way you give the user a way to run there own scripts and command
on the operating system level with the privileges of the account that
runs SQL Server's service. Since you are working with ASP anyway, it
is better to run the OSQL (or if you choose a different utility, then
the other utility) from the ASP code and not from a stored procedure.
Adi
Katie wrote:
> Hi,
> I have a bunch of stored procedures (some using temp tables) which give
> out tables which are then used in crystal to give out reports. These
> stored procs are run dynamically depending on values users select on
> forms
> Some people instead of reports want Comma Seperated Files (CSVs). I am
> trying to find a good way of outputting to csv with using either sql
> server or ASP. I tried two ways but none of them were ideal
> 1st method
> first way i tried was creating a record set in asp and then using the
> following to output the data in a comma delimited row and writing to
> file.
> Response.Write RecordSet.GetString(,,", ",vbCrLf,"")
> The problem with the above was with data sets greater than 10,000
> records the processing time increases exponentially because of memory
> usage ( i found other people with the same problem)
> So i tried the following
> 2nd method
> I used bcp using the master.dbo.xp_cmdshell command to output the
> csv. The problem with that is that the bcp executes the stored proc
> three times and also doesnot give out column headers. So i had to run
> the stored proc once in ASP and get the headers from the record set and
> store it to a text file and then run the stored proc in bcp and send
> the output to a csv file. Then merge the two files into a third file.
> The problem with this method is that it takes around 4 times as long to
> run as just the stored proceedure and also i have to create 3 files
> instead of one.
> I would appreciate any suggestions you have to output the csv from a
> stored proc in sql server. Note users have to do this dynamically when
> they submit a form so cant use the query analyzer or enterprise manager
> tools.
> Thanks for your time and help
> |||Hi,
Thanks for your response.
But isnt OSQL also a command line utitlity? how would i run that from
inside asp. Would i use some shell command utility in vbscript?

Adi wrote:[vbcol=seagreen]
> You can use the OSQL utility in order to create a file with or without
> headers (you can control the behavior of the headers with the -h
> switch). Another tool that you can use is DTS package and run it with
> DTSRun utility. Also I would not run the tools in a stored procedure
> with xp_cmdshell, because this is a huge security problem. By working
> this way you give the user a way to run there own scripts and command
> on the operating system level with the privileges of the account that
> runs SQL Server's service. Since you are working with ASP anyway, it
> is better to run the OSQL (or if you choose a different utility, then
> the other utility) from the ASP code and not from a stored procedure.
> Adi
>
> Katie wrote:|||Hi Katie
I'm sorry, but I'm not an ASP developer. As far as I know, you can
run a command line utility from an ASP code with the WScript.Shell. I
admit that I don't know much more (for example if this also has some
kind of security implications, or if there are some limitation of using
the WScript.Shell object, etc') and it would be a good idea to get more
information about running a command line utility from the ASP code.
Adi
Katie wrote:[vbcol=seagreen]
> Hi,
> Thanks for your response.
> But isnt OSQL also a command line utitlity? how would i run that from
> inside asp. Would i use some shell command utility in vbscript?
>
> Adi wrote:|||Hi,
I tried the OSQL and was able to output a text file but the problem
with that was that it was not formatted. It included blank spaces in
the column (wasnt trimmed) and also the column headers where followed
by a -- line
Unfortunately excel wouldnot import the OSql text file correctly

Adi wrote:[vbcol=seagreen]
> Hi Katie
> I'm sorry, but I'm not an ASP developer. As far as I know, you can
> run a command line utility from an ASP code with the WScript.Shell. I
> admit that I don't know much more (for example if this also has some
> kind of security implications, or if there are some limitation of using
> the WScript.Shell object, etc') and it would be a good idea to get more
> information about running a command line utility from the ASP code.
> Adi
>
> Katie wrote:

Tuesday, February 14, 2012

Crytarl Report with Dabase access

dear all ,

i use crystal report from dot net 2003 (i use dotnet version architect).
with database access.

and i have problem to set parameter for query access in crystal report.

if i use database mssqlserver 2000 in crystal report i just select store procedure and the parameter for store precedure will otomatically add in crystal report.

sampel query store procedure like this

select * from tblcustomer
where CustName = @.custName

the parameter @.CustName otomatically add in crystal report.

how to implament that if i use database access.
so i can send a value for @.Custname from my applicationAs i have worked for VB.net so i'll how i used to send parameter value externally
first u need to define
Dim pv As New CrystalDecisions.Shared.ParameterValues
Dim pdv As New CrystalDecisions.Shared.ParameterDiscreteValue

pdv.Value = EntityCode
pv.Add(pdvScriptCode1)

RptViewer.DataDefinition.ParameterFields("@.Customer").ApplyCurrentValues(pv)

thats it...
Try it out n tell me the result whther its working......

Crytal report hiding the records in version 5.0

Hi
I have a field in my report i want to hide the whole record when the field retrun 0 as value.
How to accomplish this in crystal report writer 5.0Right click on the fileds and in the suppress option write this formula

{SomeField}=0

Crystl32.ocx with vb .net

Hi all,

I've created the report with Seagate crystal reports with the source to the database through a stored procedure (with ODBC). The stored procedure contains input parameter that I want to sent it to this report from .Net through Crystal report Control Crystl32.ocx.
I installed this control into .net but I don't how can I pass the parameter which I'm writing into textbox to report that contains stored procedure as a data source.

== Some code with Crystl32.ocx that I'm using to call the report:

With cr2
.ReportFileName = Application.StartupPath + "\klient_info.rpt"
.RetrieveDataFiles()
.DiscardSavedData = True
.ParameterFields(1) = "@.KlientID;" & TextBox2.Text & ";False"
.WindowState = crptMaximized
.Action = 0

End With

With the code above I'm passing into crytal report @.klientID - what I'm writting into textbox2. But the problem is that I don't want crystal report to prompt again for inserting input parameter.

Thanks,
ProBani.With the code above I'm passing into crytal report @.klientID - what I'm writting into textbox2. But the problem is that I don't want crystal report to prompt again for inserting input parameter.

are you sure that you only have one input parameter and that it is ParameterFields(1). My guess is that Crystal either expect another paramter or that the one your inputting isn't correct. That's why you get prompted to input another param.