Matrix Row Counts

I have a matrix which sums sales to a customer by Month/Year.
Rows - Customer Name, Sum(NetSales)
Columns - Month/Year
What I want to capture is the total number of rows in the matrix.
For example, I am passing the report 144 records from my query; however
there are only 35 distinct customers.
Is there a function or property of the matrix that will give me this
information? I am currently doing a SELECT DISTICT on customer no in
another query to get this result.Using the CountDistinct Aggregate would do the job so you would not
need the second query.

Matrix hide zeros

Hi,
I have built a matrix report that counts the number of orders in a day by
customer and displays all dates regardless of whether any orders were placed
then. I would like to hide the zeros for customers without orders, but I
cannot figure out how to filter those out or alternately make the zeros
display as white on a white background.
I want it to look like this:
1/1/07 1/2/07 1/3/07 1/4/07 1/5/07 Totals
Acme 1 1 4
7
Bernett 2 1 3 2
7
Chapmen 5 1 2
8
Totals 3 6 5 0 8
22
Any help is greatly appreciated!
KathyI can think of three options.
1. Alter the query to return NULL where the value is 0
2. Place a filter expression on the Dataset in Reporting Services. This is
accessed from the Data tab
3. Place an expression on the cell in the Matrix layout to test for 0 and
return nothing or a blank space
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:2C3FB54D-8014-4771-A15E-C22551196B0D@.microsoft.com...
> Hi,
> I have built a matrix report that counts the number of orders in a day by
> customer and displays all dates regardless of whether any orders were
> placed
> then. I would like to hide the zeros for customers without orders, but I
> cannot figure out how to filter those out or alternately make the zeros
> display as white on a white background.
> I want it to look like this:
> 1/1/07 1/2/07 1/3/07 1/4/07 1/5/07
> Totals
> Acme 1 1
> 4
> 7
> Bernett 2 1 3
> 2
> 7
> Chapmen 5 1 2
> 8
> Totals 3 6 5 0
> 8
> 22
> Any help is greatly appreciated!
> Kathy|||Did you try to don't select the lines at zero ?
SELECT * FROM [table] WHERE
[field]>0
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:2C3FB54D-8014-4771-A15E-C22551196B0D@.microsoft.com...
> Hi,
> I have built a matrix report that counts the number of orders in a day by
> customer and displays all dates regardless of whether any orders were
> placed
> then. I would like to hide the zeros for customers without orders, but I
> cannot figure out how to filter those out or alternately make the zeros
> display as white on a white background.
> I want it to look like this:
> 1/1/07 1/2/07 1/3/07 1/4/07 1/5/07
> Totals
> Acme 1 1
> 4
> 7
> Bernett 2 1 3
> 2
> 7
> Chapmen 5 1 2
> 8
> Totals 3 6 5 0
> 8
> 22
> Any help is greatly appreciated!
> Kathy

Matrix custom subtotals ? How is it possible ?

Hi ALL,
I have a problem and i becoming crazy !
I create a report like this using a Matrix in Reporting Services
Sales
Area 1 250
Customer 1 100
Customer 2 150
Area 2 60
Customer 3 40
Customer 4 20
I need to add a total row witch is the sum of AREA 1 + AREA 2 lke this
IMPORTANT : rows in matrix becomes directly from my query... they are
not grouping.
Sales
Area 1 250
Customer 1 100
Customer 2 150
Area 2 60
Customer 3 40
Customer 4 20
TOTAL 310
How can I express the contition... If I add a total to the matrix my
total is 620 (wrong !...)
Thank you !
Gas1970On Jun 13, 11:16 am, alberto.gasta...@.libero.it wrote:
> Hi ALL,
> I have a problem and i becoming crazy !
> I create a report like this using a Matrix in Reporting Services
> Sales
> Area 1 250
> Customer 1 100
> Customer 2 150
> Area 2 60
> Customer 3 40
> Customer 4 20
> I need to add a total row witch is the sum of AREA 1 + AREA 2 lke this
> IMPORTANT : rows in matrix becomes directly from my query... they are
> not grouping.
> Sales
> Area 1 250
> Customer 1 100
> Customer 2 150
> Area 2 60
> Customer 3 40
> Customer 4 20
> TOTAL 310
> How can I express the contition... If I add a total to the matrix my
> total is 620 (wrong !...)
> Thank you !
> Gas1970
Traditionally, in some cases, I obtain my sums/totals in the stored
procedure/query that is sourcing my matrix report. This should be a
good option in your case as well. Also, depending on the complexity of
the matrix report, you may need to use a while loop/cursor to
determine the sum/totals. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Matrix Calculation - % Growth

I have a report that list customer's 2004 and '05 Sales by month:
Jan Feb Mar ...
Acct 10 2004 $$$ $$$ $$$ ...
2005 $$
% Growth
Acct 11 2004 $$$ $$$ $$$ ...
2005 $$
% Growth
The report currently list all of our accounts in a Matrix report. How can I
calculate the "% Growth"?
The Subtotal function obviouly isn't what I need. Any help is much
appreciated!
DavidDJONES wrote:
> I have a report that list customer's 2004 and '05 Sales by month:
> Jan Feb Mar ...
> Acct 10 2004 $$$ $$$ $$$ ...
> 2005 $$
> % Growth
> Acct 11 2004 $$$ $$$ $$$ ...
> 2005 $$
> % Growth
> The report currently list all of our accounts in a Matrix report. How
> can I calculate the "% Growth"?
> The Subtotal function obviouly isn't what I need. Any help is much
> appreciated!
hmm *scratching head**untestet*
=(100-(Sum(Fields!Jan 2004.Value)/(Sum(Fields!Jan 2005.Value)/100)))
should be the growth in percent
i.e. 100-(1555)/(1888/100) ~ 17,64%
regards
frank|||I was planning to place this formula in a group footer, but just realized
that I can't add a group footer in a Matrix style report. Is that correct?
Could I use a similar formula in a tabular report?
Thanks
"Frank Matthiesen" wrote:
> DJONES wrote:
> > I have a report that list customer's 2004 and '05 Sales by month:
> >
> > Jan Feb Mar ...
> > Acct 10 2004 $$$ $$$ $$$ ...
> > 2005 $$
> > % Growth
> >
> > Acct 11 2004 $$$ $$$ $$$ ...
> > 2005 $$
> > % Growth
> >
> > The report currently list all of our accounts in a Matrix report. How
> > can I calculate the "% Growth"?
> >
> > The Subtotal function obviouly isn't what I need. Any help is much
> > appreciated!
>
> hmm *scratching head**untestet*
> =(100-(Sum(Fields!Jan 2004.Value)/(Sum(Fields!Jan 2005.Value)/100)))
> should be the growth in percent
> i.e. 100-(1555)/(1888/100) ~ 17,64%
>
> regards
> frank
>
>

matrix - change color based on data trend up or down

I have a dataset with customer, date and count columns. Then I have a matrix
based on that information as follows:
Oct 2007 Sep 2007 Aug 2007
Customer A 123 432 959
Customer B 984 394 345
Customer C 2459 2335 234
I would like to have the color of the count red if the number is lower for
that customer for the next date (i.e. 123) and green if the number is higher
for the next date (i.e. 984).
Any idea how I do this?
StephanieOn Oct 1, 9:49 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset with customer, date and count columns. Then I have a matrix
> based on that information as follows:
> Oct 2007 Sep 2007 Aug 2007
> Customer A 123 432 959
> Customer B 984 394 345
> Customer C 2459 2335 234
> I would like to have the color of the count red if the number is lower for
> that customer for the next date (i.e. 123) and green if the number is higher
> for the next date (i.e. 984).
> Any idea how I do this?
> Stephanie
You might be able to use 'iif' in conjunction with the 'Previous'
function to set the background color. I'm not sure of the exact
syntax, but this might lead you in the right direction.
=iif(Previous(Fields!Count.Value) < Fields!Count.Value, "Green",
"Red")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Matching records using Business Intelligence Studio

Hello,

I have a database with thousands of records that contain personal details of customers. Some of these records pertain to the same customer - however, they have been submitted by different people, so they differ slightly in detail.

I've been looking to see if any of the data mining tools provided by Business Intelligence Studio in SQL Server 2005 will enable me to achieve a high degree of accuracy in matching records that pertain to the same customer. From what I can see, these tools seem more suited to making general predictions based on large groupings rather than the kind of precise prediction I am looking for.

So I'd appreciate it if anyone could tell me if there is any way I could use Business Intelligence Studio to match these 'duplicate' records together, or whether I will have to create a more SQL-based solution which attempts to match the customer records using SELECT statements and making assumptions about the data.

TIA,

Kweri

One solution is to start by creating an Integration Services project.

In the project, define a Data Flow task and add the following transforms:

- a Data Source transform, which reads from your database

- a Fuzzy Matching transform

- a Data Destination transform

The Fuzzy Matching Integration Services transform is intended to resolve the kind of problem you describe (matching records based on similarity)