Crystal Reports using SQL query

Hi gang,

Rather new to using CR, I have a SQL query I use inside of CR using the 
COMMAND inside of Database Expert. My SQL query includes:

DECLARE @BegDate datetime;
DECLARE @EndDate datetime;
SET @BegDate = '20100701'
SET @EndDate = '20100731'
***end snip***

I would like to get rid of this hard-coded date portion in the SQL COMMAND 
and have the user running the report get a prompt for the data range they 
want. Also, I would like the selected date range to appear on final report. 
With this setup I cannot display the date range used in the report.

Any help is greatly appreciated...

10/11/2010 6:12:44 PM
greatplains 29623 articles. 6 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 16

You will need to pass @BegDate and @EndDate as parameters to the report, and 
add the parameters you have to your report for view, article below might 
Mohammad R. Daoud [MVP, MCT]
Mob: +962 - 79 -999 65 85

"ChicagoPete" <> wrote in message
> Hi gang,
> Rather new to using CR, I have a SQL query I use inside of CR using the
> COMMAND inside of Database Expert. My SQL query includes:
> ***snip***
> DECLARE @BegDate datetime;
> DECLARE @EndDate datetime;
> SET @BegDate = '20100701'
> SET @EndDate = '20100731'
> ***end snip***
> I would like to get rid of this hard-coded date portion in the SQL COMMAND
> and have the user running the report get a prompt for the data range they
> want. Also, I would like the selected date range to appear on final 
> report.
> With this setup I cannot display the date range used in the report.
> Any help is greatly appreciated...
> -Pete 

10/11/2010 6:48:29 PM

Similar Artilces:

Rounding Averages on my Report
I created a summary report using the report wizard and grouped by date and selected avg. The report works fine except I would the numbers to be rounded. For example: The average for January shows 19.5, however I would like it displayed as 20. If February is 23.1678993, I would like it displayed as 23. Can someone help me with this? Thx Try... = Format([YourAvg], "#") 19.5 should display as 20 (rounding up from .5 or greater) 23.1678993 should display as 23 (rounding down from .5 or less) or... if you need numeric... ...

errors with form or query acrivated
Just converted from Access 97 to Access 2002. Here's the problem: User defined expressions (may be only text expressions) or fields show errors when a form or query window is activated or deactivated. The query will not refresh. For example, we are having trouble with the SSN in the Grants database. We haven't seen the same problem in other applications, but we haven't specifically looked for it. To be more specific: The Object Form_ParticipantsToProgramCycle has a listbox which contains a last name and a user defined expression that formats the Participants SSN i.e. 1...

how do i calculate 10% of a total in an access query
I am working in access 2007 and have a goal amount that i need to calculate a 10% increase.. I cant figure out how to do this!! Increased Price: ([YourField] * .1) + [YourField] -- KARL DEWEY Build a little - Test a little "confussed student" wrote: > I am working in access 2007 and have a goal amount that i need to calculate a > 10% increase.. I cant figure out how to do this!! On Mon, 25 Feb 2008 09:13:10 -0800, confussed student wrote: > I am working in access 2007 and have a goal amount that i need to calculate a > 10% increase.. I cant figure out how to do...

SQL General Help
I have been experimenting with using ADO to work with Excel data. I had some obstacles with the SQL until I stumbled across a web site that mentioned the table name needed a special format. Instead of using a table name like DATA, instead I had to use the format of [DATA$]. So I was wondering does anybody know of a source (document, web site, etc.) that explains all of the peculiarities of using SQL with Excel? Thanks Mike ...

Using MS Access information in Outlook
I have an MS Access DBase that contains our companies contact information. I want to be able to use that information in an Outlook contact folder. I do not want to import the information into Outlook. I want it to be automatically synchronized. Is this possible? Mikeal ...

How to generate XML file as follow, using XmlDocument
Hello, I have to generate XML file as follow, using XmlDocument. <Company> < Employee EID=3D=94111=94 Description=3D=94 xyz=94> <Dept DeptNo=3D=94D10=94> <FirstName> Abhijit </FirstName> <LastName> Babar </LastName> </Dept> <Dept DeptNo=3D=94D20=94> <FirstName> Sahine </FirstName> <LastName> Ghavane </LastName> </Dept> </Employee> < Employee EID=3D=94222=94 Description=3D=94 abc=94> <Dept DeptNo=3D=94D10=94> <FirstName> Swapnil </FirstName> ...

Excel VBA
Hi again, I have enclosed a timesheet which i want to break down the day activities by a percentage, so if my sheet says an activity took a hour then the day column would tell you percentage of a seven hou day. Also i want the date column date to change each day. Currently it work for one day but then the next day it changes all the above cells to th current date. I have attached the file Cheers Stev Attachment filename: book2.xls Download attachment: -- Message posted from ...

Change background color of query results from access in worksheet
Hi, I have Excel 2007 and selected Date => From Access and then navigated to an Access DB and selected a query. I chose "Table" view. The query results now appear in my worksheet which is great. However, I want to change the background. Right now it alternatives by row between a light blue and a darker blue. I want the results to have a white background with black borders but have been unable to change the format. If I go to Home and select No fill for a color background nothing happens. Is there a way to change the query results? Thanks, Could be the r...

Problems with Crystal Reports
Hi everyone We are currently encountering a problem with Crystal Reports on MSCRM. Launching one takes about 10-15 seconds before data is displayed (even if only 1 record is retrieved). The standard Accounts reports are worse, we get a Timed Out error everytime. We are deploying MSCRM on 2 servers - CRM Server has dual procs and 1GB RAM. Any suggestions are very much appreciated. Thanks in advance. Regards. Hon Having Crystal reports in CRM 1.0 timeout is a known bug. (Happens after you try to retrieve more than about 10K records.) There is a private hotfix. It will also be fi...

FRx Report Date won't change
We have a customer that has a GP Fiscal Year of 9073. Apparently, an AP transaction had accidentally been posted with a document date of 9073. So, in order to void that transaction, they created the fiscal year of 9073. Now, in FRx when they try to change the Report Date to December 2008, FRx changes the report date to December 9072. We've tried deleting the .g32 file and rebuilding the FRx GL Index. We've also tried deleting the SY40100 and SY40101 tables, rebuilding the Fiscal Periods in GP then rebuilding the FRx GL index but we still can use the 12/2008 date in FRx. Any...

(?) Free credit report and monitoring from Experian?
How do I get the free "credit report and monitoring from Experian" offered with Money Deluxe 2004? When I click, I'm sent to , but I see no-where to create a new account. Queries to have not been answered. Thanks, -stephen Do a little search at the Consumer Reports site and you will find the addresses and email addresses of the 3 credit reporting agencies. I believe they are required to send you a free report once per-year. You can research the referenced CR reports at your local libra...

Why doesn't indirect work with a dynamic range created using offse
Hi Has anyone else experienced this issue or I am doing something wrong. I have a series of dynamic range names defined using offset. For example the name USCities is defined as =offset(AA1,0,0,Counta(AA:AA),1) and the name Europeancities is defined as =offset(AB1,0,0,counta(AB:AB),1) In column A, dataentry into the cells is limited to USCities or Europeancities. In column B, I want to limit data entry to the range name appearing in column A. For example if USCities is selected in A1, data entry in cell B1 is to be limited to those cells forming the USCities range name. I have tri...

PatchFactory 2.0 Released. Professional and easy-to-use patch building tool for Win9x/Me/NT/2000/XP.
Description: Professional and easy-to-use patch building environment that can help you to create instant patch packages for software and file updating. Generated patch packages are small size self-extracting executable update programs in a famous installer style with adjustable user-friendly interface and multilingual support. Enhanced with features like easy-to-use interface including a Wizard mode, powerful patch engine, integrated compression technology, adjustable multilingual user-friendly interface of the update program, this program will most definitely become a valuable asset for softw...

Create Outlook 2003 Rule using 'Or' operator in 'Sent to' condition
How do I create a rule that uses the 'or' operator in the 'sent to' condition? I actually have a rule like this, but don't know how I created it. In other rules, when I try to add more that one email address or group in 'sent to,' Outlook inserts the 'and' operator. What is the exact rule in full? In general you'd create additional rules but it depends a bit on the condition or action that you are using. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 Outlook FAQ, HowTo, Downloads, Add-Ins ...

How could I force the query results to output to a specific format/template? The reason for this is that certain values don't exist in certain date ranges within the data pool. I need those values to be included in the query results anyway. I have tried linking another query(tried a table also) with the template to the query that calculated the results. Is there something I should consider when doing this? A better way? Thank you Erik Create a new query that includes the table that contains the domain of values you want to appear in the output and join that table to the existin...

Permission to use object denied.
I have a user on a Windows 2000 machine with user rights. The user is also running office 97. When he opens a excel speedsheet and Enable's Macros, he gets the following message; "Permission to use object denied". Hi Shannon............ It sounds like there is some sort of link to another workbook that a macro is calling and your user does not have rights to it. I think I would open the book without enabling the macros, and then take a look at them to see if one is trying to do such a thing. hth Vaya con Dios, Chuck, CABGx3 "Shannon" <>...

Accessing a linked file in a Word document using VBA
Hi - Basic question, in my Word template I have some linked images. I want to use VBA to change the path and filename of some of these links ... can anyone give me a clue as to how I should best do this? Thanks Robin Hi Robin, To see how to paths in Word using vba, check out the macro solution I've posted for implement relative paths at: -- Cheers macropod [Microsoft MVP - Word] "Robin" <> wrote in message news:7EA2C548-7D01-4515-BCC8-2EF3210439C9@microsof...

Report WRITER legends values #2
Hello. I need to modify existing modified report. Where can I find values for legends fields? Thanks. Sofiya. Hello Sofiya, Legend fields are placeholders that may contain pretty much anything, depending of the report you are working with. For example, on many reports, they are used to display the range values used when generating the report. (From-To) One thing you must remember though is that they are always typed as strings so you need to convert them if you want to do any arithmetic operations with them. In conclusion, if you want to evaluate the legend field values for a report,...

Data validation causing problems when using a data form in Excel 2
I have an Excel 2007 workbook that includes data validation set on a number of cells. When using a data form to enter data and I enter an invalid value on the form field corresponding to one of those cells I receive the validation error dialogue that prompts me to retry. I enter the correct data into that field on the form then close the form. My worksheet only has the data relating to the corrected field entered. All other data entered via the form is not entered onto the worksheet. Is this a bug in Excel 2007? I previously was using Excel 2002 and found that when using a ...

Excel reporting alternatives
Hi, I am part of a team currently looking for alternatives to replace Vision XL which our Finance team uses for ad-hoc reporting in Great Plains version 8. Although I am still seeking clarification I believe that Vision XL will not work with GP 10 or Office 2007 which we will soon be upgrading too hence a need for a replacement. My team will be writing sql reports using visual studio etc but our Finance team are rather hoping that they can continue using some kind of excel reporting tool, any ideas would be most hopeful. Kind regards Matthew Carpenter Hi Mathew One of the benfits of ...

Using Outlook on a MAC
Does anyone know why I can't forward HTML email with Outlook on my MAC? It is received ok but when I forward it, it goes out as text and all the formatting and images are gone. I'd appreciate the help. ...

SQL Server 2005
We recently moved an existing server which runs a SQL Server 2005 instance to a network located behind a firewall. Prior to moving the server behind the new firewall, I was able to Start/Stop/Pause/etc the SQL Server engine and Agent from my local machine using SQL Server Management Studio. Since the move though, the options to Start/Stop/ Pause/etc SQL Server and the Agent are grayed out and the status of the server just appears as a white circle instead of the normal green circle. At this point, I am assuming their is some port that we failed to open since it was working fine until w...

Append Query
Ok now Im confused, I changed the starting No of my autonumber the other week, worked fine. I have now added a few more bits to my db and want to do it again to clear it all ready for use and its not doing it, maybe me?? I am trying to make it start on 4183, so far all I have amaged for it to do is create a record with 4182 but then the next autonumber goes to 4203 which is what I was up to when testing, before the next job would just be 4184. Ok so this is what im doing, maybe someone can see If i missed anything out. I deleted all the records in my table I created a table with one number f...

Automatically sending a daily report in word as an attachment
I hope you can help with this. I have a daily report that needs to be mailed to a co-worker. It is a simple list of clients served in a word 2003 document. I would like to automate the process. I right clicked on a toolbar and went to customize, I found the command send mail recipient as attachment and dragged the button to the standard toolbar. I assigned an email hyperlink to it. The button works perfectly as far as sending an email automatically but it doesn't send the contents of the document on the screen. I want the current document to be attached to the email as it is...

Should I create a custom report?
I would like to be able to run a report of best selling items per for select vendors. We are trying to reduce our inventory and carry only the best sellers. ...