Can I have a prompt within dimension?

[nscheaffer]

I am in the process of creating a universe with all sorts of date based variations (e.g. "First Day of Last Month", "Last Day of Next Quarter", etc.).  I am basing this on a Calendar table that we have defined in SQL Server 2008 that has a row for each date 60+ years into the future.

I having some issues with my First Day of Last Week.  The following expression works provided the week starts on Sunday...

DATEADD(DAY, (7 + (DATEPART(dw, common.dbo.Calendar.dt) + 7 - 1) % 7) * -1, Common.dbo.Calendar.dt)

However, I would like to prompt for the first day of the week (1 = Sunday, 2 = Monday, etc.) so my expression would like something like this...

DATEADD(DAY, (7 + (DATEPART(dw, common.dbo.Calendar.dt) + 7 - @FirstDayOfWeek) % 7) * -1, Common.dbo.Calendar.dt)

If I plug those values in there, I get the result I seek.  Can I set up a prompt for it?  There must be a way, I am just not figuring it out.

We are on 4.1 SP01 Patch 2.

Thanks,

Noel

[Former Member · Accepted solution]

Hi,

Create a object 'FirstDayOfWeek' using

CASE

WHEN @Variable('FirstDayOfWeek')='Sunday' THEN  1

WHEN @Variable('FirstDayOfWeek')='Monday' THEN  2

---

---

END

and replace @FirstDayOfWeek in your above code with this object 'FirstDayOfWeek'.

Note: If you try to parse this object then it fails as it is not associated to any of the tables.

Go to this object properties -> Select Tables option (its next to Parse button)

and select Common.dbo.Calendar.dt table -> Apply -> OK.

[Former Member · Accepted solution]

Two things on this

Firstly, instead of @Variable, use @Prompt

The other, there is no need to associate it with the table. You're going to be using it as part of a larger case statement so it will parse. What you're better off doing is hiding the object so that it cannot be used on its own, completely out of context.

[nscheaffer · Accepted solution]

I fully acknowledge that I may not have explained my objective clearly and that I may be making this more complicated that it needs to be.  However, I do have things working here is what I did.

First, I created a static LOV called Days of Week.

I then created a Parameter called Week Start Day based on the Days of Week LOV.  I was trying this before, but it wasn't working for me because I had neglected to uncheck the "Allow multiple values" check box.

Next, I created a dimension called Week Start Day defined as follows...

     @Prompt(Week Start Day)

And then I could define my First Day of Previous Week dimension as follows...

     DATEADD(DAY, (7 + (DATEPART(dw, common.dbo.Calendar.dt) + 7 - @Select(Derived\Week\Week Start Day)) % 7) * -1, Common.dbo.Calendar.dt)

I think I could have just as well embedded the prompt like this...

     DATEADD(DAY, (7 + (DATEPART(dw, common.dbo.Calendar.dt) + 7 - @Prompt(Week Start Day)) % 7) * -1, Common.dbo.Calendar.dt)

I have tested them and they both work.

I also wanted to make available the name of the day that was selected to start the week.  I tried a case statement, but I got a Cartesian product warning/error.

     CASE

          WHEN @Select(Derived\Week\Week Start Day) = 1 THEN 'Sunday'

          WHEN @Select(Derived\Week\Week Start Day) = 2 THEN 'Monday'

          WHEN @Select(Derived\Week\Week Start Day) = 3 THEN 'Tuesday'

          WHEN @Select(Derived\Week\Week Start Day) = 4 THEN 'Wednesday'

          WHEN @Select(Derived\Week\Week Start Day) = 5 THEN 'Thursday'

          WHEN @Select(Derived\Week\Week Start Day) = 6 THEN 'Friday'

          WHEN @Select(Derived\Week\Week Start Day) = 7 THEN 'Saturday'

     END

So I simply defined my Week Start Day Name as the name of the day of the First Day of Previous Week dimension...

       DATENAME(WEEKDAY, @Select(Derived\Week\First Day of Previous Week))

Here is how is works...

As I said earlier, there may be a simpler way.  However, this works for me and I understand it.  I am sure I will find ways to improve my design with your help.

Thank you,

Noel

[Former Member · Accepted solution]

That's a good explanation, thanks.

I would imagine that it works fine - are you noticing any performance issues?
If not then I wouldn't worry about it.

[nscheaffer · Accepted solution]

Performance is good within IDT.  I have yet to test it in a WebI report.

Noel

[Former Member · Accepted solution]

Well, it looks logical and if performance is quick and accurate then the users won't complain either.

[nscheaffer]

I detailed my final solution here if you are interested.

Noel