Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Help with formula/determining proper values needed Post Reply Post New Topic
Page  of 2 Next >>
Author Message
gphreak
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 17
Quote gphreak Replybullet Topic: Help with formula/determining proper values needed
    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!  

IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
gphreak
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 17
Quote gphreak Replybullet 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? 


Edited by gphreak - 09 Apr 2007 at 10:28pm
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
gphreak
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 17
Quote gphreak Replybullet 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.


Edited by gphreak - 09 Apr 2007 at 11:38pm
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
gphreak
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 17
Quote gphreak Replybullet 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.   
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
gphreak
Newbie
Newbie


Joined: 14 Mar 2007
Online Status: Offline
Posts: 17
Quote gphreak Replybullet 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?     
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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>
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.