crosstab column headings 1

[JohnEck]

I have a cross tab report that runs for a rolling 12 month period. right now I use a frmola that coverts order date to the first of the month and then uses that as a column heading. This give me the proper order for whatever period the report is run. I have had complaims that they want a more descriptive column heading. They want the month name. Is there a way use one formula for the column heading and display something different in a cross tab. I have considered a manual cross tab but that isn't realistic because the order of the columns changes each month. Any help would be greatly appreciated.

 

[jjd231]

Hey JohnEck,

I had almost the exact same situation. Check out a thread I posted a couple of weeks ago. If you scroll through it you will see the formulas for the headings that will change as the month changes. thread149-599154

If you need any help with an explanation of the code just tell me.

Josh

 

[jjd231]

The specific post you are looking for in the thread is the thirteenth one down. It is the one by lbass on July 14th. I did have to use a manual cross tab though.

Josh

 

[JohnEck]

Thanks for the help. This will be very useful in the future. But this is a no cost report for external customers and the effort to build this would not be worth it.

 

[sbajoria]

I want to have a cross tab report where data
will come rom three tables like :
Table Name - fields
emp - empid,name,grade(e.g data : A,B,C)
category,subcategory
category - category ( e.g. data :
Gen,SC,ST,OBC )
subcat - subcategory ( e.g data :
Woman,Min,PH)

i want a report in this format

A B C

Gen
Woman
Min
PH
SC
Woman
Min
PH
ST
Woman
Min
PH
OBC
Woman
Min
PH


my prob is that i am getting the format ,
but if data pertaining to a particular
subcategory or category is not in the
employee master table it is not
displayed.for e.g : if no employee under
Category : Gen and Sub Category : PH exists
then PH is alltogether not displyed,but i
want that it should display 0 in the
report.pls help me .its urgent

 

[JohnEck]

Use left joins and sums of this formula (if ISNULL(0) else 1) to get your counts. it is not the most efficient but it does work.

 

[sbajoria]

i cant get u .can u pls help me by explaining in details.i am new to cr.pls help.

 

[JohnEck]

You need to add a table of Grades.

Your report should have 4 tables

Grades
Category
SubCategory
Emp

In the linking expert clear all the links
put the first three tables on the left side of the window
link them to the Employee table. Select the link options for each link and make it a left outer join.

Create a formula

If ISNULL(Employee_ID) then 0 else 1

Your cross tab should have two row selections
Category and Subcategory

One cloumn Selection
Grade

You summary should be a sum of the above formula.

Give this a try and let me know what results you get.

 

[sbajoria]

i created the report following the steps you mentioned.when i was creating link of the tables with the Employee table , first link with the table was ok, but second and third link creation gave me the following messages :

"The link you added causes a link configuration with multiple paths leading to the same table.you may want to consider reversing the links.

Your current link configuration contains multiple starting points.please be advised that this is generally not supported."

I got the same output as before.i am badly stuck with this problem.its sucking..... my project deadline is just few days.... can u figure out where lies my problem ???

i

 

[lbass]

Not sure of what fields are available for linking with each table, but you should either be using left joins FROM the Employee table to each of the other tables, or you could use a left join from the Employee Table to the Category table, and a left join from the category table to the subcategory table. This assumes all employees are represented in the Employee table, but might not be present in the other two.

-LB

 

[JohnEck]

I have found it. You need to create a view that is a full join of the SubCategory and Category tables. So using the sample data provided you get a single table with 12 rows. You need to include both the Category and subcategory IDs and the Catagory and sub Category REferences. Then you join from the Employee to the combo table using a right outer join. Then you build the cross tab. It does work if you want I can email you an Access data base and the report I used to verify.

 

[sbajoria]

it will be very kind of you if you can email me the database and report at rgbinfotech@yahoo.com.

thanks in advance
sandeep