I'm working on a stored procedure to populate a Crystal report. My company insists that we put the report parameters in the stored procedure instead of in Crystal...so that the SQL server (rather than the desktop)does the work of restricting the data. Is there anything I can do on the SQL side(possibly User Defined Data Type) to get Crystal to prompt me for a date WITHOUT the time? I started with this:
When Crstal prompts me for the parameters, I can type the date or use the calendar to pick a date, but I AM FORCED to enter a time. I know I could choose to ignore the time in the stored procedure, but the users don't want to see the time section of the parameter. Apparently SQL doesn't have a plain "date" parameter without a time.
However if I do it this way, I can't seem to find a way to make sure a valid date is entered when Crystal prompts the user for the dates.
If using char(10) turns out to be the best method, is there a way I can pre-populate the Crystal prompts like this:
@BeginDate = 1st day of the current month
@EndDate = the current system date
Crystal seems to allow hard coded default values, but I can't find a way to do calculated default values.
I've tried to find this documented on the Internet and found Crystal Reports User's Guid online documentation (582 pages of it!) It looks like it will be helpful for my other questions, but what I'm trying to do right now is call a stored procedure from my crystal report. So in Field Explorer under Database Fields my stored procedure is there with the three columns it selects. How do I put these three columns on my report? I try dragging them to Section 3 details and they're there but when I preview the report all I get are the headers, not the records. Is there something else I need to do? It requires an input parameter which I am not getting prompted for - maybe that is my problem, without an input parameter it can't successfully run the stored procedure, so how do I make it prompt me?
Thanks for helping me with my first Crystal Report!
i am developing a Performance Management System for my organisation and reports are quite complex. so i thought to create a CLR stored procedure and then use this in Crystal Report.
The problem I am facing is that the CLR stored procedures I create using Visual Studio 2005 are not visible from Crystal Reports XI come with Visual Studio 2005. I can create stored procedures using standard transact sql and these are visible, but the CLR stored procedures are not.
I Simply create a CLR store procedure in Visual Basic and debug it. This CLR procedure then appeares under stored procedure node in SQL Server 2005.
I've noticed when I browse the stored procedures in my SQL Management Studio that the image of the stored procedure has a padlock shown in the icon, as if they are locked?
Do I have to explicitly enable a security attribute on CLR stored procedures to make them visible?
I am new in Crystal reports so please help me on this one.
I have to create a report that will bring back the TOP 20 customers per Sales Rep. My SQL stored procedure needs a parameter @SalesRep to execute. My stored procedure links between 2 tables. How do I get this stored procedure executed in Crysal Reports to give me results. The SP works fine.
After I change a fairly complex stored procedure and I run a reportagainst it, crystal hangs at "assesing database". I have verified thedatabase. When I run a trace on SQL is shows repeated cachemiss overand over. I let it run for 30 minutes and nothing.Anyone?
Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott
Situation: If possible, create one stored procedure for Crystal Reports(CR). For any CR users ou there, looking for coding suggestion also. Thank you for your assistance.
Currently, CR has a header(Report Header) coming from 1 to 1 tables, there is a parameter which is passed in, allowing it to retrieve one record for the header(report header or RH).
The CR then has 4 subreports in which each has its own stored procedure. This I believe happens because the Report Header records relationship to the subreport is 1 to many. The 3 remaining subreports relationship to the Report Header is also a 1 to many. The main problem is the subreports is that there may or may not have any records based on this, the subreport is suppressed within CR and thus there can be alot of unused white space on the 1st page and one of the subreports prints on a 2nd page when it could have been on the 1st page.
Example:
Main Stored Procedure(sp) RH Tables: aaa, bbb, ccc, ddd are 1 to 1 record tables and have a @xyz parameter.
Results of subreports and their associated procedure are varchar(8000) decriptions. Each line should be counted in some manner in the stored procedure(sp) and then should be counted in CR to avoid excess white space. To complicate matters subreport 2 to has font, bold, showbox but can have different font sizes. These variations could cause different line space requirements. Any ideas?
Each line should be counted in some manner in the stored procedure and then should be counted in CR. There is a count of records for each the main stored procedure.
RH has 1 record to many records in subreport 1 with same @xyz parameter. RH has 1 record to many records in subreport 2 with same @xyz parameter. RH has 1 record to many records in subreport 3 with same @xyz parameter. RH has 1 record to many reords in subreport 4 with same @xyz parameter.
Because of the relationships, its seems impossible to create one stored procedure which give in one select statement with all the 1 to 1, 1 to many relationsips, as stated above. I thought concatinating i.e. 3 records together and then parsing it out some how in CR, along with utilizing the i.e. 3 record count to help count lines. Thought of some how creating a temporary table matrix for 8pt - 28pt for line and spacing considerations.
We're having problems with a number of stored procedures we have written (we're all v. new to SQL Server). The typical scenario is :
sp executes spA,spB and spC Each uses a cursor, spA,spB and spC out values that sp wants to output with some additional info.
In ISQL/W the output is fine. When the sp is executed from a Crystal Report we always get a single row of data (the 1st). We get the same problem if it is executed from the Access Upsizing Tool's SQL Server Browser utility.
We use SET NOCOUNT ON in sp (not in the others). If we remove SET NOCOUNT ON we get now rows in Crystal.
I enclose an example (with 3 sub- SPs). Help. Martin
--output from qryEmployeeNICHol @SumOfdEmployersNICHoliday FLOAT
SET NOCOUNT ON
DECLARE employee_period CURSOR FOR SELECT tblEmployees.lUniqueID, tblEmployees.lEmployeeNumber, tblEmployees.szEmployeeSurname, tblEmployees.szInitials, tblEmployees.szDeptNo, tblEmployees.dtDateLeft, tblEmployees.dTotalGrossPayThis, tblEmployees.dTaxableGrossPayThis, tblEmployees.dTotalGrossPayPrevious, tblEmployees.dTaxableGrossPayPrevious, tblEmployees.dTaxPaidThis AS EdTaxPaidThis, tblEmployees.dTaxPaidPrevious, tblEmployees.dSMPPaidToDate, tblEmployees.dSSPPaidToDate FROM tblEmployees WHERE (tblEmployees.lCompanyNumber = @lQryCompanyNumber)
DECLARE employee_ni_categories SCROLL CURSOR FOR SELECT DISTINCT tblEmployeePeriodDetails.lUniqueID, tblEmployeePeriodDetails.cNICategory FROM tblEmployeePeriodDetails WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND (tblemployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))
OPEN employee_period
FETCH NEXT FROM employee_period INTO @lUniqueID, @lEmployeeNumber, @szEmployeeSurname, @szInitials, @szDeptNo, @dtDateLeft, @EdTotalGrossPayThis, @EdTaxableGrossPayThis, @EdTotalGrossPayPrevious, @EdTaxableGrossPayPrevious, @EdTaxPaidThis, @EdTaxPaidPrevious, @EdSMPPaidToDate, @EdSSPPaidToDate
WHILE (@@FETCH_STATUS <> -1) BEGIN EXEC qryPayEmp8b @lUniqueID, @dtQryPayrollYearStartDate, @SumOfdEmployersNICHoliday OUTPUT EXEC qryPayEmp8c @lUniqueID, @dtQryPeriodEndDate, @PdTotalGrossPayThis OUTPUT, @PdTaxableGrossPayThis OUTPUT, @PdTaxPaidThis OUTPUT, @PdSMPPaid OUTPUT, @PdSSPPaid OUTPUT OPEN employee_ni_categories FETCH FIRST FROM employee_ni_categories INTO @lCCUniqueID, @szNICategory IF (@@FETCH_STATUS <> -1) WHILE (@@FETCH_STATUS <> -1) BEGIN EXEC qryPayEmp8a @lUniqueID, @szNICategory, @dtQryPayrollYearStartDate, @SumOfdNIablePay OUTPUT, @SumOfdEmployersNI OUTPUT, @SumOfdEmployeeNI OUTPUT, @SumOfdContractedOutEarnings OUTPUT, @SumOfdEmployeeNIContractedOut OUTPUT
SELECT @lUniqueID AS lUniqueID, @lEmployeeNumber AS lEmployeeNumber, @szEmployeeSurname AS szSurname, @szInitials AS szInitials, @szDeptNo AS szDeptNo, @dtDateLeft AS dtDateLeft, @EdTotalGrossPayThis AS EdTotalGrossPayThis, @EdTaxableGrossPayThis AS EdTaxableGrossPayThis, @EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious, @EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious, @EdTaxPaidThis AS EdTaxPaidThis, @EdTaxPaidPrevious AS EdTaxPaidPrevious, @EdSMPPaidToDate AS EdSMPPaidToDate, @EdSSPPaidToDate AS EdSSPPaidToDate, @SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday, @PdTotalGrossPayThis AS PdTotalGrossPayThis, @PdTaxableGrossPayThis AS PdTaxableGrossPayThis, @PdTaxPaidThis AS PdTaxPaidThis, @PdSMPPaid AS PdSMPPaid, @PdSSPPaid AS PdSSPPaid, @szNICategory AS szNICategory, @SumOfdNIablePay AS SumOfdNIablePay, @SumOfdEmployersNI AS SumOfdEmployersNI, @SumOfdEmployeeNI AS SumOfdEmployeeNI, @SumOfdContractedOutEarnings AS SumOfdContractedOutEarnings, @SumOfdEmployeeNIContractedOut AS SumOfdEmployeeNIContractedOut
FETCH NEXT FROM employee_ni_categories INTO @lCCUniqueID, @szNICategory END ELSE SELECT @lUniqueID AS lUniqueID, @lEmployeeNumber AS lEmployeeNumber, @szEmployeeSurname AS szSurname, @szInitials AS szInitials, @szDeptNo AS szDeptNo, @dtDateLeft AS dtDateLeft, @EdTotalGrossPayThis AS EdTotalGrossPayThis, @EdTaxableGrossPayThis AS EdTaxableGrossPayThis, @EdTotalGrossPayPrevious AS EdTotalGrossPayPrevious, @EdTaxableGrossPayPrevious AS EdTaxableGrossPayPrevious, @EdTaxPaidThis AS EdTaxPaidThis, @EdTaxPaidPrevious AS EdTaxPaidPrevious, @EdSMPPaidToDate AS EdSMPPaidToDate, @EdSSPPaidToDate AS EdSSPPaidToDate, @SumOfdEmployersNICHoliday AS SumOfdEmployersNICHoliday, @PdTotalGrossPayThis AS PdTotalGrossPayThis, @PdTaxableGrossPayThis AS PdTaxableGrossPayThis, @PdTaxPaidThis AS PdTaxPaidThis, @PdSMPPaid AS PdSMPPaid, @PdSSPPaid AS PdSSPPaid CLOSE employee_ni_categories FETCH NEXT FROM employee_period INTO @lUniqueID, @lEmployeeNumber, @szEmployeeSurname, @szInitials, @szDeptNo, @dtDateLeft, @EdTotalGrossPayThis, @EdTaxableGrossPayThis, @EdTotalGrossPayPrevious, @EdTaxableGrossPayPrevious, @EdTaxPaidThis, @EdTaxPaidPrevious, @EdSMPPaidToDate, @EdSSPPaidToDate END
CLOSE employee_period DEALLOCATE employee_period
DEALLOCATE employee_ni_categories RETURN
-----------------
CREATE PROCEDURE coral.qryPayEmp8a @lUniqueID INT = 1, @szNICategory VARCHAR (1) = 'A', @dtPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000', @SumOfdNIablePay FLOAT OUTPUT, @SumOfdEmployersNI FLOAT OUTPUT, @SumOfdEmployeeNI FLOAT OUTPUT, @SumOfdContractedOutEarnings FLOAT OUTPUT, @SumOfdEmployeeNIContractedOut FLOAT OUTPUT AS SELECT DISTINCT @SumOfdNIablePay = Sum(tblEmployeePeriodDetails.dNIablePay), @SumOfdEmployersNI = Sum(tblEmployeePeriodDetails.dEmployersNI), @SumOfdEmployeeNI = Sum(tblEmployeePeriodDetails.dEmployeeNI), @SumOfdContractedOutEarnings = Sum(tblEmployeePeriodDetails.dContractedOutEarning s), @SumOfdEmployeeNIContractedOut = Sum(tblEmployeePeriodDetails.dEmployeeNIContracted Out) FROM tblEmployeePeriodDetails WHERE ((tblEmployeePeriodDetails.lUniqueID = @lUniqueID) AND (tblEmployeePeriodDetails.cNICategory = @szNICategory) AND (tblEmployeePeriodDetails.dtPeriodEndDate >= @dtPayrollYearStartDate)) GROUP BY tblEmployeePeriodDetails.lUniqueID, tblEmployeePeriodDetails.cNICategory
RETURN
-----------------------------
REATE PROCEDURE coral.qryPayEmp8b @lQryUniqueID INT = 1, @dtQryPayrollYearStartDate DATETIME = '1900-01-01 00:00:00.000', @SumOfdEmployersNICHoliday FLOAT OUTPUT AS SELECT @SumOfdEmployersNICHoliday = Sum(tblEmployeePeriodDetails.dEmployersNIforNICHol iday) FROM tblEmployeePeriodDetails WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND (tblEmployeePeriodDetails.dtPeriodEndDate >= @dtQryPayrollYearStartDate))
FROM tblEmployeePeriodDetails WHERE ((tblEmployeePeriodDetails.lUniqueID = @lQryUniqueID) AND (tblEmployeePeriodDetails.dtPeriodEndDate = @dtQryPeriodEndDate))
I apologize if I posted in the wrong section, but I cannot find a solution to this. I was hoping that some one can help me figure this out. I need this solution in a stored procedure VS using dataset because there are thousands of categories and it is extremely slow with a dataset. I have a category table ID intPARENTID intNAME SAMPLE DATA:
ID NAME PARENT_ID
1 ANIMALS 0
2 DOGS 1
3 CATS 1
4 Abyssinian 3
5 Persian 3
6 Rurkish Van 3
7 Dalmation 2
8 German Shepherd 2
9 Irish Setter 2
10 Bulldog 2
I need the stored proc to return results in a single record set like this: ANIMALS - DOGS - - Dalmation - - German Shepherd - - Irish Setter - - Bulldog - CATS - - Abyssinian - - Persian - - Turkish Van Seems fairly easy but I have been battling with this for days now. Thank you in advance, EL
I'm coding in a black hole that has a moving target in it.
Ok, I now need to call an stored procedure and with the results from the stored procedure I need to populate my tables on my handheld.
Any ideas on how I can do this? I was using inline sql "select col1, col2, from table1" and doing my rda pull from that, but now I need to call stored procedures to do this and one sp takes a parameter. How can I accomplish this on handheld ?
I've created an app that connects to a SQLCE database. I need to create Crystal Reports that connect to the database. There is no ODBC driver for SQLCE. How can I do this?
I am a Web developer. I am new to VB 6.0 and Crystal Report. I havejoined an organisation where there is an application running(developedin VB6 and crystal report 7). I have to modify one report. In twocolumns I have to make minor changes i.e put and = operator. I amencountering following problem:1. When I open the report using Seagate crystal report for rational /32 bit crystal Report Designer there are three columns which are goingout side the visible area and I am not able to scroll up to them. Iincreased the page margin (left / right) and made it to zero and zerobut still I am not able to see those columns. Please guide.2. Please suggest any good web site for learning Crystal Report 7Thanks n RegardsDeepak Sinha
Hi, I am trying to run the crystal report from asp. its says SQL Server connection failed. But i could connect SQL server using the same DSN. Any idea???
Set crp = Server.CreateObject("Crystal.CRPE.Application") iReturn = crp.LogOnServer("p2sodbc.dll", "DSNNAME","USERID","PASSWORD") Set oRep = crp.OpenReport("C:inetpubwwwrootpdftestest.rpt") oRep.Preview
I have a report that feeds the data from SQL 2000 and its giving me this error:
Failed to retreive data from the database. Details: ADO Error Code: 0x80004005 Source: Microsoft OLE DB Provider for SQL Server Description: [DBNETLIB][ Connection Open (Connect ()).] SQL Server does not exist or access denied. SQL State:08001 Native Error: 17 [Database Vendor Code: 17]
I test the database and its ok, we update both Crystal and SQL and nothing happend. We test communication from PC to server and everything looks OK. We check ODBC Settings and looks fine...
we have data base in ms access and reports in crystal reports 5 recently we converted our data base to sql sever 7 and reports to crystal reports 8. when i opend directly in cystal reports and given data base connecting through oledb i am getting reports but from the application it is in vb6 the report window is poping up and latter a error msg saying un able to open sql server when i checked up the connection it is ok help me anil
I'm using Crystal 9 with SQL Server database. I have a report that was working fine for the first 8 months. As soon as I add another month, the report fails. I've tried different setting in my indexes with different variations of periods. The minute I have over 8 periods included, the report fails. No errors, just no data. I don't even have a clue where to look for possible answers on the Internet.
There is probably a really simple solution to this problem... but, here it is..
I am working on CR Designer v11 and have a report that uses a stored procedure. Originally, when the report was created, this stored procedure had one parameter. Since then, however, I have added an additional parameter to the SP in SQL. After doing so, I opened the report, in report designer, and manually added the new parameter to the parameter list. I then realized that I did not need to do this. The Verify Database function should auto populate the parameter list for me. So, I then deleted the parameter(that I had created manually) and performed the Verify Database. But now the parameter does not auto populate in the parameter list.
By deleting the parameter (that I had created manually), did I cause the designer to ignore this second parameter in the actual SP? How can I get the designer to auto populate the parameter list with this new parameter?
I have a windows .net program that creates and displays crystal reports. I was able to download all the .dlls a needed such as the CrystalDecisions, etc.., however, when i ran my program it tells methat I cannot run these .dlls outside of managed code. Where do I need to put these .dlls and how do I make my application work?
In a report called ICD_PrivateHospital, I have designed to show output to two table from two store procedures. 1st store proc: usp_RPT_Private and 2nd store proc: usp_RPT_Private2.
I have created both the store procs and it is executing successfully. I also have designed the Crystal Report in Visual Studio 2008.
I have added both the store procs in the crystal report with no error. I only wants to show top 20 records. It is running successfully when I add the fields and parameters from the first store procedure. But when I add the 2nd store procedures fields into the report, duplication occurs for both the results in store proc 1 and store proc 2. How to solve this issue?
I've got a formula written in Crystal Reports that I'm trying to re-do in SSRS 2005. I've just been using Crystal Reports for so long, I've got a mental road-block today.
Here is the formula in Crystal Reports: IF {V_VIEW.FIELD1} IN ["AAA", "BBB", "CCC", "DDD", "EEE", "FFF"] THEN ({V_VIEW.FIELD2}&"*") ELSE ({V_VIEW.FIELD2})
(It concatonates an asterisk to the end of FIELD2 if FIELD1 contains on of the values in the list.)
In SSRS I'd like to cause an entire row to be bold if FIELD1 contains one of the values in the list.
So in SSRS I'm putting an expression into the FontWeight properties of the TableRow and trying for something (which doesn't work yet) like: =iif (Fields!FIELD1.Value IN ("AAA", "BBB", "CCC", "DDD", "EEE", "FFF"), "BOLD", "NORMAL")
(SSRS doesn't like the "IN" in the above statement.)
Can anyone offer a suggestion on how to write this for SSRS?
Thanks! -ErikR
------UPDATE------ 2007-SEPT-17
Ok. I found a workable solution. Does anyone have a better suggestion than the following? The below works but it seems it could be done more simply... Any suggestions?
I am using SQL server 2000, Crystal Reports 8.5. I dont see any problem with accessing thro' VB Coding(ADODB).
Crystal Report connection thro ODBC also working fine.
But when i try to connect to database thro "More data sources" -> SQL server , i was thrown out with an error "Unable to connect to SQL server servernam"
I have uninstalled & Reinstalled both SQL server and Crystal Report. Still not working.
I have been struggling with this problem for quite a long time.
I am migrating my reports from crystal 8 to reporting services 2005 but before this my client wants answers of following question.Can anybody provide me answer or link related to mentioned question...
1 -Will there be any performance improvement when the same Report (considering big data) is being run with Sql-Server reporting services compared to the one with Crystal report engine?
2- Is it possible to make the labels which are part of Top Palette like €œ1 of 1€?, €œFind€?, €œNext€?, €œSelect a format€?, €˜Export€? according to the Account language?
3- Can both Versions of Reporting Tools (SQL-Reporting service and Crystal Report) run on the same machine? 4- What are the limitations of this Tools like whether there are any additional load on Database Server because of the involvement of Reporting services which are installed on DB Server. 5- Whether there are additional (to Crystal Report) types of Graph formats, Export file types available? 6- How is the licensing done for SQL-Server Report services?
I have a crystal report that calls a stored procedure from SQL Server 2000. The only parameter I have is @A the SP is: -------------- CREATE PROCEDURE Final @A INT AS Declare @SQL VARCHAR(2000) SET @SQL = 'SELECT * FROM Schools where Areano = @A' EXEC(@SQL) GO
-------------
From the standard report creation wizard, i choose the data (SP). and select the SP, then i got window to enter a parameter values for @A, and i check ( Set to Null value), then press OK, the following error occured
i have recently ported an old asp.net web application using crystal reports 9 from windows server 2003 to windows server 2008 . the crystal reports smoothly at first but one of the reports stopped working when the admin changed his password now the report is showing error. Failed to open a rowset
