grant total for hide duplicate...

for my query in dataset after join few tables, i got following data:
ItemNo Area Barcode Total
1 J 12345 15
1 J 2222 15
in report, i use hide duplicate and displayed like:
ItemNo Area Barcode Total
1 J 12345 15
2222
total: 30
the problem is the total...what i wanted is 15 and no 30.i use
"=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
what should i do to get the firgue what i wanted.....
i really appreciate your helps...thank a lot
--
thanksWhat you should do is to have 15 on both and the total is 30. instead if you
show just 15 and total 30, it is a kind of misleading...
so you can hide textbox wise all the values except total.
Amarnath
"kevintts" wrote:
> for my query in dataset after join few tables, i got following data:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 1 J 2222 15
> in report, i use hide duplicate and displayed like:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> total: 30
> the problem is the total...what i wanted is 15 and no 30.i use
> "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> what should i do to get the firgue what i wanted.....
> i really appreciate your helps...thank a lot
> --
> thanks|||thanks Amarnath, but what i want is hide or minus the total and the total
become 15.
let say:
ItemNo Area Barcode Total
1 J 12345 15
1 J 2222 15
2 K 54321 20
i got:
ItemNo Area Barcode Total
1 J 12345 15
2222
2 K 54321 20
total: 50
what i wanted:
ItemNo Area Barcode Total
1 J 12345 15
2222
2 K 54321 20
total: 35
The value for '2222' still in the group. any thing wrong with the
exspression"=Round(sum(Fields!Total.Value,"ItemClass"),3),3"?
thanks a lot...
"Amarnath" wrote:
> What you should do is to have 15 on both and the total is 30. instead if you
> show just 15 and total 30, it is a kind of misleading...
> so you can hide textbox wise all the values except total.
> Amarnath
>
> "kevintts" wrote:
> > for my query in dataset after join few tables, i got following data:
> > ItemNo Area Barcode Total
> > 1 J 12345 15
> > 1 J 2222 15
> >
> > in report, i use hide duplicate and displayed like:
> > ItemNo Area Barcode Total
> > 1 J 12345 15
> > 2222
> > total: 30
> >
> > the problem is the total...what i wanted is 15 and no 30.i use
> > "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> >
> > what should i do to get the firgue what i wanted.....
> > i really appreciate your helps...thank a lot
> >
> > --
> > thanks|||Expression is ok but since the text box is hidden, but the value exists so it
sums up.
If possible can you get this through query. e.g the values not present
should be null. in your case '2222' should have Total as Null. so when you
sum it sums up exactly.
Amarnath
"RE: grant total for hide duplicate..." wrote:
> thanks Amarnath, but what i want is hide or minus the total and the total
> become 15.
> let say:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 1 J 2222 15
> 2 K 54321 20
> i got:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> 2 K 54321 20
> total: 50
> what i wanted:
> ItemNo Area Barcode Total
> 1 J 12345 15
> 2222
> 2 K 54321 20
> total: 35
> The value for '2222' still in the group. any thing wrong with the
> exspression"=Round(sum(Fields!Total.Value,"ItemClass"),3),3"?
> thanks a lot...
> "Amarnath" wrote:
> > What you should do is to have 15 on both and the total is 30. instead if you
> > show just 15 and total 30, it is a kind of misleading...
> >
> > so you can hide textbox wise all the values except total.
> >
> > Amarnath
> >
> >
> > "kevintts" wrote:
> >
> > > for my query in dataset after join few tables, i got following data:
> > > ItemNo Area Barcode Total
> > > 1 J 12345 15
> > > 1 J 2222 15
> > >
> > > in report, i use hide duplicate and displayed like:
> > > ItemNo Area Barcode Total
> > > 1 J 12345 15
> > > 2222
> > > total: 30
> > >
> > > the problem is the total...what i wanted is 15 and no 30.i use
> > > "=Round(sum(Fields!Total.Value,"ItemClass"),3),3" in that textbox expression.
> > >
> > > what should i do to get the firgue what i wanted.....
> > > i really appreciate your helps...thank a lot
> > >
> > > --
> > > thanks

got error when using stored procedure with temp table in it

I got an error message when i created a dataset using a stored procedure with temp table in it. The error is:

"could not generate a list of fields for the query. check the query syntax or click the refresh fields on the query toolbar. invalid object name ' #indented' ".

after I click on the refresh button, everything looks fine. But after I put the fields in the table, and go to the Preview, it is a blank report.

Does anybody know what happened?

3x a lot!

Your problem occurs because the sproc isn't returning metadata (column descriptions, etc.) from your temp table dependably -- Nothing that you did wrong, we just can't get meta data from an object which doesn't exist yet...The way we normally get metadata back from sprocs doesn't work when they return data from a temp table.

There are two thing you can try, although I think you've already done #1...

#1. Use the generic query designer, click "Refresh Fields", and you'll get a little dialog asking you for paramter info for your sproc...provide it.. finish your work, cross your fingers, move to layout view and then Preview

#2. In your sproc, return your data using a table variable vs. Select * from ##SomeTempTable

From what I read, you'll actually only have this problem in the designer...if you went ahead and plugged in the correct RDL for your fields manually and then deployed the report to the server, it would run fine...

|||Thank you!|||I also get an error because of a temp table, but this happens when creating data-driven subscription query: The

dataset cannot be generated. An error occurred while connecting to a

data source, or the query is not valid for the data source.

(rsCannotPrepareQuery) Invalid object name '##XX'. Can you help me with

this?|||

Unfortunately, you're running into a behavior you're not going to able to avoid...if you try and set up the subscription via Report Manager it (Report Manager) will call "PrepareQuery" (which we would expect to fail when dealing with temp tables) ...That's why you get your error message.

If you're really serious about using the temp table, you can still get a data-driven subscription working, BUT the price that you pay is you'll have to do it via code, using the Web Service API to do the work...If you go directly against the web service, you bypass Report Manager calling (and then failing against) "PrepareQuery"...and even when/if you get this working, you'll NEVER be able to edit the subscription in the UI, or you'll get the same errors again. So, you really should just try and whack the use of #temp tables in your scenario. :)

|||Thank you for your

answer. My temporary solution was this: when I validate the query I get

the error described above however if I

run the same stored procedure that is used in a query in let's say

management studio query and leave it open, the query in data driven

subscription web interface is validated succesfully. It works for now,

but I will try to get rid of the temp tables.

|||Ha! Great idea...Never would have thought of this!

global dataset for n number of forms

hi friends ,

Is there anyway to share a single dataset for different forms.. coz i am repeatedly using the same query for different forms...

No, you cannot share a dataset among reports. Anyway, it doesnt make a big difference because the same query is going to run against the database even it is same for different reports.

If you are using SQL query text in your reports, consider creating a stored procedure for the query and use the stored procedure in all your reports.

Shyam

|||thank you shyam.......|||

Can you please mark the post as answer?