Firehose mode: items

Showing posts with label items. Show all posts

Friday, March 30, 2012

Grouping several items in one group

Hi Everyone,
I am new to reporting services and I am trying to create groups which
contains more then one code .
Table
Name, Code, Amount
paper 1101 Â£10
Pens 1102 Â£5
Shoes 2512 Â£20
Clothes 3455 Â£5
I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
group 2 with total.
At the moment I can only seem to group each one individually.
Please help.
John
--
John HoYou question is more of a SQL problem, and there is more than one way
to solve your problem.
SELECT 'GRP1' as groupcode, amount from paper where code =3D 1101
UNION
SELECT 'GRP1' as groupcode, amount from pens where code =3D 1102
UNION
SELECT 'GRP2' as groupcode, amount from shoes where code =3D 2512
UNION
SELECT 'GRP2' as groupcode, amount from clothes where code =3D 3455
save the above query to a View object. When you open the view, you'll
see this:
<pre>
groupcode | amount
GRP1 | =A310
GRP1 | =A35
GRP2 | =A320
GRP2 | =A35
</pre>
Now you can group & sum on your view for your report. I'm sure there
are more elegant solutions (perhaps using StoredProcs), but this is
dirty and quick...heh.
On Apr 7, 11:05 am, Learner <Lear...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I am new to reporting services and I am trying to create groups which
> contains more then one code .
> Table
> Name, Code, Amount
> paper 1101 =A310
> Pens 1102 =A35
> Shoes 2512 =A320
> Clothes 3455 =A35
> I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
> group 2 with total.
> At the moment I can only seem to group each one individually.
> Please help.
> John
> --
> John Ho

Grouping question

I have a report that is actually going to be printed on NCR (carbon) paper to be written on. On this report I have a table that displays items from five different groups plus adds blanks for written entries. The data displays correctly under each group along with the extra spaces for written data, however what I need to know is how to "force" a group that does not have any items.

Is there a way to do this?

Thanks for any information.

If you don't have any items of a group your resultset doesn't contain the group and I think its not possible to add it afterwards, so you have to change your SQL-Query..
If you join use outer joins if you don't have any joins create a table that contains all groups you need and outer join the tables..
take a look at
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=405288&SiteID=1
|||Thanks for the information. This does help greatly!

Grouping question

I have the following query:
SELECT PR_NO,
Total = CASE Items.Use_Item_Calc_Qty
WHEN 0 THEN CONVERT(money, SUM
(items.unit_price * items.qty))
ELSE CONVERT(money, SUM(items.unit_price *
items.qty * ITEM_CALC_QTY))
END
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
The query is returning two records because a record in
the items table has a value of 0 in Use_item_calc_qty and
another record has a value of one.
What I want to return is only one record showing the
total for the Purchase Request. Can anyone help me with
this. I appreciate it.Vic,
I think this is what you wanted to do (your statement of problem is not
quite clear):
SELECT PR_NO,
Total = CONVERT(money, SUM(items.unit_price *
items.qty * (CASE Items.Use_Item_Calc_Qty when 0 then 1 else
Use_Item_Calc_Qty end)))
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
hth
Quentin
"Vic" <vduran@.specpro-inc.com> wrote in message
news:000d01c3c0dc$56b27560$a501280a@.phx.gbl...
> I have the following query:
> SELECT PR_NO,
> Total => CASE Items.Use_Item_Calc_Qty
> WHEN 0 THEN CONVERT(money, SUM
> (items.unit_price * items.qty))
> ELSE CONVERT(money, SUM(items.unit_price *
> items.qty * ITEM_CALC_QTY))
> END
> FROM Items
> where pr_no = 5816
> Group By PR_NO, Use_Item_Calc_Qty
> The query is returning two records because a record in
> the items table has a value of 0 in Use_item_calc_qty and
> another record has a value of one.
> What I want to return is only one record showing the
> total for the Purchase Request. Can anyone help me with
> this. I appreciate it.sql

Monday, March 26, 2012

Grouping Data Problem

Hello all

I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.

e.g

debit �G100 - credit �G100 - debit of �G125 ( I only want to see the debit of �G125)

I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.

How can I achieve this in SQL?

All help appreciated.

hi how does your table look like?

can you please post the schema

|||

I am still a little new to SQL so am not entirely sure what the 'schema' is so here's the code from the create table command: -

Please note that I am importing data from an old DB3 file so don't have a lot of control over the data structure.

(
[KEY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[POL_IDX] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[ITEM] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[AGCY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BRCH] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[DEPT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[MONTH] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[AMT] [float] NULL ,
[TBAL] [float] NULL ,
[DESC] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[EDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[INPUTDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[FLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[TRANS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[BILL] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[ICO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[MCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[COM_P] [float] NULL ,
[PR] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P] [float] NULL ,
[PR2] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P2] [float] NULL ,
[BR_P] [float] NULL ,
[TYPE] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[POL] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[OINT] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[LOANNUM] [nvarchar] (11) COLLATE Latin1_General_CI_AS NULL ,
[PRT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CLOSED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[OP_ID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[CSR] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[JOURNAL] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[DOCTYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[REFER] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PRINTED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CODE] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[PC] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BIN] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[SPLIT_PLAN] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PR3] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P3] [float] NULL ,
[CO_AMT] [float] NULL ,
[PR_AMT1] [float] NULL ,
[PR_AMT2] [float] NULL ,
[PR_AMT3] [float] NULL ,
[BR_AMT] [float] NULL ,
[PPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PFLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CO_TYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE1] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE2] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE3] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID_MO] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[ADJUST] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TYPEGROUP] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[REFER1] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[NOTE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CONGLOM] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[SKIP] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[EXTRA] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
)

|||I have now managed to achived this by using the same approach as I did with Access, however used the min,max options

Grouping Data Problem

Hello all

I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.

e.g

debit �G100 - credit �G100 - debit of �G125 ( I only want to see the debit of �G125)

I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.

How can I achieve this in SQL?

All help appreciated.

hi how does your table look like?

can you please post the schema

|||

I am still a little new to SQL so am not entirely sure what the 'schema' is so here's the code from the create table command: -

Please note that I am importing data from an old DB3 file so don't have a lot of control over the data structure.

|||I have now managed to achived this by using the same approach as I did with Access, however used the min,max options

Grouping Data for Consolidated Notification

We have a requirement where we need to send a single consolidated list of items belonging to a user which get active on particular date.

So, for example Item 1, Item 2, Item 3 gets active. These entries are inserted as Events. Now, we need to send single notification to the user with the email as:

=========================

Dear User,

Your following items got active today:

Item 1

Item 2

Item 3

Thanks,

Customer Care

=========================

For this we set the DigestDelievery to true and the emails indeed were consolidated. But, what it did was that it send single email notification repeating the entire content for each item as follows:

=========================

Dear User,

Your following items got active today:

Item 1

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 2

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 3

Thanks,

Customer Care

=========================

Please let me know if we are missing any setting or any changes need to be made in the .xslt for this to work.

regards,

Rajiv

I assume you are using a built-in XSLT formatter.

Since I have no idea about how to use/configure XSLT, in my application I created a custom content formatter in C# (this is really easy), and over there (in the .cs class) I "manually" built the resulting HTML string and inserted my items in a loop. The result HTML string is then sent as the email body.

Advantages:

1. I have 1 message "header" and 1 "footer". What's in between, gets "populated" at runtime in a loop, whether it's just 1 item or many.

2. Maybe, it's just as easy when using XSLT, ... I just don't know. But the emails my customers get contain hyperlinks which bring them right to the web page(s) for those particular item(s). Your management will love this feature!

3. Although I did not have to use this in my project, but if you need this, you can easily fetch additional data from some other non-NS data sources and "plug" it into your email message. It's possible because in a custom content formatter (C# or VB.NET, - your choice) you can use whatever .NET techniques you need, such as ADO.NET, System.IO (if you need to read from, say, some XML files), and whatever else you might want to do: a custom content formatter is just a regular .NET assembly, and you can use it as such.

|||Hi Rajiv -

From a SSNS perspective, it sounds like you have everything configured for digest delivery properly. Since it's not being formatted the way you wish, the issue is in the content formatter.

If you are using the built-in XSLT content formatter, try adjusting the
XSLTransform document.

Try placing the header and footer text directly in the XSLT document and the notification data in an <xsl:template> Match on "notification". You can then use teh <xsl:apply-templates> to call the notification section.

HTH...

Joe|||

Thanks for reply, I was able to resolve the issue as you have suggested.

regards,

Rajiv

Friday, March 23, 2012

Grouping By A Certain Range

I would like to create a report that would group the items by their
price. For example, I would like a group for all of the items that are
between 0-50 and then 51-100 (and so on).
Does anyone know an easy way for doing that using Visual Studio?
ANY information is appreciated. Thanks in advance!you can create a "calulated" field in your dataset like:
=iif(MyPrice > 50, '51-100', '0-50')
then use this new field in your report to render it. (group, filter, sort or
any other option which could use a formula)
"axels22" <ericspreher@.hotmail.com> wrote in message
news:1137429986.049744.315060@.g44g2000cwa.googlegroups.com...
>I would like to create a report that would group the items by their
> price. For example, I would like a group for all of the items that are
> between 0-50 and then 51-100 (and so on).
> Does anyone know an easy way for doing that using Visual Studio?
> ANY information is appreciated. Thanks in advance!
>

Wednesday, March 21, 2012

grouped by month

Dear All,

I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)

So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.

Not sure what would be the right approach here.

I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?

Will this work?group by month(Date)

More over
group by year(date), month(date)

Monday, March 19, 2012

Group Footer Overflow problem

Using Crystal Reports 7

Have a report that displays items and their amounts in the Details section

The report includes a Group section where in the Group Footer it displays the total amount of all the items from the Details section above.

We print on custom paper that lays out all the sections i.e Titles on the Page Header section, a box for the Details etc...

Problem arises when the Details section takes the entire page or most of it, leaving not enough room for the Group Footer section to print. Instead of generating a following page (i.e another page header etc) it dumps the footer on to the top of the next page - so we are left with the Group Footer (the totals) being displayed on our customs titles etc.

Therefore we end up with the subtotals being unreadable because they are printed on top of the titles of the custom page.

For some reason I can't get Crystal to regonise that this particular record flows on to another page.

I've tried using a variable to force a page break, but this either leaves big gaps in the report or some parts of the footer still overflow into the top on the next page.

Ideally I need to tell Crystal that the footer section should always print from this point on the page downwards (i.e. that start of the details section) - never above this point

I hope this is clear

Can anybody offer any other suggestions I could look into ??

CheersIn Crystal Reports 8.5, there's a property in Group Footers called PrintAtBottomOfPage. Maybe there's something similar in version 7?|||Yes Print at Bottom of Page is available in Crystal 7, however this would leave quite a significant bit of white space if say I had only a couple of detail lines.

Any other suggestions ?|||FYI

Seem to have come up with a solution that works so far:

Have created another Page Header section - so now have Page Header a and b
Page Header b is the same size as my Group Header section. Under Supress format for Page Header b have inserted:

pagenumber =1 or
InRepeatedGroupHeader = true;

Meaning that Page Header b will appear when there is no Group header section on subsequent pages

Sweet as!

Friday, March 30, 2012

Grouping several items in one group

Grouping question

Grouping question

Monday, March 26, 2012

Grouping Data Problem

Grouping Data Problem

Grouping Data for Consolidated Notification

Friday, March 23, 2012

Grouping By A Certain Range

Wednesday, March 21, 2012

grouped by month

Monday, March 19, 2012

Group Footer Overflow problem

Firehose mode

Blog Archive

About Me