Report Builder Adhoc reports. Compare two columns in the same table
Hi, We are creating a proof of technology project, to give more control to Analysts who are not SQL programmers. So we started looking at Report builder. As a test, I created a report model, with only one table in it. ModelA has tableA with columns colA, colB, colC. I (business users) need to extract data with filter condition colB = colC. I get a lot of options from filter window in report builder. However, I can NOT compare two columns. Am I missing something? We are using report builder 1.0, but can migrate to 2.0 or purchase a even a new tool. Is report builder the right tool? or shall we go back to Access. I am open to other tools as-well. New Tool should be accessible via web, and help analysts to build very simple reports. Report builder matched our needs so-well, I can't get it compare two columns (attributes in its lingo). Regards Bobba
November 14th, 2012 12:43am

Hi, We can use expression for compare two columns value as: =iif(Fields!Column1.Value=Fields!Column2.Value,0,1) I hope it will works. Thanks Avanish
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2012 2:39am

Hi, We can use expression for compare two columns value as: =iif(Fields!Column1.Value=Fields!Column2.Value,0,1) I hope it will works. Thanks Avanish
November 14th, 2012 2:43am

Hi Bobba, How do you specify the filter equation in your report? If you can want to compare two columns, you can try to specify the T-SQL query of the dataset. For example, WHERE table.colB=table.colC. If you want to add a filter in the dataset, then we should compare the field values in the dataset rather than columns of table in database. Just as Avianish posted above, you can try to specify the filter equation as follows: Expression: =IIF(Fields!colB.Value=Fields!colC.Value,1,0). (Note: Select Integer in the drop-down list) Operator: = Value: 0. (Note: if you select Text in the drop-down list, use the expression: =0). For more information, please see: Add a Filter (Report Builder and SSRS) Regards, Fanny LiuFanny Liu TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2012 9:52pm

Hi Bobba, How do you specify the filter equation in your report? If you can want to compare two columns, you can try to specify the T-SQL query of the dataset. For example, WHERE table.colB=table.colC. If you want to add a filter in the dataset, then we should compare the field values in the dataset rather than columns of table in database. Just as Avianish posted above, you can try to specify the filter equation as follows: Expression: =IIF(Fields!colB.Value=Fields!colC.Value,1,0). (Note: Select Integer in the drop-down list) Operator: = Value: 0. (Note: if you select Text in the drop-down list, use the expression: =0). For more information, please see: Add a Filter (Report Builder and SSRS) Regards, Fanny LiuFanny Liu TechNet Community Support
November 14th, 2012 9:52pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics