SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Boolean Parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 12/02/2008 :  11:49:25  Show Profile  Reply with Quote
Hi Guys

I’m producing a report using the report services using the following query:

SELECT
CASE
When i.statusid = 90000 and sum(ist.InStock) > 0 Then 1
When (sum(ist.InStock) > 0) then 2 -- Purple booking diary date stock
When (sum(ist.InStock) IS NULL and min(pl.ExpDelDate) IS NOT NULL) then 3 -- blue booking diary date but no stock
When (sum(ist.InStock) IS NULL or sum(ist.InStock) = 0) then 4
ELSE 5 -- green
End AS 'OrderBy'
,i2.InventoryId
,i.InventoryID
,isgi.InventorySubGroupId
,ig.InventoryGroupId
,ig.Name
,CASE
WHEN ig.InventoryGroupId IS NOT NULL THEN 'Y'
ELSE 'N'
END AS 'MixAndMatchItem'

FROM
INVENTORY AS i WITH (nolock)
JOIN INVENTORY AS i2 WITH (nolock)
ON i.ParentID = i2.InventoryID
LEFT OUTER JOIN InventoryStock
AS ist WITH (nolock) ON i.InventoryID = ist.InventoryId
LEFT JOIN (
select inventoryID,statusid,ExpDelDate,sum(quantity) as Quantity,sum(qtyreceived) as qtyreceived
from purchaseitem
where statusid Not in (60000)
group by inventoryID,statusid,ExpDelDate
) AS POI
ON i.InventoryID = poi.InventoryID
JOIN InventorySubCategory AS isc WITH (nolock)
ON i.SubCategoryID = isc.SubCategoryID
JOIN InventoryCategory AS ic WITH (nolock)
ON i.CategoryID = ic.CategoryID
JOIN vwInventoryCategoryGroup AS ICG
ON ICG.categoryId = ic.CategoryID
JOIN SecGroup AS sg WITH (nolock)
ON isc.GroupId = sg.GroupID
LEFT OUTER JOIN vwMagazine As vm WITH (nolock)
ON i2.InventoryID = vm.InventoryId
LEFT JOIN vwLastPurchaseLockid pl
ON i.inventoryid = pl.inventoryId
LEFT JOIN dbo.InventorySubGroupItem AS isgi WITH (NOLOCK)
ON i2.InventoryId = isgi.InventoryId
LEFT JOIN dbo.InventorySubGroup AS isg WITH (NOLOCK)
ON isgi.InventorySubGroupId = isg.InventorySubGroupId
LEFT JOIN dbo.InventoryGroup AS ig WITH (NOLOCK)
ON isg.InventoryGroupId = ig.InventoryGroupId

GROUP BY
i.statusid
,i2.InventoryId
,i.InventoryID
,isgi.InventorySubGroupId
,ig.InventoryGroupId
,ig.Name

ORDER BY
OrderBy
,i2.InventoryId ASC



The MixAndMatch column is a flag determining whether the record is associated with MixAndMatch.

The thing I want to do is add a Boolean type report parameter with the intent of showing only MixAndMatch records if specified. So If the user selects ‘Yes’ only records with a MixAndMatchItem value of Y will be shown.

Is this possible to do? And if so how?

Thanking you in advance!!!

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/02/2008 :  11:53:26  Show Profile  Reply with Quote
yup. just add a new parameter of type boolean from parameters tab. Also for values to become boolean return 1 and 0 instead of Yes/No from query.
Go to Top of Page

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 12/02/2008 :  12:08:54  Show Profile  Reply with Quote
Thanks, but how would i get the report to show only mix and match items when 'True' is selected?

Sorry I'm not the greatest with SSRS!

Thanks

Edited by - rcr69er on 12/02/2008 12:09:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/02/2008 :  12:11:22  Show Profile  Reply with Quote
quote:
Originally posted by rcr69er

Thanks, but how would i get the report to show only mix and match items when 'True' is selected?

Sorry I'm not the greatest with SSRS!

Thanks


Just add the filter condition Fields!MixAndMatchItem.value = Parameter!BooleanParameter.value in the filters tab of dataset properties in datatab of the report.
Go to Top of Page

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 12/02/2008 :  12:25:54  Show Profile  Reply with Quote
Thanks again!

I get the following error message tho:
The processing of FilterExpression for the data set 'DataSet1' cannot be performed. Cannot compare data of types System.Int32 and System.Boolean. Please check the data type returned by the FilterExpression.

Is this a problem with my query? I changed the CASE statement to return 1 for Yes and 0 for No.

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/02/2008 :  12:34:58  Show Profile  Reply with Quote
cast it to bit as below

SELECT 
CASE 
When i.statusid = 90000 and sum(ist.InStock) > 0 Then 1 
When (sum(ist.InStock) > 0) then 2 -- Purple booking diary date stock
When (sum(ist.InStock) IS NULL and min(pl.ExpDelDate) IS NOT NULL) then 3 -- blue booking diary date but no stock
When (sum(ist.InStock) IS NULL or sum(ist.InStock) = 0) then 4
ELSE 5 -- green
End AS 'OrderBy'
,i2.InventoryId
,i.InventoryID
,isgi.InventorySubGroupId
,ig.InventoryGroupId
,ig.Name
,CAST(CASE 
WHEN ig.InventoryGroupId IS NOT NULL THEN 'Y'
ELSE 'N'
END AS bit) AS 'MixAndMatchItem'

FROM 
INVENTORY AS i WITH (nolock) 
JOIN INVENTORY AS i2 WITH (nolock) 
ON i.ParentID = i2.InventoryID
LEFT OUTER JOIN InventoryStock 
AS ist WITH (nolock) ON i.InventoryID = ist.InventoryId 
LEFT JOIN (
select inventoryID,statusid,ExpDelDate,sum(quantity) as Quantity,sum(qtyreceived) as qtyreceived 
from purchaseitem 
where statusid Not in (60000)	
group by inventoryID,statusid,ExpDelDate
) AS POI 
ON i.InventoryID = poi.InventoryID
JOIN InventorySubCategory AS isc WITH (nolock) 
ON i.SubCategoryID = isc.SubCategoryID 
JOIN InventoryCategory AS ic WITH (nolock) 
ON i.CategoryID = ic.CategoryID 
JOIN vwInventoryCategoryGroup AS ICG 
ON ICG.categoryId = ic.CategoryID 
JOIN SecGroup AS sg WITH (nolock) 
ON isc.GroupId = sg.GroupID 
LEFT OUTER JOIN vwMagazine As vm WITH (nolock) 
ON i2.InventoryID = vm.InventoryId 
LEFT JOIN vwLastPurchaseLockid pl 
ON i.inventoryid = pl.inventoryId
LEFT JOIN dbo.InventorySubGroupItem AS isgi WITH (NOLOCK)
ON i2.InventoryId = isgi.InventoryId
LEFT JOIN dbo.InventorySubGroup AS isg WITH (NOLOCK)
ON isgi.InventorySubGroupId = isg.InventorySubGroupId
LEFT JOIN dbo.InventoryGroup AS ig WITH (NOLOCK)
ON isg.InventoryGroupId = ig.InventoryGroupId

GROUP BY 
i.statusid
,i2.InventoryId
,i.InventoryID
,isgi.InventorySubGroupId
,ig.InventoryGroupId
,ig.Name

ORDER BY 
OrderBy
,i2.InventoryId ASC
Go to Top of Page

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 12/03/2008 :  04:24:37  Show Profile  Reply with Quote
Hi

Thanks alot for that!!!

The thing is when I select false it only displays records with a value of 0. Is there a way to display all records?

I hope this makes sense!

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/03/2008 :  06:45:33  Show Profile  Reply with Quote
then make parameter int with values as 0,1,2
0-false,1-true and 2 -all

and make filter as

=Fields!MixAndMatchItem.value
in left side

and
=IIF(Parameters!yourparam.value<>2,Parameters!yourparam.value,Fields!MixAndMatchItem.value)
in right side

also you dont require cast in above query in this case
Go to Top of Page

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 12/03/2008 :  09:15:36  Show Profile  Reply with Quote
Thanks visakh!!!

You saved my yet again :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/03/2008 :  09:23:34  Show Profile  Reply with Quote
welcome
Go to Top of Page

ravirobin
Starting Member

USA
1 Posts

Posted - 11/29/2012 :  11:24:09  Show Profile  Reply with Quote
The solution here didn't work for me. But I found out another way to display all the record rather than either yes or no.

The processing of FilterExpression for the data set 'DataSet1' cannot be performed. Cannot compare data of types System.Int32 and System.Boolean. Please check the data type returned by the FilterExpression.

This means that the data is in boolean and you can only select one. The boolean expression you want to filter on, just cast it as int and you will be fine. For example, Cast (booleancolumn as int). Then add the parameter as usual: On the main screen, select int and check allow multiple values. Then add vales 1 and 0 as available values. Put the filter on the dataset: select the column and make sure the you "In" and then select parameter. Wallah, you will be able to select both of the values.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.44 seconds. Powered By: Snitz Forums 2000