none
SSRS Expression & a WHERE clause

    Question

  • Good morning,

    I am in the process of migrating some Business Objects reports over to SSRS. I currently am using a where clause as one of the calculations in the report, however I am haveing problems changing the formula to the expression language in SSRS.

    The calcualtion that I am currently using is:-

    =Amount Where (People Soft Nominal = 1040102011 And Business Unit = 11810)

    Is there a way that I can perform the same calulation as an expression in SSRS?

     

    Thanks in advance.

     

    Steve

    Wednesday, May 19, 2010 8:43 AM

All replies

  • Suppose you have a datset called dataset1, which has the 2 fields and amount returned, you could try the below expression

    sum(iif(Fields!People_Soft_Nom.Value=1040102011 And Fields!Business_Unit.Value = 11810,Fields!Amount.Value,0),"Dataset1")


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    • Proposed as answer by Randall_M Tuesday, March 05, 2013 12:39 AM
    Wednesday, May 19, 2010 8:47 AM
  • Hi Jason,

    Apologies, I do not think I made myself completly clear. My dataset will return multiple rows with different Business Unit numbers and different People Soft Nominal codes, so when I use the above expression for this example I return three rows tow with a 0 and one with the correct answer. Is there a way just to return the rpw that I am intersted in?.

    Thanks again for you help.

    Regards,

    Steve

    Wednesday, May 19, 2010 9:00 AM
  • You can either filter it in your datset itself by adding a where clause

    or

    you can add a filter condition in your table or matrix 

     

    Would that do?


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Wednesday, May 19, 2010 9:08 AM
  • unfortantly not as there will be multiple calculated cells within the same report, all using different business units and nomials. The idea was to create a new expression in each cell.

    Thanks for your help.

    Wednesday, May 19, 2010 9:17 AM
  • Could you tell me your entire requirement clearly so that I can aid you in making a decision?
    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Wednesday, May 19, 2010 9:21 AM
  • Apologies for not being clear.

    Ok what I am trying to do is create a report showing 18 different nominal codes that will be hard coded into the report, for each of these nominal codes I want to be able to create an expression that will return data just for that specific nomial amount into it's own cell. I do not want to create a dataset for each nomial as this would mean that I would end up with far too many queries that would be tricky to maintain going forward.

    Hopefully this makes sense?

    Wednesday, May 19, 2010 9:29 AM
  • Why are you hardcoding the nominal codes when you can get it from the dataset?

     

    Anyways, could you check whether the following expression would work?

    =iif(Fields!nominal_value.value='People Soft',Fields!Amount.Value,0)

    You will have to repeat it in each cell with the condition you want


    Cheers,
    Jason
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Wednesday, May 19, 2010 10:24 AM