First, is there a reason you are using subreports (I assume 7 - one per weekday) rather than bring all the dat ainto the main report and drilling into as you desired?

 

You also may be able to do what you want by running an on demand sub report attached to an object that overlays your other subreport. It would esentially be a copy of the existing subreport with the same links but be in "detail view" (no suppression) when it opens. I have not tried ot the overlapping process but have added on-demand sub reports as links onto other objects with success as a way of mimicking a user experience of drill down.

I should have been clearer that I do not believe there is a way to force a sub-report to move change its drilldowngrouplevel when you launch it as a seperate tab from the main report. THis is why I was giving alternative solutions to make it appear as if that what was happening as I am guessing your end user(s) does not really care about the technical aspect, just how it interacts for them.

if you don't have other things in the main report you can get your 7 'columns' without subreports.

If you want to keep your same existing sub-report process going, did you try using a 'second' on demand sub report as your 'drill down' function?

look of a calendar)?

If you can use a chart you could do grouping on the weekday and drill straight into the data from say the bar in the chart.

If you have to use the boxes you could do that. Just don't want to explore it if it is not necesary.

//Monday

if weekday(table.datefield)=2 then 1 else 0

//Tuesday

if weekday(table.datefield)=3 then 1 else 0

etc.

 

Place your 7 boxes in the report header

Add a new sub report

Name =Sunday (or whatever)

set it to an 'On demand subreport (similar to a hyperlink)'

Add you links

Shrink the size of this sub report width to fit inside the sunday box and place it inside your 'sunday box'.

right click on it and select 'format sub report'

select subreport tab

click on the formula field 'on-demand subreport caption'

add a formula here

Totext(SUM( mailto:%7b@Sunday%7d%29,0 - {@Sunday}),0 )

 

Click OK

Your hyper link (in the main report) should now be the Sunday count from the main report

You can now design the sub report to look anyway you want.

Repeat for the other days.

I would get sunday exactly like you want it then just make copies of it for the other days. It would only be making changes to the select or links.

 

This is one approach.

 

 

I assume you are allwoing a user to choose the month or it is dynamic in some fashion?

Is the number shown a total for a specific day of the month?

When a user clicks on the calendar are they drilling into that calendar day?

 

Can you write a stored proc as your data source?

i think the real issue is that your looking at a full month of data and the first of each month may be any weekday. Therefore your sums, which would be based on day of the month need to slide between boxes and then roll up from there.

Correct?

you will need to group by the week of the year so add a formula field to get that value

//weekofyear

datepart("ww",table.datefield)

group on this formula field

move your week day boxes into the group header

change your summary displays to use the group condition in the sum

each of your seven sub reports will need to alter the link to pass the group value (of the week of the year, the date range and the weekday)

get your month calendar set first and alter your months to make sure this is dynamically doing what you need it to for any of the months.

 

parameter? lastfullmonth?

//sr_weekofyear

datepart('ww',table.datefield)

in the main right click on the subreport and select links

select the @weekofyear field form the main report and link it to the @sr_weekofyear from the subreport

 

In the subreport this will appear as a part of the select statement

your overall select statement will be identical for all 7 subreports except that you will change which weekday value to limit

 

Just the number of the day of the month or something else?

Do you always have at least one value/row for the entire week?

If not that whole 'row' (or group) would not exist.

this wiorks if you have data for each day-not sure how to fill in the missing days without a calendar table.

I do not think that Excel will support an outer join as a datasource for (XI)

 

Make 7 formula

//MDforSunday

if datepart("w",table.datefield)=1 then totext(datepart("d",table.field),0)