Antivirus install to db server: sales

Monday, March 26, 2012

Question about Parameter

Hi,
I need to design a Sales Order (SO) report for my company. In the SO
report, I need to print out the company name, company address, Telephone no.
... etc in report header. For this design, it serve 5 sub-company under my
company group. Therefore, I store the company data in a table - name
CompanyInfo. Everytime I need to select correct company name to print it out.
But I know that it cannot print "Field" in report header in RS. I try to
put it into parameter and print it out. It OK but all of the parameter will
be show to user. For this point, I don't want our user to see such
information.
Any idea for me?
Thank You!
TonyYou can have multiple datasets in a report. If you put a list at the top of
the report and your query has only one record, then the data will look for
all intents and purposes as the report header. Then following that have your
table or matrix or whatever. I do this all the time and it looks exactly the
same.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> Hi,
> I need to design a Sales Order (SO) report for my company. In the SO
> report, I need to print out the company name, company address, Telephone
> no.
> ... etc in report header. For this design, it serve 5 sub-company under
> my
> company group. Therefore, I store the company data in a table - name
> CompanyInfo. Everytime I need to select correct company name to print it
> out.
> But I know that it cannot print "Field" in report header in RS. I try to
> put it into parameter and print it out. It OK but all of the parameter
> will
> be show to user. For this point, I don't want our user to see such
> information.
> Any idea for me?
> Thank You!
> Tony
>|||Hi Bruce,
Thank you for your reply!
I already try this way and it look OK. But all parameters will be shown at
the top of the report! It looks no good! Can I hidden the parameter? It is
because there are 6 parameters for such report header.
Thank You!
Tony
"Bruce L-C [MVP]" wrote:
> You can have multiple datasets in a report. If you put a list at the top of
> the report and your query has only one record, then the data will look for
> all intents and purposes as the report header. Then following that have your
> table or matrix or whatever. I do this all the time and it looks exactly the
> same.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> > Hi,
> >
> > I need to design a Sales Order (SO) report for my company. In the SO
> > report, I need to print out the company name, company address, Telephone
> > no.
> > ... etc in report header. For this design, it serve 5 sub-company under
> > my
> > company group. Therefore, I store the company data in a table - name
> > CompanyInfo. Everytime I need to select correct company name to print it
> > out.
> >
> > But I know that it cannot print "Field" in report header in RS. I try to
> > put it into parameter and print it out. It OK but all of the parameter
> > will
> > be show to user. For this point, I don't want our user to see such
> > information.
> >
> > Any idea for me?
> > Thank You!
> >
> > Tony
> >
> >
>
>|||You can have hidden parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:7FABFC55-8149-4484-9C80-AD70FC318BCF@.microsoft.com...
> Hi Bruce,
> Thank you for your reply!
> I already try this way and it look OK. But all parameters will be shown
> at
> the top of the report! It looks no good! Can I hidden the parameter? It
> is
> because there are 6 parameters for such report header.
> Thank You!
> Tony
>
> "Bruce L-C [MVP]" wrote:
>> You can have multiple datasets in a report. If you put a list at the top
>> of
>> the report and your query has only one record, then the data will look
>> for
>> all intents and purposes as the report header. Then following that have
>> your
>> table or matrix or whatever. I do this all the time and it looks exactly
>> the
>> same.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
>> > Hi,
>> >
>> > I need to design a Sales Order (SO) report for my company. In the SO
>> > report, I need to print out the company name, company address,
>> > Telephone
>> > no.
>> > ... etc in report header. For this design, it serve 5 sub-company
>> > under
>> > my
>> > company group. Therefore, I store the company data in a table - name
>> > CompanyInfo. Everytime I need to select correct company name to print
>> > it
>> > out.
>> >
>> > But I know that it cannot print "Field" in report header in RS. I try
>> > to
>> > put it into parameter and print it out. It OK but all of the parameter
>> > will
>> > be show to user. For this point, I don't want our user to see such
>> > information.
>> >
>> > Any idea for me?
>> > Thank You!
>> >
>> > Tony
>> >
>> >
>>|||Thank You! I try it!
"Bruce L-C [MVP]" wrote:
> You can have hidden parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:7FABFC55-8149-4484-9C80-AD70FC318BCF@.microsoft.com...
> > Hi Bruce,
> >
> > Thank you for your reply!
> >
> > I already try this way and it look OK. But all parameters will be shown
> > at
> > the top of the report! It looks no good! Can I hidden the parameter? It
> > is
> > because there are 6 parameters for such report header.
> >
> > Thank You!
> > Tony
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You can have multiple datasets in a report. If you put a list at the top
> >> of
> >> the report and your query has only one record, then the data will look
> >> for
> >> all intents and purposes as the report header. Then following that have
> >> your
> >> table or matrix or whatever. I do this all the time and it looks exactly
> >> the
> >> same.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> >> > Hi,
> >> >
> >> > I need to design a Sales Order (SO) report for my company. In the SO
> >> > report, I need to print out the company name, company address,
> >> > Telephone
> >> > no.
> >> > ... etc in report header. For this design, it serve 5 sub-company
> >> > under
> >> > my
> >> > company group. Therefore, I store the company data in a table - name
> >> > CompanyInfo. Everytime I need to select correct company name to print
> >> > it
> >> > out.
> >> >
> >> > But I know that it cannot print "Field" in report header in RS. I try
> >> > to
> >> > put it into parameter and print it out. It OK but all of the parameter
> >> > will
> >> > be show to user. For this point, I don't want our user to see such
> >> > information.
> >> >
> >> > Any idea for me?
> >> > Thank You!
> >> >
> >> > Tony
> >> >
> >> >
> >>
> >>
> >>
>
>

Saturday, February 25, 2012

Question

Hello all,

I have the following dataset returning top 20 records for a day, changing daily

STATE

USER ID

SALES REP

SALES

QLD

6758

Liam Maddrell

QLD

6677

Lisa-Maree Findlay

QLD

6133

Benjamin Matthews

QLD

6299

Simon Tabet

QLD

6112

Deborah Williams

VIC

3428

Peter Mahar

QLD

5134

Russell Den Engelse

5279

Julie Isitt

NSW

5677

Pranav Agarwal

NSW

6093

Rosa Delizia

QLD

6684

Ron Prasad

7578

Francis Williams

Qld

7511

LigIA Borodi

QLD

6804

Rupert Ryan

QLD

6856

Maxwell MacLean

QLD

6904

Trent Sticklen

QLD

7236

Drazen Knezevic

QLD

7406

Amy Donaldson

NSW

5601

Michael Phillips

QLD

5940

Harnake guraya

I would like too now sum up the figures to get in a separate layout (total sales off the above reps by state)

Queensland 168

South
Australia

Western
Australia

176

Victoria

New South
Wales

Northern
Territory

I have tried a different dataset but it doesnt allow me to do this due to the use off top 20 in the query which needs to be grouped by state this time and to do that it doesnt know the topn 20 records.

...I am trying to figure out a way by using textboxes , tables etc, I have managed to get the results coming out in a table using grouping by state and sum of the values but because there are no records for Sth Australia and Northern Territory these are not on the list...and i need these also in the list...can someone please help

thanks

Can someone help me with some code to achieve the above ?

thanks

|||

I don't have code for you, but in your query, you need to specify something like isnull(NorthernTerritory,0)

This will return a 0 (zero) instead of a null and then the data should show up on your report.

Since you are doing a topN, why don't you use a group by in your report?

|||

I cant do a group by as a rep can have more than 1 sale and to get the top 20 it is adding up all his sales already

wa John 20 item 15

wa john 15 item 20

To get the top 20 i add the sum off the values together to get the rep...

wa john 35

if i group by state john will be treated as 2 records and the numbers wont be for the top 20 sales rep...but just the top 20 sales

thanks for trying can anyone else help?

|||

Hi,

I found it hard to understand why cant you use grouping and othere data sets,

I have a solution but It is an ugly one.... (-:

first change your Data set to return a sum colums
by addind a join in your SQL to a query tha group by the State and sum the sales

after you do that you will have all the results you need in the same data set

now you jost have to add the colums to the feilds and use the First function in the expresions to

get the currect total sales for the state