Page Break after a value in one of the report groups

There are 3 groups in my reportâ' grp1, grp2 and grp3, with grp1 at the top,
grp2 in the middle and grp3 at the bottom. I need to insert a page break when
the value of grp1 record becomes â'abcâ'. Is this possible? If so, I will
really appreciate if someone can let me know how.
Thanks in advance,
SauravDid you ever find out how to do this? I need a page break in a report just
like you are descibing.
Thanks
"sajmera" wrote:
> There are 3 groups in my reportâ' grp1, grp2 and grp3, with grp1 at the top,
> grp2 in the middle and grp3 at the bottom. I need to insert a page break when
> the value of grp1 record becomes â'abcâ'. Is this possible? If so, I will
> really appreciate if someone can let me know how.
> Thanks in advance,
> Saurav
>sql

PadLeft function on Null value

Can you use a padleft function on a null value of a field?

I get this when i try to padleft on a result from a db. This happens only when one of the fields are null.

Public member 'PadLeft' on type 'DBNull' not found.

You need handle the NULL from the database before you pass on to your PadLeft function in your program.

Limno

|||

How would I do that?

Can i set the default value of that field to be an empty string? if so how is that done.

thanks,

|||

This should work for ya in VB.NET

CTYPE("" & {Whatever you are trying to padleft that might be DBNULL} & "",string).padleft( ... )

Basically concatenating a string with DBNULL will return the string in vb.net, so it's a quick and easy way to replace fields with dbnull with an empty string. Then I'm casting the result to a string although it isn't necessary, it helps VS2005 to understand the datatype so that intellisense still works (And you can type .padleft, etc). It'll still work without it, but no intellisense (Atleast it didn't in VS2003, and old habits die hard).

|||

I would handle it in a helper function in your application.

Like:

Function fixNull(ByVal sItemAsObject)AsString

If sItemIs DBNull.ValueThen

fixNull = "-"

Else

fixNull = sItem

EndIf

EndFunction

In your code: PadLeft(fixNull(yourInterestedField...))

Or

you can handle this in the SQL statement. For example:

SELECT CASE WHEN col1isnull THEN'-' ELSE col1 ENDas col1FROM testtable

You can replace '-' with an empty string''(two single quote) if you like.

you can process this field as usaual.

Limno

padding variables

I know this may be an elementary question but can anyone tell me how to pad a
variable.
For example
@.chvcode has value ��123�� and I want to pad it with ��00123��.
Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:

> I know this may be an elementary question but can anyone tell me how to pad a
> variable.
> For example
> @.chvcode has value ��123�� and I want to pad it with ��00123��.
>
sql

padding variables

I know this may be an elementary question but can anyone tell me how to pad
a
variable.
For example
@.chvcode has value ��123�� and I want to pad it with ��00123��.Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:

> I know this may be an elementary question but can anyone tell me how to pa
d a
> variable.
> For example
> @.chvcode has value ��123�� and I want to pad it with ��00123��.
>

padding variables

I know this may be an elementary question but can anyone tell me how to pad a
variable.
For example
@.chvcode has value â'123â' and I want to pad it with â'00123â'.Logger
Wil your variable always be an int, if so you can do
SET @.chvcode = RIGHT(100000 + @.chvcode,5)
Andy
"Logger" wrote:
> I know this may be an elementary question but can anyone tell me how to pad a
> variable.
> For example
> @.chvcode has value â'123â' and I want to pad it with â'00123â'.
>

Padding partial table name with @variable value

Is there a way to this in SQL
declare @.partial_table_name varchar(128)
set @.partial_table_name = 'mytable'
select * from customer
into tbl_cust_@.partial_table_name
Above i am inserting the values from customer to table
tbl_cust__@.partial_table_name. I want to pass the @.partial_table_name value
to the table so that table name becomes tbl_cust_mytable. This is so that I
can dynamically insert into a table based opon conditions in my store proc.
Any help would be appreciated.
ThanksEXEC('SELECT * INTO tbl_cust_'+@.partial_table_name+' from customer');
I'd be very careful about where @.partial_table_name gets created. SQL
injection can yield to inadvertent statements being executed, such as:
SELECT * INTO tbl_cust_foo FROM (SELECT a=1); UPDATE Employees SET Salary =
Salary * 10 WHERE FullName='Malicious Employee'; SELECT * from customer;
Please give the following article a thorough read:
http://www.sommarskog.se/dynamic_sql.html
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:CB37F1E5-5C85-46B6-B443-6F504E1CD69D@.microsoft.com...
> Is there a way to this in SQL
> declare @.partial_table_name varchar(128)
> set @.partial_table_name = 'mytable'
> select * from customer
> into tbl_cust_@.partial_table_name
> Above i am inserting the values from customer to table
> tbl_cust__@.partial_table_name. I want to pass the @.partial_table_name
> value
> to the table so that table name becomes tbl_cust_mytable. This is so that
> I
> can dynamically insert into a table based opon conditions in my store
> proc.
> Any help would be appreciated.
> Thanks|||Thanks Aaron,
That was very helpful.Its doing what I wanted to do. I would be careful as
where the variable is created.
Appreciate your quick response.
cheers,
b_dba
"Aaron Bertrand [SQL Server MVP]" wrote:

> EXEC('SELECT * INTO tbl_cust_'+@.partial_table_name+' from customer');
> I'd be very careful about where @.partial_table_name gets created. SQL
> injection can yield to inadvertent statements being executed, such as:
> SELECT * INTO tbl_cust_foo FROM (SELECT a=1); UPDATE Employees SET Salary
=
> Salary * 10 WHERE FullName='Malicious Employee'; SELECT * from customer;
> Please give the following article a thorough read:
> http://www.sommarskog.se/dynamic_sql.html
>
>
>
> "b_dba" <b_dba@.discussions.microsoft.com> wrote in message
> news:CB37F1E5-5C85-46B6-B443-6F504E1CD69D@.microsoft.com...
>
>

Padd Character Function To Return Variable Type

All,
I have a UDF for SQL that will padd a varchar value to a specific width and
align left or right. I found this code online somewhere, but the problem is
that it accpets a value up to Varchar(8000) and returns a varchar(8000). I
am writing a query to upload to a mainframe and need fixed-width fields, but
each field is not the same size. I can pass in the correct width to the
function and it padds correctly, but in a DTS transformation to a text file,
it thinks all fields are 8000 characters wide.
I am asking if it is possible to return a variable size data type instead of
a fixed type.
Here is the orig function:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(8000) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
and what I want basically and which doesn't seem to work, is:
ALTER FUNCTION [dbo].[PaddChar] (
@.ValueToPad varchar(8000),
@.PadCharacter char(1),
@.Justification bit,
@.Width int
)
/ ****************************************
***********************************
*************************
This function allows the USER TO pass IN a string / character value AND it
will padd the value according TO given parameters. The parameters are
AS follows:
@.ValueToPad = Value to be padded BY function.
@.PadCharacter = Character used TO pad a given value.
@.Justification = Justification format bit
0 - Value will be RIGHT justified after padding.
1 - Value will be LEFT justified after padding.
@.Width = Total COLUMN width OF the output after padding.
----
--
NOTE - ALL VALUES passes IN FOR padding must be OF CHAR or
VARCHAR data type.
****************************************
************************************
*************************/
RETURNS varchar(@.Width) AS
BEGIN
DECLARE @.x int
IF @.ValueToPad IS NULL SET @.ValueToPad = ''
IF @.PadCharacter IS NULL SET @.PadCharacter = ''
SET @.X = @.Width - LEN(@.ValueToPad)
--Right Justify Value
IF @.Justification = 0
BEGIN
SET @.ValueToPad = REPLICATE(@.PadCharacter,@.X) + @.ValueToPad
END
ELSE
--Left Justify Value
BEGIN
SET @.ValueToPad = @.ValueToPad + REPLICATE(@.PadCharacter,@.Width)
END
RETURN @.ValueToPad
END
Does anyone have any ideas on this?
Thanks,
NathanWhen I need to export fixed-width fields my approach is a bit
different. I create a view that formats each column the way I need it
to a fixed length, then export from the view. Examples of formatting:
convert(char(30), ItemName) as ItemName
STR(ItemValue,9,2)
Roy|||Thank you, I rarely use CHAR since it does padd for storage in the database,
duh! That worked to replace my instances of the dbo.PaddChar in my query fo
r
all the intstances where I am just padding on the right with spaces.
However, I had to just wrap a couple of them that I am right aligning, or
padding with 0's.
Since my query only pulls back a few rows a day the performance isn't bad.
Thank you again,
Nathan
"Roy Harvey" wrote:

> When I need to export fixed-width fields my approach is a bit
> different. I create a view that formats each column the way I need it
> to a fixed length, then export from the view. Examples of formatting:
> convert(char(30), ItemName) as ItemName
> STR(ItemValue,9,2)
> Roy
>

PAD function in Data Tranformation Editor

I have a value with a float datatype in Excel that is actually a date. For example, the value displays 1272006 for January 27, 2006. I am trying to convert this value to a date. I can do this in several stages using T-SQL by converting the datatype to integer, using replicate to make it eight characters and than using substrings to make it look like 01-27-2006 which SQL Server will recognize as a date.

My problem is that there is nothing similar to a PAD (replicate) function using the Data Tranformation Editor in Integration Services. There is a replicate function but it merely functions to repeat a value. It doesn't appear to perform the same PAD function as the T-SQL replicate does. Here is my formula in T-SQL that works okay.

right(replicate('0', 8) + convert(varchar, cast([Date] as varchar)), 8)

I could do this by creating a staging table but I want to handle the entire transformation process within Integration Services. Can anyone offer me some advice? Thank you.

David

I don't think we have your full statement... I'm not seeing where you convert to a date...

Never-the-less, why not substring the "date" field and then cast it to DT_DBDATETIME?|||Question, how is Jan 1st, 2006, represented?

112006? Or 1012006?|||1012006|||

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

|||

davidg12 wrote:

I'm not able to use the substring because the values varies between seven and eight characters. For example, January 1, 2006 is 1012006 while October 1, 2006 is 10012006. I figure I need to convert the value to eight characters by padding a zero on the left. I then can use a substring to convert it to a SQL Server recognizable date like 01-01-2006.

David

Okay, so then this should be easy... If you want to do this in one derived column (are you guaranteed that the value is a correct and verified date?), then you can do something like:

(DT_DBTIMESTAMP)(SUBSTRING(RIGHT("0" + datefield,8),5,4) + "-" + SUBSTRING(RIGHT("0" + datefield,8),1,2) + "-" + SUBSTRING(RIGHT("0" + datefield,8),3,2) + " 00:00:00")

In an upstream derived column, you could do the "RIGHT("0" + datefield,8)" calculation, and then just use that field in the above expression. It would make it easier to read.
|||

Thank you Phil. This works great.

David

P

I have a report where i crunch some numbers and show an overall market value for the top 10 particular segments. The question I have is if I have some data that has lets say the top market value as being 57% and so on, why does the pie chart show larger portion that 57%? Is it because im not showing all segments and only the top 10? Any way to make the chart more accurate in its representation of the 57%? Sorry if I havent explained this very well.anyone have any thoughts on this? i will assume its the nature in how the pie chart works, my question is how to get the pie chart to be more representative of the actual percentages|||

Are you using filters on the pie chart?

Rather than using percentage values could you convert them to real values out of 100?

You may want to look at Dundas software's charting solutions as they could be more flexible than the out-of-the-box charts.

cheers,

Andrew

overriding aggregate value of a measure to only total based on the children of the an attributes

Going off of Adventure Works, there's a product attribute:

[Product].[Product].[Product]

and now I also have an old product attribute:

[Product].[Old Product].[Old Product]

(people wanted to see the before and after view of different product assignment).

I also have a estimate of sales for each product and old product. When using just this measure - that amount is around twice as much as before - that's ok, but when using just product or just old product, I just want to see the sum of its children.

How could one define a calculated measure so that if the current level is [Product].[Product].All, then the aggregate should be the sum of the children?

This is sort of how I'd be using it:

select [estimated sales] on 0, [Product].[Product].[Product] on 1

from [Adventure Works]

the all line should be the sum of the measure used for all the children (new behavior) and each product row would have the the estimated sales - so this would stay the same.

thinking about this a little more - the 'All' level would be the curren tlevel if the attribute wasn't even used, so I'm not sure what to do to really depict which product attribute was added. And if a person added both? I'd probably want to just show the product totals.

What I'm going to try for now is add another dimension measure with an attribute defaulted to 1 and in the estimated sales, I'll add a 1 to the new records and a 0 to the old ones. This should address the issue that I have.

Thanks!