SSRS Field from multi value parameter ReportBuilder

Question

I am attempting to generate a report based off a SharePoint list to display each team members work load over a dynamic amount of weeks using an SSRS stacked bar chart. Essentially I want the report to look like this: Desired Report

I am looking for a bar to represent each members's workload for each week. The report has a single parameter that accepts multiple values of dates, the next 5 Mondays hard coded into the parameter's available values (POC). When I run the report I only get a single bar for each member: Current Results

I believe this is because the source of 'Dates' field in the Dataset is =Parameters!DateRange.Value (0) I have tried to remove the '(0)' to accept the array of values rather than the first item but then I get an error when running the report. I think the crux of the issue is passing a multi value parameter to a field. I feel I need the field in order to expose the bars in the chart for each week. Any ideas on how to do this? Thanks in advance. (I apologize for the links, hopefully I have 10 reputation after this).

Dataset Query:

<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ListName>Digital ERF</ListName>
  <ViewFields>
    <FieldRef Name="Title" />
    <FieldRef Name="Current_x0020_status" />
    <FieldRef Name="AppSvc_x0020_Start_x0020_Date" />
    <FieldRef Name="MW_x0020_End_x0020_Date" />
    <FieldRef Name="App_x0020_SVC_x0020_Resource" />
    <FieldRef Name="MW_x0020_Est_x0020_Hrs_x0020__x0" />
    <FieldRef Name="MW_x0020_Work_x0020_Type" />
    <FieldRef Name="Intake_x0020_Through_x003a_" />
  </ViewFields>
  <Query>
    <Where>
      <And>
        <And>
          <Eq>
            <FieldRef Name="MW_x0020_Work_x0020_Type" />
            <Value Type="Text">Central IT- SharePoint</Value>
          </Eq>
          <IsNotNull>
            <FieldRef Name="AppSvc_x0020_Start_x0020_Date" />
          </IsNotNull>
        </And>
        <IsNotNull>
          <FieldRef Name="MW_x0020_End_x0020_Date" />
        </IsNotNull>
      </And>
    </Where>
  </Query>
</RSSharePointList>

Why are you using your parameter as a date field? Is **Dates** a calculated field that you added and use as a date field? Usually you would use a parameter to **filter** your **date field** (AppSvc_x0020_Start or MW_x0020_End) in your data. — Hannover Fist, Sep 15 '15 at 22:39
Dates is a calculated field (=Parameters!DateRange.Value(0)). I need Dates as the source data only has the start and end dates of the work and I am looking to display data where DateRange is greater than StartDate and less than EndDate. Is it possible to pass a multi value parameter to a field? — Dovahkiin, Sep 16 '15 at 10:59

score 0 · Answer 1 · answered Sep 28 '15 at 20:14

Ok, so you are looking for items in a range between a start and end date and you are trying to pass the start and end date as an array? Why? Create two parameters @start and @end and pass the two dates. Your other option is to convert the dates to a text parameter with Allow multiple values. This will pass a comma delimited list but then within the xml you have to convert that back to dates. If there will always be 2 items, that does not make sense to me.

SSRS Field from multi value parameter ReportBuilder

1 Answers1