Creating Report With Dates Range Split Per Week

building reports webintelligence
1

Hi

I’m looking to create a report with a department down the side and along from each dept I want a column with each one having the date of each Saturday for say about 3 months worth, and a total at the top, something like this:


dept Total  02/11/13      09/11/13        16/11/13  
======      ======        ======           ======    
Amount          8             20              16

dept 2      02/11/13       09/11/13        16/11/13  
====        ======         ======          ======    
Amount        5             16               7         

dept 1      02/11/13       09/11/13        16/11/13  
====       ======         ======          ======    
Amount       3             4                 9

My questions are what template should I use to do this and any things I need to know (tips/sticking points/shortcuts) as I am quite new at building these types of reports in BO?

Also if I want to display say 3 rolling months of report data everytime I run the report,and any time I run it it would update to include any new Saturday since running last time, could that be done easily enough?

Thanks

G

grifter (BOB member since 2013-01-09)

2

We cannot build the report for you. My advice is: start simple and expand the report with more complex logic.

So start with two prompts to enter the “rolling” period, create the “rolling” thing later (based on today’s date). Try to create a filter with something like daynumber(mydate) = 6 (whatever database, country setting) that filters the Saturdays.

You need need a table that is rotated 90 degrees to display what you want. But the question is will all 12 saturdays for three months fit on a page? So maybe a normal table but that depends on the number of departments yuo have.

So build one requirement at the time! :lol:

HenkK

HenkK :netherlands: (BOB member since 2004-03-02)

3

I’m not asking you to build the report, I’m asking for help and advice!

grifter (BOB member since 2013-01-09)

4

What data do you have for the report? Clearly you have a Date, a Department, and an amount of something…anything else? Which version of WEBI are you running? What are you reporting from? Do you have access to the Universe? Is there a calendar table in your universe that may contain a week object? It may help to post an example of your data, and as much information as possible.

Generally, you’re more likely to get help if you’ve tried something already, rather than ask for suggestions as to what you need to do in the first place - hence HenkK’s comment above. “I need to build a report that looks like this” is still a little vague.

Give what HenkK suggested a try and post back if you get stuck.

HTH

NMG

mcnelson :uk: (BOB member since 2008-10-09)

5

I have tried something already but not got very far. I can only get a daily date range per column, I don’t know how to sum it up into a weekly value and display the weekly date.

What I am showing below is what I have already tried. As I said I need to sum the values into weekly values and show the Saturday date only:


              01/01/2013  02/01/2013  03/01/2013  04/01/2013  05/01/2013  06/01/2013  07/01/2013  08/01/2013  09/01/2013  10/01/2013  11/01/2013  12/01/2013   
              ========     ========    ========    ========    ========    ========    ========    ========    ========    ========    ========    ========  
Dept1           283          897          1437        1046        500        777         861         1014         932         878          221         761               
Dept2           287          877          1777        1647        433        634         745         755          466         582          664         634        
Dept3           877         787           1856        1546        560        561         445         523          567         264          628         154        
Dept4           659         897           177         1943        822        221         356         1774         489         265          899         382

Like this:


              05/01/2013      12/01/2013   
               =======         =======   
Dept1           4163              5444               
Dept2           5021              4480                
Dept3           5626              3142               
Dept4           4498              4386

It’s easy enough to put in breaks down a column but how do you do this along the row?

Thanks again

G

grifter (BOB member since 2013-01-09)

6

Try this:

Replace your Date object in the crosstab with this:

=Week([Your Date])

…and see what this does to your report. It should aggregate the measures to what you need.

If you’re happy enough with the week figures, you can replace the weeknumber column heading using an alerter to show the date of the Saturday as the column header in lieu of the Week number.

HTH

NMG

mcnelson :uk: (BOB member since 2008-10-09)

7

I tried that and it seems to successfully group up the data and shows the week number so appreciate the advice thanks.

I would like to now show the Saturday date of each week rather than the week number. No idea yet how to do that but I’ll try and see waht I can come up with.

Thanks again

G

grifter (BOB member since 2013-01-09)

8

Maybe create a variable that tests if a day is a Saturday, like this

=If (daynumber(<date>) = 6) Then "Yes" Else "No"

Then filter on the Yes values.

HenkK

HenkK :netherlands: (BOB member since 2004-03-02)

9

Hi,

try this, create two variables :
[Var] = =DayNumberOfWeek(RelativeDate(ToDate(“01/01/”+FormatNumber(Year(CurrentDate());“0000”);“dd/MM/yyyy”);([weeknumber]-1)*7));

[Saturday] = =RelativeDate(ToDate(“01/01/”+FormatNumber(Year(CurrentDate());“0000”);“dd/MM/yyyy”);([weeknumber]-1)*7+[Var])

Cheers;
Rogerio

rgoulart :brazil: (BOB member since 2011-08-21)

10

Thanks for both replies so far much appreciated.

rgoulart I have plugged in the variables and appears to be showing the Saturday dates I require, although they are ordering in a text format not date, so I 'll need to try and order by date ascending somehow.

Currently it is ordering not in date sequence, and sorting doesn’t work for this either.

G

grifter (BOB member since 2013-01-09)

11

To show the Saturday of the week, use

=RelativeDate(LastDayofWeek([Date Object]);-1)
12

Used custom sort to fix the date sort. I thought I would have to do this manually every time I changed the date range when running the report but it seems to hold.

G

grifter (BOB member since 2013-01-09)

13

The dates I should have in my report are:

14-Dec-13 21-Dec-13 28-Dec-13 04-Jan-14 11-Jan-14

But I get:

13-Dec-13 20-Dec-13 27-Dec-13 04-Jan-14 11-Jan-14

The December dates are one less. Something to do with:

=DayNumberOfWeek(RelativeDate(ToDate("01/01/"+FormatNumber(Year(CurrentDate());"0000");"dd/MM/yyyy");(Week([Query 1].[End Date])-1)*7))
=RelativeDate(ToDate("01/01/"+FormatNumber(Year(CurrentDate());"0000");"dd/MM/yyyy");(Week([Query 1].[End Date])-1)*7+[var])

I tried removing the -1 but it screwed it up, not quite sure what it is I Need to change here.

G[/quote]

grifter (BOB member since 2013-01-09)

14

Have you tried my suggestion?

15

Hi , I would use Mark’s solution. It’s easier to maintain and more elegant. It looks like the first day of the week dor you is Sunday. Both proposed solutions assume the first day of the week are mondays. If it’s the case, I believe that , on Mark’s solution you have to subtract 2 instead of 1, right Mark?

rgoulart :brazil: (BOB member since 2011-08-21)

16

rgoulart :brazil: (BOB member since 2011-08-21)

17

Hi, no not yet I tried rgoulart suggestion first, but I will try your solution Mark. I’m wanting it to show Saturday date so default would need to be Sunday - 1 or -2 if Monday.

G

grifter (BOB member since 2013-01-09)

18

Got a little problem with this solution. Currently I am putting in a date range of 8th of Dec 2013 to 4th of Jan 2014, which gives me this:

http://i132.photobucket.com/albums/q11/Pichost28/BO_Capture_zpsd3b248d6.png

Trouble is because of the -1 in:

=RelativeDate(LastDayofWeek([Date Object]);-1)

I am picking up the Saturday the 7th Dec date, which I don’t want. When I put in a date range I only want it to pick up the Saturday dates within that range, not before it.

just wondering is it possible to pick up the prompt dates that I am using for my date range, if I could do that I could maybe somehow use this in conditional statement using relative date?

EDIT: This might do the trick:

G

grifter (BOB member since 2013-01-09)

19

Any idea what is wrong with this variable code, it says I have a semi-colon missing but can’t see why i would need a semi-colon there (the bit between asterisks where I am doing the condition check for the IF statement):

=if(RelativeDate(LastDayOfWeek([Query 1].[End Date])*)*=UserResponse("Enter End Date(Start):");RelativeDate(LastDayOfWeek([Query 1].[End Date]));RelativeDate(LastDayOfWeek([Query 1].[End Date]);-1))

Edit: I just realised I need the function argument (DOH) but in this case I want to use 0 but it doesn’t like it, maybe I can use something else to return the date.

Edit2: Is the user prompt date a “date” or a string data type?

This gives me an error where I am trying to remove the time stamp:

=ToDate(UserResponse("Enter Episode End Date(Start):");"dd/MM/yyyy")

and this gives error too:

=FormatDate(ToDate(UserResponse("Enter Episode End Date(Start):");"dd/MM/yyyy");"dd/MM/yyyy")

Have also tried saving, closing and rerunning report but still get #ERROR in field???

grifter (BOB member since 2013-01-09)

20

Is there anyone who knows why I keep getting an #error here:

=ToDate(UserResponse("Enter End Date(Start):");"dd/MM/yyyy")

G

grifter (BOB member since 2013-01-09)