Skip to Content
Former Member
Jan 27, 2010 at 08:21 PM

Formula, newbie - Help, please!



Help, please!

I'm a new boy to Crystal and this is my first post.

I have a report which uses just a particular line of data from a field called, 'costcentre_groups.group_code' as a hidden parameter to show all issues of sweets under it and the report will show the associated distributor names. These distributors have this same particular line of data from 'costcentre_groups.group_code', ('DERM' in my report) associated with them but they are also attached to other lines of data from the same field. As well as this field and its particular line of data ('DERM') being used as a parameter for the report I also need all the associated lines of data from that field with the distributor, including 'DERM' to be displayed. I drag and drop the field, 'costcentre_groups.group_code' on to the report but because the report has already been set to show only, 'DERM' issues, it doesn't give any other data, even though they exist for that distributor. Rather than just drag and drop the field, 'costcentre_groups.group_code', do I need to create a new formula and if so, could someone give me an indication how it should read? This 'costcentre_groups.group_code' field, needs to relate to the distributor field which is called, 'xdailyissues.costcentre'. Sorry, this is a bit tricky to explain without showing the Crystal.

{xdailyissues.issue_typeid} in [1.00, 2.00, 3.00, 4.00, 5.00, 6.00, 7.00, 8.00] and

{xdailyissues.lnkfy} = Year(CurrentDate-27)-iif(Month(CurrentDate-27)<4,1980,1979) and

{@Value Nett Returns} > 0.00 and

{costcentre_groups.group_code} startswith "DERM" and

{sweets_speccon.speccon_description} = "HIGH COST" and

not ({xdailyissues.sweet_name} startswith ["SMARTIES", "MALTESERS", "MINSTRELS", "MUNCHIES"]) and

{xdailyissues.lnkmon} = ToText(iif(Month(CurrentDate-27)<4,Month(CurrentDate-27)+12,Month(CurrentDate-27)),"00")

Also...and hopefully, this is a bit more straight forward, I want a report to do the absolute opposite of the 5th & 6th line in the one above. I changed the u2018=u2019 on the 5th line to a u2018<>u2019 and removed the u2018notu2019 from the beginning of the 6th line but all this reported on was the sweets in the 6th line. Can you tell me how I might get those lines to report on all sweets, other than the HIGH COST ones and also include the four named sweets, as well?

Any help, much appreciated!!