xerocool

agreed it works on oracle , but both nvl and IFNULL are giving me the same error :( "not a built in function" .So only option i have right now is ISNULL which is not giving me correct output

hey thanks for the help, but i have been trying nvl function in ireport and im getting this error ERROR : SQL Problems : 'nvl' is not a recognized built- in function also as for the Default Parameter Value = null ERROR : nvarchar value to data type bit error Only Oracle supports NVL function right ? Im using MYSQL .. correct me if i am wrong Thanks :) Post Edited by xerocool at 03/17/2010 09:26

oh ! , i have kept the default parameter value for each parameter as " " what should be the default parameter value ? Post Edited by xerocool at 03/17/2010 06:59

yes i am referring to the database records , parameter values 0/1 as in, if between the two parameters (Os_List,Location) if one of them is kept blank,the output is blank , but if both are entered i get correct result i went through few articles on Dynamic Where clause,but none of the syntax worked properly " SELECT Cus_Name, Cus_City, Cus_Country FROM @Customers WHERE (Cus_Name = @Cus_Name OR @Cus_Name IS NULL) AND (Cus_City = @Cus_City OR @Cus_City IS NULL) AND (Cus_Country = @Cus_Country OR @Cus_Country IS NULL) " Following is the entire code Thanks Code:SELECT Computer."Computer_Idn" AS Computer_Computer_Idn, Computer."LoginName" AS Computer_LoginName, BoundAdapter."Computer_Idn" AS BoundAdapter_Computer_Idn, Operating_System."Computer_Idn" AS Operating_System_Computer_Idn, Operating_System."OSType" AS Operating_System_OSType, Processor."Computer_Idn" AS Processor_Computer_Idn, Processor."Type" AS Processor_Type, Processor."MaxSpeed" AS Processor_MaxSpeed, Memory."Computer_Idn" AS Memory_Computer_Idn, Memory."BytesTotal" AS Memory_BytesTotal, BoundAdapter."PrimaryDNS" AS BoundAdapter_PrimaryDNS, BoundAdapter."IPAddress" AS BoundAdapter_IPAddress, Land."Computer_Idn" AS Land_Computer_Idn, Land."ClientConfigurationName" AS Land_ClientConfigurationNameFROM "dbo"."Computer" Computer INNER JOIN "dbo"."Operating_System" Operating_System ON Computer."Computer_Idn" = Operating_System."Computer_Idn" INNER JOIN "dbo"."Land" Land ON Computer."Computer_Idn" = Land."Computer_Idn", "dbo"."BoundAdapter" BoundAdapter, "dbo"."Processor" Processor, "dbo"."Memory" MemoryWHERE Computer.Computer_Idn = BoundAdapter.Computer_Idn and Computer.Computer_Idn = Operating_System.Computer_Idn and Computer.Computer_Idn = Memory.Computer_Idn and Computer.Computer_Idn = Processor.Computer_Idn and Computer.Computer_Idn = Land.Computer_Idn and Operating_System.OSType = ISNULL ($P{Os_List},Operating_System.OSType) and Land.ClientConfigurationName = ISNULL($P{Location},Land.ClientConfigurationName)ORDER BY BoundAdapter."IPAddress" ASC

yes,tried using nvl function , it wont accept it Invalid syntax error

yes its using 'AND' and i have used the Ireport wizard to create the following report , and some parts of SQL wherever needed , Im new to SQL and still in learning process Can you suggest me a solution,for selective choosing for parameters ? Thanks :)

I’ve been having a problem with using COALESCE/ISNULL. Everything seems to work fine, expect when a field has a NULL value. If the record has a NULL value the record is not returned. If the value of both parameter is 1 , it returns valid data , but if any one of them in 0 , its showing me Blank Page Here is the part of code - (where clause) WHERE Computer.Computer_Idn = BoundAdapter.Computer_Idn and Computer.Computer_Idn = Operating_System.Computer_Idn and Computer.Computer_Idn = Memory.Computer_Idn and Computer.Computer_Idn = Processor.Computer_Idn and Computer.Computer_Idn = Landesk.Computer_Idn ------> and Operating_System.OSType = ISNULL($P{Os_List},Operating_System.OSType) and Land.ClientConfigurationName = ISNULL($P{Location}, Land.ClientConfigurationName) <--------- Tried the same with COALESCE Function , but same error,also if i execute only one of the above command its working , but i need both Thanks :) Post Edited by xerocool at 03/15/2010 10:22 Post Edited by xerocool at 03/15/2010 10:24

hey i got the ISNULL function working :) thanks, also there's one more doubt i would like to ask,I need to create a Report with Prompt Box(Parameters) now my Database has PC's from 5 different Locations,So how do i group them according to Location(IP Address) ? (Subreport ?) example - It will ask for a prompt box (Location) - supposing i am differentiating them on basis of IP address, so if i enter IP Range of Location A ,it should just show me data of only those PC's Thanks

Hi, Thanks for the Help But i found out that ISNULL() works similary to NVL function,now the problem is i cant sucessfully implement ISNULL function in WHERE Clause For two parameters..any help ?,it wont retrive data Thanks :)

guys, i have passed two parameters, (example - IP,Operating_System) now when i execute the report,if 1st parameter is left blank,how can i just print the remaining parameters (excluding the one left blank) tried using NVL function in Ireport,it says not a built in function.Any other alternative's ? Thanks

Is it possible to create a Cascading Multi Select menu using the template wizard ? , Im new to this and not so good at SQL coding 2) Also is it possible to edit a default Cascading template provided in JasperServer and link it with my report ? Thanks

when i execute charts (Pie Chart),and when i hit preview it shows me the output,and next time if i log out and come back and try to execute the same chart,the output shows blank ! :@ , any idea whats going on ? this is all random 2) How do i add percentage tag to pie charts ?? 3) How to add hyperlink to each part of Pie chart, corresponding to its equivalent report ? Thanks

Im pretty new to this,but after reading few tutorials got the Database linked to ireports3.7 I have few queries I need to prepare a chart- 1.)When i add specific data during template wizard (for eg,Bios,Computer id,Antivirus Version,Manufacturer etc etc) it is showing me valid data of few random sections(example-Bios,Comp id ), while rest is some weird value(ex-Antivirus version,manufacturer) 2.)When i execute the template,if i use blank template (the output is blank),but the same if used with say Cherry(or some other template) it is displaying the output ..help on this please? 3)Under which section can i find the Internet Protocol (IP address) tab thanks :) cheers