2

Please look at the image below, my dataset has two processes, 'logs processed' and 'stacked at kilns'.

I need to take the total 'stacked at kilns' and divide it by the total 'logs processed' for each length.

so for example for field name 5.4 (dataset field length), I would like to divide 2784/2283 to return a percentage of the recovery.

my expressions currently is

=Sum(IIf(
(Fields!process.Value = "Logs Processed") AND (Fields!Length.Value=Fields!Length.Value)
    , Fields!cubes.Value
    , Nothing)
  , "Wetmill_to_Kiln")

But this returns the value of all lengths where process is 'Logs Processed' not for just length 5.4 as per example.

So each length field is dynamically created (3.3,3.6,3.9 .... 6,6.3,6.6) I would like to get the total for 'stacked at kiln'/'logs processed' for each length field.

any help appreciated as always

example of my desired output in bottom image.

current output:

enter image description here

Desired output:

*enter image description here*

*****UPDATE AS PER TPHE*********

I have created a text box inside the column group. this returns the value for that group but how can I reference the value of that text box.

if I use something like ReportItems!tbxSource.Value how can I reference the value of the textbox when the it is dynamically created across the column group? there are then mulitple instances of that textbox name?

with reference to the picture how do get the value of the white <> from the textbox with green <>

Thanks, enter image description here

Pedram
  • 6,256
  • 10
  • 65
  • 87
Smudger
  • 10,451
  • 29
  • 104
  • 179

2 Answers2

1

Since you are using a column group, you can put your expression into a text box within the group and it will execute on only the data that is captured within each column. So if your code for the Logs processed row is something like Sum(Logs) and your code for the Stacked at Kiln row is something like Sum(Stacked), your expression code for the recovery row would be Sum(Stacked)/Sum(Logs). The key is to make sure that it is within the column group.

TPhe
  • 1,681
  • 2
  • 11
  • 20
  • Thanks TPhe, can you give me more calirty here? I have updated my questions with a screenshot of my design view. currently the text box inside the column group returns the correct value but how can I fetch the value of the textbox for logs and the textbox for stacked when the textbox is dynamically generated so I dont know the name of the textbox to call with `ReportItems!tbxSource.Value` – Smudger Mar 26 '15 at 07:12
0

So what I got to work was to create two variables on the column group. one called kilntotal and one called logtotal. the variables value was equal to the result of this expression:

=sum(iif(Fields!process.Value="logs",cdbl(Fields!cubes.Value),cdbl(`0)))`

and

=sum(iif(Fields!process.Value="kiln",cdbl(Fields!cubes.Value),cdbl(0)))

I then use these variable in my logic in my recovery % row:

=Variables!kilntotal.Value/Variables!logtotal.Value

Thanks for the input and your time.

Smudger
  • 10,451
  • 29
  • 104
  • 179
  • Glad you got it to work. For future reference, I don't think the variables are necessary - you could have just written out the expressions you put into the variables and divided them by eachother. But variables would be useful as shorthand when you would need to use the expressions over and over again in different places. – TPhe Mar 26 '15 at 14:33
  • Thanks TPhe, 100% right on what you are saying. just makes the syntax allot easier int he expression. appreciate your input though, thank you. – Smudger Mar 27 '15 at 07:25
  • The key also was the cdbl() on the iif statement - checking for numeric values. – Smudger Mar 27 '15 at 07:27