Help with formula/determining proper values needed
Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=487
Printed Date: 28 Apr 2024 at 10:13pm
Topic: Help with formula/determining proper values needed
Posted By: gphreak
Subject: Help with formula/determining proper values needed
Date Posted: 09 Apr 2007 at 9:36pm
|
Hello, everyone! I need some help determining proper values for one field on a report. I have an inventory of products. Products are packaged and each package has an expiration date. For my report, I need to include information on expired packages, so I need to select package ID where expiration date is less than today and product count for package is greater than 0, meaning that there's still some products left in that package. How do I get that expired package's ID #? I tried using the formula field and when I opened up formula editor, I put in something like {package.id} = {package.id} where {package.product_count} > 0 and {package.exp_date} < DateTimeValue (now)but this apparently did not work. What would be the correct syntax/approach for determining the ID of an expired package? Thanks! |
Replies:
Posted By: BrianBischof
Date Posted: 09 Apr 2007 at 9:50pm
|
Is this the EXACT formula you used? If so, drop the WHERE clause and substitiute it with "AND". You shouldn't put WHERE in your selection formula because Crystal Reports does that for you automatically when it queries the database.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: gphreak
Date Posted: 09 Apr 2007 at 10:10pm
|
Hm, I guess I was closer than I imagined then. I must have messed something else up then because I get no results when I should clearly have at least 3, looking at raw table data. OK, I have a formula field called oldPack and it is based on the previous formula with AND instead of WHERE. I go to bind it and in select expert I choose package.id and set it equal to formula: {@oldPack} I still have no records returned. I think the problem is that the above formula seems to return boolean, not an actual number for package ID, but I am not sure how to go around it. What am I doing wrong here? |
Posted By: BrianBischof
Date Posted: 09 Apr 2007 at 10:37pm
|
To get a formula working, just do one field at a time to make sure you get back the data you expect for each one. Then put them all together when you are ready. In the second question, your formula does return boolean. However, why do you need to put a package Id in it? Isn't this already taken care of in the original formula? For the selection fomrula just use {@oldPAck}=true. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: gphreak
Date Posted: 09 Apr 2007 at 11:28pm
|
I will try this first thing in the morning. Another kind of question: if I use default parameter values from a table for my parameters, is there a way to make those default values dynamically update themselves? When new values are entered in the table, the drop down list of default parameter values continues to contain only the values selected at the time the report was created. Is there a way to make those updates using the CR designer and not manually programming them all in? A few design issues: a report user needs to specify start/end date and 1 or 2 field values at runtime. If there is no way to make those updates to default parameter values dynamically, then I guess I will need to program it all. Can those user input customizations at runtime be achieved just through selection formulas or do I need to use ParameterFields instead? Which is better and why? In this case then, is ReportDocument model better/more suitable than CrystalReportsViewer model? Thanks again, and I will continue posting questions as they arise. I apologize for not splitting this up into separate threads but I did not want to open up 3-4 threads all practically at once. |
Posted By: BrianBischof
Date Posted: 10 Apr 2007 at 9:06am
|
To have parameters that update themselves requires using DCPs. This is a new feature in CR XI and you would have to buy that program to get this feature. The other option is to build a parameter screen using your .NET 2005 app and then pass the user selection to the report. You can do this using parameters or formulas (doesn't matter). Either way, you should use the ReportDocument model because it is more robust. You can use the viewer model but might run into limitations down the road as your program grows. Just stick with ReportDocument and you'll be happy. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: gphreak
Date Posted: 10 Apr 2007 at 10:15am
|
Thanks for the info. I looked at the following this tutorial http://msdn2.microsoft.com/en-us/library/ms227697(VS.80).aspx and it seems like even though I would have controls to input parameters, it would still have the same issue of not updating itself. Am I mistaken here? The only other way around it that I can think of would be to not specify default values at design time, but do it all from the code, populating the listbox of values through a query and then passing the selected values to the report. Would this work? It may sound like it would but I am not sure. |
Posted By: BrianBischof
Date Posted: 10 Apr 2007 at 11:15am
|
What you are referring to is what I was trying to say. You have to build a .NET input form which is based off of a live SQL query. It is all done through your code and not during report design time. Once the user selects their parameter values on your form then you open the report and pass the user selections to the report.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: gphreak
Date Posted: 10 Apr 2007 at 11:59am
|
Right, right... that's the way it's gonna be then. In that case, I still need to define the parameter field at design time, but I would not be setting any values for it, right? This way then I can have multiple parameters being specified and passed at runtime. So if I want the user to specify start date and end date as well as choose from default values for some other parameter, it should be no problem? Or is there a limitation of some sort as to how many parameters can be specified and passed at runtime? |
Posted By: BrianBischof
Date Posted: 10 Apr 2007 at 12:34pm
|
You got it now. Personally, I use formulas b/c in .NET 2003 they were MUCH easier to code for. But they cleaned that up in 2005 and now parameters are easy to work with as well. No restrictions that I've seen, but its not like I've tried pushing them to the limit either. Please update us if you find anything unusual.
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: gphreak
Date Posted: 10 Apr 2007 at 12:51pm
|
I will. Is there an example anywhere on how to load those values from SQL queries? I start off with a parameter that has no default values. SQL populates a listbox of default values. I need to load those values from the listbox as parameter default values. I then select multiple values from the listbox and reload the report, which passes the selected values to the report and that's it. It all sounds quite straightforward, but without an example it may not be so simple. How would one do it using formulas? The reasons I ask are these: 1. Would a selection formula not require the loading of all data and then filtering the data? Is that not too much on the server? 2. If one needs to select all or just specific items from the table as criteria, how does one express all items or an arbitrary list of items to be passed into the selection formula? Is there an example of doing something similar using just formulas? Thanks! |
Posted By: BrianBischof
Date Posted: 10 Apr 2007 at 1:33pm
|
I don't know of any online samples for all this. I have some code in my book but not enough to walk you through all the steps you. That MSDN link you posted looked pretty good. That should get you through most of the logic. CR has many rules for when to filter the data itself or when to pass it through to the database. First you need to get the project working and then you can look at performance issues. I would say that since you have lots of parameters then it might try to filter locally. But again it just depends (I have various rules of thumb listed in my book, but all you have to do is just look at the SQL Query to see what it is doing). ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: gphreak
Date Posted: 10 Apr 2007 at 2:59pm
|
OK, I am getting somewhere with formulas... How can I print a value passed into a formula on the report? Formula contains a date that the user inputs, so how would I display on the report something like "Inventory date: " <specified date>? |
Posted By: BrianBischof
Date Posted: 10 Apr 2007 at 3:12pm
|
Just drag and drop it onto the report where you want it to appear. But to be honest, if you aren't familiar with that then you need to go to my free online chapters and read about report objects and the basics of how they work. You are going to have LOTS of questions otherwise. (I'm happy to help out, but when I have a whole book online for free then don't pass up the good info). ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: gphreak
Date Posted: 10 Apr 2007 at 3:32pm
Oh, I know there's whole bunch to learn. And I did buy your book, BTW  it's just that I am in a serious lack of time. However, the date from selection formula comes from code, not the designer. I know that within the designer I can drag and drop stuff but this comes from the code.
|
Posted By: BrianBischof
Date Posted: 10 Apr 2007 at 3:41pm
|
You have to add a formula to your report with a dummy date value and drop that onto the report. Then from your application override the dummy date value with the actual date value. See page 317-318 for more info. Oh yeah - pass it as a CDate() function (I need to add a good example to the next edition).
------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: gphreak
Date Posted: 10 Apr 2007 at 5:19pm
|
Well, I managed to make some progress even with parameters and live SQL queries. Just to throw it out there, I ran into a problem when the number of default values I tried to assign was above 1000, I got an error saying that SQL Query was too complex. Clearly, I do not necessarily need 1000+ default values, but since Brian suggested I post any discoveries, well, there's one. I don't know whether it's VS2005 CR or CR in general, or simply my code, but that is what I have found out. |
Posted By: BrianBischof
Date Posted: 10 Apr 2007 at 11:22pm
|
Hmmm. Depends on where the error is happening. When you are posting to SQL or when you are updating the report parameter collection. ------------- Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a> |
Posted By: gphreak
Date Posted: 11 Apr 2007 at 12:44am
| Actually, now that you mentioned it, it happens when updating the parameters. What I have is a button that selects all items in the listbox of values returned from initial query. So, I will need to figure out a user-friendly way to limit those parameter updates and then I should be good. With a lot of help from you and MSDN, the code seems to be working, at least on the sample data. |