Recent content by yehong

Hi, Is it possible to create a dynamic alias name based upon a field value? For exp: Select id, case when month=1 then cnt_of_tkts end as month ,case when month=2 then cnt_of_tkts end as month ,case when month=3 then cnt_of_tkts end as month from mytable; Here month is a column name of a table...

Thanks chamilz. It works perfectly. I have another question. Would it be possible to change the order of months in 'select' statement based upon the order of months from another table? Like I have another table that always has current month -12 months i.e, month year 03 2008 02 2008 01 2008 12...

Hi Guys, I am on a tight deadline and would really appreciate if you guys can provide some input. I am using SQL Server 2005. I have following table: Year Dept Month Tkt_Cnt 2008 R&D 02 2 2008 R&D 03 5 2008 R&D 01 6 2007 R&D 06 3 2007 HR 05 8 2007 HR...

Alex, thanks for sample. I am thinking about using pivot logic. I have searched all around but could'nt find any sample for my situation. I am going to create a new post for that.

Hi Guys, I am also having same problem. I have a table that I need in a pivot form. Should I create a new post or just post my question here?

Hi, Is it possible to create an alias from a concatenation of a column and a string or another column? For exmple: Select ID + ' ' + FName as ID +'2008XYZ' from employee; Result: (say ID=1001) 10012008XYZ <--- column alias 1001 John <--value I have trid but getting errors. I am using...

SQL Server 2005. thanks.

Hi guys, I have a sample table like the following: Unit, Year , SubUnit , Category , Jan_Cases Feb_Cases , March_Cases... Dec_Cases I have data for multiple years. I need to select data based upon current month -12 months. For example if current month is March, then the resulting columns...

I am trying to create a report in CR2008. The database is SQL Server 2005. The report is pulling data from one table that has say 10 columns and some columns are defined as xml datatype. All the xml columns are appearing as a memo fields on the report. I want to be able to do a record selection...

Hi guys, I have a report in which I am currently displaying dynamic color of rows by this formula: //@dynamic color if (recordnumber mod 2) = 1 then crwhilte else crsilver) It works fine but only if I don't supress duplicate records. But it does not work when I use this formula to supress...

Hi Guys, I am on a tight deadline. I have a query that returns data similar to the following: State City JanSum FebSum MarSum... DecSum CA LA 1000 500 300 ... 200 CA SF 500 500 200 ... 300 TX Dallas 300 100 50 ... 150 TX...

Hi, Alex. really appreciated. i will give it a try and let you guys know the results.

FYI - CR2008 now allows "Optional" parameters.... Even 2008 can not handle empty parameter values. I tried to set 'Optional Prompt' option, but that parameter can not be used in any formula.

Thanks for your response, Alex. The problem is that the table is designed in that way. And the number of rows for each id could be numerous. So, there needs be a dynamic code that gets a single id and loops through the table for all of its rows and creates a one horizontal row. I can do it...

Hi, I have a table whose data is in the following format: ID Column1 Value1 1 State California 1 City Los Angeles 1 Dept HR 1 EmpCount 50 2 State New York 2 City New York 2 Dept Accounting 2 EmpCount 30 2 SubDept...