Hey
Im doing a report wich is causing me some trouble:
Im doing a dataset selecting all values regarding the company, and then
im mapping the fields to textboxes in my VS designer. This should
result in a list with all my customers and their data.
Now below each customer i would like to place a table holding data from
another dataset, but i only want the data in the table to correspond to
the company just above the table.
I have placed both the customer data and the table in a list item, but
the problem seems to be the data in the table. I either get evrything
each time for all customers, or i only get the first customer data
below each customer.
Can anyone tell me how i can make this work.
Thanks
JimmyHi
I think there are two ways to solve this
first and the easiest :
get all the data in one dataset and use grouping facility in the reporting
services to group the data
second :
use sub report to build the details and pass the company id from the
parent report to the sub report
Joe
"Jimbo" wrote:
> Hey
> Im doing a report wich is causing me some trouble:
> Im doing a dataset selecting all values regarding the company, and then
> im mapping the fields to textboxes in my VS designer. This should
> result in a list with all my customers and their data.
> Now below each customer i would like to place a table holding data from
> another dataset, but i only want the data in the table to correspond to
> the company just above the table.
> I have placed both the customer data and the table in a list item, but
> the problem seems to be the data in the table. I either get evrything
> each time for all customers, or i only get the first customer data
> below each customer.
> Can anyone tell me how i can make this work.
> Thanks
> Jimmy
>|||Hey Joe
Ok, its not optimal, but it got the job done.
Im actually using a combination of the two.
Your posting lead me on the way though :)
Thanks
Jimmy
Showing posts with label causing. Show all posts
Showing posts with label causing. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Grouping accuracy
Hi,
This is a problem which is causing a few difficulties. We have a time
recording system which just tracks what people are working on. The system
basically keeps tracking of how much time is spent by a person on a certain
type of work.
The problem is when we group the data by different categories, the total
usually never matches exactly. The time spent on work is stored as an intege
r
in the table representing "minutes" e.g. 450 mintues is the equivalent of 1
day (we work a 7.5 hour day".
Run this is Query Analyser and you can see what I mean. You will see that
the the 2 query results returned will differ in their total "days" by 0.01.
The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
What can I do to make this fully accurate? Should I use a different datatype
in the table?
--Create a Test Table, it will be dropped later
create table timeRecords (person char(5),Workdate datetime,timeInMins
integer,worktype varchar(10))
--Insert Some Test Data
insert timeRecords
values('jackp','2005-10-01',225,'Client')
insert timeRecords
values('jackp','2005-10-01',225,'R&D')
insert timeRecords
values('jackp','2005-10-01',60,'Sales')
insert timeRecords
values('peter','2005-10-01',225,'Client')
insert timeRecords
values('peter','2005-10-01',180,'R&D')
insert timeRecords
values('peter','2005-10-01',160,'Sales')
insert timeRecords
values('jackp','2005-10-01',225,'Client')
insert timeRecords
values('jackp','2005-10-01',120,'R&D')
insert timeRecords
values('jackp','2005-10-01',60,'Sales')
insert timeRecords
values('peter','2005-10-01',80,'Client')
insert timeRecords
values('peter','2005-10-01',180,'R&D')
insert timeRecords
values('peter','2005-10-01',15,'Sales')
--Group by Person
select person,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by person
--Group by WorkType
select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by worktype
--Drop the table
drop table timeRecordsIt's because of round-off. When I changed the decimal casts to decimal (9,
4), the first totalled 3.9000, while the second came to 3.9001.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"NH" <NH@.discussions.microsoft.com> wrote in message
news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> Hi,
> This is a problem which is causing a few difficulties. We have a time
> recording system which just tracks what people are working on. The system
> basically keeps tracking of how much time is spent by a person on a
> certain
> type of work.
> The problem is when we group the data by different categories, the total
> usually never matches exactly. The time spent on work is stored as an
> integer
> in the table representing "minutes" e.g. 450 mintues is the equivalent of
> 1
> day (we work a 7.5 hour day".
> Run this is Query Analyser and you can see what I mean. You will see that
> the the 2 query results returned will differ in their total "days" by
> 0.01.
> The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
> What can I do to make this fully accurate? Should I use a different
> datatype
> in the table?
> --Create a Test Table, it will be dropped later
> create table timeRecords (person char(5),Workdate datetime,timeInMins
> integer,worktype varchar(10))
> --Insert Some Test Data
> insert timeRecords
> values('jackp','2005-10-01',225,'Client')
> insert timeRecords
> values('jackp','2005-10-01',225,'R&D')
> insert timeRecords
> values('jackp','2005-10-01',60,'Sales')
> insert timeRecords
> values('peter','2005-10-01',225,'Client')
> insert timeRecords
> values('peter','2005-10-01',180,'R&D')
> insert timeRecords
> values('peter','2005-10-01',160,'Sales')
> insert timeRecords
> values('jackp','2005-10-01',225,'Client')
> insert timeRecords
> values('jackp','2005-10-01',120,'R&D')
> insert timeRecords
> values('jackp','2005-10-01',60,'Sales')
> insert timeRecords
> values('peter','2005-10-01',80,'Client')
> insert timeRecords
> values('peter','2005-10-01',180,'R&D')
> insert timeRecords
> values('peter','2005-10-01',15,'Sales')
> --Group by Person
> select person,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
> decimal(9,2))
> From timeRecords
> group by person
> --Group by WorkType
> select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
> decimal(9,2))
> From timeRecords
> group by worktype
> --Drop the table
> drop table timeRecords|||"NH" <NH@.discussions.microsoft.com> wrote in message
news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> Hi,
> This is a problem which is causing a few difficulties. We have a time
> recording system which just tracks what people are working on. The system
> basically keeps tracking of how much time is spent by a person on a
> certain
> type of work.
> The problem is when we group the data by different categories, the total
> usually never matches exactly. The time spent on work is stored as an
> integer
> in the table representing "minutes" e.g. 450 mintues is the equivalent of
> 1
> day (we work a 7.5 hour day".
> Run this is Query Analyser and you can see what I mean. You will see that
> the the 2 query results returned will differ in their total "days" by
> 0.01.
> The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
> What can I do to make this fully accurate? Should I use a different
> datatype
> in the table?
>
It's not your datatype that is at issue. Decimals (and Numeric) store data
in an exact format. Unlike floats and reals, which are a close
approximation. The problem is that by default SQL Server uses a ROUND UP
strategy when performing computations. If you switch everything to decimal
(9,6) you should see better numbers pop up.
You will see:
jackp 2.033333
peter 1.866667
--
3.900000
Client 1.677778
R&D 1.566667
Sales .6555556
--
3.900001
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||thanks for the reply.
So there is no way really of making this fully accurate?
I understant it is a rounding issue, I thought maybe there was something I
was overlooking or some way of doing it better so there are no rounding
effects.
"Tom Moreau" wrote:
> It's because of round-off. When I changed the decimal casts to decimal (9
,
> 4), the first totalled 3.9000, while the second came to 3.9001.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
>
>|||The numbers need to be reported to 2 decimal places, I guess we will live
with it. I knew the cause of it was rounding up but I wasnt sure if there wa
s
a best practice way of doing this. Sounds like there isn't much we can do
about it.
"Rick Sawtell" wrote:
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> It's not your datatype that is at issue. Decimals (and Numeric) store dat
a
> in an exact format. Unlike floats and reals, which are a close
> approximation. The problem is that by default SQL Server uses a ROUND UP
> strategy when performing computations. If you switch everything to decim
al
> (9,6) you should see better numbers pop up.
> You will see:
> jackp 2.033333
> peter 1.866667
> --
> 3.900000
> Client 1.677778
> R&D 1.566667
> Sales .6555556
> --
> 3.900001
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||The problem is that the numbers work out to repeating decimals. It's just
as likely to be 0.001 too low as .001 too low. If you use WITH ROLLUP, the
total is 3.90:
select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by worktype
with rollup
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"NH" <NH@.discussions.microsoft.com> wrote in message
news:B0DAEC4B-EBF1-4F92-812D-D15852E959E4@.microsoft.com...
> thanks for the reply.
> So there is no way really of making this fully accurate?
> I understant it is a rounding issue, I thought maybe there was something I
> was overlooking or some way of doing it better so there are no rounding
> effects.
> "Tom Moreau" wrote:
>
This is a problem which is causing a few difficulties. We have a time
recording system which just tracks what people are working on. The system
basically keeps tracking of how much time is spent by a person on a certain
type of work.
The problem is when we group the data by different categories, the total
usually never matches exactly. The time spent on work is stored as an intege
r
in the table representing "minutes" e.g. 450 mintues is the equivalent of 1
day (we work a 7.5 hour day".
Run this is Query Analyser and you can see what I mean. You will see that
the the 2 query results returned will differ in their total "days" by 0.01.
The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
What can I do to make this fully accurate? Should I use a different datatype
in the table?
--Create a Test Table, it will be dropped later
create table timeRecords (person char(5),Workdate datetime,timeInMins
integer,worktype varchar(10))
--Insert Some Test Data
insert timeRecords
values('jackp','2005-10-01',225,'Client')
insert timeRecords
values('jackp','2005-10-01',225,'R&D')
insert timeRecords
values('jackp','2005-10-01',60,'Sales')
insert timeRecords
values('peter','2005-10-01',225,'Client')
insert timeRecords
values('peter','2005-10-01',180,'R&D')
insert timeRecords
values('peter','2005-10-01',160,'Sales')
insert timeRecords
values('jackp','2005-10-01',225,'Client')
insert timeRecords
values('jackp','2005-10-01',120,'R&D')
insert timeRecords
values('jackp','2005-10-01',60,'Sales')
insert timeRecords
values('peter','2005-10-01',80,'Client')
insert timeRecords
values('peter','2005-10-01',180,'R&D')
insert timeRecords
values('peter','2005-10-01',15,'Sales')
--Group by Person
select person,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by person
--Group by WorkType
select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by worktype
--Drop the table
drop table timeRecordsIt's because of round-off. When I changed the decimal casts to decimal (9,
4), the first totalled 3.9000, while the second came to 3.9001.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"NH" <NH@.discussions.microsoft.com> wrote in message
news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> Hi,
> This is a problem which is causing a few difficulties. We have a time
> recording system which just tracks what people are working on. The system
> basically keeps tracking of how much time is spent by a person on a
> certain
> type of work.
> The problem is when we group the data by different categories, the total
> usually never matches exactly. The time spent on work is stored as an
> integer
> in the table representing "minutes" e.g. 450 mintues is the equivalent of
> 1
> day (we work a 7.5 hour day".
> Run this is Query Analyser and you can see what I mean. You will see that
> the the 2 query results returned will differ in their total "days" by
> 0.01.
> The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
> What can I do to make this fully accurate? Should I use a different
> datatype
> in the table?
> --Create a Test Table, it will be dropped later
> create table timeRecords (person char(5),Workdate datetime,timeInMins
> integer,worktype varchar(10))
> --Insert Some Test Data
> insert timeRecords
> values('jackp','2005-10-01',225,'Client')
> insert timeRecords
> values('jackp','2005-10-01',225,'R&D')
> insert timeRecords
> values('jackp','2005-10-01',60,'Sales')
> insert timeRecords
> values('peter','2005-10-01',225,'Client')
> insert timeRecords
> values('peter','2005-10-01',180,'R&D')
> insert timeRecords
> values('peter','2005-10-01',160,'Sales')
> insert timeRecords
> values('jackp','2005-10-01',225,'Client')
> insert timeRecords
> values('jackp','2005-10-01',120,'R&D')
> insert timeRecords
> values('jackp','2005-10-01',60,'Sales')
> insert timeRecords
> values('peter','2005-10-01',80,'Client')
> insert timeRecords
> values('peter','2005-10-01',180,'R&D')
> insert timeRecords
> values('peter','2005-10-01',15,'Sales')
> --Group by Person
> select person,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
> decimal(9,2))
> From timeRecords
> group by person
> --Group by WorkType
> select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
> decimal(9,2))
> From timeRecords
> group by worktype
> --Drop the table
> drop table timeRecords|||"NH" <NH@.discussions.microsoft.com> wrote in message
news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> Hi,
> This is a problem which is causing a few difficulties. We have a time
> recording system which just tracks what people are working on. The system
> basically keeps tracking of how much time is spent by a person on a
> certain
> type of work.
> The problem is when we group the data by different categories, the total
> usually never matches exactly. The time spent on work is stored as an
> integer
> in the table representing "minutes" e.g. 450 mintues is the equivalent of
> 1
> day (we work a 7.5 hour day".
> Run this is Query Analyser and you can see what I mean. You will see that
> the the 2 query results returned will differ in their total "days" by
> 0.01.
> The total "days" in query 1 return 3.91 where as it is 3.9 in query 2.
> What can I do to make this fully accurate? Should I use a different
> datatype
> in the table?
>
It's not your datatype that is at issue. Decimals (and Numeric) store data
in an exact format. Unlike floats and reals, which are a close
approximation. The problem is that by default SQL Server uses a ROUND UP
strategy when performing computations. If you switch everything to decimal
(9,6) you should see better numbers pop up.
You will see:
jackp 2.033333
peter 1.866667
--
3.900000
Client 1.677778
R&D 1.566667
Sales .6555556
--
3.900001
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||thanks for the reply.
So there is no way really of making this fully accurate?
I understant it is a rounding issue, I thought maybe there was something I
was overlooking or some way of doing it better so there are no rounding
effects.
"Tom Moreau" wrote:
> It's because of round-off. When I changed the decimal casts to decimal (9
,
> 4), the first totalled 3.9000, while the second came to 3.9001.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
>
>|||The numbers need to be reported to 2 decimal places, I guess we will live
with it. I knew the cause of it was rounding up but I wasnt sure if there wa
s
a best practice way of doing this. Sounds like there isn't much we can do
about it.
"Rick Sawtell" wrote:
> "NH" <NH@.discussions.microsoft.com> wrote in message
> news:5E047499-F635-49B3-B51D-EBC120B59847@.microsoft.com...
> It's not your datatype that is at issue. Decimals (and Numeric) store dat
a
> in an exact format. Unlike floats and reals, which are a close
> approximation. The problem is that by default SQL Server uses a ROUND UP
> strategy when performing computations. If you switch everything to decim
al
> (9,6) you should see better numbers pop up.
> You will see:
> jackp 2.033333
> peter 1.866667
> --
> 3.900000
> Client 1.677778
> R&D 1.566667
> Sales .6555556
> --
> 3.900001
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||The problem is that the numbers work out to repeating decimals. It's just
as likely to be 0.001 too low as .001 too low. If you use WITH ROLLUP, the
total is 3.90:
select worktype,cast(sum(cast(timeInMins as decimal(9,2))/60/7.5) as
decimal(9,2))
From timeRecords
group by worktype
with rollup
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"NH" <NH@.discussions.microsoft.com> wrote in message
news:B0DAEC4B-EBF1-4F92-812D-D15852E959E4@.microsoft.com...
> thanks for the reply.
> So there is no way really of making this fully accurate?
> I understant it is a rounding issue, I thought maybe there was something I
> was overlooking or some way of doing it better so there are no rounding
> effects.
> "Tom Moreau" wrote:
>
Monday, March 12, 2012
Group Case Sensitive
It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?
After a little experiment, I can say the group break is definitely case sensitive.|||
Can anyone verify that it is in fact case sensitive? How to change?
I am running SQL Server 2000 and the database that I am querying is not case sensitive.
In report designer, edit the dataset properties, go to the "Data Options" tab and for the Case Sensitivity property select "False" instead of "Auto".
-- Robert
|||Made the suggested change and it didn't affect the report grouping.After a little experiment, I can say the group break is definitely case sensitive.|||
Note: you can also just change the grouping expression to make it case-insensitive by applying the LCase() function which converts the string to lower case:
=LCase(Fields!Group.Value)
-- Robert
|||Just now saw the LCase response and tested it on the problem report. It did solve the problem!|||Just a clarification on what I have found in this case.
Since DataRegion/DataSet/Grouing can be used for scoping puproses I thought it would be good to broaden this case sensitivity to include all of these.
What I found was the following on how RS compares names (short form 'I' for Case Insensitive and 'S' for Case Sensitive):
a) Grouping - S ("a" is different then "A")
b) DataSet - I
c) DataRegion - I
d) Grouping to DataSet - S (Grouping "A" can exist even if DataSet "a" exists)
e) Grouping to DataRegion - I
f) DataSet to DataRegion - I
I am perplexed as to why situations a) and especially d) exist. But it appears to be the way things work.
DK
|||Dataset fields are also S. It's probably because the report definition is compiled to a .NET assembly but I am all for (I).|||Teo is regarding case-sensitiveness. The main reason is how the ReportObjectModel works and that making scope names case-insensitive would have a negative overall performance impact.
-- Robert
Group Case Sensitive
It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?
After a little experiment, I can say the group break is definitely case sensitive.|||
Can anyone verify that it is in fact case sensitive? How to change?
I am running SQL Server 2000 and the database that I am querying is not case sensitive.
In report designer, edit the dataset properties, go to the "Data Options" tab and for the Case Sensitivity property select "False" instead of "Auto".
-- Robert
|||Made the suggested change and it didn't affect the report grouping.After a little experiment, I can say the group break is definitely case sensitive.|||
Note: you can also just change the grouping expression to make it case-insensitive by applying the LCase() function which converts the string to lower case:
=LCase(Fields!Group.Value)
-- Robert
|||Just now saw the LCase response and tested it on the problem report. It did solve the problem!|||Just a clarification on what I have found in this case.
Since DataRegion/DataSet/Grouing can be used for scoping puproses I thought it would be good to broaden this case sensitivity to include all of these.
What I found was the following on how RS compares names (short form 'I' for Case Insensitive and 'S' for Case Sensitive):
a) Grouping - S ("a" is different then "A")
b) DataSet - I
c) DataRegion - I
d) Grouping to DataSet - S (Grouping "A" can exist even if DataSet "a" exists)
e) Grouping to DataRegion - I
f) DataSet to DataRegion - I
I am perplexed as to why situations a) and especially d) exist. But it appears to be the way things work.
DK
|||Dataset fields are also S. It's probably because the report definition is compiled to a .NET assembly but I am all for (I).|||Teo is regarding case-sensitiveness. The main reason is how the ReportObjectModel works and that making scope names case-insensitive would have a negative overall performance impact.
-- Robert
Group Case Sensitive
It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?
After a little experiment, I can say the group break is definitely case sensitive.
|||
Can anyone verify that it is in fact case sensitive? How to change?
I am running SQL Server 2000 and the database that I am querying is not case sensitive.
In report designer, edit the dataset properties, go to the "Data Options" tab and for the Case Sensitivity property select "False" instead of "Auto".
-- Robert
|||Made the suggested change and it didn't affect the report grouping.After a little experiment, I can say the group break is definitely case sensitive.
|||
Note: you can also just change the grouping expression to make it case-insensitive by applying the LCase() function which converts the string to lower case:
=LCase(Fields!Group.Value)
-- Robert
|||Just now saw the LCase response and tested it on the problem report. It did solve the problem!|||Just a clarification on what I have found in this case.
Since DataRegion/DataSet/Grouing can be used for scoping puproses I thought it would be good to broaden this case sensitivity to include all of these.
What I found was the following on how RS compares names (short form 'I' for Case Insensitive and 'S' for Case Sensitive):
a) Grouping - S ("a" is different then "A")
b) DataSet - I
c) DataRegion - I
d) Grouping to DataSet - S (Grouping "A" can exist even if DataSet "a" exists)
e) Grouping to DataRegion - I
f) DataSet to DataRegion - I
I am perplexed as to why situations a) and especially d) exist. But it appears to be the way things work.
DK
|||Dataset fields are also S. It's probably because the report definition is compiled to a .NET assembly but I am all for (I).|||Teo is regarding case-sensitiveness. The main reason is how the ReportObjectModel works and that making scope names case-insensitive would have a negative overall performance impact.
-- Robert
Subscribe to:
Posts (Atom)