cancel
Showing results for 
Search instead for 
Did you mean: 

Different column detail webi report

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member207342
Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Kuldeep,

Thank you for your responde.

Could you please be more detailed regarding your solution.

I should create these 3 objects in the universe, correct?

I didn't understand what you meant by "over partiiton by year"...

thank you.

Best regards,

Inê

Former Member
0 Kudos

Hey Inês Santos,

As per my understanding,

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

Follow as below:

Drag Country | Year | Month | Measure objects what you have into Report/table.

Now, right-click on Country column and say *Insert Column to the Right * and do the same at the end.

Next, create 2 variables.

Var1 = Avg( ForEach where Year = 2009) In report /Block

Var2 = Avg( ForEach where Year = 2010) In report /Block

(Parse the variables for proper syntax, i just assumed and guided you)

Hope it is what you are looking about.

Thank You!!

Sent from iPhone

Former Member
0 Kudos

Hi,

Thank you for your email.

As far I've understood, your suggestion lists my months instead of putting them in the header which means I would have:

Country | Avg 2009 | Month | Avg 2010

A 200 Jan 300

B 100 Feb 200

When what i want is:

Country | Avg 2009 | Jan10 | Feb10 | .... | Dec10 | Avg 2010

I suppose using crosstab I'm able to have:

Country | Avg 2009 | Avg 2010 | Jan10 | Feb10 | .... | Dec10

But if someone could tell me how to have Avg2010 placed as the last column, I would highly appreciate it.

Cheers!

Inê

Former Member
0 Kudos

See Inês,

Use Cross tab for that requirement.

Now do the same steps:

Right-click on Country and say ==>> Insert Column to the Right (Here place the Variable ==>> Avg(2009))

Do the same at the end to place Variable ==>> Avg(2010).

Country | (after right clicking added column | Jan 2009 | Feb 2009 | ....... | Dec 2010 | (One more column here)

Try to use the formulas what I have sent for Avg 2009 & 2010.

If you face any problems while applying formulas , post here. Will help you.

Thank You!!

Sent from iPhone

Former Member
0 Kudos

Hello again

The thing is that if I choose cross tab, when I click the last column to enter a new column to the right (which would be used to post Avg 2010) I get this:

Country | Avg 2009 | Jan10 | Avg 2010 | Feb 2010 | Avg 2010 | .... | Dec 2010 | Avg 2010.

Is there any way for this not to happen?

Thank you for your time.

Inê

Former Member
0 Kudos

Hey Inês,

Country | Avg 2009 | Jan10 | Avg 2010 | Feb 2010 | Avg 2010 | .... | Dec 2010 | Avg 2010.

You're correct foreach Month-Year it will append one column, yesterday I didn't find enough time to see this.

Solution:

1. just drag Cross-tab from templates.

2. In Column section(far left) Drop object.

3. In Rows Just right-click on it and Say Insert row above.

4. on Newly inserted row, Drag object

Note: object should contain only years only like 2009, 2010 etc...,

5. In below row , just drag object (which has Jan10,Feb10 etc...,)

6. Now Click on object and say Insert column right

Here, use formuls to calculate Avg(2009) == See my earlier post

7. Now on Object , the top one (row), apply Break.

8. It will append two columns for you. One is exact size with Column and one more with small size(white background). Here you can remove small one.

9. Now on newly added/appended column , write Avg(2010) formula.

10. On object (top one== row), go to properties == Make Text and Back ground color to White and Width and height to 0(Zero- it will take 4 pixels== which is default)

Now you can see exact report, what you are trying to achieve.

I tried and tested. Hope it should work for you.

Thank You!!

Sent from iPhone

Former Member
0 Kudos

Hello!

Thank you for your reply.

I must be doing something wrong, so if you could answer me just a couple more questions:

1. How did you build you query? Do you have one query for year 2009 and another for year 2010 and the you merged the dimensions on webi or do you have a single query that brings you both years?

2. After applyin the break in the year object I can't have a single column in the end... I always have a column for each year.

So by now, what I have is:

Country | Avg2009 | jan 09 | Feb 09 | .... | 2009 break | white column (will be deleted) | Jan 10 | Feb 10 | ... | 2010 break | white column (will be deleted).

Thanks for your time.

My Best,

Inê

Former Member
0 Kudos

Ok! I did it! Thank you so much for your patience and time!

Cheers!

Inê

MariannevL
Advisor
Advisor
0 Kudos

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.

Answers (0)