How can I display an amalgamation/concentration of data in my SSRS report using Expressions?

Question

I use multiple similar datasets (the data coming from Stored Procedures) that I use in a Tablix in an SSRS report. I need to show the data from these similar-(in metadata)-but-disparate(in result set data) Stored Procedures side-by-side (in each row of the result set in the Tablix).

The data returned from a Stored Proc will contain a record for week1 values, and another record for week2 values for a given ItemCode, but I need to show these side-by-side; IOW, data like this:

ITEMCODE    WEEK1   WEEK2
--------    -----   -----
1           11      0
. . .
1           0       42
2           7       0
. . .
2           0       8

...needs to display on the report as:

ITEMCODE    WEEK1   WEEK2
--------    -----   -----
1           11      42
2           7       8

Based on an answer here, I tried to accomplish this using Expressions for the "Week1" and "Week2" data fields:

Week 1 Expression:

=IIF((Fields!Week.Value="WK1"),Fields!Price.Value,"")

Week 2 Expression:

=IIf(Lookup(Fields!ItemCode.Value, Fields!ItemCode.Value, Fields!Week.Value, "RockBottomRollup_Craftworks")
="WK2", Lookup(Fields!ItemCode.Value, Fields!ItemCode.Value, Fields!Price.Value, 
"RockBottomRollup_Craftworks"), "")

...but using those expressions I hear only deafening silence for week2 (the "5/22/2016 - 5/28-2016" column in the screen shot):

What do I need to do to concentrate the week1 and week2 rows into single rows for each ItemCode?

UPDATE

I think what I need to do is have the expression for week2 not only be tied to the ItemCode value, but also to the Member value.

However, being the VBScript novice/doofus (sp?) that I am, I don't know what syntax is needed to make that happen.

Do I need to change the Week 2 Expression to something like this:

=IIf(Lookup(Fields!ItemCode.Value AND Fields!Member.Value, Fields!ItemCode.Value, Fields!Week.Value, 
"RockBottomRollup_Craftworks")
="WK2", Lookup(Fields!ItemCode.Value AND Fields!Member.Value, Fields!ItemCode.Value, Fields!Price.Value, 
"RockBottomRollup_Craftworks"), "")

...or...???

UPDATE 2

I tried this suggested Expression:

=IIf(Lookup(Fields!ItemCode.Value & " - " & Fields!ShortName.Value,Fields!ItemCode.Value & " - " & Fields!ShortName.Value,
 Fields!Week.Value,"RockBottomRollup_Craftworks")
="WK2", Lookup(Fields!ItemCode.Value & " - " & Fields!ShortName.Value, Fields!ItemCode.Value & " - " & Fields!ShortName.Value,
Fields!Price.Value, "RockBottomRollup_Craftworks"), "")

...but still get nothing in the week2 column (there are values for that column in the second half of the report - the first half shows week1 vals, the second half shows week2 vals).

Note: I replaced "Member" with "ShortName" because the former is just the label, and the latter is the actual field name.

Are ampersands (&) really the right thing to use there (as opposed to "AND")?

UPDATE 3

For this to work, do I need to add a second instance of the same dataset to the report, naming it, for example "RockBottomRollup_Craftworks2" and then replacing the existing dataset name in the expression with that (appending the "2")?

UPDATE 4

I like the direction Thomas Inzina is going with this; so would my existing Stored Proc:

IF OBJECT_ID ( 'RockBottomAmalgamated', 'P' ) IS NOT NULL   
    DROP PROCEDURE RockBottomAmalgamated;  
GO
CREATE PROC [dbo].[RockBottomAmalgamated]
    @BegDate datetime,
    @EndDate datetime,
    @SortBy varchar(20)
AS
IF OBJECT_ID('#BaseCraftworksTable', 'U') IS NOT NULL
  DROP TABLE #BaseCraftworksTable; 

INSERT INTO #BaseCraftworksTable
 EXEC sp_ViewPriceMatrix_Variance_RockBottomRollup_Craftworks
   @BegDate = @BegDate
  ,@EndDate = @EndDate
  ,@SortBy  = @SortBy;

SELECT * FROM #BaseCraftworksTable;

...need to change to this:

IF OBJECT_ID ( 'RockBottomAmalgamated', 'P' ) IS NOT NULL   
    DROP PROCEDURE RockBottomAmalgamated;  
GO
CREATE PROC [dbo].[RockBottomAmalgamated]
    @BegDate datetime,
    @EndDate datetime,
    @SortBy varchar(20)
AS
IF OBJECT_ID('#BaseTable', 'U') IS NOT NULL
  DROP TABLE #BaseTable; 

INSERT INTO #BaseTable
 EXEC sp_ViewPriceMatrix_Variance_RockBottomRollup_Craftworks
   @BegDate = @BegDate
  ,@EndDate = @EndDate
  ,@SortBy  = @SortBy;

SELECT #BaseTable.ItemCode, #BaseTable.Price, #BaseTable.Member, #BaseTable.Week, #BaseTable_1.Week as Week2
FROM #BaseTable 
INNER JOIN #BaseTable AS #BaseTable_1 ON #BaseTable.ItemCode = #BaseTable_1.ItemCode
WHERE (((#BaseTable.Week)="wk1") AND ((#BaseTable_1.Week)="wk2"));

???

I don't know how I can select from a Stored Proc (as Thomas' answer shows) without first calling the Stored Proc and inserting its returned result set into a temp table or some such. ...or...???

UPDATE 5

Inspired by this link[http://www.sommarskog.se/share_data.html], I tried this, too (adding "OUTPUT" and "BEGIN/COMMIT TRANSACTION" block):

IF OBJECT_ID ( 'RockBottomAmalgamated', 'P' ) IS NOT NULL   
    DROP PROCEDURE RockBottomAmalgamated;  
GO
CREATE PROCEDURE [dbo].[RockBottomAmalgamated]
    @BegDate datetime,
    @EndDate datetime,
    @SortBy varchar(20) OUTPUT
AS

BEGIN TRANSACTION
INSERT INTO #BaseCraftworksTable
 EXEC sp_ViewPriceMatrix_Variance_RockBottomRollup_Craftworks
   @BegDate = @BegDate
  ,@EndDate = @EndDate
  ,@SortBy  = @SortBy;
COMMIT TRANSACTION  

SELECT * FROM #BaseCraftworksTable;

...but still no results are returned.

You have two different datasets with the same structure, right? One dataset gives data for WK1 and the other returns data for WK2?, right? — alejandro zuleta, Jun 08 '16 at 16:48
Yes and no; I do have multiple datasets with the same structure, but the data shown above is from the same dataset. All rows are either "Week1" or "Week2"; I need to virtually concatenate them, so that the Price value from the Week2 records display alongside their Week1 values for the same ItemCode/Member. Please see my Update. — B. Clay Shannon-B. Crow Raven, Jun 08 '16 at 16:57
I think the issue is that the Lookup is returning 1 value, but there are multiple instances of each ItemCode. It's not a row-by-row calculation. If there's any way you could union the results of the procedures in one dataset you could simply group by the ItemCode. Otherwise, you'll have to use LookupSet and work through that. — StevenWhite, Jun 08 '16 at 17:39
@StevenWhite: Yes, I thought of that, but the SP is so complex and rife with stuff I know little-to-nothing about (I'm no SQL expert, especially when it comes to cursors, dynamic altering of temp tables, and such), that I don't think that is a viable option for me. — B. Clay Shannon-B. Crow Raven, Jun 08 '16 at 17:44
@StevenWhite: I'm now trying to lookup combination of ItemCode/Member, so there should only be two values that match that - one for Week1, and the other for Week2. — B. Clay Shannon-B. Crow Raven, Jun 08 '16 at 17:45
@B.ClayShannon You can execute the procedure into a temp table for each set of parameters and then union those temp tables. — StevenWhite, Jun 08 '16 at 17:46
@StevenWhite: That sounds intriguing/promising; do you mean in a separate StoredProc, or is this possible from within SSRS itself somehow (and if so, how)? — B. Clay Shannon-B. Crow Raven, Jun 08 '16 at 17:49
@B.ClayShannon Yes, you can do this in the SSRS dataset query. See http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table — StevenWhite, Jun 08 '16 at 18:04

score 1 · Answer 1 · answered Jun 08 '16 at 17:03

Based on your update this could work but not sure, I'd need to reproduce your environment:

=IIf(Lookup(Fields!ItemCode.Value & " - " & Fields!Member.Value,Fields!ItemCode.Value & " - " & Fields!Member.Value,
 Fields!Week.Value,"RockBottomRollup_Craftworks")
="WK2", Lookup(Fields!ItemCode.Value & " - " & Fields!Member.Value, Fields!ItemCode.Value & " - " & Fields!Member.Value,
Fields!Price.Value, "RockBottomRollup_Craftworks"), "")

Let me know if this helps.

Thanks, but nope; please see Update 2. – B. Clay Shannon-B. Crow Raven Jun 08 '16 at 17:22 — B. Clay Shannon-B. Crow Raven, Jun 08 '16 at 17:22

score 1 · Accepted Answer · answered Jun 09 '16 at 14:25

1

Lets say your Stored Proc's name is t. I would create two SELECT statements

SELECT t.ItemCode, t.Price, t.Member, t.Week as Week1 FROM t WHERE t.Week = 'Wk1'

The second SELECT statements would use a table alias

SELECT t_1.Week as Week2 t as t_1 WHERE t_1.Week= 'Wk2'

Next you'd Join the two SELECT statements on there id fields

INNER JOIN t AS t_1 ON t.ItemCode = t_1.ItemCode

Put it all together

SELECT t.ItemCode, t.Price, t.Member, t.Week, t_1.Week as Week2
FROM t 
INNER JOIN t AS t_1 ON t.ItemCode = t_1.ItemCode
WHERE (((t.Week)="wk1") AND ((t_1.Week)="wk2"));

answered Jun 09 '16 at 14:25

Thanks; please see Update 4. – B. Clay Shannon-B. Crow Raven Jun 09 '16 at 15:15
1

I might be wrong but when your Stored Proc executes it in return executes other Stored Proc. But in the end it returns a dataset. I would think you could use that dataset like a normal query. However, I would probably make another Stored Proc "CreateBiweeklySummaryTableProc" that would create another temp table "BiweeklySummaryTable". Then have your original Stored Proc execute CreateBiweeklySummaryTableProc , Stored Proc would then SELECT * FROM #BiweeklySummaryTable instead of #BaseTable . It would be easier to debug and make exporting & manipulating the data a hell of a lot easier. – Jun 09 '16 at 15:57
I think I am using that dataset like a normal query - I call "Select *" at the end. – B. Clay Shannon-B. Crow Raven Jun 09 '16 at 16:18