I m crating a report that will be showing the hours worked by every user for two weeks. Everything seem to be fine . The only problem is that, as no one will be working saturday and sunday, these two days will not appear in my report.
I have created a cross tab and it should look like this:
It seams intresting but Dave’s idea seem to be bases on months coming from a celendar table. I have tried seeing my dates coming from the sesond query and there is no date for a saturday or a sunday as no entry have been made for these days.
Yes, that’s the point To find a source of all dimension values that you need in the cross tab.
So the second query can’t be based on a table where there are not all those values. The query needs to be based on a different table. And if you don’t have such table then you may want to use a derived table in the universe where you “make” those values that you need. For Oracle database, the query for such derived table would look like:
select to_char(to_date(trunc(sysdate,'d')-rownum+1),'day') as day_of_week
from all_objects
where rownum <= 7
order by rownum desc
I am sorry but I don’t know the exact syntax for SQL server.
Maybe something as simple as this might work:
select 'Monday' as day_of_week
union
select 'Tuesday' as day_of_week
union
select 'Wednesday' as day_of_week
union
select 'Thursday' as day_of_week
union
select 'Friday' as day_of_week
union
select 'Saturday' as day_of_week
union
select 'Sunday' as day_of_week
as you can see for each user, I m showing the hour worked by day and he have to shoose the right code to get paid. The problem is that the user can shoose two codes for one day example for code1 he worked 4h and for code2 he worked 3h
I get a multiValue in every column like that. The Humain resorces have been used to get such reports and for these cases they will be shon like this :
code1|code2
4h | 3h
I have been using BO for a good while and I know that show such things in the same cell is impossible but do you think that we can do this.
It looks that the [code] dimension is in the body of the cross-tab. Why don’t you move it to the header so its values will be shown across, in the table header?
Actually the client whants it like this. So I have converted the dimension to a mesure so that it gets aggregated lik that. It works of the is only one value. but when there is two values, I get multiValues . What I want is to show the two values like this “code1|code2”
Hi Marek do you mean day of mounth coming from the query one? if so as yhis dimension contains only true data from (monday to friday), it will reduce the days of week dimension and the cross tab will contain only monday to friday
Do you have a calendar table? This sort of things that you are trying to implement are easier done using a calendar table. If you don’t have it, consider creating one.
(BOB member since 2008-07-31)
(BOB member since 2003-11-27)
To find a source of 
(BOB member since 2007-12-11)
8)