I am trying to write a formula for a report that sums up payments received, both positive and negative amounts.
The issue I am running into is that when I drop the appropriate field as a Summary into the report, it displays a blank field in the details section for each entry that would've appeared if I put that transaction amount field into the report.
Further, I cannot just hide the details and do things in a group section (I tried).
I was wondering if someone could help me with this, I am at my wits end. I can display more information about the fields themselves if necessary
On a similiar note, is there anyway to do a Where clause/command in Crystal formula?
ie sum({x}) where transactioncode = "x"On a similiar note, is there anyway to do a Where clause/command in Crystal formula?
ie sum({x}) where transactioncode = "x"
1. You can create a running total.
Select and add your 'Field to summarize', set the 'type of summaries'.
Evaluate it on the 'Use a formula', click x+2 buton and enter your formula:
transactioncode = "x"
Reset summaries as you need.
-------
or
-------
2. create a formula, kind of this one:
if transactioncode = "x" then {x} else 0
then summarize it
About the issue you have, can you post your formula and some example of your desired result, please?
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Sunday, March 25, 2012
Crystal Running Group Total equivilent in Report Services
I'm trying to use Reporting Services Developer edition with SQL Server 2000
and Visual Studio 2003. Net to create a report that already existed in
Crystal Reports 8.5 so that the report can be distributed via email and other
things that Report Services allows.
The Crystal Report uses something called a 'Running Total' field that
summarizes a field and evaluates a value for each chance of the field and
resets the value on the change of a group.
Who would you go about solving this problem in Report Services?
Thanks,
JimCheck out the RunningValue value function in BOL...
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:8DDF9D20-B55F-4DD1-8B69-937AD2ACD859@.microsoft.com...
> I'm trying to use Reporting Services Developer edition with SQL Server
> 2000
> and Visual Studio 2003. Net to create a report that already existed in
> Crystal Reports 8.5 so that the report can be distributed via email and
> other
> things that Report Services allows.
> The Crystal Report uses something called a 'Running Total' field that
> summarizes a field and evaluates a value for each chance of the field and
> resets the value on the change of a group.
> Who would you go about solving this problem in Report Services?
> Thanks,
> Jim|||What is BOL and how do you get access to it?
"Jim" wrote:
> Check out the RunningValue value function in BOL...
> "Jim" <Jim@.discussions.microsoft.com> wrote in message
> news:8DDF9D20-B55F-4DD1-8B69-937AD2ACD859@.microsoft.com...
> > I'm trying to use Reporting Services Developer edition with SQL Server
> > 2000
> > and Visual Studio 2003. Net to create a report that already existed in
> > Crystal Reports 8.5 so that the report can be distributed via email and
> > other
> > things that Report Services allows.
> >
> > The Crystal Report uses something called a 'Running Total' field that
> > summarizes a field and evaluates a value for each chance of the field and
> > resets the value on the change of a group.
> >
> > Who would you go about solving this problem in Report Services?
> >
> > Thanks,
> > Jim
>
>|||BOL is Reporting Services Books On Line.
You can also find them on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_1l6b.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:74ABD5D6-72D0-43E5-97C7-BD433A7F6876@.microsoft.com...
> What is BOL and how do you get access to it?
> "Jim" wrote:
> > Check out the RunningValue value function in BOL...
> >
> > "Jim" <Jim@.discussions.microsoft.com> wrote in message
> > news:8DDF9D20-B55F-4DD1-8B69-937AD2ACD859@.microsoft.com...
> > > I'm trying to use Reporting Services Developer edition with SQL Server
> > > 2000
> > > and Visual Studio 2003. Net to create a report that already existed in
> > > Crystal Reports 8.5 so that the report can be distributed via email
and
> > > other
> > > things that Report Services allows.
> > >
> > > The Crystal Report uses something called a 'Running Total' field that
> > > summarizes a field and evaluates a value for each chance of the field
and
> > > resets the value on the change of a group.
> > >
> > > Who would you go about solving this problem in Report Services?
> > >
> > > Thanks,
> > > Jim
> >
> >
> >
and Visual Studio 2003. Net to create a report that already existed in
Crystal Reports 8.5 so that the report can be distributed via email and other
things that Report Services allows.
The Crystal Report uses something called a 'Running Total' field that
summarizes a field and evaluates a value for each chance of the field and
resets the value on the change of a group.
Who would you go about solving this problem in Report Services?
Thanks,
JimCheck out the RunningValue value function in BOL...
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:8DDF9D20-B55F-4DD1-8B69-937AD2ACD859@.microsoft.com...
> I'm trying to use Reporting Services Developer edition with SQL Server
> 2000
> and Visual Studio 2003. Net to create a report that already existed in
> Crystal Reports 8.5 so that the report can be distributed via email and
> other
> things that Report Services allows.
> The Crystal Report uses something called a 'Running Total' field that
> summarizes a field and evaluates a value for each chance of the field and
> resets the value on the change of a group.
> Who would you go about solving this problem in Report Services?
> Thanks,
> Jim|||What is BOL and how do you get access to it?
"Jim" wrote:
> Check out the RunningValue value function in BOL...
> "Jim" <Jim@.discussions.microsoft.com> wrote in message
> news:8DDF9D20-B55F-4DD1-8B69-937AD2ACD859@.microsoft.com...
> > I'm trying to use Reporting Services Developer edition with SQL Server
> > 2000
> > and Visual Studio 2003. Net to create a report that already existed in
> > Crystal Reports 8.5 so that the report can be distributed via email and
> > other
> > things that Report Services allows.
> >
> > The Crystal Report uses something called a 'Running Total' field that
> > summarizes a field and evaluates a value for each chance of the field and
> > resets the value on the change of a group.
> >
> > Who would you go about solving this problem in Report Services?
> >
> > Thanks,
> > Jim
>
>|||BOL is Reporting Services Books On Line.
You can also find them on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_1l6b.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:74ABD5D6-72D0-43E5-97C7-BD433A7F6876@.microsoft.com...
> What is BOL and how do you get access to it?
> "Jim" wrote:
> > Check out the RunningValue value function in BOL...
> >
> > "Jim" <Jim@.discussions.microsoft.com> wrote in message
> > news:8DDF9D20-B55F-4DD1-8B69-937AD2ACD859@.microsoft.com...
> > > I'm trying to use Reporting Services Developer edition with SQL Server
> > > 2000
> > > and Visual Studio 2003. Net to create a report that already existed in
> > > Crystal Reports 8.5 so that the report can be distributed via email
and
> > > other
> > > things that Report Services allows.
> > >
> > > The Crystal Report uses something called a 'Running Total' field that
> > > summarizes a field and evaluates a value for each chance of the field
and
> > > resets the value on the change of a group.
> > >
> > > Who would you go about solving this problem in Report Services?
> > >
> > > Thanks,
> > > Jim
> >
> >
> >
crystal reports running slow ?
All,
I have been tapped to help with fixing a reporting tool. We have a Sql Server database/crystal reports(10) setup. I havent had the chance to look at the tables in the DB yet, but I was told that aggregate tables were used. In my past experience with crystal reports, we used database views to feed crystal reports (in Oracle). I was thinking that I could somehow use views instead of tables and then try to re-index the base tables and compile satitics (if theres such a thing with Sql Server). I was also going to look into bottlenecking and locking (table locking as opposed to row or page locking for the lookup tables...to reduce overhead on the main tables) but, I'm not sure if it'll make a difference since this is just a demo server with no major traffic hitting it yet.
The question is, does anyone have any experience with crystal reports running slow with Sql Server, what should I look out for??
'WaleCrystal reports sucks.
Now that we have gotten the obligatory disparaging remark out of the way, you should push as much processing as possible onto the server, and use Crystal only for displaying the data. This means having Crystal call either a Procedure, View, or Table.
If your tables are pre-aggregated, then that is about as fast as you are going to get, because all the processing work is done during nightly batch processing (usually). That said, if your so-called pre-aggregated tables are actually being created on the fly each time the report is requested, well, that's about the lease efficient method.
With the (possible) exception of data aggregated for specific reports, it is seldom a good idea to reference tables directly. Reference views or (preferably) procedures instead, so that if the database schema needs to be changed you will just need to update your procedures rather than redesigning all your reports.
Make sure the aggregated tables are indexes on any columns used for filtering, and your dataset will be generated very fast. If the reports are still slow, take a close look at your network and figure out how much data is actually being passed back to Crystal. Maybe additional filters are possible.|||I couldn't agree with you more blindman, unfortunately, I don't make the decisions. I like the idea of having Crystal calling stored procedures, so it'll be a simple call and we'll have the procedure take care of any processing. I'll also looking into indexing etc.....|||a stored procedure for every report...sounds like a maintenance nightmare...and also, defeats the purpose of the tool.
you will want to look at all the WHERE clauses in the reports and index all the columns included.
the best solution would be to look into restructuring the data into a reporting friendly format (i.e. star schema).|||We decided to use DTS to create aggregate tables for us. And we added some indexes. The reports have picked up speed.|||A stored procedure for every dataset, not every report. And the result is reduced maintenance. Business logic that would have to be duplicated in every report only has to be implemented in the procedure, and you can be sure that the results of all reports based upon the same procedure will yield compatible results.
The purpose of the Crystal Reports tool is to present data, and that is what it should be used for. The purpose of the Database Server is to store and manipulate the data, and that is what it should be used for.
And Star Schemas are vastly overrated. They are the poor-man's database design.|||I disagree...
business logic should be handled at the meta-data layer for the reporting tool not at the report level. (i.e. one place)
crystal is not just for presentation...it's meant to facilitate business questions to be answered while shielding the user from the complexities of SQL.
star schemas vastly overrated? well, no...it's the best possible reporting structure....and a little thing called OLAP!|||OLAP and Star Schemas are not synonymous. A Star Schema is just a glorified pivot table. For functionality, it is just one step beyond a flatfile.
You can let your business model drive your schema, or you can let your schema drive your business model.|||For functionality, it is just one step beyond a flatfile.
I suppose...other than the functionality of robust and high performance reporting.
You can let your business model drive your schema, or you can let your schema drive your business model.
haha...Copernicus...I love it!! ;-)|||Ok, I admit it. You got me on "Copernicus". What's the reference? His planetary model?
I have been tapped to help with fixing a reporting tool. We have a Sql Server database/crystal reports(10) setup. I havent had the chance to look at the tables in the DB yet, but I was told that aggregate tables were used. In my past experience with crystal reports, we used database views to feed crystal reports (in Oracle). I was thinking that I could somehow use views instead of tables and then try to re-index the base tables and compile satitics (if theres such a thing with Sql Server). I was also going to look into bottlenecking and locking (table locking as opposed to row or page locking for the lookup tables...to reduce overhead on the main tables) but, I'm not sure if it'll make a difference since this is just a demo server with no major traffic hitting it yet.
The question is, does anyone have any experience with crystal reports running slow with Sql Server, what should I look out for??
'WaleCrystal reports sucks.
Now that we have gotten the obligatory disparaging remark out of the way, you should push as much processing as possible onto the server, and use Crystal only for displaying the data. This means having Crystal call either a Procedure, View, or Table.
If your tables are pre-aggregated, then that is about as fast as you are going to get, because all the processing work is done during nightly batch processing (usually). That said, if your so-called pre-aggregated tables are actually being created on the fly each time the report is requested, well, that's about the lease efficient method.
With the (possible) exception of data aggregated for specific reports, it is seldom a good idea to reference tables directly. Reference views or (preferably) procedures instead, so that if the database schema needs to be changed you will just need to update your procedures rather than redesigning all your reports.
Make sure the aggregated tables are indexes on any columns used for filtering, and your dataset will be generated very fast. If the reports are still slow, take a close look at your network and figure out how much data is actually being passed back to Crystal. Maybe additional filters are possible.|||I couldn't agree with you more blindman, unfortunately, I don't make the decisions. I like the idea of having Crystal calling stored procedures, so it'll be a simple call and we'll have the procedure take care of any processing. I'll also looking into indexing etc.....|||a stored procedure for every report...sounds like a maintenance nightmare...and also, defeats the purpose of the tool.
you will want to look at all the WHERE clauses in the reports and index all the columns included.
the best solution would be to look into restructuring the data into a reporting friendly format (i.e. star schema).|||We decided to use DTS to create aggregate tables for us. And we added some indexes. The reports have picked up speed.|||A stored procedure for every dataset, not every report. And the result is reduced maintenance. Business logic that would have to be duplicated in every report only has to be implemented in the procedure, and you can be sure that the results of all reports based upon the same procedure will yield compatible results.
The purpose of the Crystal Reports tool is to present data, and that is what it should be used for. The purpose of the Database Server is to store and manipulate the data, and that is what it should be used for.
And Star Schemas are vastly overrated. They are the poor-man's database design.|||I disagree...
business logic should be handled at the meta-data layer for the reporting tool not at the report level. (i.e. one place)
crystal is not just for presentation...it's meant to facilitate business questions to be answered while shielding the user from the complexities of SQL.
star schemas vastly overrated? well, no...it's the best possible reporting structure....and a little thing called OLAP!|||OLAP and Star Schemas are not synonymous. A Star Schema is just a glorified pivot table. For functionality, it is just one step beyond a flatfile.
You can let your business model drive your schema, or you can let your schema drive your business model.|||For functionality, it is just one step beyond a flatfile.
I suppose...other than the functionality of robust and high performance reporting.
You can let your business model drive your schema, or you can let your schema drive your business model.
haha...Copernicus...I love it!! ;-)|||Ok, I admit it. You got me on "Copernicus". What's the reference? His planetary model?
Thursday, March 22, 2012
Crystal Reports Export error 8.5
Our web server was running windows 2000 iis5 for several years including crystal 8.5. We just migrated to windows 2003 iis6 and all applications work great. EXCEPT exporting to PDF with (asp) using crystal reports Session("oRpt").Export.
I installed all SP & hotfixes for exporting and still no luck. Called tech support and they don't support version 8.5 so they would not help me.
Can somebody please help me! I have just 2 applications left that use crystal reports 8.5 and they are well heavily.
This is the error:
Microsoft VBScript runtime error '800a01fb'
An exception occurred: 'Export'Hi Dough,
Just wondering if you ever found a fix to the crystal report export. I am having the exact same issue with Windows Server 2003 / ASP 3.0 / IIS 6.0.
Thanks,
Troye Stonich|||I may have a solution for you to try.
I found it may be necessary to set the Display Progress Dialog to False. Most likely due to security restrictions on the IIS 6 Win2003 server.
Right before the Session Export line, Enter the following line,
Session("oRpt").DisplayProgressDialog = False
This helped me, doing a simple PDF Export (no viewer). Crystal does not present this dispaly in sample ASP they provide, (9.0 and below). For me it was pretty much a trial and error to resolve this issue.
If the above does not work, try the following 4 lines:
Dim strContentType
strContentType = "application/pdf"
Session("oRpt").DisplayProgressDialog = False
Response.ContentType = strContentType
Hope this helps.
Steve|||You guys are GREAT !
Thank you so mutch, i was getting crazy because of this problem :-)
I installed all SP & hotfixes for exporting and still no luck. Called tech support and they don't support version 8.5 so they would not help me.
Can somebody please help me! I have just 2 applications left that use crystal reports 8.5 and they are well heavily.
This is the error:
Microsoft VBScript runtime error '800a01fb'
An exception occurred: 'Export'Hi Dough,
Just wondering if you ever found a fix to the crystal report export. I am having the exact same issue with Windows Server 2003 / ASP 3.0 / IIS 6.0.
Thanks,
Troye Stonich|||I may have a solution for you to try.
I found it may be necessary to set the Display Progress Dialog to False. Most likely due to security restrictions on the IIS 6 Win2003 server.
Right before the Session Export line, Enter the following line,
Session("oRpt").DisplayProgressDialog = False
This helped me, doing a simple PDF Export (no viewer). Crystal does not present this dispaly in sample ASP they provide, (9.0 and below). For me it was pretty much a trial and error to resolve this issue.
If the above does not work, try the following 4 lines:
Dim strContentType
strContentType = "application/pdf"
Session("oRpt").DisplayProgressDialog = False
Response.ContentType = strContentType
Hope this helps.
Steve|||You guys are GREAT !
Thank you so mutch, i was getting crazy because of this problem :-)
Tuesday, March 20, 2012
Crystal Reports access denied error
Hello,
I've been stumped trying to resolve the following error when running Crystal Reports on a new server I'm setting up:
HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services
------------------------
Technical Information (for support personnel)
Error Type:
Seagate Crystal Reports ActiveX Designer (0x80043AC6)
Access denied.
/OrbitRpt/Crystal/AlwaysRequiredSteps.asp, line 57
I'm running a Win2K server (service pack 4) with Crystal Reports 8. I've tried setting the security to "Everyone - Full Control" on all folders but I'm still getting the "Access denied" error. Has anyone seen this before? Any info would be greatly appreciated. Thanks!The following changes fixed my problem:
To properly set up your System Environment Variables:
1. Create the directory "C:\Temp" if it is not already created.
2. Right-click on "My Computer" on the server machine, and choose "Properties".
3. Go to the "Environment" tab.
4. Under "User Variables", select "TEMP", and then click "Remove". Repeat for the variable "TMP".
5. Select any variable under "System Variables".
6. Change the value for "Variable" to "TEMP" (no quotes).
7. Change the value for "Value" to "C:\Temp" (no quotes).
8. Click "Set".
9. Change the value for "Variable" to "TMP" (no quotes).
10. Change the value for "Value" to "C:\Temp" (no quotes).
11. Click "Set".
12. Click "OK".
Be sure to give "Read" and "Delete" access to this TEMP folder so that the Crystal Reports web components can read and write to this directory.
I've been stumped trying to resolve the following error when running Crystal Reports on a new server I'm setting up:
HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services
------------------------
Technical Information (for support personnel)
Error Type:
Seagate Crystal Reports ActiveX Designer (0x80043AC6)
Access denied.
/OrbitRpt/Crystal/AlwaysRequiredSteps.asp, line 57
I'm running a Win2K server (service pack 4) with Crystal Reports 8. I've tried setting the security to "Everyone - Full Control" on all folders but I'm still getting the "Access denied" error. Has anyone seen this before? Any info would be greatly appreciated. Thanks!The following changes fixed my problem:
To properly set up your System Environment Variables:
1. Create the directory "C:\Temp" if it is not already created.
2. Right-click on "My Computer" on the server machine, and choose "Properties".
3. Go to the "Environment" tab.
4. Under "User Variables", select "TEMP", and then click "Remove". Repeat for the variable "TMP".
5. Select any variable under "System Variables".
6. Change the value for "Variable" to "TEMP" (no quotes).
7. Change the value for "Value" to "C:\Temp" (no quotes).
8. Click "Set".
9. Change the value for "Variable" to "TMP" (no quotes).
10. Change the value for "Value" to "C:\Temp" (no quotes).
11. Click "Set".
12. Click "OK".
Be sure to give "Read" and "Delete" access to this TEMP folder so that the Crystal Reports web components can read and write to this directory.
Crystal Reports 8.5 - Running Totals
Hi all,
Fairly new to Crystal, I am trying to build a report to show averages.
Have found this formulae in another report.
whileprintingrecords;
numbervar GrpTsec;
local numbervar AverageFix:=GrpTsec/DistinctCount ({SW_CASE.swCaseId}, {SW_CASE.dsInboxAssignedTo});
numbervar LenOfDay;
Local numberVar days := truncate(AverageFix / LenOfDay);
Local numberVar hr := truncate((AverageFix-(days*lenOfDay))/3600);
Local numberVar min := truncate((AverageFix-((days*LenOfDay)+(hr*3600)))/60);
Local numberVar sec := (AverageFix-((days*LenOfDay)+(hr*3600)+(min*60)));
Totext(days,"###") + " Day(s) " + totext(hr,"##") + " hours " + totext(min,"##") + " minutes " + totext(sec,"##") + " seconds";
averagefix/60
But on running the report it is returning an error message about Running Totals and Summary
Any ideas ?Public variables GrpTsec and LenOfDay are probably initialized in another formula. If this formula is not present, you will get "Divide by Zero" error when calculating value of "days".
Anyway, you should provide more details about the error you get.|||Hi Wrapper
The Error message that I am getting "The Summary / Running total field could not be created"
Many Thanks|||This error is common when trying to use invalid grouping option in functions like Sum(), Count() etc.
DistinctCount ({SW_CASE.swCaseId}, {SW_CASE.dsInboxAssignedTo});
Sure you have a group by {SW_CASE.dsInboxAssignedTo} in your report?|||Hi Wrapper,
Thanks, runs now !!!
Fairly new to Crystal, I am trying to build a report to show averages.
Have found this formulae in another report.
whileprintingrecords;
numbervar GrpTsec;
local numbervar AverageFix:=GrpTsec/DistinctCount ({SW_CASE.swCaseId}, {SW_CASE.dsInboxAssignedTo});
numbervar LenOfDay;
Local numberVar days := truncate(AverageFix / LenOfDay);
Local numberVar hr := truncate((AverageFix-(days*lenOfDay))/3600);
Local numberVar min := truncate((AverageFix-((days*LenOfDay)+(hr*3600)))/60);
Local numberVar sec := (AverageFix-((days*LenOfDay)+(hr*3600)+(min*60)));
Totext(days,"###") + " Day(s) " + totext(hr,"##") + " hours " + totext(min,"##") + " minutes " + totext(sec,"##") + " seconds";
averagefix/60
But on running the report it is returning an error message about Running Totals and Summary
Any ideas ?Public variables GrpTsec and LenOfDay are probably initialized in another formula. If this formula is not present, you will get "Divide by Zero" error when calculating value of "days".
Anyway, you should provide more details about the error you get.|||Hi Wrapper
The Error message that I am getting "The Summary / Running total field could not be created"
Many Thanks|||This error is common when trying to use invalid grouping option in functions like Sum(), Count() etc.
DistinctCount ({SW_CASE.swCaseId}, {SW_CASE.dsInboxAssignedTo});
Sure you have a group by {SW_CASE.dsInboxAssignedTo} in your report?|||Hi Wrapper,
Thanks, runs now !!!
Crystal reports 8 problem after running
hi,
i couldnt explain in title clearly.
the problem is i have used CR in project and when ever the project exe is run and open the reports then close everything including the report it gives runtime error .if i run the exe without opening CR it doesnt give error could there be any DLL problemWhat is the error you are getting?
Open the report and do verify database and try again
i couldnt explain in title clearly.
the problem is i have used CR in project and when ever the project exe is run and open the reports then close everything including the report it gives runtime error .if i run the exe without opening CR it doesnt give error could there be any DLL problemWhat is the error you are getting?
Open the report and do verify database and try again
Crystal Reports 8 application mapping issue...
When running CR 8 with IIS 4, there were no problems.
Our developers installed IIS 5 and then installed CR 8 but the installation failed to properly setup application mapping so that when you click a link to an .RPT file, you get the actual file without it being processed into a webpage by IIS.
I know I can custom config IIS with extension and executable path to use the proper .rpt and .cri dll's, but I was wondering if there was an explanation for the problem?
Does CR 8 have compatibility problems with IIS 5? or is it something else?
Thanx,
CWTurns out it's not the mapping... everything should be configured to work right but still not converting .rpt to html...
any ideas at all?
Our developers installed IIS 5 and then installed CR 8 but the installation failed to properly setup application mapping so that when you click a link to an .RPT file, you get the actual file without it being processed into a webpage by IIS.
I know I can custom config IIS with extension and executable path to use the proper .rpt and .cri dll's, but I was wondering if there was an explanation for the problem?
Does CR 8 have compatibility problems with IIS 5? or is it something else?
Thanx,
CWTurns out it's not the mapping... everything should be configured to work right but still not converting .rpt to html...
any ideas at all?
Monday, March 19, 2012
Crystal Report X & Access 2003 Db
I am running CR X and Access 2003 database.
I have a table defined as Employee and one of the fields is called RESDate and is defined as Date/Time.
The RESDate has a default value Date () - 1. Bascially, I am deriving yesterday's date. When I view the table in Access, I see yesterday's date in the field.
Now in CR X:
I made a new connection and brought in this Access database and this one table. I moved the RESDate to detail line.
I do not see yesterday's date in CR. I did browse of the field in CR X and it shows blanks also. Any ideas ?
Thanks for your help in advance.
Regards
LB1That sounds odd, in CR right click on the field name and select show type,
does the field show up as a string ? or as date / time or ? Also can you create a query, or a view and try reporting off the query / view ?
I have a table defined as Employee and one of the fields is called RESDate and is defined as Date/Time.
The RESDate has a default value Date () - 1. Bascially, I am deriving yesterday's date. When I view the table in Access, I see yesterday's date in the field.
Now in CR X:
I made a new connection and brought in this Access database and this one table. I moved the RESDate to detail line.
I do not see yesterday's date in CR. I did browse of the field in CR X and it shows blanks also. Any ideas ?
Thanks for your help in advance.
Regards
LB1That sounds odd, in CR right click on the field name and select show type,
does the field show up as a string ? or as date / time or ? Also can you create a query, or a view and try reporting off the query / view ?
Crystal Report Viewer on web timing out after 3 minutes
Help,
I am running CR 8.5 on the web, using IIS 6.0. The reports use stored procedures on a SQL server behind the scenes. When a report is kicked off the stored procedure begins running, and IF it completes within 3 minutes, the report viewer displays the report with no problem.
However, if the stored procedure takes longer than 3 minutes, which many longer date range reports do, the Crystal icon in the top right corner of the veiwer stops moving, and the viewer just remains blank. No error message is given. I know that the stored procedure continues to run behind the scenes, because if i hit the back button on the browser, and then forward, the icon begins moving again, and as soon as the stored procedure completes, the report returns. Sometimes i have to wait the 3 minutes, and go back and forward 3 or 4 times before the stored procedure completes and the report returns.
I have tried including a timeout request on the end of the URL calling the report, and even increasing the 'Connection Timeout' setting on the web component server, to no avail. I dont think this is the issue anyway, as the default timeout there is 20 minutes. This issue is occurring every 3 minutes.
Does anyone know of another timeout setting I should be looking for?See if this helps you
http://vyaskn.tripod.com/watch_your_timeouts.htm|||Hi I am having the same issue did you resolve this in the end?
Many thanks,
Chas|||yes i did. was a while ago and dont remember the exact steps, however, within the Crystal configuration manager on the web application server there were timeout settings which i think we just amped up to the max both the idle time & cache memory caps. worked like a charm. The only issue that still existed after that was once the cache reached that max that we set, crystal services required a manual restart, but that would normally takes weeks to happen. To alleviate that we put in a automatic cache purge to keep the cache down daily. worked like a charm, no issues since. knock on wood. :) Hope this helps
I am running CR 8.5 on the web, using IIS 6.0. The reports use stored procedures on a SQL server behind the scenes. When a report is kicked off the stored procedure begins running, and IF it completes within 3 minutes, the report viewer displays the report with no problem.
However, if the stored procedure takes longer than 3 minutes, which many longer date range reports do, the Crystal icon in the top right corner of the veiwer stops moving, and the viewer just remains blank. No error message is given. I know that the stored procedure continues to run behind the scenes, because if i hit the back button on the browser, and then forward, the icon begins moving again, and as soon as the stored procedure completes, the report returns. Sometimes i have to wait the 3 minutes, and go back and forward 3 or 4 times before the stored procedure completes and the report returns.
I have tried including a timeout request on the end of the URL calling the report, and even increasing the 'Connection Timeout' setting on the web component server, to no avail. I dont think this is the issue anyway, as the default timeout there is 20 minutes. This issue is occurring every 3 minutes.
Does anyone know of another timeout setting I should be looking for?See if this helps you
http://vyaskn.tripod.com/watch_your_timeouts.htm|||Hi I am having the same issue did you resolve this in the end?
Many thanks,
Chas|||yes i did. was a while ago and dont remember the exact steps, however, within the Crystal configuration manager on the web application server there were timeout settings which i think we just amped up to the max both the idle time & cache memory caps. worked like a charm. The only issue that still existed after that was once the cache reached that max that we set, crystal services required a manual restart, but that would normally takes weeks to happen. To alleviate that we put in a automatic cache purge to keep the cache down daily. worked like a charm, no issues since. knock on wood. :) Hope this helps
Thursday, March 8, 2012
Crystal report - using sub report
i am using a sub report in my main report. I am able to count/ total a field using the Running total within that subreport which match the criteria of selection. i am passing a parameter from my main report say Issue number to subreport.
For each issue no passed i am totalling and writing in the report - one row per issue no ( with different columns like January, Feb, March,...)
Problem: At the end of the report i want the Verticle total of each coulmn say total in January, Feb, March,...I am not able to do this.
Is there a way to do this?
In the sub report i use Formula field in running total as i need to pick up only those records in the database which match the selection criteria provided. I tried other options like Sum of a field,..but no success.
let me know if any one of you have answer. THANKSdo you use grouping in the report(Jan, Feb, Mar...)?|||NO.
I have formula fields to calculate the First and the last day of each of the months using the current date. Using these i check the date in the record to count that record whether that should fall in Jan or Feb or march in the sub report.
Data comes from 2 seperate db tables. Issueno table(main report) and Tickets table (sub report)
Only Selected Issue no is passed as parameter to the sub report to do the above calculation (count no of tickets with date in Jan or Feb or Mar..).
Sub Report footer has these running total fields with the formula to count the tickets.
Report looks like this:
Issue# Rec in Jan Recs..Feb RecMarch Recs..April Total
5 0 1 2 0 3
12 2 0 7 2 11
14 0 2 0 4 6
17 11 4 0 0 15
24 0 7 1 0 8
======================================
GTotal ?? ?? ?? ?? ??
========================================
Or is there a way to count the figures in each column at the end of the Report?
(Data: First Row:- Issue no=5, Jan=0, Feb=1, March=2, Apr=0, Total=3)|||Your bottom line is to count the tickets on january, february..., i im right?
If that is you case, try this... add running total field(january) and the type of summary is count. Then in "evaluate", click the formula option and type this
month({ticket.date})=1
This means fo the month of january...
Add the another running total field for the month of february and so for...
Sorry, im slow in english so i cant understand yor question clearly.|||Your English: Not a problem at all. Thanks for the Feedback.
My problem in Short:
I am trying to Grand Total a DERIVED Field/Column like Jan, Feb, Mar...SO there is no Month field as such to Grand Total. All this calculation is done ina Sub report using the Formula field to determine in which coulmn (jan/feb/..) the record falls and accordingly counted in that month. For every issue no passed from main report as a parameter to subreport all the attached tickets to that Issue no are validated and if the selection criteria matched then counted.
Only vertical total of a devived field like Jan/feb/.. is a problem
For each issue no passed i am totalling and writing in the report - one row per issue no ( with different columns like January, Feb, March,...)
Problem: At the end of the report i want the Verticle total of each coulmn say total in January, Feb, March,...I am not able to do this.
Is there a way to do this?
In the sub report i use Formula field in running total as i need to pick up only those records in the database which match the selection criteria provided. I tried other options like Sum of a field,..but no success.
let me know if any one of you have answer. THANKSdo you use grouping in the report(Jan, Feb, Mar...)?|||NO.
I have formula fields to calculate the First and the last day of each of the months using the current date. Using these i check the date in the record to count that record whether that should fall in Jan or Feb or march in the sub report.
Data comes from 2 seperate db tables. Issueno table(main report) and Tickets table (sub report)
Only Selected Issue no is passed as parameter to the sub report to do the above calculation (count no of tickets with date in Jan or Feb or Mar..).
Sub Report footer has these running total fields with the formula to count the tickets.
Report looks like this:
Issue# Rec in Jan Recs..Feb RecMarch Recs..April Total
5 0 1 2 0 3
12 2 0 7 2 11
14 0 2 0 4 6
17 11 4 0 0 15
24 0 7 1 0 8
======================================
GTotal ?? ?? ?? ?? ??
========================================
Or is there a way to count the figures in each column at the end of the Report?
(Data: First Row:- Issue no=5, Jan=0, Feb=1, March=2, Apr=0, Total=3)|||Your bottom line is to count the tickets on january, february..., i im right?
If that is you case, try this... add running total field(january) and the type of summary is count. Then in "evaluate", click the formula option and type this
month({ticket.date})=1
This means fo the month of january...
Add the another running total field for the month of february and so for...
Sorry, im slow in english so i cant understand yor question clearly.|||Your English: Not a problem at all. Thanks for the Feedback.
My problem in Short:
I am trying to Grand Total a DERIVED Field/Column like Jan, Feb, Mar...SO there is no Month field as such to Grand Total. All this calculation is done ina Sub report using the Formula field to determine in which coulmn (jan/feb/..) the record falls and accordingly counted in that month. For every issue no passed from main report as a parameter to subreport all the attached tickets to that Issue no are validated and if the selection criteria matched then counted.
Only vertical total of a devived field like Jan/feb/.. is a problem
Wednesday, March 7, 2012
Crystal Report
Hello every body ,
I am facing a problem while running the reportsin VB 6.0. I am connecting the crystal report using CR VEIWER and data environment . when the s/w is running in the client side its showing the error
PHYSICAL DATABASE NOT FOUND.
But if the crytal report application is in the client machine it will work properly. I even copied all the DLL files.
I know its not the conventinal way of connecting crystal report to VB. Can any one help meIt sounds like you may be trying to connect to a local database (one that's located on your development machine)? I don't know anything about using Crystal to connect to a database (I do all my database stuff in VB, then pass the records to Crystal to manipulate).
Do a search here (dev-archive's Crystal Forum) (http://www.dev-archive.com/forum/forumdisplay.php?s=&forumid=64) for more Crystal/Database info. (You should also post all future CR questions on that forum instead of on the VB6 forum.)
Good Luck and sorry I couldn't help anymore. :)
I am facing a problem while running the reportsin VB 6.0. I am connecting the crystal report using CR VEIWER and data environment . when the s/w is running in the client side its showing the error
PHYSICAL DATABASE NOT FOUND.
But if the crytal report application is in the client machine it will work properly. I even copied all the DLL files.
I know its not the conventinal way of connecting crystal report to VB. Can any one help meIt sounds like you may be trying to connect to a local database (one that's located on your development machine)? I don't know anything about using Crystal to connect to a database (I do all my database stuff in VB, then pass the records to Crystal to manipulate).
Do a search here (dev-archive's Crystal Forum) (http://www.dev-archive.com/forum/forumdisplay.php?s=&forumid=64) for more Crystal/Database info. (You should also post all future CR questions on that forum instead of on the VB6 forum.)
Good Luck and sorry I couldn't help anymore. :)
Crystal 9 crosstab export to excel (peoplesoft 8.9)
Hi
We are on Crystal 9 (9.2). I am running the report from PeopleSoft 8.9 (People Tools 8.46).
My crystal uses crosstab. When I run the report online (peoplesoft) choosing the output option 'xls', report is generated with the following formatting issues. Please help me if you figured this out already.
1) Report column width is too big. How to reduce it? Same problem with Row height.
2) The label 'Total' for each group is displayed in the column next to it. I want to print this label in the same column.
3) Row headings are repeated on every page. But they are diplayed after one row of data. Is there any way to suppress these two headings that are displayed through out the report.
4) If I have a 3 columns under a comomn group name and if one of those columns has no data, is there a way to suppress that column? Or is there a way to keep the column headers as in their place?
5) column data spans in multiple columns. How do i keep them in one column?
Your help is greatly appreciated.
Thanks
Sreedevihi,
Do u have any idea about how we can import data's from Excel Pivot table to crystal report (cross-tabs).
Thanks|||Sreedevi, read them in CR help file
Also read
http://support.businessobjects.com/
We are on Crystal 9 (9.2). I am running the report from PeopleSoft 8.9 (People Tools 8.46).
My crystal uses crosstab. When I run the report online (peoplesoft) choosing the output option 'xls', report is generated with the following formatting issues. Please help me if you figured this out already.
1) Report column width is too big. How to reduce it? Same problem with Row height.
2) The label 'Total' for each group is displayed in the column next to it. I want to print this label in the same column.
3) Row headings are repeated on every page. But they are diplayed after one row of data. Is there any way to suppress these two headings that are displayed through out the report.
4) If I have a 3 columns under a comomn group name and if one of those columns has no data, is there a way to suppress that column? Or is there a way to keep the column headers as in their place?
5) column data spans in multiple columns. How do i keep them in one column?
Your help is greatly appreciated.
Thanks
Sreedevihi,
Do u have any idea about how we can import data's from Excel Pivot table to crystal report (cross-tabs).
Thanks|||Sreedevi, read them in CR help file
Also read
http://support.businessobjects.com/
Crystal 8.5 Page Length less than 4"
Hi!
I have a VB 6.0/CR 8.5 official receipt printing on a form that is 8.5 x 3.5. It was running perfectly under Windows 98 but when I have upgrade to Windows XP I can no longer access withing CR 8.5 forms that are less than 4 inches. Any idea how can I resolve this issue.
Thanks.
rclazarito[ Thread moved to Crystal Report ]
I have a VB 6.0/CR 8.5 official receipt printing on a form that is 8.5 x 3.5. It was running perfectly under Windows 98 but when I have upgrade to Windows XP I can no longer access withing CR 8.5 forms that are less than 4 inches. Any idea how can I resolve this issue.
Thanks.
rclazarito[ Thread moved to Crystal Report ]
Friday, February 24, 2012
Crosstab Query
I have a query running on MS Access like this :
TRANSFORM Count(ActivityClass) AS CountOfActivityClass
SELECT ActivityClass
FROM Category
GROUP BY ActivityClass
PIVOT Type
but how it's can be run on SQL Server, is there any function on SQL to run
the Transform function?
ThanksHi
There is no function in SQL 2000 that will do this, the most efficient way
is to do it within the application. But you may also want to check out:
http://support.microsoft.com/defaul...b;EN-US;q175574
or
http://www.sqlteam.com/item.asp?ItemID=2955
or
http://www.rac4sql.net/
John
"Firmansyah" wrote:
> I have a query running on MS Access like this :
> TRANSFORM Count(ActivityClass) AS CountOfActivityClass
> SELECT ActivityClass
> FROM Category
> GROUP BY ActivityClass
> PIVOT Type
> but how it's can be run on SQL Server, is there any function on SQL to run
> the Transform function?
> Thanks
>
>|||Hi,
Thanks very much for the infomation
Firman
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:0195E062-2769-4202-B065-FD180D2958FF@.microsoft.com...
> Hi
> There is no function in SQL 2000 that will do this, the most efficient way
> is to do it within the application. But you may also want to check out:
> http://support.microsoft.com/defaul...b;EN-US;q175574
> or
> http://www.sqlteam.com/item.asp?ItemID=2955
> or
> http://www.rac4sql.net/
> John
>
> "Firmansyah" wrote:
>
TRANSFORM Count(ActivityClass) AS CountOfActivityClass
SELECT ActivityClass
FROM Category
GROUP BY ActivityClass
PIVOT Type
but how it's can be run on SQL Server, is there any function on SQL to run
the Transform function?
ThanksHi
There is no function in SQL 2000 that will do this, the most efficient way
is to do it within the application. But you may also want to check out:
http://support.microsoft.com/defaul...b;EN-US;q175574
or
http://www.sqlteam.com/item.asp?ItemID=2955
or
http://www.rac4sql.net/
John
"Firmansyah" wrote:
> I have a query running on MS Access like this :
> TRANSFORM Count(ActivityClass) AS CountOfActivityClass
> SELECT ActivityClass
> FROM Category
> GROUP BY ActivityClass
> PIVOT Type
> but how it's can be run on SQL Server, is there any function on SQL to run
> the Transform function?
> Thanks
>
>|||Hi,
Thanks very much for the infomation
Firman
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:0195E062-2769-4202-B065-FD180D2958FF@.microsoft.com...
> Hi
> There is no function in SQL 2000 that will do this, the most efficient way
> is to do it within the application. But you may also want to check out:
> http://support.microsoft.com/defaul...b;EN-US;q175574
> or
> http://www.sqlteam.com/item.asp?ItemID=2955
> or
> http://www.rac4sql.net/
> John
>
> "Firmansyah" wrote:
>
Tuesday, February 14, 2012
CROSS APPLY doesn't work in some databases
I've run across a weird error trying to use CROSS APPLY. Running SQL2005SP2 developer edition on XP2 (but same error occurs on Enterprise running on W2K3).
A simple CROSS APPLY always works when run from master, but doesn't work when run from most (but not all) of my other user databases. I can't figure out what could be causing the error, or is there some reason it would only work when the current database is master?
Here is the simplest testcase I can come up with:
select a.spid,b.text from master..sysprocesses a cross apply ::fn_get_sql(a.sql_handle) b
If this is run while the current database is master, it returns spid and SQL without a problem. If it's run in the other SQL-delivered databases (model, msdb, tempb), it also works fine. However, if it's run in all but one of the user databases, I get back an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.
I'm stumped on how to troubleshoot this. There is no difference in the query; the only difference is the current database. The databases are all owned by sa. I get the same results with sys.sysprocesses instead of master..sysprocesses.
As another datapoint, the employee/department example in the BOL for CROSS APPLY exhibits the same behavior; it works fine if run with master as the current database (regardless of where the tables are created), it fails with
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
if run from one of the other user databases.
Any ideas on what could be wrong?
Thanks,
Vince
Sorry to reply in my own thread, but I found the problem. I took drastic measures and created a new, empty database, then deleted all objects out of one of the ones that didn't work. The new one worked, the old one didn't.
Then I started looking at every dialog in SMSS for each database. The difference (which everyone else has probably figured out already) was the compatability level. The old databases were created with 80, the new ones had 90.
My apologies for the noise.
|||Actually, it wasn't necessary to drop these databases - there is a special stored proc to change their compatibility level:
exec sp_dbcmptlevel MyOldDB, 90
Subscribe to:
Posts (Atom)