0
votes

Do you know how to get the last non-empty value using mdx query.. then after that.. i want to count how many null values are next to it up to the last date. My main purpose for this is to count how many days a customer has no transaction..

i have to make a report in ssrs (using adventure works cube) that counts how many days a customer has no transaction..

thanks..

2

2 Answers

0
votes

Use this article to obtain set of non empty dates.

To obtain count you can:

  1. Use member properties (day of year or something more suitable in this case) and just subtract one value from another.
  2. You can find last date with non empty value, previous one using LAG(1), include them to the next construction {prev.date:last.date} and use COUNT for set
0
votes

Recently I follow this article, It really helps me. The most important part is to understand scope usage. Even If you don't have an Enterprise Edition, you can use it.