Question On How To Display Data In This Unique Situation Using Matrix.....

Mar 16, 2007

Hi guys,

Man I do come up with strange scenarios, but that is the joy of working in software field right ? ;-)
First off, thanks to anyone taking their time to read this, and Ihope this post paints a clearer picture better than my previous posts.

I have an old stored procedure (which I didn't create) that produces a dataset of the following:-

((All names and values had been changed to protect confidentiality))

region           agent_type               mailpackage1                    mailpackage2              mailpackage3
New York        Agenttype1                       2000                             2300                          0
New York        Agenttype2                          0                                   0                               5
New York        Agenttype3                        150                                2                             4000
Central            Agenttype2                        1234                              5678                        9
Central            Agenttype4                        435                                1                               0
MidWest         Agenttype1                        555                                0                               0
West                Agenttype1                        1                                    45                             0
West                Agenttype2                        0                                    2                               3

A little bit of explanation:-
Each region can have any type of agents, specified by the number to distinguish different agent types. these agent types mail specific packages to their customers depending on the situation and what the customers asked for. the numbers in each mail package indicate the total that had been sent out by a particular type of an agent. So in this case we are not dealing wtih how many agents are there, just how many packages had been sent out by a specific type of an agent in a region.

Previously the report was produced like you would see in the above dataset. However the client would want it the other way around. Though I didn't show it here, there are plenty of other packages but I am picking three for clarity sake.

So the "new" Report would have to look something like this.

Region: New York
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                        2000                             0                                  150                            0
Package2                                        2300                             0                                  2                                0
Package3                                          0                                  5                                  4000                          0

--------------- break page ---------------------------

Region: Central
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                        0                               1234                              0                                   435
Package2                                        0                                5678                             0                                      1
Package3                                        0                                  9                                  0                                      0

---------- break Page -------------------- and so on

I had created a table in the RS  that looked like the above with expressions written into the each cell that holds a value. The expression is

=IIF(Fields!agent_type = "AgentType1", mailpackage1.Value, Cint(0)) in the first row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage2.Value, Cint(0)) in the second row, first column of the table.
=IIF(Fields!agent_type = "AgentType1", mailpackage3.Value, Cint(0)) in the third row, First column of the table.
And so on....... alternating between agent_type and mailpackage for each cell.

Grouping1: Group by Region, insert page after each group.

What happened was the following:- ((I am putting the first region, because it is also happening for the other regions too)

Region: New York
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                        2000                             0                                  0                                0
Package2                                        2300                             0                                  0                                0
Package3                                          0                                  0                                  0                                0

--------------- break page ---------------------------

Region: New York
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                          0                                  0                                  0                                0
Package2                                          0                                  0                                  0                                0
Package3                                          0                                  5                                  0                                0

--------------- break page ---------------------------


Region: New York
                                                    AgentType1              AgentType2                AgentType3              AgentType4
Package1                                          0                                  0                                  150                            0
Package2                                          0                                  0                                  2                                 0
Package3                                          0                                  0                                  4000                          0

--------------- break page ---------------------------

(on a side note, this region didn't print out AgentType4 because there were no data associated with it)

The question is, is there anything else I  could have done to prevent this  ? as you can see, the data is correct and placed in their right cells but somehow, they won't join together. I got a feelin that it has something to do with the expression that I had put  in each cell.

Can someone help or point me in the right direction ? This is really bothering me and I couldn't figure out why it was doing this. Couldnt find any links or maybe i am putting in the wrong keywords in the search. Thanks muchly !

Bernard Ong

View 14 Replies


ADVERTISEMENT

Matrix - No Data Display NaN

May 6, 2007

Hello,



I have matrix object in reportin (SQL 2005 reporting services)

When in some field have no data it display NaN.

How can I change it ?

I try to use iif(my function = nothing.....) but didn't help

View 1 Replies View Related

Display Data From Matrix In Footer

Jun 6, 2007

I have a matrix within a list and would like to display a field value in the page footer on each page. In the page footer, if I reference a textbox from the matrix the value is always blank? I realize this is most likely a scoping issue but how can I accomplish this?



Any ideas?



Thx

View 2 Replies View Related

Reporting Services :: How To Display 3 Rows Of Data In A Matrix

Nov 3, 2015

My stored procedure give me top 3 activities. My SSRS report design, I am able to display first activity when I select the Activity field from the Stored procedure, how to display 3 activities for each category.. Also I need a solution to sort the categories based on the requirement below.

View 2 Replies View Related

Display Problem In Matrix Report

Jun 27, 2007

I have a matrix contained inside a rectangle. When the report is published, a fraction of the last column of matrix is stripped out, about 0.2 cm of it. When I hit Refresh, the report renders properly displaying the last matrix column in its entirety. Any ideas why its doing that?

View 2 Replies View Related

How Do You Display Headers In The Row Group In A Matrix??

Jun 26, 2007

Hi, I am creating a matrix and adding multiple row groups. The problem is that I am unable to display headers for each of these groups - can anyone help??

View 6 Replies View Related

DISPLAY COLUMN GRANDTOTALS IN A MATRIX REPORT

Mar 21, 2007

Hi there...

I have a matrix report within a list so it functions as a report with dynamic columns.

Matrix a:

a b c d e

x 1 2 4 9 12 <NEED SUBTOTAL across Columns for row x>

y 6 2 4 5 9 <<NEED SUBTOTAL across Columns for row y>

TOTAL <need totals going down rows and need a grand total>



Remember the number of rows and columns are dynamic so I have NO CLUE how to add a column and display it if and only all the columns have been displayed ...sort of like a COLUMN and ROW footer for a matrix report.



Any help will be appreciated

thank you

Prashanth Prasanna

View 1 Replies View Related

Green Bar Matrix Display Difference? VS2005 Vs Web View

Sep 12, 2007

Hello:

I've created a simple greenbar matrix using the tips suggested by Chris Hays. In Visual Studio the row totals display with the correct alternating background color, but when viewing the report via the web, the report does not render any background color for the totals?

I've attached a rough example (I trimmed out a couple of colums, so don't try to add up the column values displayed with the total value). As you can see, the values for the Total column do not get shaded, even though in VS2005 it shows the Total column shading the rows correctly.












200707
200708
200709
Total

AM 12
-
-
-
-

AM 1
-
-
-
-

AM 2
-
-
-
-

AM 3
-
-
-
-

AM 4
-
-
-
-

AM 5
-
-
-
-

AM 6
-
-
-
-

AM 7
-
-
1
2

AM 8
4
5
2
11

AM 9
1
2
1
6

AM 10
4
5
3
12

AM 11
2
3
2
8

PM 12
10
19
4
103

PM 1
3
3
1
8

PM 2
4
7
2
13

PM 3
-
12
1
16

PM 4
1
5
-
8

PM 5
-
-
-
-

PM 6
-
-
-
-

PM 7
-
-
-
-

PM 8
-
2
-
2

PM 9
-
-
-
-

PM 10
-
-
-
-

PM 11
-
-
-
-

I know how to explicitly set the various properties of the totals value field (i.e, click on the little green triangle to set the properties of totals), but I can't figure out how to make it dynamic. Trying to reference ReportItems!ColorTextbox.value generates scope error.s

Thanks in advance for help on this one.
--Pete

View 7 Replies View Related

Controlling Number Of Rows To Display In A Table And Matrix On One Page

Jul 19, 2007

Is there a way to control how many Detail Rows are displayed on one page in Table and Matrix controls?

View 1 Replies View Related

SQL Server 2014 :: Query To Display Unique Comments Based On Certain Conditions

Jul 1, 2015

TABLE DEFINITION

Column NameComments
CustomerIDINT
SequenceNoINT
CommentsVARCHAR(MAX)

CUSTOMER TABLE

CustomerIDSequenceNoComments
11ABC D
12CDE
13ABC
14ABC D
15CDE
16abc
27ABC DEF
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST

OUTPUT

CustomerIDSequenceNoComments
13ABC
14ABC D
15CDE
16abc
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST

Records should be filtered by

1.Display only Unique Comments from Customer Table for all the customers,
2.If Comments are same then display the row which has maximum SequenceNo

View 2 Replies View Related

Reporting Services :: Display Columns When There Is No Data To Display

Apr 30, 2015

I would like to display a portion of report where there is data or no data

There is data subreport  display   

     Product Name Latex Gloves  
     Product ID      
xxxx5678

 There NO data in the subReport
 
  Product Name                          
   Product ID    

View 3 Replies View Related

Collapsing/Expanding Group Data In Table And Matrix Data Regions

May 30, 2007

Hi,



Is it possible to create Expand/Collapse functionality for the grouped data in Table and Matrix data regions? Essentially, the idea is for the user to be able to see the group/subgroup data if she wishes to by clicking on (+/-) symbols, as is usually the case in Tree View style data grid control in web apps. Any ideas how to accomplish the same in reporting services?



Thanks.

View 1 Replies View Related

Delete Rows With Duplicate Column Data But Unique Row Data

May 25, 2000

Hello,

This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.

I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.

I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,

Kerry

View 3 Replies View Related

Show Detail Data On A Matrix Rather Than Aggregate Data

Dec 12, 2007



Hi all experts,

I have a result set like:





Project

Milestone

Info


Project1

M1

Info1


Project1

M1

Info2


Project1

M2

Info3


Project2

M1

Info4


Project2

M2

Info5


Project3

M1

Info6


I need to create a report like:







M1

M2


Project1

Info1
Info2

Info3


Project2

Info4

Info5


Project3

Info6




But while I use matrix to build this report, I got the result like:







M1

M2


Project1

Info1

Info3


Project2

Info4

Info5


Project3

Info6




The report will not show the multiple records on the row group "Project" like item "Info2".

After I referred to the similar problem mentioned on this forums, I tried to use "RowNumber("Matrix1_Project")" to resolve it. But still I got the result like:









M1

M2


Project1

RowNumer

Info1






RowNumer

Info1






RowNumer



Info3


Project2

RowNumer

Info4






RowNumer



Info5


Project3

RowNumer

Info6



It would be much appreciated if anyone could give me some hints to complete the report I need.

Thanks in advance,
Steve Wang 2007/12/12

View 5 Replies View Related

Import Data Where Current Data Has Unique Identity

Aug 10, 2000

I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition.

How can I maintain a unique field to act as primary key in my data when
I want to add (and delete) data that doesn't have this field.

I tried adding the uniqueidentifier field but this gives error message.

The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming

Any help appreciated,Thanks, Keith

View 1 Replies View Related

HELP - Urgent Situation

Feb 10, 2004

Hey all

I've got a DTS package with a script that does this:

Set xlApp = CreateObject("Excel.Application")

This fails though. Do I have to install MS Office on the machine the SQL Server is on? Or can I simply copy a few DLLs over and register them??

Thanks! I gotta get this solved within 5 hrs :(

View 9 Replies View Related

Sorting Situation

May 14, 2004

Hi,

I am trying to get data as it is available in the table but every time I run sql statement it shows in asending order. How can I avoid this asending order situation?

Select distinct db_contract,title1 from titles where db_contract=39624
db_contract Title1
39624 HIGHWAY
39624 M-24-001
39624 M-24-002
39624 R-24-003
39624 M-24-006
39624 Z-24-007

Any help will be appreciated.

View 7 Replies View Related

Do I Use An Index In This Situation?

Jun 1, 2008

Hi everyone -

I am relatively new to this and trying to make my database as efficient as possible.

Here is the situation. In my database, there are multiple "stocks" that each have a "pricing history". When something happens that changes the price of the stock, an entry containing information about the time the change occured, who changed it, etc. is added.

I have many stocks. The information of these stocks is independant in the sense that I never really need to grab/compare information about two stocks at once. I only look at one stock at a time.

So, do I set up a "pricing history" table for every single stock (pricinghist1,pricinghist2,etc.)? Or do I set up one "pricing history" table it and index the table by stock number?

Are these equivilant? Is one more efficient than the other?

Thanks!
N

View 3 Replies View Related

How Would You Handle This Situation?

Apr 29, 2008

Here is the basic situation.

(1) I have 2 packages that run independently of eachother.
(2) Both are scheduled in SQLServer Agent Jobs as separate jobs.
(3) Job A is scheduled to run every 12 hours and Job B is scheduled to run every 10 minutes.
(4) However, I want to prevent Job B from running if Job A happens to be running.
(5) It is unknown exactly how long Job A will take to finish so I can't schedule Job B around it.

The way I wanted to approach this situation is as follows.

Within Job A's package, create a "marker" file when the package starts and delete it when the package finishes. So the existence of this marker file will tell Job B's package if it should run or not.

The concept is simple, but I'm not sure how to implement this.

For example, to create the marker file, I would use a File System Task, but I don't see an option in there to "create file". (However, I do see an option for "delete file".) Also, what task would I use in Job B's package to check if the marker file exists.

Lastly, If you have better approach, I would like to hear about it.

Thank you.




View 11 Replies View Related

A Tough Situation To Get ID....

Apr 17, 2008



Hello All

I have a table called Tax Act

In that I have Tax Act ID. Which is supposed to be NOT NULL.

I have to create IDs by my self using some MAX and MAX+1 incrementing function.

Please guide me on how to

View 3 Replies View Related

What's The Best Way To Approach This Situation?

Jul 12, 2007

Hello everyone,



I'm creating a database for a new application and I'm currently facing a design problem, regarding a business requierment for the membership module.



The Membership Module of the application has several business requierments, specified by the client. One of them is the ability to add and remove details about their members. So far, I've created a schema, named Person, which will contain a number of tables responsible for everything related with the membership, as an individual.



To help you guys understand the design I'm trying to implement, I'll post the fields of two of the tables that belong to the Person schema, as follows:



Person.Base

Id (uniqueidentifier)

UniqueIdTypeId (uniqueidentifier)

UniqueId (uniqueidentifier)

Password (char(88))

PasswordSalt (char(10))

PasswordRecoveryQuestion (nvarchar(256)) [NULLABLE]

PasswordRecoveryAnswer (char(88)) [NULLABLE]

CreationDate (datetime)

AuthenticationWindowStartDate (datetime)

AuthenticationWindowAttemptCount (tinyint)

IsActive (bit)

IsBanned (bit)

IsLocked (bit)

Status (bit)



Person.Emails

PersonId (uniqueidentifier)

EmailAddress (nvarchar(256))

CreationDate (datetime)

ValidationCode (char(10))

ValidationDate (datetime) [NULLABLE]

IsValid (bit)

IsPrimary (bit)

IsRollbackTarget (bit)

Status (bit)



So far so good. This design works great to preserve the data integrety. Nonetheless, this is where the problems start. Now, imagin you need to let someone from that company add an item to the user (through the application). Let's say we want to allow the company application manager to add an item to the person called "PreviousEmployer". Such item would then be used for statics, thus would probably need to be indexed.



In order to meet this business requierment I would create some addicional tables. Let's get started:



Person.CustomFields

Id (uniqueidentifier)

Name (nvarchar(50))

Description (nvarchar(3000)) [NULLABLE]

Status (bit)



Person.CustomField_Value

FieldId (uniqueidentifier)

Value (nvarchar(450))



This could work just fine if both are indexed (that's why the nvarchar size is set to 450). But I'm guessing this is far from the optimal solution for many reasons, one of them being the efficiency of the index if the company decides to go and use this for a flag (true or false [bit]). Another "solution" breaking scenario would be if the company wants to add the CV of the person, situation in which we were unlikely to be able to add a file in this datafield.



How would you guys approach this issue? The bottom line is that the client needs to be able to add pretty mcuh any type of custom field and perform searches againts it. So, besides being a dynamic solution it needs to be efficient.



Best regards and thanks in advance.

View 11 Replies View Related

Importing Unique Data && MAX Data To Table Using DTS

Nov 28, 2005

I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table.

Now that I have the data in this table, I want to import any data that is not in my main table.

This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column.

DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table.

How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates.

Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar.


Code:



select
partno2,
MAX (partno) as partno,
alt,
MAX (C_alt) as C_alt,
Max (cmpycd) as cmpycd,
MAX (type) as type,
compFN,
MAX (pndesc) as pndesc,
MAX (equipment) as equipment

into tbl_CLEANED
from tbl_CLEANING
group by partno2, alt, compFN
ORDER BY partno, compFN



The three main columns I need to check against are:
partno2
alt
compFN
I have named the columns the same in both tables.

partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table.

How can I compare these tables and import only unique info to the MAIN table?

In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?

View 3 Replies View Related

How Can I Use Cascade Delete For The For The Following Situation.

Jan 25, 2008

Hi, I have the following tables:
Categories {Category_ID, Column2, ...} 
Articles { Article_ID, Category_FK, Column3, ...}
Discussions {Discussion_ID, Article_FK, Column3, ...}
Now, all what I have is just category_ID value (Let us say 3), how can I do cascade delete to delete category's record that its ID = 3 and delete all articles and all discussions that found in that category? 
 

View 2 Replies View Related

Very Urgent Situation (SQL Server 6.5)

Apr 3, 2002

I have a very urgent situation and to do with SQL Server 6.5, cannot start and the error message I got was: Initdata: Suballocation for buffer pages failed (838860 Bytes requested. Can anyone help me urgent matter.

Regards,

View 1 Replies View Related

Reproduce A Deadlock Situation

Dec 6, 1999

I need help on reproducing a deadlock. The sample that I have so far is only creating blocking and it never deadlocks. It's because I'm not sure what order the system tables are being help when creating temp tables in a transaction, therefore, I can't tell which pages need to be requested from another transaction to cause the deadlock. Any help will be much appreciated.

Thanks,
Mike

View 2 Replies View Related

Is This A Stored Procedure Situation?

Jun 21, 2004

We have 2 SQL tables being accessed through an Access form. The tables are an ORDER table and an ORDER-DETAIL table comprised of data regarding the Parts in any given Order. (Yes -- the classic Order-Entry situation.) The Access form is used to view/create new Orders, and shows ORDER data in fields, plus has a large field which presents a "spreadsheet"-like view of the related records from the ORDER-DETAIL table.

The users enter and modify data in the ORDER-DETAIL table directly through this "spreadsheet" in the Access form. However, because there is no PARTS table yet (that's part of what I'm working on), they have to enter part numbers and descriptions *manually* in each ORDER.

So... here's my question:

After I implement a PARTS table, I would like for users to be able to open an ORDER in the Access form, type in a Part # in a row of the ORDER-DETAIL "spreadsheet", and then have the rest of the row populate with the appropriate Part description and other data from the PARTS table. How do I go about making that a reality? Some kind of stored procedure triggered by a change in the Part # field? Ha ha – if so, I am clueless as to how to make that happen. ANY information would greatly appreciated!

Thanks!
whill96205 the Noob :confused:

View 3 Replies View Related

How To Fix This Situation On Replication?[Urgent.]

Jun 26, 2004

Hi, all..
I by mistake Overwrites distribution DB.
Server1 and Server2 replicates each other.
Applications are using Server1, and Server2 is back up purpose.
When server1 fails application redirected SErver2, using same data since there is replication.
Server2 had distribution DB.

Now there is no distribution DB.. it's messed up...
I want to restore replication between Server1 and Server2.
I tried to make replication from Server1, I got errors..

How can I do this???

View 1 Replies View Related

Tricky Replication Situation

Nov 18, 2005

I have a Transaction replication running between 2 SQl 2000 Servers. The distributor is on seperate machine.

Server1 is an online Db which is sending replicated trn to Server2 which is a reporting Server. Because of space issues, I have to remove about 100 Million Rows from Server1 Table without affectiing Server2 Table (Server 2 Retains those rows)
Is there any Stored Proc available to disable the replication only during that time ?

Thanks

View 3 Replies View Related

Produce All Combinations For A Situation

Sep 6, 2013

How would I write a query to produce all combinations for a situation such as the following?

Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.

Adjective table looks like e.g.

ID Adj
1 good
2 so so
3 bad

Noun table looks like e.g.

ID Noun
1 apple
2 orange
3 banana

And the result set would look like

This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.

I would take a stab at this myself and post even something that doesn't work...

View 1 Replies View Related

Do I Need A Derived Table For This Situation

Feb 1, 2007

OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW.
CONDITION 1: RateReview field should have yesterday's date
CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier
CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL
ONe Customer can have many transwactions
Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets.
Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and
do not send any email. This would be done by some tool known as StrongMail.
SELECT
CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID,
CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID,
Film_id as MovieId,
First_nm as FirstName,
Last_nm as LastName,
Email_nm as EmailAddress
FROm
Table1 where RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), -1) -- Greater or same as Yesterday day
and RateReview_dm < dateadd(day, datediff(day, 0, getdate()), 0) -- Less than today's date
and
and Terminate_dm is null
(I don;t know what condition to give that same customer good should not be send email again if send once)
i don't know whether i need to create a derive table or it can work without drive table

Cananyone help me with this query please

View 2 Replies View Related

Matrix Data

Nov 12, 2007

Is there a way in matrix where i can determine if a certain field in my resultset has no value??Thanks

View 1 Replies View Related

Matrix Data

May 6, 2008



I have a stored procedure that give the (sample) data as below.

Country Category State/Province Year Month quantity
Canada cat1 AB 2008 1 10
Canada cat2 BC 2008 2 10
Canada cat3 AB 2008 3 10
Canada cat4 SK 2008 1 10
US cat1 BB 2008 1 20
US cat3 GJ 2008 2 15
US cat4 CO 2008 2 15

When I configure the matrix I EXPECT TO SEE THE FOLLOWING:




AB BC SK
2008 2008 2008
1 3 2 1
________________________________________________________________________

Canada cat1 10
cat2 10
cat3 10

cat4 10




BB GJ CO
2008 2008 2008
1 2 2
________________________________________________________________________

US cat1 20
cat3 15
cat4 15



BUT I AM GETTING THE FOLLOWING RESULT:





AB BC SK BB GJ CO
2008 2008 2008 2008 2008 2008
1 3 2 1 1 2
________________________________________________________________________

Canada cat1 10
cat2 10
cat3 10

cat4 10




BB GJ CO AB BC SK
2008 2008 2008 2008 2008 2008
1 2 2 1 3 2 1
________________________________________________________________________

US cat1 20
cat3 15
cat4 15



In Crystal it automatically displays as above. How do I do this in SRS? Matrix is not showing the state relevant to the country properly. It is showing all the states.

Thank you for your help in advance

View 5 Replies View Related

Stuck - Cant Figure Out A Query For This Situation

Jan 3, 2006

I have run into a problem, I have 2 fields in my database, both keyfields:Table 1=====Field X <key>Field Y <key>In field X, there are say about 3 records for each unique Field Y. Ilet my users query the data base like follows:Enter the Codes you want: 1000 and 3000 and 8500So I want to pick up records where there will be the above values forAll Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there iseven ONE of the X values not matching a record without a matching Xvalue, leave it out.i.e:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBX=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCWhen the query runs, I want to see the following records:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBBUT NOT:X=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCbecause one of the X values was not matched (the last X value =9999 andnot one of the requirements of the search)So I guess I want something like this:SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES(X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD^^ Hope the above makes sense... but I am really stuck. The only otherway I think I could do it is, copy all records that match all 3 Xvalues into a temp table, and weed out any that are missing any one ofthe X values after they are copied but, I am running this on MYSQL 5.0Clustered, and there is not enough room in memory for it probably...and query time has to remain under a second.Anyhelp would be appreciated...

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved