Need assistance with creating a "Datediff"/"CASE" type of formula. I have timestamps in the {incilog} table, in different rows across the same column {incilog.timestamp} that I need to subtract from one another, but only if the {incilog.transtype} equals "TR" from the row that has the {incilog.transtype} equal "FPS". Logic is: If {incilog.transtype} equals "TR" and {incilog.transtype} equals "FPS", then subtract {incilog.timestamp} from {inciog.timestamp} in the corresponding row. Basically, I need to get how many seconds it took from "TR" to "FPS". THANK YOU FOR YOUR TIME AND ASSISTANCE! **EXAMPLE OF DATA** | timestamp | inci_id | transtype | descript | |-----------|---------|-----------|----------| |8/22/2022 12:14:46 AM | 2022264051| TR | Time Received | |8/22/2022 12:17:00 AM | 2022264051| FPS| Fire Pri. Started |

I have many reports executing from Crystal reports 10 using ODBC connection on SQL Server 2008R2 database which are consuming memory. As per the SQL Profiler I can see when a crystal reports session executed it first executed system procedure SP_Columsn and Sp_Stored_procedure which are actually consuming memory. I did some research and found about a report option 'verify at first refresh' and 'Verify database' which I by default checked which calls those System SP internally so I unchecked the option but still I can see system Sp executing in profiler. Can anyone please tell me how can I avoid calling system SP from Crystal Report?

I have a server which runs third party software (called Visualcut) to email Crystal Reports to our users. When I log onto the Visualcut Server, I can open the software and then preview a report. This will ask me to enter a username and password (for an Microsoft SQL account) and then the report will display with no errors however, whenever I do this, an error is logged in the SQL Server error logs as follows: > 10/26/2015 08:15:39 Logon Unknown Login failed for user ''. Reason: An > attempt to login using SQL authentication failed. Server is configured > for Windows authentication only. [CLIENT: 10.10.10.26] > > 10/26/2015 08:15:39 Logon Unknown Error: 18456 Severity: 14 > State: 58. I don't understand why the report would authenticate seemingly without any issues however, the log file states otherwise. I would like to add that our SQL Server is configured to use mixed authentication.

As a DBA I would like to include a read only replica into the always on availability group. XSLT / Crystal Reports will only be used. Please see below 1) Primary Replica (Same Data center) Synchronous Mode Automatic Failover 2) Secondary Replica (Same Data center) Synchronous Mode Automatic Failover 3) Secondary Replica (Read Only , Same Data center for Reporting ) Asynchronous Mode 4) Secondary Replica (Off Site , DR) Asynchronous Mode The will be a file share witness to form the quorum My questions is will there be any problem if the read only replica for reporting is included as part of the always on availability group instead of setting Transaction Replication with the Listener. The secondary replica (Read Only) will be used for reporting only mainly XSLT and Crystal Reports , SSRS will not be used. Any expert feedback will be highly appreciated.

Original symptom: User sa locked out, too many bad attempts. Problem was sudden (no issues before), and pervasive (couple of fails in one day). As this was a new roll out of SQL backend to Sage300, Crystal Reports and Misys inventory package, we could reboot to recover. (We have since confirmed a Windows Login will allow us to unlock account. No more reboot.) After seeing the recommendation on how to "disable enforcement policy", we halted the lockouts. (And yes, there was a windows update, and SQL "uses the policy of the underlying OS" - did Windoze something change?) **Ahh! But here the mystery deepens!** Our log indicates a login failure for user sa at each Crystal/Sage report generated. Login failed for user 'sa'. Password did not match that for the login provided. [CLIENT: -or- 192.168.x.x of machine] Error: 18456 Severity: 14 State: 8 The report IS generated and delivered to the user. No indication of an issue/error. We tried to *intentionally* enter a bad password; exact same error in the logs, BUT the user gets a failure dialog box. Please note: with the exception of occasional fat-fingered logins, we had no login failures in the logs up until this started. The verbose message is present (duplicated) in both the WindowsNT:Application and the SQL server logs. So the problem is still there! But the user doesn't see it. But it is in the logs. WHY? I expect this will come to bite me later, if I don't address today. Since this is new roll-out, and end-user expects some teething pain, I have some leeway to try things, now.... Maybe not so much, down the line, if/when this blooms into a real issue. Any suggestions??

There are several options to choose which data source we want to connect in Crystal Reports including SAP BEx, Relational Connection, and **Universe**. I have difficulties in understanding what exactly do they mean my saying a Universe data source. How are Universe data sources created and where are they used for? What is its difference with relational connection? Thanks.

I have just started a new job where I am responsible for building and improving reports. There is one report I am trying to move from Access to Crystal. The data is stored in an application called Acctivate. The previous person who held my job built the report with a series of queries that built multiple tables, and then a final query which aggregated the information from the tables built by the first five queries. The PTB want the report in Crystal so the management for each client can call up their info and run the report, rather than have to call me and have me do it. What is the best approach to this multi-level query (currently make-table) that will let me move the report out of Access?

I am converting a crystal report to ssrs report, i am trying to implement "Format With Multiple Columns" feature of crystal report to ssrs. I have a Main tablix that has two Two Groups - 1st Group - PolicyType - 2nd Group - WeekNumber Under Week Number group, i have inserted a rectangle and a matrix within it. The matrix will show the policy number generated in every week. The matrix should only contain 6 columns and the grouping for matrix is as below - Row Grouping : Ceiling(RowNumber("WeekNumber") / 6) - Column Grouping : Ceiling(RowNumber("WeekNumber") MOD 6) I need a output like below **InsuranceType1**
***Week 1***
Policy Number
1001 1002 1003 1004 1005 1006
1007 1008 1009 1010
***Week 2***
Policy Number
2001 2002 2003 2004 2005 2006
2007 2008 2009 2010

I am able to get a similar output but the sorting within the matrix is not working, ie., i get the following result
**InsuranceType1**
***Week 1***
Policy Number
1002 1003 1001 1004 1007 1006
1005 1008 1009 1010
***Week 2***
Policy Number
2008 2009 2003 2004 2005 2006
2007 2001 2002 2010

i have not used any sorting on the Matrix, nor in the group of matrix the only sorting is on the main tablix groups PolicyType and WeekNumber and both are in ascending order. What i have tried is: 1. Sort Matrix using PolicyNumber, but that will only output first row and first column (very strange!!!). 2. Add an additional sort of PolicyNumber in Group WeekNumber (did not work either)
i have no option of modifying the stored procedure to return the row number back to the report as the client is strict on SP modification as they want to get this report backward compatible. Please help!!

Using Crystal Reports version 14.0.3.613RTM With SQL Server 2012 onward, if a field has both number and string data, I can use try_convert to compare, i.e. TRY_CONVERT(int, resource_code) > 999999999 How do I handle this in Crystal Reports? I tried: ToNumber({sch_resource.resource_code}) But if resource_code isn't a number, CR barfs.

Currently in a crystal report I have each order grouped together where the user is displayed the order lines in the orders group. I have made a running total field for a subtotal for all of the order lines prices. But I wan't to make a parameter for the end user to select whether the price is > or < than a specific amount. And what I thought may be the best solution is calculate the subtotal in the stored procedure and pass it to the report to build the parameter off of. But it looks like SQL Server 2000 does not contain the ROLLUP function in later versions.

With attempting to convert a crystal report formula into a SQL CASE expression, I seem to be having trouble understanding the concept of "total_pallet_weight" and "order_no" utilizing the "AND" in the formula with "cubic feet" and "order_no". I thought with my attempt below I would start out with a CASE just for "total_pallet_weight" then have a sub CASE for cubic feet to represent the "AND" in the formula. But I'm not to familiar with how the syntax "({a_ras_shipping_order_PCF_vw.total_pallet_weight}, {a_ras_truck_shipment_vw.order_no}) > 0" works. Is the forumla saying "total pallet weight + order_no > 0"? ***Crystal Report Formula:*** IF Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight}, {a_ras_truck_shipment_vw.order_no}) > 0 AND Sum ({a_ras_shipping_order_PCF_vw.cubic_feet}, {a_ras_truck_shipment_vw.order_no}) > 0 THEN Sum ({a_ras_shipping_order_PCF_vw.total_pallet_weight}, {a_ras_truck_shipment_vw.order_no}) /Sum ({a_ras_shipping_order_PCF_vw.cubic_feet}, {a_ras_truck_shipment_vw.order_no}) ELSE 0 ***Attempt at replacing formula:*** CASE WHEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) > 0 THEN ,CASE WHEN (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) > 0 THEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) + (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) ELSE 0 END ELSE 0 END AS 'Total_PCF' ***Syntax for "total pallet weight"*** SUM (TCT.[weight]) + SUM(TPM.[weight]) AS 'Total_Pallet_Weight' ***Syntax for "cubic feet"*** CASE WHEN TRCB.skid_height_inches > 0 AND TPM.dim_ext_x > 0 AND TPM.dim_ext_y > 0 THEN CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches * TPM.dim_ext_x * TPM.dim_ext_y) / 1728) ELSE 0 END AS 'cubic_feet',

OK I know there are hundreds of posts out there that state how to pass a parameter from a stored procedure to a crystal report. But unfortunately with some research I haven't come across a specific answer to my question with regards to SQL Server variables and crystal report parameters. Question: If I have a stored procedure and I am declaring a variable called "Location" and I want to reference that variable in my crystal report as a new parameter and end users to be presented a dialog box that allows them to select from various locations and that would in return results only for a specific location chosen from a drop down. How would that occur? Methods tried so far: Crystal Report Builder 11 1. In "parameter fields" under field explorer, select new and then choose "List of Values" to by Dynamic and choose the data source and column that's been used with my variable and it says "Parameters" is @location. But when I run the report I am presented with a dialog box for a location value but there are no values to select from. (To make a note the location field is being supplied by the users drop down selection via the report thus returning results from the stored procedure) 2. In "parameter fields" select new and then leave list of values as "static" and choose value field as column "location" and enter multiple values with regards to locations for the user to select from. (This does present the user with a dialog box suggesting drop down values for a location but no results returned so I was unsure if this was the proper way?) Code: ALTER PROCEDURE [dbo].[a_ras_TruckSummary_SP] AS BEGIN IF (object_id('tempdb..#tempTruckSummary') IS NOT NULL) BEGIN DROP TABLE #tempTruckSummary END DECLARE @location VARCHAR(10) SELECT O.order_no, L.shipped, L.price, L.[location] FROM orders O WITH (NOLOCK) INNER JOIN Ord_list L ON O.order_no = L.order_no WHERE O.routing NOT LIKE 'FEDX%' AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3') AND ISNULL(O.void,'') 'V' AND L.location = @location GROUP BY O.order_no, L.shipped, L.price, L.[location]

The company I'm working for has a SQL Server with read-only access. They use Crystal Reports hooked up to PostgreSQL for reporting. Is there any way to make it so I can move all the data from the MSSQL DB to PostgreSQL **without** user interaction? That seems to be the caveat to what I'm trying to do. They need to be able to run this report after I leave without having to interact with it during the process. Or am I looking at this the wrong way? Is there a way to save a "snapshot" of the SQL Server DB that can be manipulated in Crystal Reports? The ultimate goal is that since the DB is dynamic we need to be able to have a static DB at the end of the month that all the reports can be ran on without having to worry about it changing.

There's a field in a FoxPro table that I'm trying to pull in a Crystal Report (v10), and it begins with an _ and it doesn't appear in Field Explorer->Fields->Database Fields->->_fieldName Are fields that start with _ invisible to Crystal Reports?