string - SSRS choosing dynamic Columns -

string - SSRS choosing dynamic Columns -

for study need take columns access database dynamically. more precice:

in database columns named this:

fcst_sales_2014_q4_with_sf fcst_sales_2015_q1_with_sf fcst_sales_2015_q2_with_sf fcst_sales_2015_q3_with_sf

i need study take current year , quarter, hence i'm using now() function composite look need. example:

=year(now())

and

=switch( month(now()) = 1, 1, month(now()) = 2, 1, month(now()) = 3, 1, month(now()) = 4, 2, month(now()) = 5, 2, month(now()) = 6, 2, month(now()) = 7, 3, month(now()) = 8, 3, month(now()) = 9, 3, month(now()) = 10, 4, month(now()) = 11, 4, month(now()) = 12, 4)

the result 2014 , 4 need. if i'm using following:

=("fields!fcst_sales_" + cstr(year(now())) + "_q" + cstr(switch(month(now()) = 1, 1, month(now()) = 2,1,month(now()) = 3,1,month(now()) = 4, 2, month(now()) = 5,2,month(now()) = 6,2,month(now()) = 7,3,month(now()) = 8,3,month(now()) = 9,3,month(now()) = 10, 4, month(now()) = 11,4,month(now()) = 12,4)) + "_with_sf")

i string need (fcst_sales_2014_q4_with_sf) problem is, somehow can't utilize .value operation. if add together sum() , .value look create error message, .value no fellow member of string. look used is:

=sum(("fields!fcst_sales_" + cstr(year(now())) + "_q" + cstr(switch(month(now()) = 1,1, month(now()) = 2,1,month(now()) = 3,1,month(now()) = 4,2, month(now()) = 5,2,month(now()) = 6,2,month(now()) = 7,3,month(now()) = 8,3,month(now()) = 9,3,month(now()) = 10, 4, month(now()) = 11,4,month(now()) = 12,4)) + "_with_sf").value)

is there solution problem?

you can't build field - building string. want build name of fields in fields collection, instead of this:

fields!fieldname.value

looks this:

fields("fieldname").value

so utilize big expression:

=sum(fields("fcst_sales_" + cstr(year(now())) + "_q" + cstr(switch(month(now()) = 1, 1, month(now()) = 2, 1, month(now()) = 3, 1, month(now()) = 4,2, month(now()) = 5, 2,month(now()) = 6, 2, month(now()) = 7, 3, month(now()) = 8, 3, month(now()) = 9, 3, month(now()) = 10, 4, month(now()) = 11, 4, month(now()) = 12, 4)) + "_with_sf").value)

you can simplify expression. quarter, need this:

(month(today) + 2) \ 3

so look comes downwards

=sum(fields("fcst_sales_" + cstr(year(today)) + "_q" + cstr((month(today) + 2) \ 3) + "_with_sf").value)

alternative method: using sql build field name dynamically

there perchance simpler way using dynamic sql string returning consistent field name. example, if sql looked this:

select fcst_sales_2014_q4_with_sf mytable

you can convert string look resolves sql statement. right-click dataset, take dataset properties , click look editor fx button beside query. can build sql statement string, so

="select fcst_sales_" + cstr(year(today)) + "_q" + cstr((month(today) + 2) \ 3) + "_with_sf fcst_sales " &"from mytable"

now dynamically building field name in sql query , gets returned aliased fcst_sales in dataset can utilize known field value throughout report, making calculations much easier:

=fields!fcst_sales.value

and

=sum(fields!fcst_sales.value)

string dynamic reporting-services composite