How to get labels for rows and columns in crosstab by default 1

[gvg356]

I have a report which needs to look like this
timeperiod 1/1/2012 1/2/2012 1/3/2012 1/4/2012 ...1/31/2012
0
1
2
...
23
but in the database I am missing some, like I have only 0,3,5,6,8,9 but by default I need to get all 24 values(0-23) and even there are some missing dates but by default how can I get dates from 1 to 31...the database fields are logindate(column names), loginhour(row names) and total as summary.

thank you very much

 

[gvg356]

As per the above post how to display 1/1/2012 to 1/16/2012 in one page and remaining in second page..
The issue that I am facing is I need to get rowtotal and columntotal for each page and monthtotal column in 2nd page is like sum(total1+total2)
output should be like
1st page
timeperiod 1/1/2012.....1/16/2012 Total1
0
....
23
total
2nd page
timeperiod 1/17/2012.....1/31/2012 Total2 MonthTotal
0
....
23
total
Thanks

 

[lbass]

Regarding your first post, you need to bring all dates and times into the report for them to appear in the crosstab. So, if you have a table with all dates/times, then you should use a left join from that TO your login table--with no selection critieria on the login table. Select only on the alldatetimes tables.

With regard to your second question, you could insert a group #1 on date (on change of month), and then a group #2 on a formula like this:

select day({alldatetimes.datetime})
case 1 to 16 : "Days 01-16"
case 17 to 31 : "Days 17+"

Insert a group on this and then place the crosstab in the group#2 footer or header section. You will automatically get page totals as long as the group fits on the page (you can uncheck "show cell margins" if necessary. To get the month total, insert another crosstab that omits the dates in the Group #1 footer section.

-LB

 

[gvg356]

My version is crystal reports 11
I am not sure how to write the formula for group#2 could you please write the formula in detail if possible and could you please elaborate on how to get month total also ...
Thanks a lot .....

 

[lbass]

The formula for group #2 is the one I showed above.

For the month total, you would just set up the crosstab just like the other one, without a column field and place it in Group #1 footer.

-LB

 

[gvg356]

Thanks..
While I was about to create a group with the above formula it was throwing me an error..
Ther result of the selection formula must be a boolean

 

[gvg356]

I tried this way but I am stuck at one poinr
I created two sub reports in the main report and in each subreport I used cross tab to generate the totals in each page as of the row total as well as column total...
The output is as follows
1st page
timeperiod 1/1/2012.....1/16/2012 Total1
0
....
23
total
2nd page
timeperiod 1/17/2012.....1/31/2012 Total2
0
....
23
total
As I mentioned above How to get the monthtotal column at the end of second report which is sum(Total1 +Total2)

 

[lbass]

The formula should be created in the field explorer->formula->new and then insert a group on it. You don't need subreports.

-LB

 

[gvg356]

I created formula and I inserted two groups as you said then I inserted crosstab also then I am getting 24 crosstabs. I am not sure if I am doing it correctly or no.
I need only two pages in the first page days 1 to 16 and in the 2nd page 17 to 31 with their totals.
Thanks

 

[gvg356]

I got the report as required based on your advice....I am unable to append the monthtotal column to the report in the second page which contains days 17 to end...Any help is appreciated.....Thanks

 

[lbass]

Okay, I see. Insert a new crosstab in the page header, set up as suggested above, but format the page header to "underlay following sections". Then stretch the page header so that it is page length and so that it aligns properly with the first crosstab. Overlay it on the first crosstab, and suppress the row labels and remove the grid for the labels by going into customized style tab->format grid lines->uncheck "draw" for each unwanted line. Then conditionally suppress the page header so that it only shows up on every other page (even pages) by using a conditional suppression formula on the page header in the section expert:

remainder(pagenumber,2)<>0

-LB

 

[gvg356]

set up as suggested above means creating the two groups too ..because I created only one group with the condition that you gave me above

select day({alldatetimes.datetime})
case 1 to 16 : "Days 01-16"
case 17 to 31 : "Days 17+"
it's working but the problem is in getting the monthtotal column at the end of second page.
Is it possible to it with one group or do I need to create 2 groups and do the procedure u suggested above.

 

[lbass]

I'm sorry. The crosstab would have to be in a subreport placed in the page header for this to work. You DO need the Group #1 on Month so that the Group #2 on days works properly. You can suppress the group #1 header and footer and the details section. In the subreport, you should link the subreport to the datefield in the main report and then in the sub, change the record selection formula to:

month({table.field})=month({?pm-table.field})

-LB

 

[gvg356]

No problem...That worked thanks