User Views Data Call with Variable

[Jerry Clough]

Good day!

I have a user view that is referring to another database. I would like it to refer to different sheets in the data base based on the month of the order date. Right now I have it working with this code:

@{GCKCMatrix}:
Select @[June].[FIRSTCARD]
From @[June]
where @[June].[GCKC]=|->[Card1Sku]

But I want to be able to dynamically switch it to a sheet called "July" when the order date is July using a variable called "@DateNumber". So it would look like this:

@{GCKCMatrix}:
Select @[@{DateNumber}].[FIRSTCARD]
From @[@{DateNumber}  ]
where @[@{DateNumber}  ].[GCKC]=|->[Card1Sku]

Is it even possible to put a XMPie expression in a User View?

Thank you!

Jerry Clough

[Amit Cohen]

Yes, you can use variables in the user views, But you can not use them as a dynamic table or field names.

Yet, here is a solution for you:

@{GCKCMatrix}:
Select @[June].[FIRSTCARD] From @[June] where @[June].[GCKC]=|->[Card1Sku] AND @{Month} = N'June'

UNION ALL

Select @[July].[FIRSTCARD] From @[July] where @[July].[GCKC]=|->[Card1Sku] AND @{Month} = N'July'

and a variable "Month", should contain the month that you want.

[Jerry Clough]

Thank you, Amit. I'll give this a try. It is certainly easier than the work around way I figured out.

Jerry Clough