ssrs 2008 r2 dataset tied to main sql

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  January 20, 2014 at 9:07 pm

  #396132

  In an existing ssrs 2008 r2 application, I am having a problem trying to understand the existing logic.

  I am hoping you can it explain the logic to me of how the parts are tied together for both part 1

  and part 2:

  1. The following is Part 1:

  a. The main sql the

  SELECT

  sum(case when @AbsT like '%UN1%'

  then [absUN1]

  else 0 end) +

  sum(case when @AbsT like '%AB1%'

  then [absAB1]

  else 0 end) +

  sum(case when @AbsT like '%IL1%'

  then [absIL1]

  else 0 end) +

  sum(case when @AbsT like '%ME1%'

  then [absME1]

  else 0 end) +

  sum(case when @AbsT like '%SU1%'

  then [absSU1]

  else 0 end) +

  sum(case when @AbsT like '%TR1%'

  then [absTR1]

  else 0 end) as SelectedAb1

  FROM [OP1].[dbo].[AttReport]

  where SelectedAb1 > isnull(@AMin,-1)

  and SelectedAb1 <= isnull(@AMax,100)

  b. The following is the dataset and parameter that refer to the sql above:

  @AbsT is the parameter

  select 'AB1' as value, 'Absent (ABS)' as Label

  union

  select 'IL1', 'Ill (ILL)'

  union

  select 'ME1','Medical (MED)'

  union

  select 'SU1','Sus (SUS)'

  union

  select 'TR1','Tru (TRU)'

  union

  select 'UN1','Unverify (UNV)'

  Can you explain to me how the parameter listed above is used by the sql listed above?

  2. The following is part 2:

  a. The following is the sql behind the parameter called AbsMinMax

  select 0 as Num

  union

  select 1

  union

  select 2

  union

  select 3

  union

  select 4

  union

  select 5

  b. the following are the parameter values a user enters:

  @AMin as int,

  @AMax as int,

  c. the following is the where clause:

  where SelectedAb1 > isnull(@AMin,-1)

  and SelectedAb1 <= isnull(@AMax,100)

  Can you explain how the items listed under part 2 are tied together? Can you explain he logic?

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  January 27, 2014 at 3:53 pm

  #1683745

  The query is basically "asking" which column to total, and then setting the query up to accomplish that. It's just a slightly more advanced version of using parameters.

  It's saying something like this:

  I want the user to choose what column he wants manipulated (by picking it in the dropdown).

  Then I'll do some standard thing (use a standard function like SUM) against that column and show the relevant records in my tablix.

  Does that make sense, or did I misunderstand the question? (entirely possible!)