| Author |
Topic |
|
gavakie
Posting Yak Master
221 Posts |
 Posted - 2009-10-14 : 14:43:57
|
| so i have a report im building and if column a = "X" then i dont want to say show its sales in a group. How would i do that? |
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-10-14 : 21:39:30
|
| in Hide properties of Sales, =Iif(Fields.ColumnA.value=X, true, false) so when columnA = X the column would hide.Could also be in the value entry for sales...=Iif(Fields.ColumnA.value=X, " ", Fields.Sales.Value) that way if ColumnA=X then force to display blanks otherwise show real value. Both have merits. I like choice 2 because sometimes when you hide a cell, you lose the borders and other attributes.John |
 |
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2009-10-15 : 09:44:15
|
| How would i sum the sales of only the columns I have shown then? |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-10-15 : 10:05:52
|
| If you used the second option, the fields that meet your omit criterea would be zero (or Null in this case) so a normal =SUM(Fields!Sales.Value) should work on a Footer or grouping row.And, sorry, my previous examples are showing "Fields." instead of the syntactically correct "Fields!"John |
|
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2009-10-15 : 10:18:36
|
| so this is my sum and its giving me an error.=sum(Iif(Fields!Letter.Value="Service", " ", ((Fields!Mailed.Value / Fields!Distinct_Customers_Contacted.Value) * Fields!Program_Cost.Value))) |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-10-15 : 10:38:29
|
| No, I probably was not clear. You do the Iif on the Table Detail row to over-ride the value if you match the column value.=Iif(Fields!Letter.Value="Service", " ", ((Fields!Mailed.Value / Fields!Distinct_Customers_Contacted.Value) * Fields!Program_Cost.Value))But I am not sure now what you would have in the Footer/Totals row. Maybe you are better off calculating the new value for each row in your SQL (using a CASE statement) and referencing the new field in the report. That way you could eliminate the Iif in the report table cell. Just a thought...John |
|
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2009-10-15 : 10:43:35
|
| Im coming back reporting services and i thought there was to create new fields from your data and thought that may work. thanks for the help. |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-10-15 : 10:46:17
|
| Unfortunately I do not know of a way to Alias the field/column name like you can in SQLJohn |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-10-15 : 11:07:44
|
quote:
Originally posted by gavakie
so this is my sum and its giving me an error.=sum(Iif(Fields!Letter.Value="Service", " ", ((Fields!Mailed.Value / Fields!Distinct_Customers_Contacted.Value) * Fields!Program_Cost.Value)))
how can you apply sum over " "? i think that should be 0 |
|
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-10-15 : 11:41:25
|
| " " actually works, treats like null or zero but you are correct. For clarities sake should be 0 and use formatting to zero suppress in the column/row.John |
|
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2009-10-15 : 16:35:55
|
| =sum(Iif(Fields!Letter.Value= "Service", 0, ((Fields!Mailed.Value / Fields!Distinct_Customers_Contacted.Value) * Fields!Program_Cost.Value))) this???it still gives an error. |
|
|
|
gavakie
Posting Yak Master
221 Posts |
Posted - 2009-10-16 : 09:44:26
|
| nevermind i read that wrong |
|
|
|
|
display a field in a group != to another field