Crosstab formulae in columns 1

[nb4884]

Hi All,

I have a cross tab report with about 15 fields as rows and 1 summary. This summary displays sum of costs.

I had 2 formulae field in Columns: 1 for Year and the other is actually formulated in a way that it makes 4 columns.

So basically I have Year1C1, Year1C2, Year1C3,Year1C4 and then 4 cols for Year 2.

Now I want to add another formula but want it to display next to Year1C4 , ie this new formula should be Year1C5 but when i created a formula and added it under columns (now there are 3 fields in columns in Xtab) it displays 3 levels.

However I want only 2 levels 1 for year and other for formula fields. Like wise I have to build 3 more formula. If it keeps on increasing the levels , I am afraid it will have 6 total levels but I want only 2.

How do I place these 5 formula in columns to have only 2 levels?

Thanks

 

[nb4884]

Any suggestions, please let me know if you need more info, Thanks

 

[lbass]

Please identify your CR version (always) and show the content of all of your current column formulas, including the new one.

Crosstab columns nest within each other, just like groups in a main report. So do rows. It may be that a crosstab is not the best solution for what you want to do. What are the additional columns you want to add--I mean, conceptually. What is the report intended to show?

-LB

 

[nb4884]

Version XIR2

Rows are just the normal project fields.
Summary is the number of employees.
In columns, i have a formula for year , which actually calculates emps for this year and next year.

I have another formula in columns, which calculates emps locationwise. That is if region = north then deparment HR, like wise there are 3 deparmets meaning 3 columns. Now i want to add few motre columns like if region = all but not in north and north east then department. Similarily there are 2more formulae. Is there a way to do this in crosstab? The reason i want to do it in xtab is since there is a formulae for year in columns and if it was standard report then ill have to add year condion in each formula for column, about 10 of them. Can you think of any other option?
Thanks a lot

 

[lbass]

Please show the actual content of all column formulas.

-LB

 

[nb4884]

Have 2 Column Formula:

1) Column formula for year:

if ({query1.hire Date}) in date(2011,04,01) to date(2012,03,30) then "Year 1" else
if ({query1.hire Date}) in date(2012,04,01) to date(2013,03,30) then
"Year 2"

2) This is for emps location wise . I have 10 locations 1-10 and this location is also the parameter. Each location has some departments A-Z could be anything in a particular location.

Now I created a formula (Loc1) for location as:
if location = ?location and
(Department = 'A' then 'A' else if Department = B then 'B' else Others)

And placed this formula in Columns under the year formula. Till this it was fine.

But now I want to add more columns and I created a formula but when i placed it under the Loc1 it nested those columns and the whole design got screwed up.

Anyways, my Loc2 formula looks like :
if Location in 1 to 10 and Location not egual to ?Location then department (this will consider all the depts A-Z)

My third formula Loc3 is If Location = 10 and Department = F then F

Finally Loc4 =
if location = 6 then Department (this too considers all depts A-z)

I understand this is a complex design.

Is there a way to get a cross tab on this? If I were to build standard reprot I guess performance will be very slow.

Summary Formula: {query1.employee number}.

Here is what the design will look like

Proj Year 1 Year 2
Loc1 Loc2 Loc3 Loc4 Loc1 Loc2 Loc3 Loc4


Thanks

 

[lbass]

Is there a reason you are paraphrasing rather than showing the actual formula? Is it in a non-English language? I would like to see the actual formulas, not your descriptions of them.

-LB

 

[nb4884]

Here is the formula:
@Year:

Column formula for year:

if ({query1.hire Date}) in date(2011,04,01) to date(2012,03,30) then "Year 1" else
if ({query1.hire Date}) in date(2012,04,01) to date(2013,03,30) then
"Year 2

then i need to display all these formulae below @Year but dont want them nested(need only 2 levels , one for year and one for all the below)
Loc1 works well , then as soon as i added Loc2 it got nested. I did not yet create for Loc3 and Loc4 but the description is above.

Loc1
if
{?Location} = {Query2.Location} and

({Query2.Department}) = "A" then "A" else if
{Query2.Department} = "B" then "B"
else
"Others"

Loc 2
If {Project.Location} in "01" to "10" and
not({Project.Location} in ["{?Location}" , "5" , "6" ] )
then {Query2.Department else ""

Please note that ?Location is a filter for Project.Location

Havent created formula for Loc3 and Loc 4 , but this is how it would look.

Loc3

If {Project.Location} = "10" and
({Query2.Department}) = "F" then "F" else "others"

Loc4

If {Project.Location} = "06" then
({Query2.Department})


Summary Formula: {query1.employee number}

Let me know if you need more info,

Thanks

 

[lbass]

What is the purpose of the {?Location} parameter--I mean conceptually?

If each of these Location formulas were expected to return non-overlapping records, you could use one formula that incorporates all of these criteria to return results like "Loc1", "Loc2", etc., for use as a column field. But it appears that there is overlap, e.g., the same records could appear in Loc2 as Loc3 if = 10. So a different approach would be to use the year column, and then add separate conditional formulas for multiple summaries, e.g.,

If {Project.Location} = "10" and
({Query2.Department}) = "F" then
{table.employeeID} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything. Remove the tonumber if the employeeID is a string. Then insert as a summary and use distinctcount as your summary field.

You can then go into the customize style tab and set the summary fields to display horizontally with labels that you can tailor to your needs. Repeat for each condition. These summaries will automatically nest within the appropriate year.

-LB

 

[nb4884]

Thanks LB , looks like this might work.

Quick Question , with this approach do you mean I will have to create 2 different summary formulae for Loc1 as well?

Just asking as my other report has eight departments like Loc1 has 2 depts. In that case i guess in my other report I will have to create 8 summary formulae in addition to Loc2,Loc3,Loc4

Also what does @null formula do?

Yes my groups are overlapping and the conceptual meaning of ?Location is they want to see report for the location they choose, (atleast for few columns).

Thanks

 

[lbass]

Yes, they would have to be separate summaries if you want separate results per department. If they didn't overlap, it would be much simpler.

By using {@null} you can insert a count or distinctcount on a conditional formula and not pick up the default value (for records that don't meet the criteria).

-LB

 

[nb4884]

Thanks LB,

I should have closed this , but ran into another issue.

I did my columns and few rows and summaries it worked well.

But I had 2 rows for dates when I added those the numbers in summaries got screwed.

In rows I had project info, now the 2 dates formula that were added to rows is are Maxdate1 and Maxdate2

@date1 = if Query1.Stage = "1" then totext({query1.finishdate}) else ""

Maxdate1 = Maximum((@date1), (Query2.ID))

Maxdate2 is same as above only that the Sage = 2

Just to let you know there are multiple stages in a project. And i want dates for those 2 stages.

I used Max date formula thinking that if it was a standard report, I would do a max formula and place the summaries in gp footer and hide details.

Do you think there could be anyother option?

Also , I tried to simply add @date1 formula instead of Maxdate1 formula. It gave me 2 rows for date for each project. One is the one I want and other is empty Also at the same time for summary columns it gave me 2 rows for each column ,one against the date is the same I want but the other one which has an empty row for date is some kind of sum which i dont want.

Hope I was able to explain the scenario :

Project date1 employees
A 03/02/2012 4598
"blank" as empty 546487909

In the above example i dont want to display the second row.


I know I should have stared a second thread for this but explaining the situation would have become complex.


Thanks

 

[lbass]

if there is only one date per stage, then the row field should be based on a formula like this:

if {Query1.Stage} in ["1","2"] then
{query1.finishdate} else
date({@null})

You should not be converting to text either.

-LB

 

[nb4884]

Thanks LB, the situation remains the same:

I am still seeing a row with empty row header and all a big number for employyes nest to it. I really want to display only the row that has date on it and how do i suppress the row that has empty label ("blank" as empty)

Project date1 employees
A 03/02/2012 4598
"blank" as empty 546487909
B 04/04/2012 4782
"blank" as empty 844301288

For example if there are 100 projects. I see those 100 project Names, Project Manager, but when it comes to dates and emps it shows me an additional unwanted row.


Also I added the above formula for 2 stages, 1 & 2
Now it gives me

Project date1 employees
A 03/02/2012 (stage 1) 4598
04/06/2012 (Stage 2) 4598
"blank" as empty 546487909
B 04/04/2012(stage 1) 4782
07/08/2012(stage 2) 4782
"blank" as empty 844301288

Again I dont want to display the empty row also wanted 2 dates next to each other.

Sorry for taking too much of your time .


Thanks LB

 

[lbass]

If you want the dates next to each other, it suggests that you are really looking for two more summary fields, one for each date--not another row field.

-LB

 

[nb4884]

Thanks LB, for all your valuable suggestions