Combining Table and Matrix format in one Report in RS2005

Hello,

I am using RS 2005 trying to create the following report. My report consists of the following columns: Question, Sub Question, N as Number of Responses, All as Average for all responses per given question and sub question, and Ethnicity column which is presented here in a Matrix format with ethnic group as columns and average response as Data values. It looks like my challenge is to combine Matrix format report (Ethnicity column) with a data such as N and All columns which are more like a table format. Any input how I could tackle this is greatly appreciated.

Thank you!

--

1.How often have you done each of the following?

N All F M Asian Multi-cultural a. Worked on a paper or project that required integrating ideas or information from various sources 1134 3.96 3.95 3.99 3.54 4.50 b. Used library resources 1132 4.21 4.26 4.09 4.12 4.33 c. Prepared multiple drafts of a paper or assignment before turning it in 1130 3.90 3.97 3.76 3.80 4.50

-How the source data looks like?sqlsql

Combining date and time

I have 2 fields, one containing just the date and another containing the time. I want to combine them into a proper date/time format. No matter what I try, I lose 2 days in the process. Combining

2005-12-21 00:00:00.000
and
1899-12-30 14:30:00.000

will get me the 19th at 14:30. What am I missing here?

TIAThis was addressed recently in the following thread: link (http://www.dbforums.com/t1203973.html)

Regards,

hmscott|||Thank you. I searched and somehow missed that thread. Bad choice of search words I suppose. I guess my problem was not using varchar. I had tried using convert but used datetime as the type, using a style of 101 for the date portion and 108 for the time. This seems to be working correctly:

Convert(datetime,Convert(varchar(11), DateField, 101) + ' ' + Convert(varchar(8), ReqTime, 108))

Comapring date fields

I am trying to compare two date fields; one is a string and one is a getdate() field. I am trying to get them into the same format so I can compare them. What am I doing wrong? :eek:

select convert(integer, substring(loc_86, 1, 2)) as tmonth,
convert(integer, substring(loc_86, 3, 2)) as tday,
convert(integer, '20'+right(loc_86, 2)) as tyear,
datepart(month, (dateadd(day, -1, (getdate())))) as ymonth,
datepart(day, (dateadd(day, -1, (getdate())))) as yesterday,
datepart(year, (dateadd(day, -1, (getdate())))) as yyear
from ub_chg_tbl join ubmast_tbl
on (ubmast_tbl.patient_nbr = ub_chg_tbl.patient_nbr)
where tmonth = ymonth and tday = yesterday and ty= yyeartry this as a template:


declare @.str char(06)
select @.str = '060228' -- Feb 28 2006
select convert(datetime,@.str)|||How about

where convert(varchar(10), dateadd (dd, -1, getdate()), 101) = loc_86

Depending on your delimiter, of course.|||How closely do you want to compare them? Getdate() returns results in milliseconds. Are you just trying to match on the day?|||I'm only trying to match the day.

When I use this script:

Convert(VarChar(12),GetDate(),112) as '112'

I get a date in a format of YYMMDD.

I need the date in the format of MMDDYY. How can I do this?|||Open BOL. Search the index for CONVERT. Read the topic "CAST and CONVERT". All the formats are given there.|||There is not a format listed for MMDDYY. Is this really not possible? :shocked:|||You should NOT be storing date values as strings. Possibly the most common noob DBA mistake of all time. I strongly urge you to change your datetype to datetime.

That said, this should work for you:select *
from YourTable
where datediff(day, getdate(), convert(datetime, left(DateString,2) + '-' + substring(DateString, 3, 2) + '-' + right(DateString,2), 10)) = 0|||I wish we could change it. We are in healthcare and this database is federally regulated, so we are not allowed to change the format of the fields.|||Now I am receiving this error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.|||Because you have an invalid date in your table, because you are not using datetime datatypes.

Format your datestring as 'YYYY-MM-DD' and run it through the ISDATE() function to find the bad records.|||There is not a format listed for MMDDYY. Is this really not possible? :shocked:SELECT Replace(Convert(VARCHAR(10), GetDate(), 1), '/', '')-PatP|||Now I am receiving this error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.As Sallah once told Indy: Bad Dates.

The following shows how to weed them out relatively painlessly:CREATE TABLE #patp_date_demo (patp_date CHAR(6))

INSERT INTO #patp_date_demo (patp_date)
SELECT '122505' UNION
SELECT '022900' UNION
SELECT '022901' UNION
SELECT '063104' UNION
SELECT '131211'

SELECT patp_date
FROM #patp_date_demo
WHERE 0 = IsDate(Stuff(Stuff(patp_date, 5, 0, '-'), 3, 0, '-'))

DROP TABLE #ptp_date_demo-PatP|||Thanks so much! The following script worked:

REPLACE(CONVERT(varchar(10), DATEADD(day, - 1, GETDATE()), 1), '/', '') AS Yesterday

column with a number and 2 decimal like 10.10 or 150.30

Hello,

How should I create a column to save data with the folowing format 10.10 or 10.20 or 10.30 or 150.30 or 10 or 150.
It is basically process step in a diagram flow.
I tried with decimal but with 10.10 , it removes automatically the 0.

ThanksYou could set the precision to 2in the design view for the particular column. Alternatively, this kind of formatting is best done from your presentation layer. There are different varieties of DataFormatStrings available to preent the data in different ways.|||

Hi

You column type must be decimal(18, 0) .

change to decimal(18, 2) .

Column Headings on Matrix

Hi everyone, this may be a very stupid question or a stupid way of doing
things, but I have a report in a matrix in the following format:
--Year
--Month
Customer--Part--Currency--=Sum(Amount)
The problem I'm having is that above the columns, there is one textbox
merged across all 3 columns and across both rows, but no column headings
appear. Is there anyway to have column headings? We've experimented with
adding a table inside that merged textbox and adding static headings into it,
but this table will not export into Excel.
Any help would be greatly appreciated.Instead of a table, try a rectangle. You can manually place textboxes
inside the rectangle. (Not as convenient as a table, but it should work
with the Excel renderer).
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
news:59C51C17-AD76-499F-AE6C-892132117242@.microsoft.com...
> Hi everyone, this may be a very stupid question or a stupid way of doing
> things, but I have a report in a matrix in the following format:
> --Year
> --Month
> Customer--Part--Currency--=Sum(Amount)
> The problem I'm having is that above the columns, there is one textbox
> merged across all 3 columns and across both rows, but no column headings
> appear. Is there anyway to have column headings? We've experimented with
> adding a table inside that merged textbox and adding static headings into
it,
> but this table will not export into Excel.
> Any help would be greatly appreciated.

Column Comparison

Hi,
I work at a university and am dealing with government data. We
obtain a recordset from them called ISIRS. These are in a fixed length
field format that I put into a table. This table has upwards of 450
fields (there was no better way to break it up, no relations exist).
Each student can have multiple ISIR records, each with a transaction
number always incrementing. I was hoping someone could help me out
here...
I need to be able to compare the current ISIR with the students
previous ISIRs in T-SQL. I could do this in code(.NET), but I am using
SQL Server Reports to present this data to the user and need a stored
procedure to do a column by column comparison and indicate the
differences in the row it returns.
psuedo-code:
returnRow = requested isir row
for each row in student's isirs
for each column in row's column
if returnRow[column] <> row[column] then
returnRow[column] = returnRow[column] + '#'
end if
end for each
end for each
I hope this makes sense. If we were using SQL Server 2005, I could do
this using CLR SP's, but we are on 2000.
Thanks for your help.Even with SQL Server 2005, I wouldn't reccomend doing this in a stored
procedure, becuase it would still involve hard coding the column names.
With 450 columns, it would make more sense to write a client side
application that iterates through each column programatically using the
column object reference number. The pseudo code you provided is similar to
the actual ADO.NET needed to do this.
http://support.microsoft.com/defaul...kb;en-us;310107
You said that each student can have multiple records, so you will need to
identify a primary key (ex: SSN + CourseNumber) to join each new records
with the appropriate new record.
"craiggwilson" <craiggwilson@.gmail.com> wrote in message
news:1140533890.693887.38640@.g14g2000cwa.googlegroups.com...
> Hi,
> I work at a university and am dealing with government data. We
> obtain a recordset from them called ISIRS. These are in a fixed length
> field format that I put into a table. This table has upwards of 450
> fields (there was no better way to break it up, no relations exist).
> Each student can have multiple ISIR records, each with a transaction
> number always incrementing. I was hoping someone could help me out
> here...
> I need to be able to compare the current ISIR with the students
> previous ISIRs in T-SQL. I could do this in code(.NET), but I am using
> SQL Server Reports to present this data to the user and need a stored
> procedure to do a column by column comparison and indicate the
> differences in the row it returns.
> psuedo-code:
> returnRow = requested isir row
> for each row in student's isirs
> for each column in row's column
> if returnRow[column] <> row[column] then
> returnRow[column] = returnRow[column] + '#'
> end if
> end for each
> end for each
> I hope this makes sense. If we were using SQL Server 2005, I could do
> this using CLR SP's, but we are on 2000.
> Thanks for your help.
>|||That's what I was afraid of. The front end to this is an ASP.NET /
WindowsForms application, but the report is straight from a SQL Query.
I appreciate your help.|||I have had some success generating brute force SELECTs to do this sort
of thing by writing queries against the system tables (or
INFORMATION_SCHEMA views).
Start by writing a SELECT that tests one column of the table and
returns the key and both the current and prior values. What you need
is 450 such SELECTS with just the column names changed.
So then write a SELECT against syscolumns (or
INFORMATION_SCHEMA.COLUMNS) that creates one long string matching the
SELECT you just wrote, but concatenating in the column name in place
of the one you used in the example.
Obviously this is just a starting point, but I have used such
techniques successfully.
Good luck!
Roy
On 21 Feb 2006 06:58:10 -0800, "craiggwilson" <craiggwilson@.gmail.com>
wrote:

>Hi,
> I work at a university and am dealing with government data. We
>obtain a recordset from them called ISIRS. These are in a fixed length
>field format that I put into a table. This table has upwards of 450
>fields (there was no better way to break it up, no relations exist).
>Each student can have multiple ISIR records, each with a transaction
>number always incrementing. I was hoping someone could help me out
>here...
>I need to be able to compare the current ISIR with the students
>previous ISIRs in T-SQL. I could do this in code(.NET), but I am using
>SQL Server Reports to present this data to the user and need a stored
>procedure to do a column by column comparison and indicate the
>differences in the row it returns.
>psuedo-code:
>returnRow = requested isir row
>for each row in student's isirs
> for each column in row's column
> if returnRow[column] <> row[column] then
> returnRow[column] = returnRow[column] + '#'
> end if
> end for each
>end for each
>I hope this makes sense. If we were using SQL Server 2005, I could do
>this using CLR SP's, but we are on 2000.
>Thanks for your help.

Color Property Formatting not working correctly

Hi,

I have a problem with a conditional format of the Color property in a matrix report. When the value of the textbox is greater than 0, the color should be Red otherwise it should be Blue. This is implemented via an IIF statement and works perfectly in Preview in Visual Studio 2005.

When the report is deployed, the formatting appears to be ignored and all values are the default value of Black.

Anyone else experienced this problem and, if so, is there a workaround?

I wish I could help you, however I cannot.

I can however state with confidence that posting 7 times in roughly 1 hour about the same thing is not the best way to get the attention of those who can.

Just my

|||Yea I can't help you either. . .|||

I agree with the sentiment in the above posts - spamming a forum is not the best way to get an answer.

What would also help is if you posted the expression you are using - without it we are just guessing.

Color Property Formatting not working

Hi,

I have a problem with a conditional format of the Color property in a matrix report. When the value of the textbox is greater than 0, the color should be Red otherwise it should be Blue. This is implemented via an IIF statement and works perfectly in Preview in Visual Studio 2005.

When the report is deployed, the formatting appears to be ignored and all values are the default value of Black.

Anyone else experienced this problem and, if so, is there a workaround?

Thanks in advance

If I am not wrong, matrix conditional formatting is applied by default at the details. You may need to do scope it at the right level, e.g.; =Iif(SUM(<field name, "group name")>0, "Red", "Blue"). Also, download your deployed report definition from the Report Manager (General report properties) and make sure the expression is there.