none
compare values in the same column group RRS feed

  • Question

  • I have ssrs report i want in vs 2010 design to compare value in the same column group and put result in a separate column, this column group group by year so i do this expression but does not work.


    =SWITCH(
    Fields!Rank.Value=NOTHING,"-",
    Fields!Rank.Value & Fields!YearofDate.Value =Year(Today()) > Fields!Rank.Value & Fields!YearofDate.Value =Year(Today())-1,"↑",
    Fields!Rank.Value & Fields!YearofDate.Value =Year(Today()) < Fields!Rank.Value & Fields!YearofDate.Value =Year(Today())-1,"↓"
    )

    Monday, June 5, 2017 10:22 AM

All replies

  • Hi Mostafa Hegazy,

    Could you please share more details about your report design using some screenshots, and the dataset you are using? 

    I am not clear about your dataset, in my testing environment, I am using the one below:

    Applying the expression you provided, it gives me "#Error" in the result. So may I know how would you like to compare the fields in the expression? (the exact condition for "↑" and "↓" ) 

    If your requirement is to display "-" when Rank is NOTHING, and "↑" when Fields!YearofDate.Value is larger than Year(Today())-1, and "↓" when Fields!YearofDate.Value is smaller than Year(Today())-1, we can use the expression below:

    =Switch(Fields!Rank.Value="NOTHING","-",

    Fields!Rank.Value<>"NOTHING" and Fields!YearofDate.Value>Year(Today()),"↑",

    Fields!Rank.Value<>"NOTHING" and Fields!YearofDate.Value<Year(Today()),"↓")

    Please correct me if I misunderstand your requirement. 

    Best Regards,

    Henry 


    Tuesday, June 6, 2017 2:57 AM
  • Hi Henry,

    I am pleased to your response, i have a column group by year start from 2015 to now, i want to compare rank field in 2017 by rank in 2016(year before)  so i used switch statement attached before in <<Expr>> in  Deviation column out side column group. 

    i can not upload image as my email does not verified.

    Tuesday, June 6, 2017 10:02 AM
  • Hi Mostafa Hegazy,

    So is your requirement to compare the rank trend between year 2017 and year 2016? Have you tried the expression in my last reply? If it is not exactly what you want, could you please share more about your report design using screenshots, also sharing your dataset? 

    If you have any questions, please feel free to ask.

    Best Regards,

    Henry 

    Monday, June 12, 2017 9:14 AM
    • Thanks Henry,
    • I am so sorry for late,  i have column group called "YearofDate" in each year i have qty called "netsales" so i want to compare this net sales in year of today by net sales of previous year , so i made column outside column group called "Deviation" and put this expression in it to do what i need but does not work successfully:
    =SWITCH( Fields!NetSales.Value=NOTHING and Fields!YearofDate.Value =Year(Today()),"-", 
    
    Fields!NetSales.Value and Fields!YearofDate.Value =Year(Today()) > Fields!NetSales.Value and Fields!YearofDate.Value =Year(Today())-1,"↑",
    
     Fields!NetSales.Value and Fields!YearofDate.Value =Year(Today()) < Fields!NetSales.Value and Fields!YearofDate.Value =Year(Today())-1,"↓" )


    -- i can not upload image as site not allowed for me



    Sunday, June 18, 2017 1:39 PM