Group Page number

I have a report which is group by invoice no. For each new invoice no, page
break is inserted. I want to set page number for each invoice (e.g. Page 1
of 1). But if the invoice has so many charge line that occupy more than 1
page, I want to set page 1 as Page 1 of 10, page 2 as Page 2 of 10 and so on
...
HOW CAN I DO IT ? Also the page number will be reset to 1 for new invoice.
Another question is that, I have sub-total for each new invoice which is
placed in Group footer. In crystal report, I can fix the footer position.
So the position of sub-total will not be altered (move up and down) no matter
how many charge lines under this invoice. Is RS has this feature ?Here is a reference to the answer:
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=reset+page+number+&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=030990a7-1731-4e50-9e0b-4ed69ac1bd63&mid=2bdeaef4-35b0-49ed-86c6-5c69a155be38
"May Liu" wrote:
> I have a report which is group by invoice no. For each new invoice no, page
> break is inserted. I want to set page number for each invoice (e.g. Page 1
> of 1). But if the invoice has so many charge line that occupy more than 1
> page, I want to set page 1 as Page 1 of 10, page 2 as Page 2 of 10 and so on
> ...
> HOW CAN I DO IT ? Also the page number will be reset to 1 for new invoice.
> Another question is that, I have sub-total for each new invoice which is
> placed in Group footer. In crystal report, I can fix the footer position.
> So the position of sub-total will not be altered (move up and down) no matter
> how many charge lines under this invoice. Is RS has this feature ?

Group By clause with an inserted column

Good afternoon all,
I'm trying to write SQL that adds a descriptive column and groups on that
column but I get an error saying my inserted column is invalid. Can anyone
help? An example follows.
Sales Table:
Sales Type Amount
A 5.00
A 6.00
B 2.00
SQL:
SELECT
(CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES'
END), SUM(Amount)
GROUP BY '?
Desired Result:
TAXABLE SALES 11.00
NONTAXABLE SALES 2.00
Thanks in advance,
Don J> GROUP BY '?
CASE
WHEN Sales Type = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END
AMB
"Don Jellie" wrote:

> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can anyon
e
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES
'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J|||Don Jellie wrote:
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on
> that column but I get an error saying my inserted column is invalid.
> Can anyone help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
> SALES' END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J
create table #a (SalesType char(1) NOT NULL, Amount DECIMAL(10, 2) NOT
NULL)
go
Insert Into #a Values ('A', 5.00)
Insert Into #a Values ('A', 6.00)
Insert Into #a Values ('B', 2.00)
go
SELECT
CASE
WHEN SalesType = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END,
SUM(Amount)
From
#a
GROUP BY SalesType
go
drop table #a
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Since your column named Sales Type has a space in it, you must "Quote" it by
wrapping it in " " or [ ] like so:
SELECT
CASE WHEN [Sales Type] = 'A'
THEN 'Taxable Sales'
ELSE 'NonTaxable Sales'
END AS "Sales Type",
SUM(Amount) AS "Amount"
GROUP BY [Sales Type]
"Don Jellie" <Jelliebean1@.msn.nospam.com> wrote in message
news:ADDCEAC9-659D-4EA2-8252-25E5DB0D6840@.microsoft.com...
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can
anyone
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
SALES'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J

GridView Help

Hi,

I use WVD and SQL Express 2005.

I have a table "SignIn" that one of fields inserted automatically by getdate()

And I have GridView that I use to display this table because I would like take advantage of GridView sorting and paging methods that are embedded in.

Currently I display all records at once.

My problem is how to make the GridView show today's records only.

I tried this code below, but I get only this message "There are no data records to display."

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:RC1%>"

ProviderName="<%$ ConnectionStrings:RC1.ProviderName%>"

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

<SelectParameters>

<asp:QueryStringParameterName="SignIn"QueryStringField="Format(Now, "M/dd/yyyy")" Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

Help Please!

I don't think you need use QueryStringParameter here. You can add the selectparameter from page load to assign today's date to it:

Like:

protectedvoid Page_Load(object sender,EventArgs e)

{

SqlDataSource1.SelectParameters.Add(

"SignIn",DateTime.Today.ToString("MM/dd/yyyy"));

}

}

The following is for a QueryStringParameter, but you don't need it.

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

<SelectParameters>

<%

-- <asp:QueryStringParameter DefaultValue="01/01/2007" Name="SignIn" QueryStringField="mydate" Type="DateTime" />

--

%><asp:QueryStringParameterDefaultValue="1/1/2007"Name="SignIn"QueryStringField="mydate"Type="DateTime"/>

</SelectParameters>

Your URL will look something like this:

http://localhost:2013/WebSite1/queryDate.aspx?mydate=1/2/2006

|||

Thank you Limno,

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

This SelectCommand solved my problem.

I tried the

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

SqlDataSource1.SelectParameters.Add("SignIn", DateTime.Today.ToString("MM/dd/yyyy"))

EndSub

And I inserted SelectParameters like this:

<asp:SqlDataSourceID="SqlDataSource1" . . .

.

.

.

<SelectParameters>

<asp:QueryStringParameterDefaultValue="1/1/2007"Name="SignIn"QueryStringField="mydate"Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

I keep getting the following error:

The variable name '@.SignIn' has already been declared. Variable names must be unique within a query batch or stored procedure

I wonder what I did wrong?

Thank you.

|||

alexmu06:

Thank you Limno,

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

This SelectCommand solved my problem.

I tried the

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

SqlDataSource1.SelectParameters.Add("SignIn", DateTime.Today.ToString("MM/dd/yyyy"))

EndSub

And I inserted SelectParameters like this:

<asp:SqlDataSourceID="SqlDataSource1" . . .

.

.

.

<SelectParameters>

<asp:QueryStringParameterDefaultValue="1/1/2007"Name="SignIn"QueryStringField="mydate"Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

I keep getting the following error:

The variable name '@.SignIn' has already been declared. Variable names must be unique within a query batch or stored procedure

I wonder what I did wrong?

Thank you.

When you add the selectparameter from your code, you cannot use the declaratively again. You can use only one of them. I would prefer teh code one in your case and I hope this answers your question.

|||

Sorry but i don't get it.

if its possible could you expand a bit.

Thanks

|||

Hello:

SelectCommand="SELECT [student_ID], [SignIn], [SignOut], [Location] FROM [Stud_data] WHERE (CONVERT(VARCHAR(10),[SignIn],101) = @.SignIn)">

and the following is all you need.

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

SqlDataSource1.SelectParameters.Add("SignIn", DateTime.Today.ToString("MM/dd/yyyy"))

EndSub

Please do not add that QueryStringParameter in your SelectParameters for this "SignIn"(you've already had it from the above code).

|||

Thank you,

I'll try that

Alex