Author |
Topic |
rcr69er
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-12-02 : 11:49:25
|
Hi GuysI’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.InventoryIDLEFT 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.InventoryIDJOIN 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.inventoryIdLEFT JOIN dbo.InventorySubGroupItem AS isgi WITH (NOLOCK) ON i2.InventoryId = isgi.InventoryIdLEFT JOIN dbo.InventorySubGroup AS isg WITH (NOLOCK) ON isgi.InventorySubGroupId = isg.InventorySubGroupIdLEFT JOIN dbo.InventoryGroup AS ig WITH (NOLOCK) ON isg.InventoryGroupId = ig.InventoryGroupIdGROUP BY i.statusid ,i2.InventoryId ,i.InventoryID ,isgi.InventorySubGroupId ,ig.InventoryGroupId ,ig.NameORDER 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
52326 Posts |
Posted - 2008-12-02 : 11:53:26
|
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. |
|
|
rcr69er
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-12-02 : 12:08:54
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-02 : 12:11:22
|
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. |
|
|
rcr69er
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-12-02 : 12:25:54
|
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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-02 : 12:34:58
|
cast it to bit as belowSELECT CASE When i.statusid = 90000 and sum(ist.InStock) > 0 Then 1 When (sum(ist.InStock) > 0) then 2 -- Purple booking diary date stockWhen (sum(ist.InStock) IS NULL and min(pl.ExpDelDate) IS NOT NULL) then 3 -- blue booking diary date but no stockWhen (sum(ist.InStock) IS NULL or sum(ist.InStock) = 0) then 4ELSE 5 -- greenEnd 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.InventoryIDLEFT 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.InventoryIDJOIN 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.inventoryIdLEFT JOIN dbo.InventorySubGroupItem AS isgi WITH (NOLOCK)ON i2.InventoryId = isgi.InventoryIdLEFT JOIN dbo.InventorySubGroup AS isg WITH (NOLOCK)ON isgi.InventorySubGroupId = isg.InventorySubGroupIdLEFT JOIN dbo.InventoryGroup AS ig WITH (NOLOCK)ON isg.InventoryGroupId = ig.InventoryGroupIdGROUP BY i.statusid,i2.InventoryId,i.InventoryID,isgi.InventorySubGroupId,ig.InventoryGroupId,ig.NameORDER BY OrderBy,i2.InventoryId ASC |
|
|
rcr69er
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-12-03 : 04:24:37
|
HiThanks 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-03 : 06:45:33
|
then make parameter int with values as 0,1,20-false,1-true and 2 -alland make filter as=Fields!MixAndMatchItem.value in left sideand=IIF(Parameters!yourparam.value<>2,Parameters!yourparam.value,Fields!MixAndMatchItem.value) in right sidealso you dont require cast in above query in this case |
|
|
rcr69er
Constraint Violating Yak Guru
327 Posts |
Posted - 2008-12-03 : 09:15:36
|
Thanks visakh!!!You saved my yet again :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-03 : 09:23:34
|
welcome |
|
|
ravirobin
Starting Member
1 Post |
Posted - 2012-11-29 : 11:24:09
|
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. |
|
|
|