Webreport should show count of "active" projects


    • Webreport should show count of "active" projects


      I have the following problem where I couldn't find a good solution for.
      My cube contains in general dimensions for projects, locations, years and savings (to keep it simple).
      One project can have savings in one or more years and I want to count a 1 for every year the project has savings > 0.

      The final result report should list the locations and the amount of active projects for each year.
      (the location is also written as an attribut on every single project)

      My first thought was using a rule to determine wether a project is active (savings > 0) for each year or not.
      ['active projects'] = IF(['savings','All'] > 0,1,0)
      But I couldn't make it to sum that up in my final web report by location.

      Thanks in advance for your help!,

      Stephan Wetzelsberger
    • Hello Stephan,

      in one of my cubes I enter employee salaries and wanted to use that information to count employees, which is similar to your problem. Here is what you could try:

      ['active projects'] = B:['savings'] / [['savings']]

      What is does is for every project with savings it returns 1 and for a project without savings the formula gives you 0. That way the count always gives you the number of active projects for any given view (e.g. you are looking at one region and it gives you the active projects for that region).

    • What Hobee suggested is also possible. I didn't want to suggest it because it is returning 1 also for negative savings values.
      So if your savings are always positive numbers you can use it too.
      In 5.1 and 6.0 versions you can define the rule without using markers, if you have no other rules in the cube using markers.
      ['active projects'] = B:['savings'] / ['savings']
      would do the job.
      Actually in version 6.0 the version "['active projects'] = B: IF(['savings'] > 0,1,0)" whould be as fast as the "B:['savings'] / ['savings']"