How to use report filter to exclude certain data?

Mohammad Rehman shared this question 7 months ago
Answered

Hello Experts,

I am using BMC version of reporting 7.3 and having difficulty to exclude certain criteria data, I tried NOT IN LIST as well Different FROM operators but no luck.


I need your help to build the filter on operational category tier 1, 2 and 3 to exclude the count.

I want the count all the tickets between two resolved dates and exclude the following tiers.

Categorization Tier 1*Categorization Tier 2Categorization Tier 3Employee CheckoutReminderFacility SupportFloor CheckMiscellaneousOtherNotificationsNISNSchedulingNotificationsSGIFeedbackPostmasterSpamBanned FilePostmasterSpameMailPostmasterUndeliveredDelayedPostmasterUndeliveredErrorPostmasterUndeliveredReturnedI don't see NOT operator in filter section, I can not use the Tier 1 Not in List kind of operator because the results will be different.

Is there a way to use the qualification we use on Remedy form advance search or Database search?

i.e.

NOT (('Operational Categorization Tier 1+' ="Postmaster" AND 'Operational Categorization Tier 2'="Undelivered" AND 'Operational Categorization Tier 3'="Delayed") OR ('Operational Categorization Tier 1+' ="Postmaster" AND 'Operational Categorization Tier 2'="Undelivered" AND 'Operational Categorization Tier 3'="Returned") OR ('Operational Categorization Tier 1+' ="Postmaster" AND 'Operational Categorization Tier 2'="Undelivered" AND 'Operational Categorization Tier 3'="Error") OR ('Operational Categorization Tier 1+' ="Postmaster" AND 'Operational Categorization Tier 2'="Spam" AND 'Operational Categorization Tier 3'="eMail") OR ('Operational Categorization Tier 1+' ="Postmaster" AND 'Operational Categorization Tier 2'="Spam" AND 'Operational Categorization Tier 3'="Banned File") OR ('Operational Categorization Tier 1+' ="Notifications" AND 'Operational Categorization Tier 2'="SGI" AND 'Operational Categorization Tier 3'="Feedback") OR ('Operational Categorization Tier 1+' ="Notifications" AND 'Operational Categorization Tier 2'="NISN" AND 'Operational Categorization Tier 3'="Scheduling") OR ('Operational Categorization Tier 1+' ="Facility Support" AND 'Operational Categorization Tier 2'="Floor Check" ) OR ('Operational Categorization Tier 1+' ="Employee Checkout" AND 'Operational Categorization Tier 2'="Reminder" ) OR ('Operational Categorization Tier 1+' ="Miscellaneous" AND 'Operational Categorization Tier 2'="Other" ) )


Thanks

Mohammad

Comments (1)

photo
2

Hi Mohammad,

Thanks for reaching out. Support is available primarily to provide documentation, investigate and log potentially defective behavior, and answer other product related questions. This however all appears to be quite data and report setup specific, requiring knowledge of your setup and the data itself. I'm afraid report setup and creation such as this falls more in the realm of consulting services, and based on all the information I have here, this doesn't seem to be defective behavior.

I also cannot speak to the "Is there a way to use the qualification we use on Remedy form advance search or Database search?" question since I do not know what that is, as we do not support Remedy or the SmartReporting integration. Your question may be better suited for BMC Support.

Regards,

Mike

photo
1

Thanks Mike, for your time and help. My main objective was to get the answer about filters, is there a way to achieve the report filter as i posted the SQL.

You may close this topic.

Regards,

Mohammad

photo
2

Hi Mohammad,

From a high level overview, you can use NOT statements in Calculated Fields. However, the query it looks like you're trying to accomplish may be too complex to do in a Simple Calculated Field, which would be problematic because BMC disallows its users from using Freehand SQL statements in their users reports. Hopefully this helps!


Regards,

Mike

photo
1

Yes Mike, you got that right No Free SQL option otherwise i would not have landed here. Apart from that we can't add custom db functions etc. I have no more question, felt like BMC version of reporting failed me today to fulfill management simple report ( I have already created Database view and going Birt Report option).


Thanks for your time and help.

Regards,

Mohammad

photo
2

Hi Mohammad,

Thanks for your response. You're very welcome. Sorry there's not better news on this!


I'll go ahead and close this case out this considered.

Regards,

Mike

photo