Integrated Security: values

Showing posts with label values. 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 changes time field values to 0

Hello all, I have a report which connects to a Pervasive database via an ODBC connection on the reporting server (RS2005). When I initially bring the report up, a time field (OdbcType.Time) shows the correct format: hh:mm:ss. When I sort any of the sortable columns on the report, the time field (which is not sortable) values all change to 0. Backing out of the report and re-running it restores the correct time values. Any ideas?
Thanks, KenThis issue could be related to the data type of the field. There is a fixed set of data types RS supports: string, boolean, numeric, datetime, timespan. When you sort, we have to use the data we temporarily store (so that we don't have to query the data source) to process the report. If it's not one of the types supported, it might cause the loss of the value. Can you check what CLR type the time field is of?|||Hi Fang, Please forgive my ignorance, but I'm not sure what the 'clr' type is. VS2005 says the table field type is OdbcType.Time. If I try to convert it to something silly, VS2005 complains that type 'TimeSpan' cannot be converted to the silly type. So I guess the CLR type is TimeSpan?|||Can you check your RDL file? Look under the <Field> element of that field, what's the value for <rd:TypeName>?|||Thanks for your time Fang, I've pasted the snippet for the field in question:
<Field Name="TIME_RECEIVED">
<rd:TypeName>System.TimeSpan</rd:TypeName>
<DataField>TIME_RECEIVED</DataField>
</Field>|||Hmm, we have not seen this problem before. Can you submit it along with your .rdl and .rdl.data files at https://connect.microsoft.com/SQLServer? We'll investigate it. Thanks.|||Thank you Fang. I have submitted a bug report and uploaded the files.|||Thanks. We have investigated the issue and the fix will hopefully be included in the next service pack.

Interactive sort changes time field values to 0

Friday, March 9, 2012

interact with every detail row

Hi;
is there a way to interact with every detail row that is about to print?;
for acumulating values or perform special validations
TIAIf I understand you correctly, you could use ReportItems![fieldname].value.
fieldname would be the name of the textbox(es) you are interested in
interacting with.
"Willo" wrote:
> Hi;
> is there a way to interact with every detail row that is about to print?;
> for acumulating values or perform special validations
> TIA
>
>|||let me try to clarify my problem...
lets say i have table1 with accounts
--
TABLE1
accountid balance
1 10000
2 20000
and table2 with debits and credits
--
TABLE2
accountid debit credit
1 450
1 300
1 50
2 1000
2 3450
and table3 with account names
--
TABLE3
accountid accountname accountnumber
1 AAAAAAAA 3243223432
2 BBBBBBBBB 9832798273
i need an output like this
---
account
3243223432 AAAAAAAAA initial balance debit credit
final balance
10000
450 10450
10450
300 10150
10150
50 10100
account
9832798273 BBBBBBBBB initial balance debit credit
final balance
20000 1000
21000
21000
3450 17550
"honus" <honus@.discussions.microsoft.com> wrote in message
news:A47B82AB-EA96-4D42-B412-FAEFE83B8ADA@.microsoft.com...
> If I understand you correctly, you could use
> ReportItems![fieldname].value.
> fieldname would be the name of the textbox(es) you are interested in
> interacting with.
> "Willo" wrote:
>> Hi;
>> is there a way to interact with every detail row that is about to print?;
>> for acumulating values or perform special validations
>> TIA
>>|||using a conditional statement and ReportItems! should work out for you. Name
your textboxes in tables 1 and 2 for easier referencing.
Or change your SQL to do this for you, which would probably be the easiest
way.
--
I am living proof that 95% of programmers are idiots.
"Willo" wrote:
> let me try to clarify my problem...
> lets say i have table1 with accounts
> --
> TABLE1
> accountid balance
> 1 10000
> 2 20000
> and table2 with debits and credits
> --
> TABLE2
> accountid debit credit
> 1 450
> 1 300
> 1 50
> 2 1000
> 2 3450
> and table3 with account names
> --
> TABLE3
> accountid accountname accountnumber
> 1 AAAAAAAA 3243223432
> 2 BBBBBBBBB 9832798273
>
> i need an output like this
> ---
> account
> 3243223432 AAAAAAAAA initial balance debit credit
> final balance
> 10000
> 450 10450
> 10450
> 300 10150
> 10150
> 50 10100
> account
> 9832798273 BBBBBBBBB initial balance debit credit
> final balance
> 20000 1000
> 21000
> 21000
> 3450 17550
>
>
>
>
> "honus" <honus@.discussions.microsoft.com> wrote in message
> news:A47B82AB-EA96-4D42-B412-FAEFE83B8ADA@.microsoft.com...
> > If I understand you correctly, you could use
> > ReportItems![fieldname].value.
> > fieldname would be the name of the textbox(es) you are interested in
> > interacting with.
> >
> > "Willo" wrote:
> >
> >> Hi;
> >>
> >> is there a way to interact with every detail row that is about to print?;
> >> for acumulating values or perform special validations
> >>
> >> TIA
> >>
> >>
> >>
>
>|||im trying to do it in sql but i think i cant be done on a single SELECT
statment.
On RS, all i need is to define a variable, initializing it with INITIAL
BALANCE for each account, suming each debit and subtrating each credit and
print th FINAL BALANCE for each record.
It is a simple task on most of other report tools, but i dont know how to do
it on RS... im very frustrated with this tool!
"honus" <honus@.discussions.microsoft.com> wrote in message
news:36EE88E4-29A6-44CD-AAB0-0642DC7B0958@.microsoft.com...
> using a conditional statement and ReportItems! should work out for you.
> Name
> your textboxes in tables 1 and 2 for easier referencing.
> Or change your SQL to do this for you, which would probably be the easiest
> way.
> --
> I am living proof that 95% of programmers are idiots.
>
> "Willo" wrote:
>> let me try to clarify my problem...
>> lets say i have table1 with accounts
>> --
>> TABLE1
>> accountid balance
>> 1 10000
>> 2 20000
>> and table2 with debits and credits
>> --
>> TABLE2
>> accountid debit credit
>> 1 450
>> 1 300
>> 1 50
>> 2 1000
>> 2 3450
>> and table3 with account names
>> --
>> TABLE3
>> accountid accountname accountnumber
>> 1 AAAAAAAA 3243223432
>> 2 BBBBBBBBB 9832798273
>>
>> i need an output like this
>> ---
>> account
>> 3243223432 AAAAAAAAA initial balance debit credit
>> final balance
>> 10000
>> 450 10450
>> 10450
>> 300 10150
>> 10150
>> 50 10100
>> account
>> 9832798273 BBBBBBBBB initial balance debit credit
>> final balance
>> 20000
>> 1000
>> 21000
>> 21000
>> 3450 17550
>>
>>
>>
>>
>> "honus" <honus@.discussions.microsoft.com> wrote in message
>> news:A47B82AB-EA96-4D42-B412-FAEFE83B8ADA@.microsoft.com...
>> > If I understand you correctly, you could use
>> > ReportItems![fieldname].value.
>> > fieldname would be the name of the textbox(es) you are interested in
>> > interacting with.
>> >
>> > "Willo" wrote:
>> >
>> >> Hi;
>> >>
>> >> is there a way to interact with every detail row that is about to
>> >> print?;
>> >> for acumulating values or perform special validations
>> >>
>> >> TIA
>> >>
>> >>
>> >>
>>|||Why not it can be done using sql, I created your 3 tables and wrote a query
to retrieve the final balance as well using calculated field.
Amarnath, MCTS
"Willo" wrote:
> im trying to do it in sql but i think i cant be done on a single SELECT
> statment.
> On RS, all i need is to define a variable, initializing it with INITIAL
> BALANCE for each account, suming each debit and subtrating each credit and
> print th FINAL BALANCE for each record.
> It is a simple task on most of other report tools, but i dont know how to do
> it on RS... im very frustrated with this tool!
>
>
> "honus" <honus@.discussions.microsoft.com> wrote in message
> news:36EE88E4-29A6-44CD-AAB0-0642DC7B0958@.microsoft.com...
> > using a conditional statement and ReportItems! should work out for you.
> > Name
> > your textboxes in tables 1 and 2 for easier referencing.
> >
> > Or change your SQL to do this for you, which would probably be the easiest
> > way.
> > --
> > I am living proof that 95% of programmers are idiots.
> >
> >
> > "Willo" wrote:
> >
> >> let me try to clarify my problem...
> >>
> >> lets say i have table1 with accounts
> >> --
> >> TABLE1
> >> accountid balance
> >> 1 10000
> >> 2 20000
> >>
> >> and table2 with debits and credits
> >> --
> >> TABLE2
> >> accountid debit credit
> >> 1 450
> >> 1 300
> >> 1 50
> >> 2 1000
> >> 2 3450
> >>
> >> and table3 with account names
> >> --
> >> TABLE3
> >> accountid accountname accountnumber
> >> 1 AAAAAAAA 3243223432
> >> 2 BBBBBBBBB 9832798273
> >>
> >>
> >> i need an output like this
> >> ---
> >> account
> >> 3243223432 AAAAAAAAA initial balance debit credit
> >> final balance
> >> 10000
> >> 450 10450
> >> 10450
> >> 300 10150
> >> 10150
> >> 50 10100
> >>
> >> account
> >> 9832798273 BBBBBBBBB initial balance debit credit
> >> final balance
> >> 20000
> >> 1000
> >> 21000
> >> 21000
> >> 3450 17550
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "honus" <honus@.discussions.microsoft.com> wrote in message
> >> news:A47B82AB-EA96-4D42-B412-FAEFE83B8ADA@.microsoft.com...
> >> > If I understand you correctly, you could use
> >> > ReportItems![fieldname].value.
> >> > fieldname would be the name of the textbox(es) you are interested in
> >> > interacting with.
> >> >
> >> > "Willo" wrote:
> >> >
> >> >> Hi;
> >> >>
> >> >> is there a way to interact with every detail row that is about to
> >> >> print?;
> >> >> for acumulating values or perform special validations
> >> >>
> >> >> TIA
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||maybe im just plain idiot, could you show me how you did it?
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:B8EA7478-B492-4D96-B1D5-73F24693268A@.microsoft.com...
> Why not it can be done using sql, I created your 3 tables and wrote a
> query
> to retrieve the final balance as well using calculated field.
> Amarnath, MCTS
> "Willo" wrote:
>> im trying to do it in sql but i think i cant be done on a single SELECT
>> statment.
>> On RS, all i need is to define a variable, initializing it with INITIAL
>> BALANCE for each account, suming each debit and subtrating each credit
>> and
>> print th FINAL BALANCE for each record.
>> It is a simple task on most of other report tools, but i dont know how to
>> do
>> it on RS... im very frustrated with this tool!
>>
>>
>> "honus" <honus@.discussions.microsoft.com> wrote in message
>> news:36EE88E4-29A6-44CD-AAB0-0642DC7B0958@.microsoft.com...
>> > using a conditional statement and ReportItems! should work out for you.
>> > Name
>> > your textboxes in tables 1 and 2 for easier referencing.
>> >
>> > Or change your SQL to do this for you, which would probably be the
>> > easiest
>> > way.
>> > --
>> > I am living proof that 95% of programmers are idiots.
>> >
>> >
>> > "Willo" wrote:
>> >
>> >> let me try to clarify my problem...
>> >>
>> >> lets say i have table1 with accounts
>> >> --
>> >> TABLE1
>> >> accountid balance
>> >> 1 10000
>> >> 2 20000
>> >>
>> >> and table2 with debits and credits
>> >> --
>> >> TABLE2
>> >> accountid debit credit
>> >> 1 450
>> >> 1 300
>> >> 1 50
>> >> 2 1000
>> >> 2 3450
>> >>
>> >> and table3 with account names
>> >> --
>> >> TABLE3
>> >> accountid accountname accountnumber
>> >> 1 AAAAAAAA 3243223432
>> >> 2 BBBBBBBBB 9832798273
>> >>
>> >>
>> >> i need an output like this
>> >> ---
>> >> account
>> >> 3243223432 AAAAAAAAA initial balance debit credit
>> >> final balance
>> >> 10000
>> >> 450 10450
>> >> 10450
>> >> 300 10150
>> >> 10150
>> >> 50 10100
>> >>
>> >> account
>> >> 9832798273 BBBBBBBBB initial balance debit credit
>> >> final balance
>> >> 20000
>> >> 1000
>> >> 21000
>> >> 21000
>> >> 3450 17550
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> "honus" <honus@.discussions.microsoft.com> wrote in message
>> >> news:A47B82AB-EA96-4D42-B412-FAEFE83B8ADA@.microsoft.com...
>> >> > If I understand you correctly, you could use
>> >> > ReportItems![fieldname].value.
>> >> > fieldname would be the name of the textbox(es) you are interested in
>> >> > interacting with.
>> >> >
>> >> > "Willo" wrote:
>> >> >
>> >> >> Hi;
>> >> >>
>> >> >> is there a way to interact with every detail row that is about to
>> >> >> print?;
>> >> >> for acumulating values or perform special validations
>> >> >>
>> >> >> TIA
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||hey it is pretty simple, fortunately you have exact link between tables. So
join them and create a query by selecting the required columns and a
calculated column of (balance +debit - credit) will give you Final balance.
If this is what you want, do it and let me know whether it works.
Amarnath, MCTS
"Willo" wrote:
> maybe im just plain idiot, could you show me how you did it?
> "Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
> news:B8EA7478-B492-4D96-B1D5-73F24693268A@.microsoft.com...
> > Why not it can be done using sql, I created your 3 tables and wrote a
> > query
> > to retrieve the final balance as well using calculated field.
> >
> > Amarnath, MCTS
> >
> > "Willo" wrote:
> >
> >> im trying to do it in sql but i think i cant be done on a single SELECT
> >> statment.
> >>
> >> On RS, all i need is to define a variable, initializing it with INITIAL
> >> BALANCE for each account, suming each debit and subtrating each credit
> >> and
> >> print th FINAL BALANCE for each record.
> >>
> >> It is a simple task on most of other report tools, but i dont know how to
> >> do
> >> it on RS... im very frustrated with this tool!
> >>
> >>
> >>
> >>
> >> "honus" <honus@.discussions.microsoft.com> wrote in message
> >> news:36EE88E4-29A6-44CD-AAB0-0642DC7B0958@.microsoft.com...
> >> > using a conditional statement and ReportItems! should work out for you.
> >> > Name
> >> > your textboxes in tables 1 and 2 for easier referencing.
> >> >
> >> > Or change your SQL to do this for you, which would probably be the
> >> > easiest
> >> > way.
> >> > --
> >> > I am living proof that 95% of programmers are idiots.
> >> >
> >> >
> >> > "Willo" wrote:
> >> >
> >> >> let me try to clarify my problem...
> >> >>
> >> >> lets say i have table1 with accounts
> >> >> --
> >> >> TABLE1
> >> >> accountid balance
> >> >> 1 10000
> >> >> 2 20000
> >> >>
> >> >> and table2 with debits and credits
> >> >> --
> >> >> TABLE2
> >> >> accountid debit credit
> >> >> 1 450
> >> >> 1 300
> >> >> 1 50
> >> >> 2 1000
> >> >> 2 3450
> >> >>
> >> >> and table3 with account names
> >> >> --
> >> >> TABLE3
> >> >> accountid accountname accountnumber
> >> >> 1 AAAAAAAA 3243223432
> >> >> 2 BBBBBBBBB 9832798273
> >> >>
> >> >>
> >> >> i need an output like this
> >> >> ---
> >> >> account
> >> >> 3243223432 AAAAAAAAA initial balance debit credit
> >> >> final balance
> >> >> 10000
> >> >> 450 10450
> >> >> 10450
> >> >> 300 10150
> >> >> 10150
> >> >> 50 10100
> >> >>
> >> >> account
> >> >> 9832798273 BBBBBBBBB initial balance debit credit
> >> >> final balance
> >> >> 20000
> >> >> 1000
> >> >> 21000
> >> >> 21000
> >> >> 3450 17550
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "honus" <honus@.discussions.microsoft.com> wrote in message
> >> >> news:A47B82AB-EA96-4D42-B412-FAEFE83B8ADA@.microsoft.com...
> >> >> > If I understand you correctly, you could use
> >> >> > ReportItems![fieldname].value.
> >> >> > fieldname would be the name of the textbox(es) you are interested in
> >> >> > interacting with.
> >> >> >
> >> >> > "Willo" wrote:
> >> >> >
> >> >> >> Hi;
> >> >> >>
> >> >> >> is there a way to interact with every detail row that is about to
> >> >> >> print?;
> >> >> >> for acumulating values or perform special validations
> >> >> >>
> >> >> >> TIA
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Integrity checks keeps failing

Hi All,
I am driving myself crazy trying to debug this one. My Integrity check
keeps failing due to:
Test (m_freeCnt == freeCnt) failed. Values are 7798 and 8094.
And
Test (emptySlotCnt == 0) failed. Values are 1 and 0.
I have been researching this all morning. I ran a simple select on the
offending table and it turned out there where 2 columns that had a date that
went beyond the year 9999. I have nulled out the offending columns since
these dates are meaningless in the table. I can now run a select on this
table and return all rows, but the integrity check still fails for the same
reasons.
Can anyone please tell me how to fix this error? restoring the DB is out of
the question since i dont have a clean one without this issue. I am new to
this company and this job has been failing for long before I got here.
TIA,
Joejaylou
It is unclear, please provide DDL+ sample data+ keys to test the problem
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:F5825753-E60C-4062-9361-859BBFA5E9C0@.microsoft.com...
> Hi All,
> I am driving myself crazy trying to debug this one. My Integrity check
> keeps failing due to:
> Test (m_freeCnt == freeCnt) failed. Values are 7798 and 8094.
> And
> Test (emptySlotCnt == 0) failed. Values are 1 and 0.
> I have been researching this all morning. I ran a simple select on the
> offending table and it turned out there where 2 columns that had a date
> that
> went beyond the year 9999. I have nulled out the offending columns since
> these dates are meaningless in the table. I can now run a select on this
> table and return all rows, but the integrity check still fails for the
> same
> reasons.
> Can anyone please tell me how to fix this error? restoring the DB is out
> of
> the question since i dont have a clean one without this issue. I am new
> to
> this company and this job has been failing for long before I got here.
> TIA,
> Joe
>|||Can you tell me how to send you that info?
"Uri Dimant" wrote:
> jaylou
> It is unclear, please provide DDL+ sample data+ keys to test the problem
>
>
>
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:F5825753-E60C-4062-9361-859BBFA5E9C0@.microsoft.com...
> > Hi All,
> > I am driving myself crazy trying to debug this one. My Integrity check
> > keeps failing due to:
> > Test (m_freeCnt == freeCnt) failed. Values are 7798 and 8094.
> > And
> > Test (emptySlotCnt == 0) failed. Values are 1 and 0.
> >
> > I have been researching this all morning. I ran a simple select on the
> > offending table and it turned out there where 2 columns that had a date
> > that
> > went beyond the year 9999. I have nulled out the offending columns since
> > these dates are meaningless in the table. I can now run a select on this
> > table and return all rows, but the integrity check still fails for the
> > same
> > reasons.
> >
> > Can anyone please tell me how to fix this error? restoring the DB is out
> > of
> > the question since i dont have a clean one without this issue. I am new
> > to
> > this company and this job has been failing for long before I got here.
> >
> > TIA,
> > Joe
> >
>
>|||Post it here
CREATE TABEL tbl....
INSERT INTO tbl VALUES (.....
Desired result :
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:547DA248-751B-48FB-A5FC-B14D399554BC@.microsoft.com...
> Can you tell me how to send you that info?
> "Uri Dimant" wrote:
>> jaylou
>> It is unclear, please provide DDL+ sample data+ keys to test the problem
>>
>>
>>
>>
>> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
>> news:F5825753-E60C-4062-9361-859BBFA5E9C0@.microsoft.com...
>> > Hi All,
>> > I am driving myself crazy trying to debug this one. My Integrity check
>> > keeps failing due to:
>> > Test (m_freeCnt == freeCnt) failed. Values are 7798 and 8094.
>> > And
>> > Test (emptySlotCnt == 0) failed. Values are 1 and 0.
>> >
>> > I have been researching this all morning. I ran a simple select on the
>> > offending table and it turned out there where 2 columns that had a date
>> > that
>> > went beyond the year 9999. I have nulled out the offending columns
>> > since
>> > these dates are meaningless in the table. I can now run a select on
>> > this
>> > table and return all rows, but the integrity check still fails for the
>> > same
>> > reasons.
>> >
>> > Can anyone please tell me how to fix this error? restoring the DB is
>> > out
>> > of
>> > the question since i dont have a clean one without this issue. I am
>> > new
>> > to
>> > this company and this job has been failing for long before I got here.
>> >
>> > TIA,
>> > Joe
>> >
>>

Monday, March 19, 2012

Interesting Average problem

Monday, March 12, 2012

Interactive sort changes time field values to 0

Interactive sort changes time field values to 0

Friday, March 9, 2012

interact with every detail row

Integrity checks keeps failing

Integrity checks keeps failing

Integrity checks keeps failing

Integrated Security

Blog Archive

About Me