Data Report Problem

I have a transaction table which contains 2 columns, Amount and Cat_id (category)

eg my table is like

Amount | Cat_id
500 | 1
300 | 1
800 | 2
400 | 2

So now ive 2 queries
select amount from transaction where category = 1;

select amount from transaction where category = 2;

I want to display the result of these 2 queries in my report.

If i try to add these queries in two seperate commands in the data environment and then add it to the report then it shows an error.

So I created 2 views cat1 and cat 2 with those queries, created a new query which takes the value from those two views and placed it on the report.

But now wen i see the report i get this

Cat_1 | Cat_2
500 | 800
300 | 800
500 | 400
300 | 400

While i want my report like this.

Cat_1 | Cat_2
500 | 800
300 | 400

Please help me guys, have to build a small program.i am not sure i am reading this correctly, but i am going to take a stab at helping just in case...
have you tried grouping your results by category?

hope this helps...|||What about rewriting your query to:

select amount from transaction where category = 1 OR category = 2
Order By category

That will get your results in the form of:

Amount | Cat_id
500 | 1
300 | 1
800 | 2
400 | 2

... unless category 1 and category 2 come from 2 different tables. If they do, you could use a JOIN.

Data not getting plotted in line graph

Hi,
I have a LINE chart with 2 data fields, and 1 category field.
I havent included any series field in the chart.
If the query returns more than 1 records then both the data field values are
getting plotted in the graph.
If the query returns only one record then only the first data point is
plotted. I dont know why the value for second data field is not plotted...
Is it a bug in reporting services?
Please any one reply me asap... I have to fix it for the client by today...
Thanks for help...
Regards,
RanjithTo add more information,
if i change the chart type to any other chart other then LINE graph, it is
getting plotted. Only for Line graph its not working...
Thanks,
Ranjith
"Ranjith Prakash" wrote:
> Hi,
> I have a LINE chart with 2 data fields, and 1 category field.
> I havent included any series field in the chart.
> If the query returns more than 1 records then both the data field values are
> getting plotted in the graph.
> If the query returns only one record then only the first data point is
> plotted. I dont know why the value for second data field is not plotted...
> Is it a bug in reporting services?
> Please any one reply me asap... I have to fix it for the client by today...
> Thanks for help...
> Regards,
> Ranjith
>
>|||Please read this recent thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a3bce317-0626-47f4-9ec2-aebe559a41b3&sloc=en-us
It sounds like you have a very similar situation. You can either request the
SP1-based hotfix or use SP2 Beta (http://support.microsoft.com/kb/842440) or
wait till the official release of SP2.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ranjith Prakash" <Ranjith Prakash@.discussions.microsoft.com> wrote in
message news:158BA878-86E5-4D17-93F1-E19F79350E92@.microsoft.com...
> To add more information,
> if i change the chart type to any other chart other then LINE graph, it is
> getting plotted. Only for Line graph its not working...
> Thanks,
> Ranjith
> "Ranjith Prakash" wrote:
> > Hi,
> >
> > I have a LINE chart with 2 data fields, and 1 category field.
> > I havent included any series field in the chart.
> >
> > If the query returns more than 1 records then both the data field values
are
> > getting plotted in the graph.
> >
> > If the query returns only one record then only the first data point is
> > plotted. I dont know why the value for second data field is not
plotted...
> >
> > Is it a bug in reporting services?
> > Please any one reply me asap... I have to fix it for the client by
today...
> >
> > Thanks for help...
> >
> > Regards,
> > Ranjith
> >
> >
> >|||Hi Robert,
Thanks a lot for helping me. That did fix my bug.
Thanks again,
Ranjith
"Robert Bruckner [MSFT]" wrote:
> Please read this recent thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=a3bce317-0626-47f4-9ec2-aebe559a41b3&sloc=en-us
> It sounds like you have a very similar situation. You can either request the
> SP1-based hotfix or use SP2 Beta (http://support.microsoft.com/kb/842440) or
> wait till the official release of SP2.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Ranjith Prakash" <Ranjith Prakash@.discussions.microsoft.com> wrote in
> message news:158BA878-86E5-4D17-93F1-E19F79350E92@.microsoft.com...
> > To add more information,
> >
> > if i change the chart type to any other chart other then LINE graph, it is
> > getting plotted. Only for Line graph its not working...
> >
> > Thanks,
> > Ranjith
> >
> > "Ranjith Prakash" wrote:
> >
> > > Hi,
> > >
> > > I have a LINE chart with 2 data fields, and 1 category field.
> > > I havent included any series field in the chart.
> > >
> > > If the query returns more than 1 records then both the data field values
> are
> > > getting plotted in the graph.
> > >
> > > If the query returns only one record then only the first data point is
> > > plotted. I dont know why the value for second data field is not
> plotted...
> > >
> > > Is it a bug in reporting services?
> > > Please any one reply me asap... I have to fix it for the client by
> today...
> > >
> > > Thanks for help...
> > >
> > > Regards,
> > > Ranjith
> > >
> > >
> > >
>
>

Data modeling question

I'm facing the next problem:

I have a table with two columns (among others) modeling category and
subcategory data for each row. I need to summarize info on this two
columns, but with the next specs:

1.- Some grouping is only on the category column.
2.- Some other grouping consider the two columns.

The values for the two columns come from external source, i.e. I have
no means to know the precise universe of data (I suppose soon or later
we'll have a sufficient sample of data, but for now it's not the
case). So, I would like to have a grouping table so it's not necessary
to insert a row for every pair of category and subcategory (although
it would be the best approach for the sake of design's simplicity). As
I don't know every possible combination, I would prefer something like
'this category is a - no matter the subcategory', and 'this other
category + subcategory is b'. Let's go with a sample:

------------------

Create Table B ( -- groupings --
categ char(8),
subcateg char(5),
what_group char(10)
)

-- All rows with 432 code are cat. A --
Insert B ( '00000432', ' ', 'Category A' )

-- All rows with 636 code are cat. C except when subcat is 8552 (cat.
B) --
Insert B ( '00000636', '08552', 'Category B' )
Insert B ( '00000636', ' ', 'Category C' )

-- Some data --

Create Table A ( -- data --
categ char(8),
subcateg char(5)
)

Insert A ( '00000432', '01322' )
Insert A ( '00000432', '01222' )
Insert A ( '00000432', '01100' )
Insert A ( '00000432', ' ' )

Insert A ( '00000636', '08552' )
Insert A ( '00000636', '08552' )
Insert A ( '00000636', '01100' )
Insert A ( '00000636', ' ' )
Insert A ( '00000636', '01111' )

-- The query like:

Select b.what_group, count(*) as cnt
From a
Left Join b
On /* ? ? ? ? */

-- Should give --

what_group cnt
----- ----
Category A 4
Category B 2
Category C 3

--------------------

It would be easier knowing all the pairs categ - subcateg. If I don't
know them, is a good idea to model the grouping table as I've done
with rows in B?

TIA,

Diego
Bcn, Spain[posted and mailed, please reply in news]

Diego Buendia (dbuendiab@.yahoo.es) writes:
> I'm facing the next problem:
> I have a table with two columns (among others) modeling category and
> subcategory data for each row. I need to summarize info on this two
> columns, but with the next specs:
> 1.- Some grouping is only on the category column.
> 2.- Some other grouping consider the two columns.
> The values for the two columns come from external source, i.e. I have
> no means to know the precise universe of data (I suppose soon or later
> we'll have a sufficient sample of data, but for now it's not the
> case). So, I would like to have a grouping table so it's not necessary
> to insert a row for every pair of category and subcategory (although
> it would be the best approach for the sake of design's simplicity). As
> I don't know every possible combination, I would prefer something like
> 'this category is a - no matter the subcategory', and 'this other
> category + subcategory is b'. Let's go with a sample:

I have done one change to your set up: rather than using space to
mean "no subcategory", I'm using NULL. Here is a repro which appears
to give the correct result:

Create Table B ( -- groupings --
categ char(8) NOT NULL,
subcateg char(5) NULL,
what_group char(10) NOT NULL
)

-- All rows with 432 code are cat. A --
Insert B VALUES( '00000432', NULL, 'Category A' )

-- All rows with 636 code are cat. C except when subcat is 8552 (cat. B)
--
Insert B VALUES ( '00000636', '08552', 'Category B' )
Insert B VALUES ( '00000636', NULL, 'Category C' )

-- Some data --

Create Table A ( -- data --
categ char(8) NOT NULL,
subcateg char(5) NULL
)

Insert A VALUES( '00000432', '01322' )
Insert A VALUES( '00000432', '01222' )
Insert A VALUES( '00000432', '01100' )
Insert A VALUES( '00000432', NULL )

Insert A VALUES( '00000636', '08552' )
Insert A VALUES( '00000636', '08552' )
Insert A VALUES( '00000636', '01100' )
Insert A VALUES( '00000636', NULL )
Insert A VALUES( '00000636', '01111' )
go
SELECT what_group , COUNT(*)
FROM (
SELECT B.what_group
FROM A
JOIN B ON A.categ = B.categ
AND A.subcateg = B.subcateg
UNION ALL
SELECT B.what_group
FROM A
JOIN B ON A.categ = B.categ
AND B.subcateg IS NULL
WHERE NOT EXISTS (SELECT *
FROM B b1
WHERE A.categ = b1.categ
AND A.subcateg = b1.subcateg)
) AS x
GROUP BY what_group
go
DROP TABLE A, B

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp