Displaying count of records for a given month 1

[maddyyikes]

Using: CR 8.5
P4(proprietary database)
ODBC driver

I have to display the following report:

Department Jan Feb .... Dec YTD(year to date)

Security 500 600 2450

Mainframe 250 100 1780

Intranet 750 450 2000

I need to generate a cross tab report for the same as it would be difficult to represent the above information over 12 months in a standard report format.
I am also unable to understand as to how to display the values for a given month. Please let me know of the same. Thanks in advance.
PS: the "Department" column is not mapped onto one of the database fields directly and I have retrieved the values in this column by mapping it onto the "Budget code" field ( a numeric field) which is present in the database. Hence, please let me know as to how to generate the crosstab report for the same.

 

[lbass]

Since you need to use the department as the row field in the crosstab, you need to show us exactly how department is incorporated in the budget code field.

-LB

 

[maddyyikes]

There are various departments like Client Services having budget codes in the range of 10000 to 30000.Another department, for instance Risk Mgmt. may have budget codes in the range of 20000 to 40000. The range of budget codes are not unique for a given department. However the individual values are unique. For instance Client Services may have the following budget codes: 10000, 12180, 13000.... while Risk Mgmt. may have the following budget codes - 10100, 12000, 12580,... I have already developed the logic(formula) for displaying the departments according to their budget codes by writing a comprehensive code in SQL - (displaying the relevant department names based on individual select statements for each budget code!) I am also able to insert this formula field (formula field name: @dept) in the row section of the cross-tab report. However, I do not know how to fit the months (Jan to Dec) in the column section of the cross tab. The other problem that I face, is in accounting for the no. of problem types encountered (database field: problem_type) by a given department for a given month, in the Summarized field of the CrossTab report. I tried making a formula:
Count({<table name>.problem_type},{@dept}) and tried inserting it in the Summarized field but it did not work. The error that it gave was as follows:
"The Running Total / Summary field could not be created"
However, the formula did work once, but still gave me an error of the following type:
"There are too many records that have been retrieved. Try giving a smaller range as part of the search criteria"
Unfortunately, I cannot give you the list of departments with their respective budget codes, as company policies prevent me from disclosing any information. I have thus, tried my level best to provide you with the information, you require. Please let me know as to what can be done further. Thanks in advance.

 

[lbass]

Place your department formula (we'll call it {@dept}) as the row field. Add {table.date} as the column field, and then click on it and choose "Group Options". Then choose "Print on change of month" and also check "Customize group name"->"use a formula to customize group name"->x+2 and enter:

totext({table.date},"MMM")

Then add {table.problemtype} as the summary field, and choose "count" as the summary. Place the crosstab in the report header or footer.

-LB

 

[maddyyikes]

Thanx a lot!!! That was of gr8 help to me!! But just a few other queries reg. the presentation of cross tab reports.

1. In this report, I need the data only for the current year and want to discard the records being displayed for other years. Currently there are more than 12 cols. (30 cols.) which indicates that the data in the cols. are being displayed for the previous years as well.

2. The col. names - Jan, Feb, Mar,.....upto Dec are not being displayed in the column headings. The row headings however are being correctly displayed (Client Services, Risk Mgmt,....) What could be the reason for this and how could it be rectified?

3. The data is being represented continuously in the horizontal direction. If anyone wanted a printout of this report it might be difficult to scale it to the required size. Is there an efficient way of representing the entire data on a single page, without extending it to the next page (horizontally)?

Thanx in advance!

 

[maddyyikes]

I also have another question:

Is it possible to accomodate the sub-totals for every quarter, for instance,

Here 1Q refers to the 1st quarter(the values in the first quarter col. reflect the sub-totals for the months of Jan, Feb and Mar : 10+10+10 = 30) and 2Q refers to the second quarter and so on....

Problem Jan Feb Mar 1Q Apr May Jun 2Q ..........
Type

Client
Services 10 10 10 30 20 10 30 60

Risk Mgmt. 5 5 5 15 10 5 10 25

Thanx in advance!!

 

[lbass]

1-Limit the records to this year in your record selection formula, e.g.,:

year({table.date}) = 2004

2-If you followed my earlier suggestion, you should see three-letter month names. Highlight the column field (format->crosstab->column field)->group options->customize group name->use a formula to customize the name->x+2 and enter:

totext({table.date},"MMM")

If you still don't see the headings, refresh the report.

3-I would first change the print orientation to landscape (file->printer setup). You can also resize the columns by dragging the edges of the cells.

If this still doesn't work, there is a way to eliminate virtual pages (to the right) and instead print the rest of the columns below the others. First create a formula:

if month({table.date}) in 1 to 6 then 1 else 2

Group on this formula and then place the crosstab in the group header or footer section. Then within the crosstab expert, go to the customize screen tab and check "Suppress row totals", since these now show only totals for six months. If you want to show annual totals by department, add another crosstab in the report footer, using only the row field and the summary, no column.

I'm not sure there's a real advantage to this method though. If you leave the crosstab on virtual pages, you can go to the customize style tab and check "Repeat row labels". The virtual pages will also print out, and the annual total will be correct as well.

4-To get quarterly totals, create a formula {@quarters}:

if month({table.date}) in 1 to 3 then "1st Qtr" else
if month({table.date}) in 4 to 6 then "2nd Qtr" else
if month({table.date}) in 7 to 9 then "3rd Qtr" else
if month({table.date}) in 10 to 12 then "4th Qtr"

In the crosstab expert, add this formula as a column field and drag it into the topmost position.

-LB

 

[maddyyikes]

I did follow your suggestion earlier, but the problem that I face here is that I dont have a <table.date> field present in the database. Instead I have a field (incidentsm1.open.time)(incidentsm1 is the table and open.time is the timestamp) which displays the complete timestamp i.e., YYYY/MM/DD as well as hh:mm:ss together . Now in all of the formulae you mentioned in the previous posts, you have used <table.date> directly. I tried converting the same to <table.date> and extracting the required date by using:
totext(DTSToDate({incidentsm1.open.time}),"MMM")
Does this make a difference in the way the months are displayed on the screen? Also, I have replaced <table.date> by DTSToDate({incidentsm1.open.time}) wherever applicable in your above mentioned formulae. Please let me know if this will help or I need to take a different approach for the same (esp. in representing the month names as I am correctly getting the totals for each month using DTSToDate() function). Thanx in advance!

 

[lbass]

I would use:

DTStoDate({incidentsm1.open.time}) as the column field, since this will give you the correct chronological month order. In group options, then set that to print on change of month and then enter:

totext(DTStoDate({incidentsm1.open.time}),"MMM")

in the customize group name formula area in the same screen.

-LB

 

[maddyyikes]

I tried using the following formula:
totext(DTSToDate({incidentsm1.open.time}),"MMM") as you suggested, but am still unable to display the month names for the current year. Is there any other method by which this can be achieved? For instance, is the function - DTSToDate() making a difference in the display of the related data? Should I use some other date function for displaying the month names? Please let me know of the same at your earliest convenience. You have been of gr8 help!! Thanx yet again, in advance!!

 

[lbass]

I just created a crosstab to replicate your issue. I went into the crosstab expert and used {@strtodate}:

dtstodate({table.stringdate})

...for the column field, then (still in the crosstab expert) while highlighting the column field went to "group options" and checked "Print on change of month" and also checked "customize group name" and "use a formula for group name". In the x+2 area next to "use a formula for group name", I entered:

totext({@strtodate},"MMM")

And this returns "Jan", "Feb", etc. You might need to refresh the report to see the change. Please review these steps and make sure you followed each one correctly.

-LB

 

[maddyyikes]

Thanx a lot ....I am now able to display the month names for the given report. Another clarification.......how do I represent the totals present under each month (for a given dept.) as percentage values instead of absolute values?

 

[lbass]

Easier said than done, but Shelby and Ken Hamady presented a solution for creating percentages in crosstabs in CR versions earlier than 9.0 in thread149-769395. Ken expanded upon this in his most recent newsletter, so you might want to check that out, too.

-LB

 

[maddyyikes]

Using: CR 8.5
P4 database(proprietary)
ODBC driver

Thanx yet again! I will try out the suggestions mentioned in the other threads. As of now I have another immediate requirement to be fulfilled. I have already generated the following report. I require a few clarifications regarding the same:

Explanation of fields used in report:

@Month and @Previous Month: Formulae used to display any 2 consecutive months in a given year.

Prob.: Concatenation of Category and Subcategory fields present in the database. The formula name used to concatenate them is: @cat_subcat

Prob. Description: Represents the database field: incidentsm1.problem.type, where incidentsm1 is the related table being used.

Prob Prob. Description <@Month> <@Previous Month>

security- access violated 10 20
mainframe

NT domain-
password account locked 35 30

Note: This is not a crosstab report but a standard one.

The @Month formula is as follows:
StringVar Array X:= ["January", "Febuary", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
Numbervar Y:= Month(DTSToDate({incidentsm1.open.time}));
X[Y];

The @PreviousMonth formula is as follows:
StringVar Array X:= ["January", "Febuary", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
Numbervar Y:= Month(DTSToDate({incidentsm1.open.time})) - 1;
X[Y];

There are mainly 2 problems that I am not able to resolve currently.

1. The running totals are not getting displayed correctly for any 2 given months, in their respective cols. How should this be done?

2. The "Select Expert" does not give me the option of selecting any 2 consecutive months in a given year (say 2004) It only gives me 2 options:

Option 1: Display records for the current month:

Prob. Prob. July June
Description

Option 2: Display records for the last month:

Prob. Prob. May June
Description


However I do not understand as to how I can retrieve the relevant records for the months of March (@Month) and February(@Previous Month). Please let me know of the same. Thanx in advance!! You've been a world of help!!!!

 

[lbass]

This is a new topic, and should have been a new thread...but anyway, I would set this up with a number parameter {?month}. I personally never use the select expert, but instead go to edit selection formula->record and enter my selection formula there. You would use something like:

{table.date} in Dateadd("m",-1,date(2004,{?month},01) to dateadd(m",1,date(2004,{?month},01))-1

You don't need the variables for the column display. Instead create two formulas:

//{@month}:
monthname({?month})

//{@previousmonth}:

monthname(month(Dateadd("m",-1,date(2004,{?month},01))))

You mentioned "running totals" but did not provide any information about them. If you are using running totals, you must create two, one for each month. Select the field you are summarizing, choose "evaluate using a formula" and enter:

month({table.date}) = month(Dateadd("m",-1,date(2004,{?month},01)))

For the second running total, use the formula:

month({table.date}) = {?month}

Reset on change of group for a group level summary. If you need these at the report level, create two more running totals like these with reset = never. Place the formulas side by side to get the display you desire. This is, by the way, a manual crosstab.

-LB

 

[maddyyikes]

This worked out gr8 for me.....however I still have another problem remaining !

I have generated running totals as suggested by you in my previous threads but am unable to sort the top 10 records in descending order according to the no. of problems encountered for a given month. I do know that it is not possible to sort the running totals field. Is there an alternate workaround possible? Please let me know as to how this can be resolved. Thanx in advance!!!

 

[lbass]

I don't recall recommending running totals to you for any specific problem, nor could I find a recommendation from me in any of your other threads. SynapseVampire recommended running totals for a manual crosstab for a month and previous month type of manual crosstab. If you don't have duplicate records, instead of running totals, you can use formulas like:

//{@month}:
if {table.date} in Date(2004, {?month}, 01) to Dateserial(2004, {?month}+1, 01)-1 then {table.amt}

//{@previousmo}:
if {table.date} in Dateserial(2004, {?month}-1, 01) to Dateserial(2004, {?month}, 01)-1 then {table.amt}

Then when you insert summaries on these, they would be available for topN/group sorts.

-LB