| Author |
Message |
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
   Topic: Multiple Parameters not working
Posted: 16 Feb 2015 at 11:06am |
|
Hi,
I have a report that i am trying to create. i have
a few parameters for the user to enter.
Having an issue with the catagory parameters part of my select.
when i enter a value for only catagory 1 it pulls correct data, but when i start entering values in catagory 1 and 2 , or 1,2 and 3 and 4 it pulls data incorrectly.
i know i have an issue with the OR part, how would you write so that the user can enter a value in any of the catagorys and i will only see the data that is selected, kinda like the filter on an excel spreadsheet is what i am trying to do for this report.
Here is my selct formula:
{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
{AR_InvoiceHistoryHeader.InvoiceDate} in Date (2013, 01, 01) to Date (2015, 01, 31) and
{CI_Item.ProductType} = "F" and
{CI_Item.Category1} = {?MASTER CATAGORY} OR
{CI_Item.Category2} = {?ITEM CATAGORY} OR
{CI_Item.Category3} = {?TYPE} OR
{CI_Item.Category4} = {?FACTORY}
Thanks in advance
|
|
Peter F
|
 IP Logged |
|
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 17 Feb 2015 at 3:50am |
|
tried using the following, the result when i enter something into the catagory 1 parameter is it still brings back all values.
{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
{AR_InvoiceHistoryHeader.InvoiceDate} in Date (2013, 01, 01) to Date (2015, 01, 31) and
{CI_Item.ProductType} = "F" and
(not(ISNULL({?MASTER CATAGORY})) or {CI_Item.Category1} = {?MASTER CATAGORY}) and
(not(ISNULL({?ITEM CATAGORY})) or {CI_Item.Category2} = {?ITEM CATAGORY}) and
(not(ISNULL({?TYPE})) or {CI_Item.Category3} = {?TYPE}) and
(not(ISNULL({?FACTORY})) or {CI_Item.Category4} = {?FACTORY})
|
|
Peter F
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Feb 2015 at 3:58am |
if you are not allowing nulls in the last 4 options just parenth around the 4 OR options. If you are you allowing the user to not select any of the last 4 options?
if so you need to account for that too. I believe it is the hasvalue() function in crystal 2008 and after.
It will look something like this
{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
{AR_InvoiceHistoryHeader.InvoiceDate} in Date (2013, 01, 01) to Date (2015, 01, 31) and
{CI_Item.ProductType} = "F" and
(
(NOT (hasvalue({?MASTER CATAGORY})) or {CI_Item.Category1} = {?MASTER CATAGORY})
OR
(NOT (hasvalue({?ITEM CATAGORY})) or {CI_Item.Category2} = {?ITEM CATAGORY})
OR
(NOT (hasvalue({?TYPE})) or {CI_Item.Category3} = {?TYPE})
OR
(NOT (hasvalue({?FACTORY})) or {CI_Item.Category4} = {?FACTORY})
)
|
|
IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 17 Feb 2015 at 4:35am |
|
Dblank,
tried using the hasvalue, but when i do a syntax check, it highlights the first hasvalue and tells me the ) is missing. the hasvalue is also not highlighted like the other key words like OR , ISNULL and such, not sure that XI likes that keyword.
|
|
Peter F
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Feb 2015 at 4:48am |
I assumed you were using a version more recent than XI.
How are you allowing NULLs (or no selection) in the 4 different parameters?
|
|
IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 17 Feb 2015 at 5:01am |
|
just having the user leave them blank if not using,
was going to try the following code, do you see any issues with something like this?
{AR_InvoiceHistoryDetail.ItemCode} in {?FROM ITEM} to {?TO ITEM} and
{AR_InvoiceHistoryHeader.InvoiceDate} in Date (2013, 01, 01) to Date (2015, 01, 31) and
{CI_Item.ProductType} = "F" and
( IF {?MASTER CATAGORY} = "" THEN True ELSE {CI_Item.Category1} = {?MASTER CATAGORY}) and
( IF {?ITEM CATAGORY} = "" THEN True ELSE {CI_Item.Category2} = {?ITEM CATAGORY}) and
( IF {?TYPE} = "" THEN True ELSE {CI_Item.Category3} = {?TYPE}) and
( IF {?FACTORY} = "" THEN True ELSE {CI_Item.Category4} = {?FACTORY})
|
|
Peter F
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Feb 2015 at 5:25am |
I was not aware that XI allowed you to leave a param blank.
Are you using some sort of custom deployment process?
Try a few test formula to see what a blank param is coming in as
test1
ISNULL({?MASTER CATAGORY})
test2
{?MASTER CATAGORY}=""
place these on you report header and leace that param blank when you run it.
what do each formula return?
|
|
IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 17 Feb 2015 at 5:54am |
|
I Wasnt sure where to put it, so i opened up section expert, selected report header, and then clicked the x-2 next to suppress and put the isnull statement in there.
ran it with both tests leaving catagory 1 blank and same results for both, i did get the correct results.
did i do this test right?
|
|
Peter F
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 17 Feb 2015 at 6:05am |
sorry no. remve the suppression formula.
create two formula fields using the field explorer.
drop those new formula fields onto the report header.
they should return either True or False values when you run the report
|
|
IP Logged |
|
Stircrazy08
Newbie
Joined: 12 Nov 2014
Location: United States
Online Status: Offline
Posts: 34
|
Posted: 17 Feb 2015 at 6:12am |
|
TEST 1 comes back as False
TEST 2 comes back as True
|
|
Peter F
|
|
IP Logged |
|
|
|
