floor function

I am trying to pull the number preceeding the decimal, but I want my output in a fixed lengh. Here is what I tried thinking it might work, however it did not.

sf_retail = right('000' + floor(cast(labsf.last_retail_price as varchar)),3),

the number I am running this against is '0000001.45' I would like my output to read '001'.....I am getting only '1'

Any suggestions?We just did something like that...

Check out...

http://www.dbforums.com/t987264.html|||Thanks, that was helpful. I ended up using:

sf_retail = right('000' + convert(varchar(3), floor(labsf.last_retail_price)),3),

Flat Files Containing Dates

Hi everyone.
I'm trying to use a Flat File Connector to read in a fixed field width file that contains some date columns.
The problem is that the date column is in a CCYYMMDD format (with no delimiters) so that todays date, as an example, would be 20050711.
When it attempts to import the file it fails due to a "Data Conversion Failed" error. I can't find any way to specify the format of the column in the FFC dialog so my only option appears to be read in the column as a string and transform it later.
Is that correct?
Steve
Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie|||Thanks Jamie, that's just what I was expecting.
Steve
|||

Jamie Thomson wrote:

Steve,
It sounds like it is, yes. Your other option is to write a custom connection manager and source component but that's like using a sledgehammer to crack a nut.

-Jamie

Or you could also use a script component as a source. Again, it may be overkill!

-Jamie|||Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

|||Thanks for those replies guys.
I'd like to create a derived column transform programmatically using the SSIS object model. I can't find any help in BOL regarding this - but I've managed to get this so far, which creates the derived column transformation object (the dataFlow object is a MainPipe object created elsewhere):

DTSComponentMetaData90 DerivedColumn;
DerivedColumn = dataFlow.ComponentMetaDataCollection.New();
DerivedColumn.Name = "DateTransform";
DerivedColumn.ComponentClassID = "DTSTransform.DerivedColumn.1";
CManagedComponentWrapper instance = DerivedColumn.Instantiate();
instance.ProvideComponentProperties();
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

The problem I have now is that I don't know how to create new columns from old columns ( as I will need to do in my case ). I have used other components which have mapped the virtual columns from the input to the output, so I'm assuming it's something similar, but I can't get it to work.
I've even tried creating a transform in the BIDS and then opening the package in code to see what the object looks like, but some of the properties were read-only and must be set another way. I'm really stuck now so any help would be really appreciated.
Thanks.
Steve
|||Steve,

To create a new column from an existing column you need to add an output column to the derived column transform (InsertOutputColumAt) and then set the FriendlyExpression (or Expression) custom property on that column (SetOutputColumnProperty). The FriendlyExpression would be something like LEFT([oldcolname], 5) to take the left 5 chars of the [oldcolname] column (assuming the oldcolname column was a string or wstring). You could use the expression property but it isn't as obvious and you need to get the existing column's lineage id (e.g. LEFT(#27, 5) if 27 was oldcolname's lineageid). Additionally, you have to set the virtual input column's usage type (IDTSDesigntimeComponent90::SetUsageType) to read only to tell the dataflow that this component needs to use this column for reading.

HTH,|||I tried this but got following error:

Derived Column [2497]: An error occurred while attempting to perform a type cast.
thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com

|||

KirkHaselden wrote:

Looks like ISO 8601 sans the '-' character. You can write a simple derived column expression to parse this out and convert it to a date. Like you say, just retrieve it as a string the new column will be a date.

Here's one way to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date Derived Column 1 20050112 1/12/05 20031122 11/22/03 20050509 5/9/05 20010101 1/1/01 20000301 3/1/00 20021003 10/3/02 20022002 2/20/02 19631003 10/3/63 19621002 10/2/62 20051111 11/11/05

To be more specific, I used the above idea and wrote this expression:
(DT_DATE)(SUBSTRING((YYYYMM + "01"),6,2) + "-" + SUBSTRING((YYYYMM + "01"),8,2) + "-" + SUBSTRING((YYYYMM + "01"),1,5))

This throws a cast exception.
Any suggestions?

thanks,
Nitesh Ambastha
nitesh.ambastha@.csfb.com|||May be the cast error is due to the fact that input YYYYMM can be null or empty string. Can someone suggest a better expression? Or I have to write a script?|||

What do you mean when you say it "throws a cast exception"?

Have you tried entering this expression in the derived column UI to see if it gives an error message?

If you think the input column might be null or empty, you could check that with ISNULL() or LEN() calls first using a conditional operator.

sql

Flat File With Fixed Length Header and No Delimeter

Hi,

I'm trying to extract data from a Flat File which is as fixed length as they come. The file has a header, which simply contains the number of records in the file, followed by the records, with no header delimeter (No CR/LF, nothing).

For example a file would look like the following:

00000003Name1Address1Name2Address2Name3Address3

So this has 3 records (indicated by the first 8 characters), each consisting of a Name and Address.

I can't see a way to extract the data using a flat file connection, unless we add a delimeter for the header (not possible at this stage). Am I wrong?

Any suggestions on possible solution would be much appreciated - I'm thinking Ill have to write a script to parse the file manually.

Thanks in advance,

Scott

Do you need the data in the first row?

You can just ignore it by setting the "Header rows to skip" setting to 1.

K

|||

Yes. Essentially the file is just one row..Which would include the header details (number fo records) then all of the fixed length records follow (on the same line)

Scott

|||

Scott,

Given the unstructured nature of this file I think you will have to parse it out yourself in a script task. This isn't as daunting as it sounds. First clue I can give you is that it will have to be an asynchronous script task.

You can still import it into the pipeline using a Flat File Connection Manager though. It'll be a 1-column, 1-row file that's all.

-Jamie

|||

Using a script component of type source should be easiest. A little example follows.

Here is my sample file, representing an 8 byte header, followed by three rows of two columns, 10 and 20 bytes respectively.

00000003A234567890B234567890C234567890D234567890E234567890F234567890G234567890H234567890I234567890

The results table will look a bit like this-

Name Address
A234567890 B234567890C234567890
D234567890 E234567890F234567890
G234567890 H234567890I234567890

You will need to create the two columns in the script component , Name and Address as DT_WSTR 10 and 20 in length.

Now the code-

Public Class ScriptMain

Inherits UserComponent

Private stream As StreamReader

Public Overrides Sub CreateNewOutputRows()

Dim headerRecordCount As Integer

Dim recordCount As Integer = 0

'// Get filename from connection, using full acquire method

Dim filename As String = CType(Me.Connections.Connection.AcquireConnection(Nothing), String)

'// Open source file

stream = New StreamReader(filename)

'// Reader header block, 8 characters

Dim headerBuffer(7) As Char

If stream.ReadBlock(headerBuffer, 0, 8) = 8 Then

'// Store record count for later use in validation

headerRecordCount = CType(New String(headerBuffer), Integer)

Else

Throw New Exception("Invalid file format, header not valid.")

End If

With Output0Buffer

While stream.Peek > 0

'// Add data rows

.AddRow()

.Name = ReadColumn(10)

.Address = ReadColumn(20)

recordCount = recordCount + 1

End While

'// Close down buffer

.SetEndOfRowset()

'// Check record count

If recordCount = headerRecordCount Then

Me.Log(String.Format("Header row count ({0}) matched toital rows found.", headerRecordCount), 1, Nothing)

Else

Throw New Exception(String.Format("Invalid file format, header row count ({0}) not equal to rows found ({1}).", headerRecordCount, recordCount))

End If

End With

End Sub

Private Function ReadColumn(ByVal length As Integer) As String

Dim buffer(length - 1) As Char

If stream.Read(buffer, 0, length) = length Then

Return New String(buffer)

Else

Throw New Exception("Invalid file format, full column length not found.")

End If

End Function

End Class

|||Thanks for the answers guys..

Have gone with using a script component of type source, as per code above. With one change...Just ensured that the stream is closed after processing to ensure the resources are released...

I also added an extra Output for the script which holds the header details - my real data file has extra (useful) details in the header.

Thanks again..

Scott

flat file source and destination - need fixed width output

I have a text file that is comma delimited and im pulling it in with a flatfile connection manager. I want to read some of the data, then output another flat file but in a fixed column width. What settings do I made to the connection manager of the output flatfile ?Choose a fixed-width format when setting up the destination. And then define your columns.|||

Hi

A solution is:

- Go to advanced properties of output flat file.

- Define the fields as text and the property OutputColumnWith with the size you want

- Export your data to that file and convert it to text.

Raul

|||I had some other steps in the middle, so I took them out just to simplify things. In the connection mgr for my destination, it wants to know the input column widths. Should I really need to bother with this, since in the end, I just want whatever output i get during any previous steps, to simply be output to the flat file as fixed width ?|||Ok, ive managed to get my output in fixed width in the output file, but it appears the lines arent terminating where they should be. What controls where the lines terminate ? I dont have a header row (no column names in the first row), so what, if anything should the "header row" settings be set to ?|||Ive got a data file with values seperated with commas. I want to read in this text file, do a lookup and add the lookup column on the front of the other columns and save the output in a fixed width format, similar to what you would get if you saved the query reqults to a file in sql mgt studio.

What I have so far is just a flat file source , a lookup and a flat file destination.

I can get the output to generate, but there doesnt seem to be any row terminating, its all one big string.

help ?|||Use "Fixed width with Row Delimiters" option for the flat file connection manager.|||

Eric Wisdahl wrote:

Use "Fixed width with Row Delimiters" option for the flat file connection manager.

Where is that option available ? Under the general tab on my flat file conn mgr, I have only the options:
"fixed width"
"delimited"
"ragged right"

If I have fixed width selected, and go to the advanced tab, start entering my own columns, the column delimiter option is greyed out.|||When you are first creating the flat file connection manager it gives you the option of delimited, fixed width, fixed width with row delimiters, ragged right. All that fixed width with row delimiters does is add another column to each record which contains the row delimiter. You can accomplish the same thing by adding it in with the derived column and adding it to the end of your output record.

Flat File Records Dropped During Import

Hello,
I am attempting to import a fixed width flat file into a SQL Server table. When I import the file, 704 records don't make it into the table. I know this because if I do the import with MS Access 2003 into an Access table, all of the records from the flat file make it into the table. The flat files have a .txt extension.

The only possible problem that I can see is that some of the rows in the flat file do not contain the full set of characters. When I do the import into SQL Server and create a table on the fly, I still end up 704 records short. There are no error messages during or after the import.

I suppose I could isolate some of the missing records, put them into a different file and try to import them to see what would happen. Other than that, how do I begin to troubleshoot this problem? Are there known issues where records can be dropped from a fixed width file?

Thank you for your help!

cdun2I may have found the problem. The first record does not contain a full set of characters, and when I set up the fixed field column positions originally, I was not able to define the columns for the full string. Only the first one third of the row characters need to be imported, so I ignored this issue.

I have moved a single full length record to the top of the flat file, and the text file properties box now can 'see' the full string. I'll include the rest of the columns (which are not imported) and see if that works.|||No, that didn't work. I moved a record containing all characters to the top of the file, redefined the columns based on the full string, changed the column mappings, and reconfigured the transformations. When I did the import, the destination SQL Server table received even fewer records.

What can I do about this problem?

Thanks again.

cdun2|||If this is a one time only situation, why don't you just import the flat file into Access 2003 and then import the Access table to SQL server?|||What are you using to import the data? DTS? BCP?sql

Flat File import question

I have a fixed width flat file that I'm trying to import, and I'm just about there. The last column that I'm struggling with, is a decimal amount. The data in the column looks like this 00000000500 and I need to dump it into a column as 5.000 In otherwords, the data in the file does not have any decimals, and I'm putting it into a sql server column that has the datatype numeric(11,4) I've set the InputColumnWidth to 11, the DataPrecision to 11 and the datascale to 2, and the value is still being imported as 500.000 Is there any way to achieve this other than using a script component to calculate the value? Thanks!

tee bone wrote:

I have a fixed width flat file that I'm trying to import, and I'm just about there. The last column that I'm struggling with, is a decimal amount. The data in the column looks like this 00000000500 and I need to dump it into a column as 5.000 In otherwords, the data in the file does not have any decimals, and I'm putting it into a sql server column that has the datatype numeric(11,4) I've set the InputColumnWidth to 11, the DataPrecision to 11 and the datascale to 2, and the value is still being imported as 500.000 Is there any way to achieve this other than using a script component to calculate the value? Thanks!

Add a derived column to take that value and divide it by 100 (or 1000, if you need)|||Thanks for the quick reply, so in other words there's no actual way to handle this situation in the connection manager itself? The solution that you have proposed will work, I was just trying to cut down on the time it takes for the package to process. We have to load a daily file that's about 300mb, so it takes a long time to process. I've cut it down to under a minute, but I was afraid adding another step in the flow would add quite a bit of time. I'm pretty new to SSIS, so please let me know if I'm worrying about it for no reason. Thanks!
|||On a side note, I'd go try this out and test it out for myself, but I'm going to have to apply this derived column transformation on about 150 columns, so I'm trying to get a good idea of what to expect before I get started
|||Yeah, the connection manager just reads the data as it is. What it currently sees (without a decimal point) is an integer. It is what it is. Either fix it in the source, or use a derived column. Sucks, yes, but that's the way it is.|||Sounds good, thanks for the help!

FixedHeader JavaScript Bug?

Hi
I have a report with a table containing 26 columns, the tableheader is fixed when scrolling (nice) and I have set the leftmost column to FixedHeader. It is possible via paramters for the user to select which columns that should be visible. But when some columns not are visible then I get a script exception after rendering, 'children.0.style' is null or not an object, resulting in no fixed headers at all.

If I remove som columns so that there is only 18... it works.
Seems lika bug to me?
Where can I supply bug reports, I also have one upgrading bug concerning code page?

Regards

I can take care of getting the issue investigated and verified. Do you have a sample RDL file you can post here? That would be helpful in reproducing the bug.|||

Hi!
Thank You for youre response.
I can send You the .rdl if I only had some way of contact You. I dont want to publish it here.
Look at my profile for my e-mail.

|||

Hi

I too have this same problem.

When i am previewing the rdl file, the header is getting fixed But when

i try to see the report throgh the report viewer from the web page the header is not getting fixed

and i am getting a javascript error children.0.style is null or not an object

Can anybody suggest a way to deal with this problem?

|||

Hi Fredrike

Did you get any solution to fix the header of a rdl file which was showing the javascript error

children.0.style is null or not an object ?

Can you give me some solutions to solve this issue?

my mail id is bikram.mallick@.lntinfotech.com

Thank you

Vikram

|||

There are some fixes to bugs in fixed headers in SP2. Without more details, there is no way I can know if these apply to your situation.

Can you post an rdl file that is causing this problem?

|||

Hi Fredrike

Did you get any solution to fix the header of a rdl file which was showing the javascript error

children.0.style is null or not an object ?

Can you give me some solutions to solve this issue?

my mail adds is it@.foodchina.com.tw

Thank you

Johnny from Taiwan

|||

I have just had the same problem and in my case it appears to have been caused by cells being merged in a row that are across both fixed and scrolling columns. I had to re-design my report so that the cells in the fixed columns are not merged with any that are not fixed, then it worked fine.

|||

Hi, no sorry no fix. No one contacted me and now I have left that customer so... can't send no .rdl.

I hope someone else finds the proper solution and posts here.

Regards

/Fredrike

FixedHeader JavaScript Bug?

Hi
I have a report with a table containing 26 columns, the tableheader is fixed when scrolling (nice) and I have set the leftmost column to FixedHeader. It is possible via paramters for the user to select which columns that should be visible. But when some columns not are visible then I get a script exception after rendering, 'children.0.style' is null or not an object, resulting in no fixed headers at all.

If I remove som columns so that there is only 18... it works.
Seems lika bug to me?
Where can I supply bug reports, I also have one upgrading bug concerning code page?

Regards

I can take care of getting the issue investigated and verified. Do you have a sample RDL file you can post here? That would be helpful in reproducing the bug.|||

Hi!
Thank You for youre response.
I can send You the .rdl if I only had some way of contact You. I dont want to publish it here.
Look at my profile for my e-mail.

|||

Hi

I too have this same problem.

When i am previewing the rdl file, the header is getting fixed But when

i try to see the report throgh the report viewer from the web page the header is not getting fixed

and i am getting a javascript error children.0.style is null or not an object

Can anybody suggest a way to deal with this problem?

|||

Hi Fredrike

Did you get any solution to fix the header of a rdl file which was showing the javascript error

children.0.style is null or not an object ?

Can you give me some solutions to solve this issue?

my mail id is bikram.mallick@.lntinfotech.com

Thank you

Vikram

|||

There are some fixes to bugs in fixed headers in SP2. Without more details, there is no way I can know if these apply to your situation.

Can you post an rdl file that is causing this problem?

|||

Hi Fredrike

Did you get any solution to fix the header of a rdl file which was showing the javascript error

children.0.style is null or not an object ?

Can you give me some solutions to solve this issue?

my mail adds is it@.foodchina.com.tw

Thank you

Johnny from Taiwan

|||

I have just had the same problem and in my case it appears to have been caused by cells being merged in a row that are across both fixed and scrolling columns. I had to re-design my report so that the cells in the fixed columns are not merged with any that are not fixed, then it worked fine.

|||

Hi, no sorry no fix. No one contacted me and now I have left that customer so... can't send no .rdl.

I hope someone else finds the proper solution and posts here.

Regards

/Fredrike

FixedHeader JavaScript Bug?

Hi
I have a report with a table containing 26 columns, the tableheader is fixed when scrolling (nice) and I have set the leftmost column to FixedHeader. It is possible via paramters for the user to select which columns that should be visible. But when some columns not are visible then I get a script exception after rendering, 'children.0.style' is null or not an object, resulting in no fixed headers at all.

If I remove som columns so that there is only 18... it works.
Seems lika bug to me?
Where can I supply bug reports, I also have one upgrading bug concerning code page?

Regards

I can take care of getting the issue investigated and verified. Do you have a sample RDL file you can post here? That would be helpful in reproducing the bug.|||

Hi!
Thank You for youre response.
I can send You the .rdl if I only had some way of contact You. I dont want to publish it here.
Look at my profile for my e-mail.

|||

Hi

I too have this same problem.

When i am previewing the rdl file, the header is getting fixed But when

i try to see the report throgh the report viewer from the web page the header is not getting fixed

and i am getting a javascript error children.0.style is null or not an object

Can anybody suggest a way to deal with this problem?

|||

Hi Fredrike

Did you get any solution to fix the header of a rdl file which was showing the javascript error

children.0.style is null or not an object ?

Can you give me some solutions to solve this issue?

my mail id is bikram.mallick@.lntinfotech.com

Thank you

Vikram

|||

There are some fixes to bugs in fixed headers in SP2. Without more details, there is no way I can know if these apply to your situation.

Can you post an rdl file that is causing this problem?

|||

Hi Fredrike

Did you get any solution to fix the header of a rdl file which was showing the javascript error

children.0.style is null or not an object ?

Can you give me some solutions to solve this issue?

my mail adds is it@.foodchina.com.tw

Thank you

Johnny from Taiwan

|||

I have just had the same problem and in my case it appears to have been caused by cells being merged in a row that are across both fixed and scrolling columns. I had to re-design my report so that the cells in the fixed columns are not merged with any that are not fixed, then it worked fine.

|||

Hi, no sorry no fix. No one contacted me and now I have left that customer so... can't send no .rdl.

I hope someone else finds the proper solution and posts here.

Regards

/Fredrike

FixedHeader JavaScript Bug?

Hi
I have a report with a table containing 26 columns, the tableheader is fixed when scrolling (nice) and I have set the leftmost column to FixedHeader. It is possible via paramters for the user to select which columns that should be visible. But when some columns not are visible then I get a script exception after rendering, 'children.0.style' is null or not an object, resulting in no fixed headers at all.

If I remove som columns so that there is only 18... it works.
Seems lika bug to me?
Where can I supply bug reports, I also have one upgrading bug concerning code page?

Regards

I can take care of getting the issue investigated and verified. Do you have a sample RDL file you can post here? That would be helpful in reproducing the bug.|||

Hi!
Thank You for youre response.
I can send You the .rdl if I only had some way of contact You. I dont want to publish it here.
Look at my profile for my e-mail.

|||

Hi

I too have this same problem.

When i am previewing the rdl file, the header is getting fixed But when

i try to see the report throgh the report viewer from the web page the header is not getting fixed

and i am getting a javascript error children.0.style is null or not an object

Can anybody suggest a way to deal with this problem?

|||

Hi Fredrike

Did you get any solution to fix the header of a rdl file which was showing the javascript error

children.0.style is null or not an object ?

Can you give me some solutions to solve this issue?

my mail id is bikram.mallick@.lntinfotech.com

Thank you

Vikram

|||

There are some fixes to bugs in fixed headers in SP2. Without more details, there is no way I can know if these apply to your situation.

Can you post an rdl file that is causing this problem?

|||

Hi Fredrike

Did you get any solution to fix the header of a rdl file which was showing the javascript error

children.0.style is null or not an object ?

Can you give me some solutions to solve this issue?

my mail adds is it@.foodchina.com.tw

Thank you

Johnny from Taiwan

|||

I have just had the same problem and in my case it appears to have been caused by cells being merged in a row that are across both fixed and scrolling columns. I had to re-design my report so that the cells in the fixed columns are not merged with any that are not fixed, then it worked fine.

|||

Hi, no sorry no fix. No one contacted me and now I have left that customer so... can't send no .rdl.

I hope someone else finds the proper solution and posts here.

Regards

/Fredrike

Fixed X-Axis (-100 to + 100) in chart

Hi,
I have a problem to get a fixed X-Axis in my chart.
I need a scale from -100 to +100 which does not depend on the values that come from my database. At this moment The scale increases or decreases when I for example only have values between 20 and 30. This is not useful for comparing different charts.
I tried different settings for the chart (setting maximum to 100 and minimum to -100 and Cross at 0) without effect.
Anyone got a suggestion? Thanks!Pull up chart properties dialog, go to X Axis tab, and check the "Numeric or
timescale values" checkbox.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jan" <Jan@.discussions.microsoft.com> wrote in message
news:9B4FCC9E-9CCD-44C9-A198-CF268D3A5053@.microsoft.com...
> Hi,
> I have a problem to get a fixed X-Axis in my chart.
> I need a scale from -100 to +100 which does not depend on the values that
come from my database. At this moment The scale increases or decreases when
I for example only have values between 20 and 30. This is not useful for
comparing different charts.
> I tried different settings for the chart (setting maximum to 100 and
minimum to -100 and Cross at 0) without effect.
> Anyone got a suggestion? Thanks!|||You will also need to set the Scale Minimum and Maximum on the X Axis tab
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%239fYJXnaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Pull up chart properties dialog, go to X Axis tab, and check the "Numeric
or
> timescale values" checkbox.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jan" <Jan@.discussions.microsoft.com> wrote in message
> news:9B4FCC9E-9CCD-44C9-A198-CF268D3A5053@.microsoft.com...
> > Hi,
> >
> > I have a problem to get a fixed X-Axis in my chart.
> >
> > I need a scale from -100 to +100 which does not depend on the values
that
> come from my database. At this moment The scale increases or decreases
when
> I for example only have values between 20 and 30. This is not useful for
> comparing different charts.
> >
> > I tried different settings for the chart (setting maximum to 100 and
> minimum to -100 and Cross at 0) without effect.
> >
> > Anyone got a suggestion? Thanks!
>|||Thanks for both tips. Fixed my problems (and axis...).
"Bruce Johnson [MSFT]" wrote:
> You will also need to set the Scale Minimum and Maximum on the X Axis tab
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:%239fYJXnaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> > Pull up chart properties dialog, go to X Axis tab, and check the "Numeric
> or
> > timescale values" checkbox.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Jan" <Jan@.discussions.microsoft.com> wrote in message
> > news:9B4FCC9E-9CCD-44C9-A198-CF268D3A5053@.microsoft.com...
> > > Hi,
> > >
> > > I have a problem to get a fixed X-Axis in my chart.
> > >
> > > I need a scale from -100 to +100 which does not depend on the values
> that
> > come from my database. At this moment The scale increases or decreases
> when
> > I for example only have values between 20 and 30. This is not useful for
> > comparing different charts.
> > >
> > > I tried different settings for the chart (setting maximum to 100 and
> > minimum to -100 and Cross at 0) without effect.
> > >
> > > Anyone got a suggestion? Thanks!
> >
> >
>
>

Fixed Width Text Report

I'm trying to create a fixed width text report. I've created a function that
takes a string and a length and returns the string either truncated or padded
with spaces to the given length.
I then use url parameters to modify the CSV device information settings to
change the encoding to ascii, change the extension to txt and change the
FieldDelimiter to %1f (unicode symbol for some kind of field grouping or
something).
Things seems to properly but I don't like having to set the FieldDelimiter
to anything. I tried setting it to null by saying isnull=true but that
generates an error about referencing a null object. I read something that
said to make it an empty string but I can seem to be able to do that using
url parameters. I could try it programmatically but I would prefer using the
url.
Does anyone have any ideas?(The parent post is mine, I just changed my login)
I decided that setting the FieldDelimiter to %1f was not a good idea. I
did try to set the parameter to an empty string programmatically but it
just went to the default comma delimiter.
I've decided to just plug in the url encoded value of which stands
for a null ascii character. I don't know if this is the best solution
but I am going with it. Here is my final url:
http://localhost/ReportServer?/Devel/TestFile&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=ascii
If anyone else comes up with any other ideas of how to use RS to create
fixed width text files I would be glad to hear them. I can't find
anything that explains a way of doing this. This would work perfect if
I could set the FieldDemlimiter parameter to nothing but it keeps going
to the default comma.
Thanks.
Gary

Fixed width output problem

I'm sending the results of an SSIS data flow to an fixed-width flat file output, but instead of getting separate rows of data, like so:

row1data...
row2data...
row3data...
etc...

I get:

row1data...row2data...row3data...etc...

Is there some setting I'm missing in either the flat file output or the file connection to turn this on?

The fixed width format does not include the row delimiters.

You can use the Ragged right format if your last column always has a fixed format or you do not care for it to be fixed.

If you do have a requirement for the fixed length of the last column (like you always want integer values to take 10 characters in the file), you can create your Flat File Connection manager by clicking "New..." on the Flat File Destination UI and choose the "Fixed width with row delimiters" option. That will actually create the ragged right file with a dummy row delimiter column.

HTH.

|||Thank you. That's what I needed.|||Thanks so much for this. I wanted SSIS to mimic the fixed width behavior of DTS or SQL Server 2000 export wizard. This was exactly what I was looking for.

Fixed v Changing v Historical attribute conflicts

We have an issue in a SCD where a number of records may be presented that have changes to their attributes of EVERY type.

Example,

BusinessKey: xxxxxxxx

BuildingTypeId: 7

BusinessUnitHistoryId: 4019

BusinessUnitId: 4019

CurrencyId: 26

DevelopmentTypeId: 14

MarketId: 182

Name: abcdefgh

CurrencyId is a fixed attribute

MarketId & BuildingTypeId and the BusinessUnitId & BusinessUnitHistoryIds are historical attributes

Name is a changing attribute

The behaviour of the ETL seems to suggest that if fixed attribute changes are detected, these rows will error and therefore the changing & historical attributes will NOT be amended during the SCD transformation. Is this correct... as it seems to be what is happening.

Yep, that's an error. A fixed attribute cannot change. How do you propose the SCD handles that scenario? To me, it's bad data and should be redirected.|||

Thanks for the confirmation Phil.

I figure we are going to amend CurrencyId from Fixed to Changing as it seems they just overwrite at source and do not keep the history.

So, fixed attributes aside, what happens if the same row contains both a changing attribute amendment AND a historical attribute amendment - do both get successfully changed or does one type take precedence over the other?

Let's say (in the above example) both the Name (changing) and MarketId (historical) had changed.... do both get done or just one... if it's just one, do you have a suggestion as to how we reflect both changes?

Thanks for your help

Will

|||The historical attribute change will always occur, and I think there's a setting on whether or not to change all historical instances with changing attributes.|||

Yes,

Ah i think i get it...

I'm aware that if a changing attribute is detected, you can elect to change all instances of that record.

What I was unclear about is whether during a single SCD pass, if a record contains a field which is a changing attribute AND a field which is a historical attribute, do BOTH get done ?

From what you have said, the historical attribute will get done AND the record will also pass down the changing attribute route if such a change is detected. ?

If that's that case then hats off the MSFT... I just have a sneaky feeling that only one will get done.... maybe I just need to set up some samle data and test this.

|||It works correctly in my test. The new row is inserted as a result of a historical attribute change with the changed attribute being written to it at the same time. If you tell it to change all attributes, they get changed as well.

Think about it -- it's just input into the historical row -- so when it writes the new row it just reads in the value of whatever's in that column. As for updating all of the "expired" rows, that's similar behavior to a multicast and is executed independent of the historical row change.

If that isn't clear, just know that it will work!|||

No... it's clear enough Phil and I never had cause to question it before..... You know when you start questioning stuff like this you've obviously been working on a problem for too long!!

Thanks alot

Will

FIXED THE PROBLEM I AM GETTING

Hello Friends

I am very glad to tell all u ppl that i ve got the solution to my problem at last
I ve just posted my problem(few mins back) in which i was getting problem with connecting to the ASP.NET Web application. I have made a new user on sql through SQL Enterprise Manager as <machinename>\ASPNET, and my problem gets solved

sorry to bother u all

Regards
BSSGlad you got this worked out. Next time please be sure to reply to your original post letting people know the problem is solved. :-)

Regards,
Terri

Fixed text in SQL being truncated

Hi

This might be an obvious question to some of you, but I'm stumped and any help would be appreciated! I used to work in SQL Server, and am new to Teradata SQL. I'm not sure if this query is not working because of limitation differences between the two, or if it is a pure SQL problem. (Seeing how I couldn't find a teradata forum, I posted this problem here).

This is an excerpt of a query I'm trying to run in Teradata SQL Assistant:
SELECT
'TYLastRollUpCompWSSI' as WSSISet,
'FcastClosing Stock' as Measure,
sum(ZEROIFNULL(a.CCP_Closing_Stk_SV)) as Amt
FROM VWI0GPP_WKLY_SGRP_RPAS_PLAN a

In the resultset, however, the fixed text 'FcastClosing Stock' is being truncated to just 10 characters (i.e. 'FcastClosi) and the fixed text 'TYLastRollUpCompWSSI' is being truncated to just 20 characters (i.e. 'TYLastRollUpCompW').

It might seem silly that I need this ridiculously long names in the first place, but my resultset needs to conform with what is currently in the table.OK ... I managed to come up with a solution that seems to work: I have used "cast" in the select:

SELECT
cast('TYLastRollUpCompWSSI' as CHAR(30)) as WSSISet,
cast('FcastClosing Stock' AS CHAR(25)) as Measure, ... etc.

Fixed Table Size

I have a report with several tables inside and I would like to fix the
size of the tables regarding the number of detail rows and appear a
scroll bar to show the rest of the table and therefore having acess
to all table in one page. Is it possible? I'm using SRS 2000.
Thanks,
Pedro GeraldesOne suggestion is that you can use subreport to get the required functionality.
Amarnath
"pedro.geraldes@.netvisao.pt" wrote:
> I have a report with several tables inside and I would like to fix the
> size of the tables regarding the number of detail rows and appear a
> scroll bar to show the rest of the table and therefore having acess
> to all table in one page. Is it possible? I'm using SRS 2000.
> Thanks,
> Pedro Geraldes
>|||On Feb 4, 2:09 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> One suggestion is that you can use subreport to get the required functionality.
> Amarnath
>
I create the Subreport but it still show all information. How can I
limit the Display Area size of the Subreport?
>
> "pedro.geral...@.netvisao.pt" wrote:
> > I have a report with several tables inside and I would like to fix the
> > size of the tables regarding the number of detail rows and appear a
> > scroll bar to show the rest of the table and therefore having acess
> > to all table in one page. Is it possible? I'm using SRS 2000.
> > Thanks,
> > Pedro Geraldes- Hide quoted text -
> - Show quoted text -|||When you place a subreport from the toolbox, you need to expand the subreport
rectangle to fit into your page and the hori/verti scroll automatically comes
to your page depending up on the length of the report inside a subreport
Amarnath
"pedro.geraldes@.netvisao.pt" wrote:
> On Feb 4, 2:09 pm, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > One suggestion is that you can use subreport to get the required functionality.
> >
> > Amarnath
> >
> I create the Subreport but it still show all information. How can I
> limit the Display Area size of the Subreport?
> >
> >
> > "pedro.geral...@.netvisao.pt" wrote:
> > > I have a report with several tables inside and I would like to fix the
> > > size of the tables regarding the number of detail rows and appear a
> > > scroll bar to show the rest of the table and therefore having acess
> > > to all table in one page. Is it possible? I'm using SRS 2000.
> >
> > > Thanks,
> >
> > > Pedro Geraldes- Hide quoted text -
> >
> > - Show quoted text -
>
>|||Are you working on SRS 2000? Because when I run the report the
subreport grows to show full information, that is more that the size
that I define for the subreport.|||currently I am on 2005 and I have worked on 2000 as well, very much. See that
you remove most of the spaces from the report which you are refering in your
subreport, ofcourse you cant make it very small subreport area and refer a
big report. Try different sizes..
Amarnath
"pedro.geraldes@.netvisao.pt" wrote:
> Are you working on SRS 2000? Because when I run the report the
> subreport grows to show full information, that is more that the size
> that I define for the subreport.
>

fixed table header overlays text

Wondering if anyone else has had this problem? I'm using RS for SQL 2005 and
checked the option "Header should remain visible while scrolling" on my
table. And the header line does remain visible, but it's on top of the data
as you scroll down the page. (the header line is 3 lines long - if that
matters).
Thanks for any help.
MarkWe had this issue. To keep it from looking badly when the user scrolled
down the resultset, we set the background of the header to White instead of
transparent.
Regards.
Chris E.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16C3E1FF-7CB5-47F6-9C6B-C4136B96CE5E@.microsoft.com...
> Wondering if anyone else has had this problem? I'm using RS for SQL 2005
> and
> checked the option "Header should remain visible while scrolling" on my
> table. And the header line does remain visible, but it's on top of the
> data
> as you scroll down the page. (the header line is 3 lines long - if that
> matters).
> Thanks for any help.
> Mark|||Chris - thanks. worked perfectly.
m
"Chris" wrote:
> We had this issue. To keep it from looking badly when the user scrolled
> down the resultset, we set the background of the header to White instead of
> transparent.
> Regards.
> Chris E.
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:16C3E1FF-7CB5-47F6-9C6B-C4136B96CE5E@.microsoft.com...
> > Wondering if anyone else has had this problem? I'm using RS for SQL 2005
> > and
> > checked the option "Header should remain visible while scrolling" on my
> > table. And the header line does remain visible, but it's on top of the
> > data
> > as you scroll down the page. (the header line is 3 lines long - if that
> > matters).
> >
> > Thanks for any help.
> > Mark
>
>|||I just want to say "Thank you!". work for me too.
"Chris" wrote:
> We had this issue. To keep it from looking badly when the user scrolled
> down the resultset, we set the background of the header to White instead of
> transparent.
> Regards.
> Chris E.
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:16C3E1FF-7CB5-47F6-9C6B-C4136B96CE5E@.microsoft.com...
> > Wondering if anyone else has had this problem? I'm using RS for SQL 2005
> > and
> > checked the option "Header should remain visible while scrolling" on my
> > table. And the header line does remain visible, but it's on top of the
> > data
> > as you scroll down the page. (the header line is 3 lines long - if that
> > matters).
> >
> > Thanks for any help.
> > Mark
>
>

Fixed reports in Reporting Services using SSAS

I am quite new with Reporting Services so appologies if I'm asking a
really simple question without relaising it.
I have a Analysis Services cube that I'm querying from Reporting
Services 2005, I'm wondering how I can create a fixed style report that
has specfic members from one dimension in one axis and specific members
from another dimension in another.
Eg:
Jan Feb Mar Apr May Jun Jul Aug
Cost1 2 6 5 8 88 0 8 8
Cost15 0 0 0 0 0 6 54 5
Cost65 52 5 545 1 42 55 54 455
Cost15 545 0 555 0 22 2252 5 55
Cost95
I have found that it's quite easy to create this format in a dymamic
fashion but I have had no hope in doing so with fixed members in both
axis.
Any help would be much appreciated.
Thanks
SimonI think that you can make 1,15,65,15,95 members as a 'named set'
hope that helps
-Aaron
sk wrote:
> I am quite new with Reporting Services so appologies if I'm asking a
> really simple question without relaising it.
> I have a Analysis Services cube that I'm querying from Reporting
> Services 2005, I'm wondering how I can create a fixed style report that
> has specfic members from one dimension in one axis and specific members
> from another dimension in another.
> Eg:
> Jan Feb Mar Apr May Jun Jul Aug
> Cost1 2 6 5 8 88 0 8 8
> Cost15 0 0 0 0 0 6 54 5
> Cost65 52 5 545 1 42 55 54 455
> Cost15 545 0 555 0 22 2252 5 55
> Cost95
> I have found that it's quite easy to create this format in a dymamic
> fashion but I have had no hope in doing so with fixed members in both
> axis.
> Any help would be much appreciated.
> Thanks
> Simon|||I think you need to organize the way your data gets outputed. If you manage
to create a "grid" of data in the form you want it to, you could just use a
fixed table and add the fields to the column or row that you want it to.
That would probably mean a lot of crossjoins or unions on the coloumns to
get the months tagged right, and have the different costs as rows.
Sorry to ask, but why do you want to do it fixed? This is sort of what
matrixes are made for, contrary to the tables.
Kaisa M. Lindahl Lervik
"sk" <simon.m.knight@.gmail.com> wrote in message
news:1159979952.751107.275360@.m73g2000cwd.googlegroups.com...
>I am quite new with Reporting Services so appologies if I'm asking a
> really simple question without relaising it.
> I have a Analysis Services cube that I'm querying from Reporting
> Services 2005, I'm wondering how I can create a fixed style report that
> has specfic members from one dimension in one axis and specific members
> from another dimension in another.
> Eg:
> Jan Feb Mar Apr May Jun Jul Aug
> Cost1 2 6 5 8 88 0 8 8
> Cost15 0 0 0 0 0 6 54 5
> Cost65 52 5 545 1 42 55 54 455
> Cost15 545 0 555 0 22 2252 5 55
> Cost95
> I have found that it's quite easy to create this format in a dymamic
> fashion but I have had no hope in doing so with fixed members in both
> axis.
> Any help would be much appreciated.
> Thanks
> Simon
>|||Thanks for your reply,
The report needs to look exactly the same as an Excel based matrix
report that we use. the format is defined by our corporate entity so I
can't change it.
Thanks
Simon|||Excel isn't a reporting platform.
Tell them to eat shit
-Aaron
sk wrote:
> Thanks for your reply,
> The report needs to look exactly the same as an Excel based matrix
> report that we use. the format is defined by our corporate entity so I
> can't change it.
>
> Thanks
> Simon|||Thanks for your suggestion, I'll give it a try!
Still, it must br possible to create a fixed grid report. I'll try your
suggestions and the others posted here..
Simon|||There's a discription in Chris Hays' blog about Horizontal Tables. Might be
what you need:
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
As for using a matrix, what parts are different between your Excel report
and a matix based report?
Kaisa M. Lindahl Lervik
"sk" <simon.m.knight@.gmail.com> wrote in message
news:1160074016.884219.105770@.i42g2000cwa.googlegroups.com...
> Thanks for your suggestion, I'll give it a try!
> Still, it must br possible to create a fixed grid report. I'll try your
> suggestions and the others posted here..
>
> Simon
>|||Thanks for that - I'll look into it.
The difference between my Excel report and a matrix based report is
that the Excel report is completely static. just a table with fixed
members in each axis. The members in each axis need to be fixed like a
PL report (see example)
http://www.ilytix.com/ImageFiles/Popup_P&L_Report.gif|||OK, the picture looks like a standard RS table. As long as your output is in
a usefull format, you could use a RS table. If you make sure you always get
the right data out (like you know you only get the same 4 rows of growth),
you can probably use a dynamic table. Or you can really hard code it,
returning several data sets with one row of data in each and creating
several single line tables put close in the report designer so they look
like one big table.
If your data set returns both dynamic growth and dates (months in your first
example), you could solve it by using a matrix. Make a row group for your
growth, and a column group for dates. "Pad" your data set to make sure you
always return the same number of months for dates, so you always get the
same number of month columns, and the same for the growth numbers. You might
have to tweak your mdx statemement a bit, but that's probably more usefull
than customizing the table.
Kaisa M. Lindahl Lervik
"sk" <simon.m.knight@.gmail.com> wrote in message
news:1160083140.237336.110450@.i3g2000cwc.googlegroups.com...
> Thanks for that - I'll look into it.
> The difference between my Excel report and a matrix based report is
> that the Excel report is completely static. just a table with fixed
> members in each axis. The members in each axis need to be fixed like a
> PL report (see example)
> http://www.ilytix.com/ImageFiles/Popup_P&L_Report.gif
>

Fixed position of table at bottom of report problem...

I have a table that just contains text at the bottom, but depending on
the query, it might land anywhere. it always needs to be one inch from
the bottom.
Any help is appreciated.
trinttrint wrote:
> I have a table that just contains text at the bottom, but depending on
> the query, it might land anywhere. it always needs to be one inch
> from the bottom.
> Any help is appreciated.
You can set the location for the absolute position of the table relative to
the position of its container!
You know your page-size...so fill the top- and left-coordinates with the
right values
regards
Frank|||Ok,
That works...but I can't get the top to go higher than one inch from
the top
Frank Matthiesen wrote:
> trint wrote:
> > I have a table that just contains text at the bottom, but depending
on
> > the query, it might land anywhere. it always needs to be one inch
> > from the bottom.
> > Any help is appreciated.
> You can set the location for the absolute position of the table
relative to
> the position of its container!
> You know your page-size...so fill the top- and left-coordinates with
the
> right values
> regards
> Frank|||trint wrote:
> Ok,
> That works...but I can't get the top to go higher than one inch from
> the top
You can set the top margin / lower margin in report-settings for the whole
report to 0
regards
Frank|||I have a similar issue with absolute positioning. I have a table and a text
box in a report. The textbox should be at the bottom always. The table can
span any number of pages. In this case, fixing the left and top locations is
not working. Can you please suggest me a solution.