Skip to Content
author's profile photo Former Member
0
Former Member

Different column detail webi report

Posted on Oct 12, 2010 at 09:52 AM 109 Views

Dear experts,

Is it possible to have a webi report with the following columns' layout:

Country | Average 2009 | Jan 2010 | Feb 2010 | .... | Dec 2010 | Average 2010

I've created a variable directly in the webi report but I'm not able to have detail by month in 2010 and a column with the 2010 total.

Any ideas?

thank you!

Best,

Inês Santos

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Kuldeep Chitrakar
    Kuldeep Chitrakar
    Posted on Oct 12, 2010 at 11:19 AM
    4

    Can you try this. Create three objects and use cross tab.

    Average2009

    Avg(column) over (partition by year=2009)

    Average2010

    Avg(column) over (partition by year=2010)

    Monthwise

    CASE

    WHEN Date >='01-01-2010' and Date<='31-01-2010' THEN 'Jan2010'

    WHEN..

    ....

    ...

    END

    Add a comment
    10|10000 characters needed characters exceeded

    • Marianne Loenen Former Member
      Oct 13, 2010 at 05:48 PM

      Hi Inês,

      I only just saw this, so may I suggest the approach I would use?

      Use two queries, one for this year (on month level).

      Copy that and make it select last year (no month object).

      Use a crosstab, country going down, year/month going across.

      In the body put the measure from this years query, not sure if that should be sum or average, but you'll manage

      Now you can use the table footer on the right to place the average of that object (= average of this year).

      So the only column left to do is the average of 2009.

      Create a new column on the right of the left header of the table, so basically next to country.

      In this column place the average of the last years query.

      Only thing left to do is a little formatting so it looks like a "normal" table.

      Hope this helps,

      Marianne

      P.S. it's also possible in one query, but then you will have to split out the years based on the userresponse.

      and create report variables for the measures of this year and last year.

    Comment on This Answer

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.