Variable Measures are not allowing me to show a Count

building reports webintelligence
1

Hi I’m currently using WEBI (Enterprise XI)

Morning, I have various warehouses that have stock and I am trying to create a measure variable that will show the items that are available for the whole of the month, but every time I add the variable to a total figures report it shows an empty column.

Firstly I have created a variable to calculate the number of items in stock at each warehouse

=Count([Location Name]+[Product Number Sku];Distinct)

This works fine.

Then in order to show the items that have stock available all month, I have created a measure variable to show that the item was available every day

=Count(Sum(If([Value Start of Day Opening Stock In Gbp]>0.01;1)))

Then as there is no object regarding the number of days in a month, I have created a measure variable for this also

=If([MMM]InList("Jan";"Mar";"May";"Jul";"Aug";"Oct";"Dec");31;If([MMM]InList("Apr";"Jun";"Sep";"Nov");30;If([MMM]InList("Feb");28)))

Then to find the items that are available for the whole month, another variable to show that stock was available for every day of that particular month

=If([Count Days]=[Days in Month];1)

But now when I try and create a count variable, using either Sum or Count against this variable it returns no figures.

Any help is as always greatly appreciated

fatboylesg (BOB member since 2012-06-28)

2

Go back to the query and click on the SQL/View Script button

Do you have 1 query or multiple queries?

You may have universe contexts at play here that prevent you from combining objects in the manner you have described

jemstar :ireland: (BOB member since 2006-03-30)

3

Jemstar,

thank you, SQL is a bit of a mystery to me (as you probably realise, reporting is not my normal day job and I have no support regarding issues)

Here is the SQL, it doesn’t appear to have multiple queries, but I’m sure you will tell me differently!!! :lol:

SELECT
  DIM_TIME_EFF_DATE.PK_Date,
  DIM_LOCATION_CRUISE.LOCATION_NAME,
  DIM_PRODUCT_CATEGORY.PRODUCT_CATEGORY,
  DIM_PRODUCT_CATEGORY.PRODUCT_SUB_CATEGORY,
  DIM_PRODUCT_CATEGORY.PRODUCT_MICRO_CATEGORY,
  DIM_PRODUCT_CATEGORY.PRODUCT_NUMBER_SKU,
  DIM_PRODUCT_CATEGORY.PRODUCT_NAME,
  cast(convert(char(10),getdate(),23) as datetime),
  ISNULL (sum(T_SS90_SS_TRANSACTIONS_HISTORY.SS1_VALUE_SOD_BEGINNING_IN_GBP),0),
  DIM_LOCATION_CRUISE.BRAND,
  DIM_STORAGE.STORAGE_NAME
FROM
  ODS_BI_SHIP_STOCK.dbo.T_DIM_LOCATION_CRUISE  DIM_LOCATION_CRUISE INNER JOIN ODS_BI_SHIP_STOCK.dbo.T_SS90_SS_TRANSACTIONS_HISTORY  T_SS90_SS_TRANSACTIONS_HISTORY ON (DIM_LOCATION_CRUISE.CRUISE_NUMBER=T_SS90_SS_TRANSACTIONS_HISTORY.CRUISE_NUMBER and DIM_LOCATION_CRUISE.LOCATION_ID=T_SS90_SS_TRANSACTIONS_HISTORY.LOCATION_ID)
   INNER JOIN ODS_BI_SHIP_STOCK.dbo.T_DIM_STORAGE  DIM_STORAGE ON (DIM_STORAGE.LOCATION_ID=T_SS90_SS_TRANSACTIONS_HISTORY.LOCATION_ID and DIM_STORAGE.STORAGE_ID=T_SS90_SS_TRANSACTIONS_HISTORY.STORAGE_ID)
   INNER JOIN ODS_BI_SHIP_STOCK.dbo.T_DIM_TIME  DIM_TIME_EFF_DATE ON (DIM_TIME_EFF_DATE.PK_Date=T_SS90_SS_TRANSACTIONS_HISTORY.EFFECTIVE_DATE)
   INNER JOIN ODS_BI_SHIP_STOCK.dbo.T_DIM_PRODUCT_CATEGORY  DIM_PRODUCT_CATEGORY ON (DIM_PRODUCT_CATEGORY.PRODUCT_ID=T_SS90_SS_TRANSACTIONS_HISTORY.PRODUCT_ID)
  
WHERE
  (
   DIM_TIME_EFF_DATE.PK_Date  BETWEEN  '10/01/2018 00:0:0'  AND  '10/31/2018 00:0:0'
   AND
   DIM_STORAGE.STORAGE_NAME  LIKE  '%inventory%'
   AND
   DIM_LOCATION_CRUISE.LOCATION_NAME  NOT IN  ( 'Main Office '  )
  )
GROUP BY
  DIM_TIME_EFF_DATE.PK_Date, 
  DIM_LOCATION_CRUISE.LOCATION_NAME, 
  DIM_PRODUCT_CATEGORY.PRODUCT_CATEGORY, 
  DIM_PRODUCT_CATEGORY.PRODUCT_SUB_CATEGORY, 
  DIM_PRODUCT_CATEGORY.PRODUCT_MICRO_CATEGORY, 
  DIM_PRODUCT_CATEGORY.PRODUCT_NUMBER_SKU, 
  DIM_PRODUCT_CATEGORY.PRODUCT_NAME, 
  DIM_LOCATION_CRUISE.BRAND, 
  DIM_STORAGE.STORAGE_NAME
HAVING
  ISNULL (sum(T_SS90_SS_TRANSACTIONS_HISTORY.SS1_VALUE_SOD_BEGINNING_IN_GBP),0)  >=  0.01
/* Universe: BISS ODS DW, User: @variable('BOUSER'), Report: @variable('DOCNAME') */

fatboylesg (BOB member since 2012-06-28)

4

does this variable return anything?

=Count(Sum(If([Value Start of Day Opening Stock In Gbp]>0.01;1)))

If not, perhaps put the sum inside the if()

Did you create a table with this variable to see what it returns?

sum([Value Start of Day Opening Stock In Gbp])

jemstar :ireland: (BOB member since 2006-03-30)

5

Thanks for that Jemstar,

unfortunately when I try the variable suggested it shows a value of 1 in each line alongside the warehouse name.

At your suggestion I created a separate table and the sum of each days stock value was aggregated and returned

fatboylesg (BOB member since 2012-06-28)

6

OK, so try this?

-sum(if(sum([Value Start of Day Opening Stock In Gbp]) > 0.01;1;0))

jemstar :ireland: (BOB member since 2006-03-30)

7

Morning, this time each line returned a value of 1.00

:hb:

fatboylesg (BOB member since 2012-06-28)

8

Is the value of stock per stock item? Is stock item in the table you have created with this variable?

jemstar :ireland: (BOB member since 2006-03-30)

9

I’ve attempted to add an attachment which shows both of the Tabs I have on the report.

My ultimate aim is to produce a summary results tab (the first screen shot) with the 4 calculations showing that I want to achieve.

The second screen shot shows the data table that I have generated. As you can see the data table looks as expected taking into account the variables I have created and mentioned in my earlier posts. It’s just this calculation for the Available to count, it’s showing ones and zeroes correctly, but just won’t sum them to put in the results tab.

There is only one query running to generate this data, so it is not incompatible objects (cross contamination)

Again, thanks for all your help on this one, my IT support department we’re about as much help as a chocolate teapot!!
Doc1.doc (64.0 KB)

fatboylesg (BOB member since 2012-06-28)