How to create a dummy variable for 31 days to keep report block static

[Former Member]

Hi All,

I am having an issue with the data in the webi report.

I am having a crosstab, with 3 dimensions and 1 measure

[Current Month],[Product],[Item],[Sales]

I am using the above block to create a dashboard using BI Webservices

My issue is, because of the "Current Month", my total comumns will change every month, like for jan 31 columns, feb 29 columns, march 31 columns.

Because of the dynamic columns my dashboard failing. (need to map the columns every month)

I want to create a dummy variable in webi or at universe level, to restrict the table block for 31 columns.

Any way to create a dummy variable with 31 rows???

[Former Member]

If I understand correctly, you want to display fixed number of columns; display zero for the unavailable dates.

In that case, create 31 variables for each date and place it in each date column; instead of one measure in the cross tab. Each variable should allow one date value only.

E.g.,

Var_Sales_Date1 =  [Sales] where Day(sale_Date)=1

Var_Sales_Date2 =  [Sales] where Day(sale_Date)=2

Etc.,

[Former Member]

Hi

You can create a formula for each day of Jan month itself since its going to have always 31 days irrespective of Year you can always make use of that.

Regards

Niraj