Showing posts with label footer. Show all posts
Showing posts with label footer. Show all posts

Monday, March 19, 2012

Interesting Average problem

I have a table that has a numeric field and the footer of the field has to
display the AVG of the field.
Some of the field values are null, and those should be left out of the
calculation.
Doing a AVG(Field!Name.Value) of course take them into account as well.
Any ideas?
Thanks.Shrink, shrink, shrink, in the end it works if the field is null, different
story if it is zero.
"Chris Botha" <chris_s_botha@.AT_h.o.t.m.a.i.l.com> wrote in message
news:ujqmWjUZEHA.1480@.TK2MSFTNGP10.phx.gbl...
> I have a table that has a numeric field and the footer of the field has to
> display the AVG of the field.
> Some of the field values are null, and those should be left out of the
> calculation.
> Doing a AVG(Field!Name.Value) of course take them into account as well.
> Any ideas?
> Thanks.
>

Monday, March 12, 2012

interactive sort in group

hi,

i created a report using table with added group header and footer. i set a few columns at the table header for interactive sort. however, the sort doesn't work for thhe field that i place in the group header.

Is interactive sort supported in group header?

Thanks.

Yes, interactive sort is supported in group header. When you click on the sort in the group header, it just doesn't do anything? Can you attach your rdl so that I can see why it's not working for you?

- Fang

|||

thanks Fang for your reply. sorry if this seems silly. how do i attach the rdl file here?

thanks again

|||Looks like there is no way to attach a file here. Can you just post the XML in the thread?|||

hi Fang.. sorie... probably i should rephrase my question.

actually i have fields in my group header and place the labels for the respective fields at the table header. can i sort the fields in group header using the label in table header?

sorie for asking the wrong question.. thanks!

Friday, February 24, 2012

Integration Services - Header and Footer

I have been struggling to get this right in Integration Services, Would really appreciate the help.

Basically I have a file with fixed length columns that I would like to import into my database using Integration Services, the file looks something like this

AN_HeaderRecord0001Testfile.txt

MyNameMySurname0127721506

MyName2 MySurname2 0127722322

FooterRec0000000004_02

What I need to find out is how do I import the data, the name and surname and telephone no, but skip the header and footer records. Also I need to be able to use the header and footer data for later use.

Any ideas ?

Rudolf,

You could do that easily in 2 steps(dataflows); the first one to add an extra column with the row number and determine the number of rows in the file (put the number in a variable using a rowcount transformation); then in the second one you can use a conditional split to re-direct the first and last row by comparing the rownumber column with the number in the variable from the first dataflow.

|||Thanx a mil for the response, I think I am getting the hang of this, will try it and let you know|||Your flat file connection manager should let you chose the option of skipping the first row altogether. Then all you'd have to worry about is removing the footer. If the detail records have a different indicator than the footer records, you can use a conditional split transformation to direct only the detail records to your destination.|||

Hi, Rapael

Could you perhaps point me in the correct direction (what components to use) to get the row number next to the row data

in the first data flow.

First Data Flow

I should have a flat file source and then a row count transformation component linked to each other ? I am having problems getting the row

data and the row number next to each other

Thanx in advance

|||

you can use an script component:

http://support.microsoft.com/kb/908460

|||Or use the Row Number transform here: http://www.sqlis.com/93.aspx