Report with multiple data items tied to value prompt

[fkli222]

I have a question in Report Studio regarding Data Items. I am trying to create a report with 2 different data items using the same type of value (measure) but with 2 different depedent variables. The report would need a value prompt where selecting a month (or range of months) would aggregate the value into Data Item 1, and where selecting another month in the 2nd value prompt would aggregate the value into Data Item 2. What would be the best way to go about this? I have attached a screenshot of an example.

I greatly appreciate your help and thank you in advance!

[gpollock]

The way I would do this is by using a case statement.  Here's some crude--you can modify it to test it.

Data Item Value1:
case
    when date.month in (?parameter1?) then value
    else 0
end

Data Item Value2:
case
    when date.month in (?parameter2?) then value
    else 0
end

[fkli222]

The way I would do this is by using a case statement.  Here's some crude--you can modify it to test it.

Data Item Value1:
case
    when date.month in (?parameter1?) then value
    else 0
end

Data Item Value2:
case
    when date.month in (?parameter2?) then value
    else 0
end

Thank you gpollock. I thought this would work but I am getting back no data.

Dataitem 1:

case
    when [MMC Fee Tracker].[MonthYear Calc].[MonthYear Calc Name] in (?Scenario1?) then [MMC Fee Tracker].[Measure].[Measure]
    else 0
end

Dataitem 2:

case
    when [MMC Fee Tracker].[MonthYear Calc].[MonthYear Calc Name] in (?Scenario2?) then [MMC Fee Tracker].[Measure].[Measure]
    else 0
end


I am pretty sure I didn't do anything wrong as you instructed? I created each parameter off of the database values and named them Scenario1 and Scenario2. I then called it out in the case statement that you provided and replaced date.month with the date field used in the parameter.

[gpollock]

Okay, let's do some detective work.  You're going to be looking at the query object by run->view tabular data.

Check to see if you're getting data in your query at the lowest level of detail.  Make sure the data items have their aggregates set to total.  If you don't see anything, add the measure by itself, and add a data item that just contains the parameter.  If you use a filter to only get a few records, how is it looking?

[fkli222]

I removed both data items and added one by one. Adding Data Item 1 with only one parameter works fine. Then I added the second Data Item with my second parameter. If I filter on both parameters the same month, the report gets data. If I select a different date in each parameter, it doesn't get data.

[gpollock]

Ahhh, it's the filter.  My guess is you have two filters, one for each parameter, and they are both set to optional?  An order can never be in both January and February.  It can, however, be in January or February.  Open up one of your parameter filters, and paste its text in the other filter, using an or clause to separate.  You should only have one filter for the months, and can use an or to separate.

Example 1:
Date.Month in (?parameter1?) or Date.Month in (?parameter2?)

Example 2:
Date.Month in (?parameter1?,?parameter2?)

[fkli222]

Yes it seems to be the filter!

So just to clarify what you are saying. I do have 2 different parameter filters. One is date.month = ?parameter1? and other is date.month = ?parameter2? with being set to optional.

1) Are you proposing put in the Or into both filters calling out both parameters? Do these need to be Optional?
or
2) Are you proposing to only have one filter and call out both parameters in filter? Does this need to be Optional?

Thanks!

[gpollock]

Have one filter, set it to optional or required, and have some way of saying or in it.  Either of the examples I gave should work.  I don't know what optional/required will do.  It will be fun to try it.  But the point is you can't have mutally-exclusive filters, unless you make them "or-ish".

[fkli222]

This definitely makes sense and works, greatly appreciate it!

I do have one more question on top of that now. For each set of data we have different versions. What if I want to select a version pertaining to Dataitem1 and Dataitem 2 as well?

Do I just add to my Dataitem1 clause?

case
    when [MMC Fee Tracker].[MonthYear Calc].[MonthYear Calc Name] in (?Scenario1?) and version.name in (?Version1?) then [MMC Fee Tracker].[Measure].[Measure]
    else 0
end

[gpollock]

Basically.  Get familiar with Case statements, and experiment with your query subject.  Once you get the hang of it, it will be fine.