crystal reports - sql query with conditional where only works sometimes -


i'm creating report (in crystal reports xi) based on sql stored procedure in database. query accepts few parameters, , returns records within specified date range. if parameters passed in, used determine records return. if 1 or more parameters not passed in, field not used limit types of records returned. it's bit complicated, here's clause:

where  ((date > @start_date) , (date < @end_date))      , (@employeeid null or emp_id = @employeeid)      , (@clientid null or client_id = @clientid)     , (@projectid null or project_id  = @projectid)     , (@group null or group = @group)

now, problem:

the query (and report) works beautifully old data, within range of years 2000-2005. however, clause not filtering data more recent years: returns records parameter @group null (ie: not passed in).

any hints, tips, or leads appreciated!

solved!

it had nothing clause, after all. had let sql server generate inner join me, should have been left join: many records recent years not contain entries in joined table (expenses), weren't showing up. interestingly, few recent records have entries in expenses table have null value group, why got records when @group null.

morals of story: 1. double check automatically generated; , 2. out null values! (n8wl - giving me hint closely @ nulls.)


Comments

Post a Comment

Popular posts from this blog

asp.net - repeatedly call AddImageUrl(url) to assemble pdf document -

i'm using abcpdf , i'm curious if can recursively call addimageurl() function assemble pdf document compile multiple urls? something like: int pagecount = 0; int theid = thedoc.addimageurl("http://stackoverflow.com/search?q=abcpdf+footer+page+x+out+of+", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetochain(theid); } pagecount = thedoc.pagecount; console.writeline("1 document page count:" + pagecount); //flatten document (int = 1; <= pagecount; i++) { thedoc.pagenumber = i; thedoc.flatten(); } //now try again theid = thedoc.addimageurl("http://stackoverflow.com/questions/1980890/pdf-report-generation", true, 0, true); //assemble document while (thedoc.chainable(theid)) { thedoc.page = thedoc.addpage(); theid = thedoc.addimagetoc
Read more

java - Android recognize cell phone with keyboard or not? -

in android app, want detect user's device if device has keyboard(like motorola milestone) , show corresponding user interface. guys know how that? ! regards justicepenny http://developer.android.com/reference/android/content/res/configuration.html public int keyboard the kind of keyboard attached device. 1 of: keyboard_nokeys, keyboard_qwerty, keyboard_12key. that's it!
Read more

iphone - How would you achieve a LED Scrolling effect? -

how achieve led scrolling effect example below? led sign - led ticker emulator iphone , ipad http://img.skitch.com/20101201-rsfh4p1bajb1wp94k466pdpiuj.preview.jpg sometimes remunerates comment in other posts :) have put code you, should implement font '8pin matrix' realistic feeling. uilabel *label; - (void)viewdidload { [super viewdidload]; label = [[uilabel alloc]initwithframe:cgrectmake(10, 50, 300, 20)]; label.font = [uifont fontwithname:@"courier-bold" size:16.0]; label.backgroundcolor = [uicolor blackcolor]; label.textcolor = [uicolor greencolor]; label.linebreakmode = uilinebreakmodecharacterwrap; // otherwise "…" label.text = @"this led text scrolled "; // blanks space between [self.view addsubview:label]; [nstimer scheduledtimerwithtimeinterval:0.12f target:self selector:@selector(scrolllabel) use
Read more