Bitwise operations in Cognos

[JCarter]

Good afternoon all
I'm having a tough time trying to figure out how to solve this issue.

We have a working report in Cognos 11.0.13, against an Oracle datasource. It uses Oracle's BITAND function to compare record membership to a group that the user specifies on a prompt page.
Example:
Row1:
   ID: 1
   Name: ABC
   Group Membership: 15 (binary 00001111, belongs to groups 1, 2, 4, and 8 )
   Attribute 1: X
   Attribute 2: Y
   Attribute 3: Z
Row2:
   ID: 2
   Name: DEF
   Group Membership: 22 (binary 00010110, belongs to groups 2, 4, and 16)
   Attribute 1: X
   Attribute 2: Y
   Attribute 3: Z
Row3..65000ish:

User specifies on the prompt page, he wants to see rows belonging to a particular Group #, so the report shows and aggregates totals only for those rows that belong to that group.

This all works great (although slow due to table layout and aggregation issues).
The problem is that we're migrating the datasource off of Oracle to a SQL Server backend (I don't have a choice).
While we're doing this, I decided to create datasets to speed up the reports (currently ~5 minutes to run). Data source now looks like: SQL Server > Framework Package > Multiple Datasets > Data Module > Report. I'm new with data modules and datasets, but I think I have them set up correctly.

My question is, how can I perform a bitwise AND with Cognos or SQL Server?
SQL Server uses '&' as it's bitwise AND operator, but Cognos sees the character as invalid syntax. Is there a way to escape the '&', or use a macro maybe to push the bitwise operation as a pass-through SQL?

Thanks for your input,
Jeremy

[aetcognos]

Have you tried using the function within {}

[JCarter]

I haven't. Let me see if I can get that to work.

Thanks aetcognos

[JCarter]

Doesn't look like that's going to work.

I just get: XQE-GEN-0005 Found an internal error:
Index: 1, Size: 1

Since the report is based off a module pulling from datasets, I don't think it's sending the query back to the SQL Server.

I might be able to add the fm package as another source in the module and pull in just the field I want to do the bitwise AND on, which should run very quickly. Will give that a shot, but also open to other suggestions, even a different way to determine group membership based on a single field. . . . Now that I said that, I guess I could do pretty much the same thing I'm doing with bits, but using digits in a decimal number using a combination of floor() and modulus. Just thinking about converting it hurts my head though . . .

[JCarter]

Looks like I got this to work using a macro prompt in Native SQL with a where clause within the model. Not thrilled about static SQL, but it seems to work as a test.

Sample query:
Select
   *
From
   dbo.GroupTable GroupTable
Where
   Group_BID & #prompt('Group','integer',1,'', 'GroupTable.Group_BID')# > 0

Then in the report, on a prompt page, created a list prompt that populates the 'Group' parameter and pulls the use/display values from a table.