Define variable
Hi , I had created SSRS like as below. I need use the results to set formula for further use. It requires to show on same report. For example , I want to set formula = B/A * 100 Anyone would provide the solutions in details? Thanks, Bobo Choi
November 9th, 2011 5:31am

Hi, The easiest way to do this is to use the report items function. Bascially in reporting services you can refer to cell (a bit like excel) and refer to the value of cell b divided by the value cell a and then multiply by 100. To do this first click on the textbox b and look at the properties for this textbox and find the name for this item, it will be under the general heading. Then find the name for textbox b. Select the textbox you would like to create this formula in, right clikc on this textbox select expression and then type the following: =(reportitems!TEXTBOXNAMEB.value/reportitems!TEXTBOXNAMEA.value) * 100
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2011 5:58am

Hi, The easiest way to do this is to use the report items function. Bascially in reporting services you can refer to cell (a bit like excel) and refer to the value of cell b divided by the value cell a and then multiply by 100. To do this first click on the textbox b and look at the properties for this textbox and find the name for this item, it will be under the general heading. Then find the name for textbox b. Select the textbox you would like to create this formula in, right clikc on this textbox select expression and then type the following: =(reportitems!TEXTBOXNAMEB.value/reportitems!TEXTBOXNAMEA.value) * 100
November 9th, 2011 1:41pm

Hi, Thank you for your prompt answer. According to the solutions provided, I faced antoher expression error. "Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope." Anyone would help?
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2011 10:38pm

Hi BoboChoi, Thanks for the question. Due to the scope limitation, for this requirement, I had created one custom code for you, please test it in your report. Replace current textbox24’s expression with: =originalexpression +Code.GetFormula(originalexpression,0) Replace current textbox28’s expression with: =originalexpression +Code.GetFormula(0,originalexpression) Type in the textbox that you will display the formula with the expression: =Code.formula The code segment: Public Rgroup3 as Integer Public Rcdb as Integer Public formula as Integer Public function GetFormula (Byval Ogroup3 as Integer,Byval Ocdb as Integer) IF Ogroup3 =0 Then Rgroup3 = Rgroup3 else Rgroup3 = Ogroup3 end IF IF Ocdb =0 Then Rcdb = Rcdb else Rcdb = Ocdb end IF IF Rgroup3 > 0 AND Rcdb > 0 Then formula = Rgroup3/Rcdb*100 else formula=0 end IF return 0 End function Thanks, Sharp Wang Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
November 10th, 2011 11:35pm

Hi Sharp Thanks you for your answer. What parameter would be pass for the textbox expression formula? I try set expression := Code.formula(textbox24,textbox28). It prompts "textbox24" is not declared. When I haven't pass the parameter to the textbox expression formula. It prompts "Argument not specified for parameter 'Ocdb' of 'Public Function GetFormula(Ogroup3 As Integer, Ocdb As Integer) As Object'". Best Regards Bobo
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2011 1:55am

Right-click the textbox24, select Expression, type in it with: = Iif(Fields!group3.Value=”SALES”, sum(Fields!CMA.Value),Sum(Fields!CMA.Value)*-1) +Code.GetFormula(Iif(Fields!group3.Value=”SALES”, sum(Fields!CMA.Value),Sum(Fields!CMA.Value)*-1) ,0) Note that the expression is the original expression that you displayed in the posted screenshot. Textbox28 has the same settings too. Thanks, Sharp Wang Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
November 14th, 2011 2:28am

Sorry Sharp. I may be disunderstand. "Type in the textbox that you will display the formula with the expression: =Code.formula". Where to set? Do I need to create one textbox to show the forumla? So, I created textbox55 and set the expression:=Code.formula. For this new textbox, what paramater I will pass? The code segment is writen at Report Properties-> Code. Am I correct? Yes. I revised the expression on textbox24 & textbox28. Best Regards Bobo
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2011 2:54am

Sorry Sharp. I may be disunderstand. "Type in the textbox that you will display the formula with the expression: =Code.formula". Where to set? Do I need to create one textbox to show the forumla? So, I created textbox55 and set the expression:=Code.formula. For this new textbox, what paramater I will pass? The code segment is writen at Report Properties-> Code. Am I correct? Yes. I revised the expression on textbox24 & textbox28. Best Regards Bobo The syntax of Textbox55 is correct. Do you obtain the expect result? Thanks, Sharp WangPlease remember to mark the replies as answers if they help you and unmark them if they provide no help.
November 14th, 2011 2:58am

Is I only input "=Code.formula" in expression? If yes, it will prompt error for reqest to provide paramater. So, I didn't obtain the expect result. Another question, while I follow your soultion. If I need to set formula for column "Year to Date Annual", "Annual Budget" and "YTD Annual", I need to add more 3 function respectively? Best Regards Bobo
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2011 3:15am

@Bobo, thanks for the reply. Could you please post the currene expressions of Textbox24, 28, 55 here? Thanks, Sharp Wang Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
November 14th, 2011 3:19am

Hi Sharp Please note I rename to textbox24, 28, 55 to CMA_2, NP_CMA , R_MA CMA_2 =IIF(Fields!group3.Value= "SALES", Sum(Fields!CMA.Value), Sum(Fields!CMA.Value)* -1) + Code.GetFormula(IIF(Fields!group3.Value="SALES", Sum(Fields!CMA.Value), Sum(Fields!CMA.Value)* -1),0) NP_CMA =Sum(cdbl(IIF(Fields!group1.Value<> "Fund Employed", Fields!CMA.Value,0))) + Code.GetFormula(0,Sum(cdbl(IIF(Fields!group1.Value<> "Fund Employed", Fields!CMA.Value,0)))) R_MA =Code.GetFormula
Free Windows Admin Tool Kit Click here and download it now
November 14th, 2011 3:26am

Hi Sharp Please note I rename to textbox24, 28, 55 to CMA_2, NP_CMA , R_MA CMA_2 =IIF(Fields!group3.Value= "SALES", Sum(Fields!CMA.Value), Sum(Fields!CMA.Value)* -1) + Code.GetFormula(IIF(Fields!group3.Value="SALES", Sum(Fields!CMA.Value), Sum(Fields!CMA.Value)* -1),0) NP_CMA =Sum(cdbl(IIF(Fields!group1.Value<> "Fund Employed", Fields!CMA.Value,0))) + Code.GetFormula(0,Sum(cdbl(IIF(Fields!group1.Value<> "Fund Employed", Fields!CMA.Value,0)))) R_MA =Code.GetFormula R_MA =Code.formulaPlease remember to mark the replies as answers if they help you and unmark them if they provide no help.
November 14th, 2011 4:06am

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

Other recent topics Other recent topics