A bit bizarre I could be overlooking something since tempdb is not like a user database.
Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
When I run a Financial apps that uses tempdb, I receive the message that the templog files are full backup and ...
When I go back to tempdb the templog file is back at 15mb.
Jeff
--
Message posted via http://www.sqlmonster.comPerhaps you restarted SQL Server in between. Size for tempdb at startup you set using ALTER
DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c83bbdcd541d46a4bb16394dcf882151@.SQLMonster.com...
>A bit bizarre I could be overlooking something since tempdb is not like a user database.
> Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
> I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
> When I run a Financial apps that uses tempdb, I receive the message that the templog files are
> full backup and ...
> When I go back to tempdb the templog file is back at 15mb.
> Jeff
> --
> Message posted via http://www.sqlmonster.com|||actually i thought the same..but when i change it on one of my local machine
using EM and restart sql server i see size of tempdb after the modification...
"Tibor Karaszi" wrote:
> Perhaps you restarted SQL Server in between. Size for tempdb at startup you set using ALTER
> DATABASE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:c83bbdcd541d46a4bb16394dcf882151@.SQLMonster.com...
> >A bit bizarre I could be overlooking something since tempdb is not like a user database.
> >
> > Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
> >
> > I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
> >
> > When I run a Financial apps that uses tempdb, I receive the message that the templog files are
> > full backup and ...
> >
> > When I go back to tempdb the templog file is back at 15mb.
> >
> > Jeff
> >
> > --
> > Message posted via http://www.sqlmonster.com
>
>|||Ahh yes, the lightbulb went off. tempdb needs the ALTERDATABASE command to adjust its data/log files.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com|||Ok now I am stumped.
I did
use master
ALTER DATABASE tempdb
MODIFY FILE
(name = templog,
size = 20MB)
go
The templog is then adjusted to 20mb.
I run my financial stored procedure and receive the message templog ran out of space blah blah...
So I go back to review and increase so more, but lo and behold templog is Back at 15mb!!!!
A stored procedure that simply taking data from one database (less than 2000 records) and using temp tables to do some calculations make the log file shift back to 15mb.
Any ideas?
Jeff
--
Message posted via http://www.sqlmonster.com|||What size does master..sysaltfiles specify for the file? Also, perhaps you have autoshrink turned on
for the tempdb database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:29ce9a8218c945548d497982ae85ee55@.SQLMonster.com...
> Ok now I am stumped.
> I did
> use master
> ALTER DATABASE tempdb
> MODIFY FILE
> (name = templog,
> size = 20MB)
> go
> The templog is then adjusted to 20mb.
> I run my financial stored procedure and receive the message templog ran out of space blah blah...
> So I go back to review and increase so more, but lo and behold templog is Back at 15mb!!!!
> A stored procedure that simply taking data from one database (less than 2000 records) and using
> temp tables to do some calculations make the log file shift back to 15mb.
> Any ideas?
> Jeff
> --
> Message posted via http://www.sqlmonster.com
Thursday, March 29, 2012
Adjusting reportwidth dynamically.. please help me
hi,
I am new to reporting services.I am using sqlserver Reporting services to generate Report.I have designed a static RDL file based on a view which contains 40 columns..i included all the 40 column in rdl file..i have used hidden field expressions for hiding columns..Now at run time if i select three columns for generating report, The remaining hidden column spaces are still there in the report ... The report width is not adjusting to the selected column width... how to eliminate the hidden column spaces in the report.Please help me..it is really urgent for me..
Thanks in advance...
You can change the width of the ReportViewer in the Load() event like so...
ReportViewer1.Width=500
I think if you try to do it in another event it has no effect becasue the size has already been rendered.
Good luck.
|||try to set the width = "auto"
Adjusting Report width automatically
Hi All,
We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.
Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.
Any help is highly appreciated.
Regards, kart
Sizing is one of the few aspects of RS that cannot be controlled using expressions. Column widths are fixed and only row height supports auto sizing.
Paging is one of the last aspects of the rendering to be determined and it's also specific (and petentially different) to each rendering format. So HTML, Excel and PDF will all treat paging their own way. Hence I don't the possibility of there being an object that exposes a "CurrentPage" property in order to control it's properties at runtime. Maybe in future version MS will exposes some events raised by the renderers that you could hook into, but to my knowledge that's not available right now.
Hence the only way I see of achieveing what you suggest is to write your own rendering extension - which is not trivial - that handles the sizing of each page.
|||You can try to change all the columns width to zero. (its not a good solution for export to excel but only to HTML)
Adjusting Report width automatically
Hi All,
We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.
Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.
Any help is highly appreciated.
Regards, kart
Sizing is one of the few aspects of RS that cannot be controlled using expressions. Column widths are fixed and only row height supports auto sizing.
Paging is one of the last aspects of the rendering to be determined and it's also specific (and petentially different) to each rendering format. So HTML, Excel and PDF will all treat paging their own way. Hence I don't the possibility of there being an object that exposes a "CurrentPage" property in order to control it's properties at runtime. Maybe in future version MS will exposes some events raised by the renderers that you could hook into, but to my knowledge that's not available right now.
Hence the only way I see of achieveing what you suggest is to write your own rendering extension - which is not trivial - that handles the sizing of each page.
|||You can try to change all the columns width to zero. (its not a good solution for export to excel but only to HTML)
adjusting parameter controls of a report
Hi,
I'm making use of parameters to filter data in an report.
But the parameters appear in fixed-width listboxes in the report. Some
values of the parameters are longer than the width of the listboxes. My
question is how to adjust the width of these parameter listboxes. Any ideas?
Regards, Harry.Hi Harry,
apparently this is not possible.. I had the same question yesterday and I
got the following reply:
"On Jul 17, 3:54 am, mischa <mis...@.discussions.microsoft.com> wrote:
> I have designed a few reports in VS2005 with multi-parameter controls. When I
> view these reports in VS they look fine, however once deployed and viewed in
> the webbrowser (MS Internet Explorer) the drop-down boxes are too small (or
> the font size is too big).
> Is there a way to adjust (resize) the control boxes so you can make them
> larger so it's easier for people to actually read where they can choose
> between?
> thanks,
> mischa
As far as I know, there is not. This has been a common request on this
news group. Hopefully, this will be addressed in SSRS 2008. Otherwise,
you could try to inject CSS; however, I think that styling is only
allowed at the Report Manager page and report border level (though I'm
not sure). Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
"
best regards,
mischa
"Harry2007" wrote:
> Hi,
> I'm making use of parameters to filter data in an report.
> But the parameters appear in fixed-width listboxes in the report. Some
> values of the parameters are longer than the width of the listboxes. My
> question is how to adjust the width of these parameter listboxes. Any ideas?
> Regards, Harry.sql
I'm making use of parameters to filter data in an report.
But the parameters appear in fixed-width listboxes in the report. Some
values of the parameters are longer than the width of the listboxes. My
question is how to adjust the width of these parameter listboxes. Any ideas?
Regards, Harry.Hi Harry,
apparently this is not possible.. I had the same question yesterday and I
got the following reply:
"On Jul 17, 3:54 am, mischa <mis...@.discussions.microsoft.com> wrote:
> I have designed a few reports in VS2005 with multi-parameter controls. When I
> view these reports in VS they look fine, however once deployed and viewed in
> the webbrowser (MS Internet Explorer) the drop-down boxes are too small (or
> the font size is too big).
> Is there a way to adjust (resize) the control boxes so you can make them
> larger so it's easier for people to actually read where they can choose
> between?
> thanks,
> mischa
As far as I know, there is not. This has been a common request on this
news group. Hopefully, this will be addressed in SSRS 2008. Otherwise,
you could try to inject CSS; however, I think that styling is only
allowed at the Report Manager page and report border level (though I'm
not sure). Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
"
best regards,
mischa
"Harry2007" wrote:
> Hi,
> I'm making use of parameters to filter data in an report.
> But the parameters appear in fixed-width listboxes in the report. Some
> values of the parameters are longer than the width of the listboxes. My
> question is how to adjust the width of these parameter listboxes. Any ideas?
> Regards, Harry.sql
Adjusting Employee Hours
I wrote a post earlier, but my manager has asked that I do it
differently. I have a table with employee hours for particular
projects. (I work for a telemarketing company)
Table1:
Project Task Emp Hours
A1 TPV 1 5
A1 TPV 2 2
A3 AUDIT 3 4
TOTAL: 11
A2 ITM 4 10
A2 ITM 5 15
TOTAL: 25
I need to figure out the adjusted hours for these employees. The end
result should look like this:
Project Task Emp Hours AdjHours
A1 TPV 1 5 0
A1 TPV 2 2 0
A3 AUDIT 3 4 0
A2 ITM 4 10 14.4
A2 ITM 5 15 21.6
I need to add the hours for the TPV and Audit records and adjust
against the ITM records by doing the following calculation:
Emp1, 2, 3 hours (tpv and audit) = 11 hours
Emp4, 5 hours = 25 hours
Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
(project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
14.4
Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
(project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
21.6
I cannot hardcode anything. I need to use the table2 to figure out the
projects I am totaling and the projects to adjust against.
Table2:
Project Task AdjProject Billable PercentBillable
A1 TPV A2 1 100%
A2 ITM 0 0
A3 AUDIT A2 1 100%
Billable = 1 tells me that projects A1 and A3 need to be adjusted
against project A2.
Please, can anyone help?
Thanks,
NinelHi
The principles are the same as your previous post.
SELECT p.Project, p.Emp, p.Task, p.Hours,
CASE WHEN p.Task in ('Audit','TPV') THEN 0 ELSE p.hours + (p.Hours/t.total1)
* t.total2 END
FROM #ProjectTime P,
( SELECT CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN 0 ELSE Hours END)
AS DECIMAL(8,3)) as Total1,
CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN Hours ELSE 0 END) AS
DECIMAL(8,3)) as Total2
FROM #ProjectTime ) t
John
"ninel" wrote:
> I wrote a post earlier, but my manager has asked that I do it
> differently. I have a table with employee hours for particular
> projects. (I work for a telemarketing company)
> Table1:
> Project Task Emp Hours
> A1 TPV 1 5
> A1 TPV 2 2
> A3 AUDIT 3 4
> TOTAL: 11
> A2 ITM 4 10
> A2 ITM 5 15
> TOTAL: 25
> I need to figure out the adjusted hours for these employees. The end
> result should look like this:
> Project Task Emp Hours AdjHours
> A1 TPV 1 5 0
> A1 TPV 2 2 0
> A3 AUDIT 3 4 0
> A2 ITM 4 10 14.4
> A2 ITM 5 15 21.6
> I need to add the hours for the TPV and Audit records and adjust
> against the ITM records by doing the following calculation:
> Emp1, 2, 3 hours (tpv and audit) = 11 hours
> Emp4, 5 hours = 25 hours
> Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
> (project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
> 14.4
> Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
> (project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
> 21.6
> I cannot hardcode anything. I need to use the table2 to figure out the
> projects I am totaling and the projects to adjust against.
> Table2:
> Project Task AdjProject Billable PercentBillable
> A1 TPV A2 1 100%
> A2 ITM 0 0
> A3 AUDIT A2 1 100%
> Billable = 1 tells me that projects A1 and A3 need to be adjusted
> against project A2.
> Please, can anyone help?
> Thanks,
> Ninel
>
differently. I have a table with employee hours for particular
projects. (I work for a telemarketing company)
Table1:
Project Task Emp Hours
A1 TPV 1 5
A1 TPV 2 2
A3 AUDIT 3 4
TOTAL: 11
A2 ITM 4 10
A2 ITM 5 15
TOTAL: 25
I need to figure out the adjusted hours for these employees. The end
result should look like this:
Project Task Emp Hours AdjHours
A1 TPV 1 5 0
A1 TPV 2 2 0
A3 AUDIT 3 4 0
A2 ITM 4 10 14.4
A2 ITM 5 15 21.6
I need to add the hours for the TPV and Audit records and adjust
against the ITM records by doing the following calculation:
Emp1, 2, 3 hours (tpv and audit) = 11 hours
Emp4, 5 hours = 25 hours
Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
(project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
14.4
Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
(project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
21.6
I cannot hardcode anything. I need to use the table2 to figure out the
projects I am totaling and the projects to adjust against.
Table2:
Project Task AdjProject Billable PercentBillable
A1 TPV A2 1 100%
A2 ITM 0 0
A3 AUDIT A2 1 100%
Billable = 1 tells me that projects A1 and A3 need to be adjusted
against project A2.
Please, can anyone help?
Thanks,
NinelHi
The principles are the same as your previous post.
SELECT p.Project, p.Emp, p.Task, p.Hours,
CASE WHEN p.Task in ('Audit','TPV') THEN 0 ELSE p.hours + (p.Hours/t.total1)
* t.total2 END
FROM #ProjectTime P,
( SELECT CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN 0 ELSE Hours END)
AS DECIMAL(8,3)) as Total1,
CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN Hours ELSE 0 END) AS
DECIMAL(8,3)) as Total2
FROM #ProjectTime ) t
John
"ninel" wrote:
> I wrote a post earlier, but my manager has asked that I do it
> differently. I have a table with employee hours for particular
> projects. (I work for a telemarketing company)
> Table1:
> Project Task Emp Hours
> A1 TPV 1 5
> A1 TPV 2 2
> A3 AUDIT 3 4
> TOTAL: 11
> A2 ITM 4 10
> A2 ITM 5 15
> TOTAL: 25
> I need to figure out the adjusted hours for these employees. The end
> result should look like this:
> Project Task Emp Hours AdjHours
> A1 TPV 1 5 0
> A1 TPV 2 2 0
> A3 AUDIT 3 4 0
> A2 ITM 4 10 14.4
> A2 ITM 5 15 21.6
> I need to add the hours for the TPV and Audit records and adjust
> against the ITM records by doing the following calculation:
> Emp1, 2, 3 hours (tpv and audit) = 11 hours
> Emp4, 5 hours = 25 hours
> Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
> (project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
> 14.4
> Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
> (project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
> 21.6
> I cannot hardcode anything. I need to use the table2 to figure out the
> projects I am totaling and the projects to adjust against.
> Table2:
> Project Task AdjProject Billable PercentBillable
> A1 TPV A2 1 100%
> A2 ITM 0 0
> A3 AUDIT A2 1 100%
> Billable = 1 tells me that projects A1 and A3 need to be adjusted
> against project A2.
> Please, can anyone help?
> Thanks,
> Ninel
>
Adjusting column width in queried dataset
I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.
Thanks in advance.
Mark.
Did you try rtrim(ltrim(columnname) in the query?
cheers,
Andrew
|||Thanks Andrew,
rtrim(columnname) did it.
Cheers,
Mark.
adjust x,y position of crystal report data field from VB
dear friend,
as you know, to adjust x,y position of any data field on Crystal report,
we just open CR and manually put and adjust the datafield...
But how if our VB application want to change this setting too ?
how to pass the x,y parameter of specific data field to crystal report, so crystal report will adjust automatically before it displays. ??
Thank you,If you use RDC to display or print the report, you can do it by changing the properties of the object.
Report1.Object1.Property1 = Value1
MyRpt.fldDate.Left = 1500
MyRpt.fldDate.Top = 250
as you know, to adjust x,y position of any data field on Crystal report,
we just open CR and manually put and adjust the datafield...
But how if our VB application want to change this setting too ?
how to pass the x,y parameter of specific data field to crystal report, so crystal report will adjust automatically before it displays. ??
Thank you,If you use RDC to display or print the report, you can do it by changing the properties of the object.
Report1.Object1.Property1 = Value1
MyRpt.fldDate.Left = 1500
MyRpt.fldDate.Top = 250
Adjust values in db base in the value modified on the form?
Hi everyone,
Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.
Thanks for your help!Hi everyone,
Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.
Thanks for your help!
Paste your query or the tables involved and explain what you want in new one?|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.
Ex. #1 - Discount value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (hasn't been changed) -> I need to calculate it then. it becomes $180
Ex. #2 - Discount and TotalSellPrice values are changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (changed to) $150
Then I need to calculate TotalSellPrice since both Discount and TotalSellPrice values have been changed. TotalSellPrice becomes $180
Ex. #3 - TotalSellPrcie value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to calculate it. Discount is 25%
TotalSellPrice $0 (changed to) $150
Ex. #4 - TotalSellPrice value is changed to number bigger then it's original one then I have to keep Discount at 0% don't go negative
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to keep Discount at 0%
TotalSellPrice $0 (changed to) $250|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.
Whatever I make out from the above problem is, you want to adjust the data according to the changes in the other fields.I think you can easily do it at the front end and send the update query to the database according to its invoice no.
An user can see the store data and update it anytime by changing the different fields.You can do the neccessary calculation at the front end.And pass a simple update query to the database.That will do the trick..|||I think you can easily do it at the front end and send the update query to the database according to its invoice no.
The thing is it would not be efficient from a programming stand point, because I have to grab all the information from the database and store it somewhere in the session, then compare it to what user enters, then create a query dynamically instead of using stored procedure. I need a back end query that will handle all these stuff for me.
Any idea?|||Any idea?
And you need not have to create a stored procedure for such a small calculation.I think so...;)|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database
Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"
After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database
Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"
After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.
So...?? You can write the adjustment code in asp page,and simply update the record.Mind it you are not doing anything more than that..so I don't think on the point of efficiency ,use of stored procedure will make any difference...|||After user updates any of writable fields I do update and populate that form again but base on the rules I described before...You think it is efficient or good programming practice to make a call to the database every time a user changes a value on a form?
It's not, and you certainly wouldn't design scalable enterprise applications this way.
Your form should get complete recordsets from the database (even defaults), and should submit complete recordsets to the database.
And frankly, you don't have to store all the detail data to keep track of the new total. NewTotal = OldTotal - OldValue + NewValue. That is just three variables.
Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.
Thanks for your help!Hi everyone,
Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.
Thanks for your help!
Paste your query or the tables involved and explain what you want in new one?|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.
Ex. #1 - Discount value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (hasn't been changed) -> I need to calculate it then. it becomes $180
Ex. #2 - Discount and TotalSellPrice values are changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (changed to) $150
Then I need to calculate TotalSellPrice since both Discount and TotalSellPrice values have been changed. TotalSellPrice becomes $180
Ex. #3 - TotalSellPrcie value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to calculate it. Discount is 25%
TotalSellPrice $0 (changed to) $150
Ex. #4 - TotalSellPrice value is changed to number bigger then it's original one then I have to keep Discount at 0% don't go negative
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to keep Discount at 0%
TotalSellPrice $0 (changed to) $250|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.
Whatever I make out from the above problem is, you want to adjust the data according to the changes in the other fields.I think you can easily do it at the front end and send the update query to the database according to its invoice no.
An user can see the store data and update it anytime by changing the different fields.You can do the neccessary calculation at the front end.And pass a simple update query to the database.That will do the trick..|||I think you can easily do it at the front end and send the update query to the database according to its invoice no.
The thing is it would not be efficient from a programming stand point, because I have to grab all the information from the database and store it somewhere in the session, then compare it to what user enters, then create a query dynamically instead of using stored procedure. I need a back end query that will handle all these stuff for me.
Any idea?|||Any idea?
And you need not have to create a stored procedure for such a small calculation.I think so...;)|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database
Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"
After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database
Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"
After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.
So...?? You can write the adjustment code in asp page,and simply update the record.Mind it you are not doing anything more than that..so I don't think on the point of efficiency ,use of stored procedure will make any difference...|||After user updates any of writable fields I do update and populate that form again but base on the rules I described before...You think it is efficient or good programming practice to make a call to the database every time a user changes a value on a form?
It's not, and you certainly wouldn't design scalable enterprise applications this way.
Your form should get complete recordsets from the database (even defaults), and should submit complete recordsets to the database.
And frankly, you don't have to store all the detail data to keep track of the new total. NewTotal = OldTotal - OldValue + NewValue. That is just three variables.
Adjust Parameter Dropdown control Size/Length
Dear Anyone,
Is there a way to adjust Parameter Dropdown control Size/Length of a report so that it would no longer display a scrollbar?
Thanks,
JosephSorry, this is not a customizable property.
-Danielsql
Adjust Parameter Dropdown control Size/Length
Dear Anyone,
Is there a way to adjust Parameter Dropdown control Size/Length of a report so that it would no longer display a scrollbar?
Thanks,
JosephSorry, this is not a customizable property.
-Daniel
Adjust number of decimal places
I have a column used in my report that has values such as:
496.1000
These are money values and thus I require them to be in the format:
496.10
How do I get rid of the two extra zeroes?
(my sql statement actually does it, but they still appear in reporting
services for some reason)
Thanks!Don't worry about the above - I found the answer in the SQL 2005
documentation
For those interested, it was under:
Formatting Numeric and Date Values in a Report
Cheers
On Dec 18, 9:27 am, "David Conte" <davco...@.gmail.com> wrote:
> I have a column used in my report that has values such as:
> 496.1000
> These are money values and thus I require them to be in the format:
> 496.10
> How do I get rid of the two extra zeroes?
> (my sql statement actually does it, but they still appear in reporting
> services for some reason)
> Thanks!
496.1000
These are money values and thus I require them to be in the format:
496.10
How do I get rid of the two extra zeroes?
(my sql statement actually does it, but they still appear in reporting
services for some reason)
Thanks!Don't worry about the above - I found the answer in the SQL 2005
documentation
For those interested, it was under:
Formatting Numeric and Date Values in a Report
Cheers
On Dec 18, 9:27 am, "David Conte" <davco...@.gmail.com> wrote:
> I have a column used in my report that has values such as:
> 496.1000
> These are money values and thus I require them to be in the format:
> 496.10
> How do I get rid of the two extra zeroes?
> (my sql statement actually does it, but they still appear in reporting
> services for some reason)
> Thanks!
adjust my code
Hi,
I've created this code to determine the dates for an exam when a
certification must be met within a X number of months or years. The last
deadline date for an exam is the enddate. The marge where one can
schedule his time to study depends on how many exams he must take. So
based on the enddate i calculate backwards to the startdate to get
intervals of when the next examdate should be.
In my testscenario i used 12 months and the number of exams are 4. The
code gave me the results i needed, but when i tried e.g. 18 months the
results are not what i expected. The dates are somewhat correct but
there were too many dates.
Can someone see the error in my code?
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
set @.begindate = '2005-11-23'
set @.enddate = dateadd(mm,12,@.begindate)
set @.exams = 4
set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
while (select count(*) from #examdates) <= @.intervals
begin
set @.examdate = (select examdate from #examdates where id in
(select max(id) from #examdates))
insert into #examdates(examdate)
select dateadd(mm,-@.exams,@.examdate) as examdate
end
select examdate from #examdates order by examdateJason
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
>
What do you want to return if you put 18 months in?
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can schedule
> his time to study depends on how many exams he must take. So based on the
> enddate i calculate backwards to the startdate to get intervals of when
> the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be
> done --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate|||You are creating a variable called intervals which is really the number of
months that you have to take each exam based on the way that you set it.
datediff(mm,@.begindate,@.enddate)/@.exams
So for 12 months this would be 3 but for 18 months it would be 4.5.
You then use this as the loop constraint for adding in your exam dates.
It seems to me like your look constraint should just be the number of exams
that you need to take since you are trying to find a date when to take each
of the exams.
This is the core of your problem. I didn't put any thought into the rest of
the algorithm and how you might be able to accomplish your goal easier.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:
> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
> schedule his time to study depends on how many exams he must take. So
> based on the enddate i calculate backwards to the startdate to get
> intervals of when the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be done
> --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate
>|||Ryan Powers wrote:
> You are creating a variable called intervals which is really the number of
> months that you have to take each exam based on the way that you set it.
> datediff(mm,@.begindate,@.enddate)/@.exams
> So for 12 months this would be 3 but for 18 months it would be 4.5.
> You then use this as the loop constraint for adding in your exam dates.
> It seems to me like your look constraint should just be the number of exam
s
> that you need to take since you are trying to find a date when to take eac
h
> of the exams.
>
> This is the core of your problem. I didn't put any thought into the rest
of
> the algorithm and how you might be able to accomplish your goal easier.
> HTH
>
Hi Ryan,
I want to calculate a date for taking an exam. The intervals are just
the number of months when the next exam must be taken.
Could you point out to me where i should better my code?|||Uri Dimant wrote:
> Jason
>
>
> What do you want to return if you put 18 months in?
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
>
>
Hi Uri,
I want to return 4 examdates because the number of exams to be taken are
4, the higher the months the longer someone may study until the next
date occurs.
In case of adding 18 months to the begindate, divide that with the
number of exams, you'll get 4.5 months. So knowing the enddate (is also
the last examdate deadline) i substract the 4.5 months from the enddate
which will give me the examdate before that and so on.
Can you find the mistake i have made in the code?|||Try the following script. I have given some hint about changes made in your
script. Inside the While loop, I have removed the select statement from the
#examdates table. This will enhance performance.
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int
-- Added script
declare @.NextExamDate datetime, @.LastExamDate datetime
--
set @.begindate = '20051101'
set @.enddate = dateadd(mm,12,@.begindate)
-- Added script
set @.LastExamDate = @.enddate
--
set @.exams = 4
--[replaced with below line] set @.intervals =
datediff(mm,@.begindate,@.enddate)/@.exams
set @.intervals = datediff(dd,@.begindate,@.enddate)/@.exams
--create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
-- While loop script with lots of changes
while @.exams > 1
begin
set @.NextExamDate = dateadd(dd,-@.intervals,@.LastExamDate)
insert into #examdates(examdate) values(@.NextExamDate)
set @.LastExamDate = @.NextExamDate
set @.exams = @.exams -1
end
--
select examdate from #examdates order by examdate
"Jason" wrote:
> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||Sure. You don't need the intervals variable, since you are actually
recalcing it on the fly within the loop. I am going to just keep your base
logic, and show you how you can correct it so it works.
I changed it slightly to find days between exams because I'm thinking that
you can evenly divide the months by the number of exams is not correct. Wha
t
I did will not necessarily give you your last exam on your end date due to
integer math. But, it should be close. We could put in a condition that
checks if we are setting the last date and just set it as the enddate. Let
me know if you need help with that.-
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
@.months int,
@.daysBetweenExams
set @.begindate = '2005-11-23'
set @.exams = 4
set @.months = 12
set @.enddate = dateadd(mm,@.months,@.begindate)
set @.daysBetweenExams = datediff(dd, @.begindate, @.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
set @.examdate = @.begindate
while (select count(*) from #examdates) <= @.exams
begin
SELECT @.examdate = dateadd(dd, @.daysBetweenExams, @.examdate)
insert into #examdates(examdate)
values(@.examdate)
end
select examdate from #examdates order by examdate
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:
> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:
>Hi,
>I've created this code to determine the dates for an exam when a
>certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
>schedule his time to study depends on how many exams he must take. So
>based on the enddate i calculate backwards to the startdate to get
>intervals of when the next examdate should be.
>In my testscenario i used 12 months and the number of exams are 4. The
>code gave me the results i needed, but when i tried e.g. 18 months the
>results are not what i expected. The dates are somewhat correct but
>there were too many dates.
>Can someone see the error in my code?
Hi Jason,
Why not use a set-based query instead of looping?
-- inputs:
DECLARE @.begindate datetime,
@.enddate datetime,
@.exams int
SET @.begindate = '2005-11-23'
SET @.enddate = '2007-05-23'
SET @.exams = 4
-- generate exam dates
--INSERT INTO @.examdate(examdate)
SELECT DATEADD(month,
Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
@.begindate)
FROM dbo.Numbers
WHERE Number BETWEEN 1 AND @.exams
Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:
>
>
> Hi Jason,
> Why not use a set-based query instead of looping?
> -- inputs:
> DECLARE @.begindate datetime,
> @.enddate datetime,
> @.exams int
> SET @.begindate = '2005-11-23'
> SET @.enddate = '2007-05-23'
> SET @.exams = 4
> -- generate exam dates
> --INSERT INTO @.examdate(examdate)
> SELECT DATEADD(month,
> Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
> @.begindate)
> FROM dbo.Numbers
> WHERE Number BETWEEN 1 AND @.exams
> Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
>
Hi hugo,
your solution did the job. Thnx!
I've created this code to determine the dates for an exam when a
certification must be met within a X number of months or years. The last
deadline date for an exam is the enddate. The marge where one can
schedule his time to study depends on how many exams he must take. So
based on the enddate i calculate backwards to the startdate to get
intervals of when the next examdate should be.
In my testscenario i used 12 months and the number of exams are 4. The
code gave me the results i needed, but when i tried e.g. 18 months the
results are not what i expected. The dates are somewhat correct but
there were too many dates.
Can someone see the error in my code?
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
set @.begindate = '2005-11-23'
set @.enddate = dateadd(mm,12,@.begindate)
set @.exams = 4
set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
while (select count(*) from #examdates) <= @.intervals
begin
set @.examdate = (select examdate from #examdates where id in
(select max(id) from #examdates))
insert into #examdates(examdate)
select dateadd(mm,-@.exams,@.examdate) as examdate
end
select examdate from #examdates order by examdateJason
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
>
What do you want to return if you put 18 months in?
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can schedule
> his time to study depends on how many exams he must take. So based on the
> enddate i calculate backwards to the startdate to get intervals of when
> the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be
> done --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate|||You are creating a variable called intervals which is really the number of
months that you have to take each exam based on the way that you set it.
datediff(mm,@.begindate,@.enddate)/@.exams
So for 12 months this would be 3 but for 18 months it would be 4.5.
You then use this as the loop constraint for adding in your exam dates.
It seems to me like your look constraint should just be the number of exams
that you need to take since you are trying to find a date when to take each
of the exams.
This is the core of your problem. I didn't put any thought into the rest of
the algorithm and how you might be able to accomplish your goal easier.
HTH
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:
> Hi,
> I've created this code to determine the dates for an exam when a
> certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
> schedule his time to study depends on how many exams he must take. So
> based on the enddate i calculate backwards to the startdate to get
> intervals of when the next examdate should be.
> In my testscenario i used 12 months and the number of exams are 4. The
> code gave me the results i needed, but when i tried e.g. 18 months the
> results are not what i expected. The dates are somewhat correct but
> there were too many dates.
> Can someone see the error in my code?
> declare @.exams int,
> @.begindate datetime,
> @.enddate datetime,
> @.examdate datetime,
> @.intervals int,
> set @.begindate = '2005-11-23'
> set @.enddate = dateadd(mm,12,@.begindate)
> set @.exams = 4
> set @.intervals = datediff(mm,@.begindate,@.enddate)/@.exams
> create table #examdates(id int identity(1,1) , examdate datetime)
> --i inserted the enddate as startingpoint, but this could be done
> --better, i think
> insert into #examdates(examdate)
> values(@.enddate)
> while (select count(*) from #examdates) <= @.intervals
> begin
> set @.examdate = (select examdate from #examdates where id in
> (select max(id) from #examdates))
> insert into #examdates(examdate)
> select dateadd(mm,-@.exams,@.examdate) as examdate
> end
> select examdate from #examdates order by examdate
>|||Ryan Powers wrote:
> You are creating a variable called intervals which is really the number of
> months that you have to take each exam based on the way that you set it.
> datediff(mm,@.begindate,@.enddate)/@.exams
> So for 12 months this would be 3 but for 18 months it would be 4.5.
> You then use this as the loop constraint for adding in your exam dates.
> It seems to me like your look constraint should just be the number of exam
s
> that you need to take since you are trying to find a date when to take eac
h
> of the exams.
>
> This is the core of your problem. I didn't put any thought into the rest
of
> the algorithm and how you might be able to accomplish your goal easier.
> HTH
>
Hi Ryan,
I want to calculate a date for taking an exam. The intervals are just
the number of months when the next exam must be taken.
Could you point out to me where i should better my code?|||Uri Dimant wrote:
> Jason
>
>
> What do you want to return if you put 18 months in?
>
> "Jason" <jasonlewis@.hotmail.com> wrote in message
> news:eonG0vfEGHA.2040@.TK2MSFTNGP14.phx.gbl...
>
>
>
Hi Uri,
I want to return 4 examdates because the number of exams to be taken are
4, the higher the months the longer someone may study until the next
date occurs.
In case of adding 18 months to the begindate, divide that with the
number of exams, you'll get 4.5 months. So knowing the enddate (is also
the last examdate deadline) i substract the 4.5 months from the enddate
which will give me the examdate before that and so on.
Can you find the mistake i have made in the code?|||Try the following script. I have given some hint about changes made in your
script. Inside the While loop, I have removed the select statement from the
#examdates table. This will enhance performance.
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int
-- Added script
declare @.NextExamDate datetime, @.LastExamDate datetime
--
set @.begindate = '20051101'
set @.enddate = dateadd(mm,12,@.begindate)
-- Added script
set @.LastExamDate = @.enddate
--
set @.exams = 4
--[replaced with below line] set @.intervals =
datediff(mm,@.begindate,@.enddate)/@.exams
set @.intervals = datediff(dd,@.begindate,@.enddate)/@.exams
--create table #examdates(id int identity(1,1) , examdate datetime)
--i inserted the enddate as startingpoint, but this could be done
--better, i think
insert into #examdates(examdate)
values(@.enddate)
-- While loop script with lots of changes
while @.exams > 1
begin
set @.NextExamDate = dateadd(dd,-@.intervals,@.LastExamDate)
insert into #examdates(examdate) values(@.NextExamDate)
set @.LastExamDate = @.NextExamDate
set @.exams = @.exams -1
end
--
select examdate from #examdates order by examdate
"Jason" wrote:
> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||Sure. You don't need the intervals variable, since you are actually
recalcing it on the fly within the loop. I am going to just keep your base
logic, and show you how you can correct it so it works.
I changed it slightly to find days between exams because I'm thinking that
you can evenly divide the months by the number of exams is not correct. Wha
t
I did will not necessarily give you your last exam on your end date due to
integer math. But, it should be close. We could put in a condition that
checks if we are setting the last date and just set it as the enddate. Let
me know if you need help with that.-
declare @.exams int,
@.begindate datetime,
@.enddate datetime,
@.examdate datetime,
@.intervals int,
@.months int,
@.daysBetweenExams
set @.begindate = '2005-11-23'
set @.exams = 4
set @.months = 12
set @.enddate = dateadd(mm,@.months,@.begindate)
set @.daysBetweenExams = datediff(dd, @.begindate, @.enddate)/@.exams
create table #examdates(id int identity(1,1) , examdate datetime)
set @.examdate = @.begindate
while (select count(*) from #examdates) <= @.exams
begin
SELECT @.examdate = dateadd(dd, @.daysBetweenExams, @.examdate)
insert into #examdates(examdate)
values(@.examdate)
end
select examdate from #examdates order by examdate
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Jason" wrote:
> Ryan Powers wrote:
> Hi Ryan,
> I want to calculate a date for taking an exam. The intervals are just
> the number of months when the next exam must be taken.
> Could you point out to me where i should better my code?
>|||On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:
>Hi,
>I've created this code to determine the dates for an exam when a
>certification must be met within a X number of months or years. The last
> deadline date for an exam is the enddate. The marge where one can
>schedule his time to study depends on how many exams he must take. So
>based on the enddate i calculate backwards to the startdate to get
>intervals of when the next examdate should be.
>In my testscenario i used 12 months and the number of exams are 4. The
>code gave me the results i needed, but when i tried e.g. 18 months the
>results are not what i expected. The dates are somewhat correct but
>there were too many dates.
>Can someone see the error in my code?
Hi Jason,
Why not use a set-based query instead of looping?
-- inputs:
DECLARE @.begindate datetime,
@.enddate datetime,
@.exams int
SET @.begindate = '2005-11-23'
SET @.enddate = '2007-05-23'
SET @.exams = 4
-- generate exam dates
--INSERT INTO @.examdate(examdate)
SELECT DATEADD(month,
Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
@.begindate)
FROM dbo.Numbers
WHERE Number BETWEEN 1 AND @.exams
Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:
> On Thu, 05 Jan 2006 14:28:42 +0100, Jason wrote:
>
>
> Hi Jason,
> Why not use a set-based query instead of looping?
> -- inputs:
> DECLARE @.begindate datetime,
> @.enddate datetime,
> @.exams int
> SET @.begindate = '2005-11-23'
> SET @.enddate = '2007-05-23'
> SET @.exams = 4
> -- generate exam dates
> --INSERT INTO @.examdate(examdate)
> SELECT DATEADD(month,
> Number * DATEDIFF(month, @.begindate, @.enddate) / @.exams,
> @.begindate)
> FROM dbo.Numbers
> WHERE Number BETWEEN 1 AND @.exams
> Note: this requires the use of a numbers table. See www.aspfaq.com/2516.
>
Hi hugo,
your solution did the job. Thnx!
adjust colours of bars in bar charts at run time
Hi,
does anyone know if it is possible to adjust the colours of the bars in
bar charts at run time using custom assemblies or functions? I have
clicked on all the options I can find and I can see no expression
capability for the colours of the bars, only a selection of color
schemes...is this just not an option in 2000?
Thanks
Peter
www.peternolan.comYou will need to have at least SP1 of Reporting Services 2000 installed (on
the server and on the report designer). Please check the SP1/SP2 Readme for
more details:
http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm#_chart_enhancements
You can also search the archives of this newsgroup and you should find many
related threads about setting datapoint colors in charts.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1127599073.728978.199980@.g44g2000cwa.googlegroups.com...
> Hi,
> does anyone know if it is possible to adjust the colours of the bars in
> bar charts at run time using custom assemblies or functions? I have
> clicked on all the options I can find and I can see no expression
> capability for the colours of the bars, only a selection of color
> schemes...is this just not an option in 2000?
> Thanks
> Peter
> www.peternolan.com
>|||Hi Robert,
I just installed RS...SP2.....yes, I understand I can change the
colour of the data points printed on the chart at run time.....but I
cannot see any way to change the colour of the bar itself....it only
appears that I can change the style of the report between things like
'excel', 'earthy', 'transparent' etc...and the colours of the bars are
rendered according to these definitions for all bars in the
series.....
No big deal as I will probably use another charting package on
2005...but I was wondering if I can do this in RS charts...
Thanks
Peter|||Peter,
I've done this in the following way:
In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
function:
= Code.GetColor(Fields!RightsHolder.Value)
And in the code for the report I have:
Public Function GetColor(ByVal RightsHolder As String) As String
if RightsHolder = "EMI" Then
Return "#CC3333" ' "Red"
end if
if RightsHolder = "EMI RW" Then
Return "Pink"
end if
if RightsHolder = "WMG" Then
Return "#3300CC" ' "Blue"
end if
if (RightsHolder <> "WMG") and (RightsHolder <> "EMI") and (RightsHolder
<> "EMI RW") Then
Return "Black"
end if
End Function
HTH,
Paul Ibison, SQL Server MVP|||Paul,
thanks for that...I will give it a go.
Peter|||You cannot define your own custom palettes directly. The closest you can get
is to use the approach discussed in the following blog posting (it also
provides a sample for download):
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
The sample defines its own "color palette" by always overriding the colors
that come from the selected color palette.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1127656949.717291.17150@.z14g2000cwz.googlegroups.com...
> Hi Robert,
> I just installed RS...SP2.....yes, I understand I can change the
> colour of the data points printed on the chart at run time.....but I
> cannot see any way to change the colour of the bar itself....it only
> appears that I can change the style of the report between things like
> 'excel', 'earthy', 'transparent' etc...and the colours of the bars are
> rendered according to these definitions for all bars in the
> series.....
> No big deal as I will probably use another charting package on
> 2005...but I was wondering if I can do this in RS charts...
> Thanks
> Peter
>|||Robert,
apologies for not referencing/crediting your great blog example - knew I got
my code template from somewhere but couldn't remember where :)
Cheers,
Paul Ibison, SQL Server MVP|||That's fine. I'm glad people find my samples, tips, answers, etc. useful and
it helps them solving their issues.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u3iZoK2wFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Robert,
> apologies for not referencing/crediting your great blog example - knew I
> got my code template from somewhere but couldn't remember where :)
> Cheers,
> Paul Ibison, SQL Server MVP
>|||Paul,
you are brilliant!!! Thank You!!!!
When I read this I thought it related to the numbers that were printed
on the chart if they were printed...but when I tested I found it was
the bar and line colour that it was referring to...now I can pass data
from the row to a member of a custom assembly and get it to generate
the bars on the chart to be the colours I want!!
Thank you very much for this...this is a feature I really wanted on my
charts....
Best Regards
Peter
www.peternolan.com|||Hello,
I read the blog and for HTML and PDF it works great. However when i export a
report to excel, excel reinterprets the colors to what it can handle.
In my case i made a colorpalette of only oranges and browns. When i export
it to excel, the graph itself is exported ok. Only the colors i used in the
legend table are changed. eg. GoldenRod, Orange and DarkOrange are all
reverted to the same color.
Is there a way to have the table also exported as a picture or something to
get the right color across?
"Robert Bruckner [MSFT]" wrote:
> You cannot define your own custom palettes directly. The closest you can get
> is to use the approach discussed in the following blog posting (it also
> provides a sample for download):
> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> The sample defines its own "color palette" by always overriding the colors
> that come from the selected color palette.
>|||Hi,
I don't seem to have the 'Series Style" section showing on my 'Appearance'
tab.
Have installed SP2 for RS 2000 on the server, but surely this is a "client"
issue with
SQL Server Report Designer. I'm using Ver 8.00.743
Any ideas?
"Paul Ibison" wrote:
> Peter,
> I've done this in the following way:
> In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
> function:
> = Code.GetColor(Fields!RightsHolder.Value)
> And in the code for the report I have:
> Public Function GetColor(ByVal RightsHolder As String) As String
> if RightsHolder = "EMI" Then
> Return "#CC3333" ' "Red"
> end if
> if RightsHolder = "EMI RW" Then
> Return "Pink"
> end if
> if RightsHolder = "WMG" Then
> Return "#3300CC" ' "Blue"
> end if
> if (RightsHolder <> "WMG") and (RightsHolder <> "EMI") and (RightsHolder
> <> "EMI RW") Then
> Return "Black"
> end if
> End Function
> HTH,
> Paul Ibison, SQL Server MVP
>
>
does anyone know if it is possible to adjust the colours of the bars in
bar charts at run time using custom assemblies or functions? I have
clicked on all the options I can find and I can see no expression
capability for the colours of the bars, only a selection of color
schemes...is this just not an option in 2000?
Thanks
Peter
www.peternolan.comYou will need to have at least SP1 of Reporting Services 2000 installed (on
the server and on the report designer). Please check the SP1/SP2 Readme for
more details:
http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm#_chart_enhancements
You can also search the archives of this newsgroup and you should find many
related threads about setting datapoint colors in charts.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1127599073.728978.199980@.g44g2000cwa.googlegroups.com...
> Hi,
> does anyone know if it is possible to adjust the colours of the bars in
> bar charts at run time using custom assemblies or functions? I have
> clicked on all the options I can find and I can see no expression
> capability for the colours of the bars, only a selection of color
> schemes...is this just not an option in 2000?
> Thanks
> Peter
> www.peternolan.com
>|||Hi Robert,
I just installed RS...SP2.....yes, I understand I can change the
colour of the data points printed on the chart at run time.....but I
cannot see any way to change the colour of the bar itself....it only
appears that I can change the style of the report between things like
'excel', 'earthy', 'transparent' etc...and the colours of the bars are
rendered according to these definitions for all bars in the
series.....
No big deal as I will probably use another charting package on
2005...but I was wondering if I can do this in RS charts...
Thanks
Peter|||Peter,
I've done this in the following way:
In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
function:
= Code.GetColor(Fields!RightsHolder.Value)
And in the code for the report I have:
Public Function GetColor(ByVal RightsHolder As String) As String
if RightsHolder = "EMI" Then
Return "#CC3333" ' "Red"
end if
if RightsHolder = "EMI RW" Then
Return "Pink"
end if
if RightsHolder = "WMG" Then
Return "#3300CC" ' "Blue"
end if
if (RightsHolder <> "WMG") and (RightsHolder <> "EMI") and (RightsHolder
<> "EMI RW") Then
Return "Black"
end if
End Function
HTH,
Paul Ibison, SQL Server MVP|||Paul,
thanks for that...I will give it a go.
Peter|||You cannot define your own custom palettes directly. The closest you can get
is to use the approach discussed in the following blog posting (it also
provides a sample for download):
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
The sample defines its own "color palette" by always overriding the colors
that come from the selected color palette.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1127656949.717291.17150@.z14g2000cwz.googlegroups.com...
> Hi Robert,
> I just installed RS...SP2.....yes, I understand I can change the
> colour of the data points printed on the chart at run time.....but I
> cannot see any way to change the colour of the bar itself....it only
> appears that I can change the style of the report between things like
> 'excel', 'earthy', 'transparent' etc...and the colours of the bars are
> rendered according to these definitions for all bars in the
> series.....
> No big deal as I will probably use another charting package on
> 2005...but I was wondering if I can do this in RS charts...
> Thanks
> Peter
>|||Robert,
apologies for not referencing/crediting your great blog example - knew I got
my code template from somewhere but couldn't remember where :)
Cheers,
Paul Ibison, SQL Server MVP|||That's fine. I'm glad people find my samples, tips, answers, etc. useful and
it helps them solving their issues.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u3iZoK2wFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Robert,
> apologies for not referencing/crediting your great blog example - knew I
> got my code template from somewhere but couldn't remember where :)
> Cheers,
> Paul Ibison, SQL Server MVP
>|||Paul,
you are brilliant!!! Thank You!!!!
When I read this I thought it related to the numbers that were printed
on the chart if they were printed...but when I tested I found it was
the bar and line colour that it was referring to...now I can pass data
from the row to a member of a custom assembly and get it to generate
the bars on the chart to be the colours I want!!
Thank you very much for this...this is a feature I really wanted on my
charts....
Best Regards
Peter
www.peternolan.com|||Hello,
I read the blog and for HTML and PDF it works great. However when i export a
report to excel, excel reinterprets the colors to what it can handle.
In my case i made a colorpalette of only oranges and browns. When i export
it to excel, the graph itself is exported ok. Only the colors i used in the
legend table are changed. eg. GoldenRod, Orange and DarkOrange are all
reverted to the same color.
Is there a way to have the table also exported as a picture or something to
get the right color across?
"Robert Bruckner [MSFT]" wrote:
> You cannot define your own custom palettes directly. The closest you can get
> is to use the approach discussed in the following blog posting (it also
> provides a sample for download):
> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> The sample defines its own "color palette" by always overriding the colors
> that come from the selected color palette.
>|||Hi,
I don't seem to have the 'Series Style" section showing on my 'Appearance'
tab.
Have installed SP2 for RS 2000 on the server, but surely this is a "client"
issue with
SQL Server Report Designer. I'm using Ver 8.00.743
Any ideas?
"Paul Ibison" wrote:
> Peter,
> I've done this in the following way:
> In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
> function:
> = Code.GetColor(Fields!RightsHolder.Value)
> And in the code for the report I have:
> Public Function GetColor(ByVal RightsHolder As String) As String
> if RightsHolder = "EMI" Then
> Return "#CC3333" ' "Red"
> end if
> if RightsHolder = "EMI RW" Then
> Return "Pink"
> end if
> if RightsHolder = "WMG" Then
> Return "#3300CC" ' "Blue"
> end if
> if (RightsHolder <> "WMG") and (RightsHolder <> "EMI") and (RightsHolder
> <> "EMI RW") Then
> Return "Black"
> end if
> End Function
> HTH,
> Paul Ibison, SQL Server MVP
>
>
Adhoc,prepared,dynamic,what else...
I keep hearing about these different types of SQL statements. Can someone
tell me what they all are vs differences in brief or maybe point me to a
link if one exists ?
Hi Hassan
"Hassan" wrote:
> I keep hearing about these different types of SQL statements. Can someone
> tell me what they all are vs differences in brief or maybe point me to a
> link if one exists ?
>
I am not sure if there is one that specifically exists but you may be able
to piece together something for yourself from
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/aa175762(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms998569.aspx
http://msdn2.microsoft.com/pt-br/library/aa175244(SQL.80).aspx
John
sql
tell me what they all are vs differences in brief or maybe point me to a
link if one exists ?
Hi Hassan
"Hassan" wrote:
> I keep hearing about these different types of SQL statements. Can someone
> tell me what they all are vs differences in brief or maybe point me to a
> link if one exists ?
>
I am not sure if there is one that specifically exists but you may be able
to piece together something for yourself from
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/aa175762(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms998569.aspx
http://msdn2.microsoft.com/pt-br/library/aa175244(SQL.80).aspx
John
sql
Adhoc,prepared,dynamic,what else...
I keep hearing about these different types of SQL statements. Can someone
tell me what they all are vs differences in brief or maybe point me to a
link if one exists ?Hi Hassan
"Hassan" wrote:
> I keep hearing about these different types of SQL statements. Can someone
> tell me what they all are vs differences in brief or maybe point me to a
> link if one exists ?
>
I am not sure if there is one that specifically exists but you may be able
to piece together something for yourself from
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/aa175762(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms998569.aspx
http://msdn2.microsoft.com/pt-br/library/aa175244(SQL.80).aspx
John
tell me what they all are vs differences in brief or maybe point me to a
link if one exists ?Hi Hassan
"Hassan" wrote:
> I keep hearing about these different types of SQL statements. Can someone
> tell me what they all are vs differences in brief or maybe point me to a
> link if one exists ?
>
I am not sure if there is one that specifically exists but you may be able
to piece together something for yourself from
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/aa175762(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms998569.aspx
http://msdn2.microsoft.com/pt-br/library/aa175244(SQL.80).aspx
John
Adhoc vs PROC
Hi Guru,
When I ran my adhoc script below it generated only 45000 reads or 4 seconds but when I wrapped it into procedure it took about two minutes or millions of reads. The parameters calling both adhoc and proc are indeed the same. I'm pretty 99.9% sure that the proc does not recompile because I don't mix up between DDL and DML, no temp tables or any thing to cause proc to recompile. The big difference is adhoc used index scan for 45% but proc used bookmark lookup for 75%. Why it's so difference since they both returned the same results?
Please help...
Below is my code,
DECLARE @.Mode varchar(10),
@.UserID varchar(36),
@.FromDate smalldatetime,
@.ToDate smalldatetime,
@.Inst tinyint,
@.LocationID smallint,
@.BunitID tinyint,
@.TeamID int
SET @.Mode='TEAM'
SET @.UserID=''
SET @.FromDate='Dec 1 2006 12:00AM'
SET @.ToDate='Dec 31 2006 12:00AM'
SET @.Inst=28
SET @.LocationID=0
SET @.BunitID=2
SET @.TeamID=805
--IF @.Mode = 'TEAM'
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped RIGHT OUTER JOIN
dbo.MyTeamsRpt ON vw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerId
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (MyTeamID = @.TeamID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate))
GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
IF @.Mode = 'RM'
BEGIN
IF @.BUnitId > 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, '' As TeamName, OffBUnitDesc As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate)) AND OffBUnitID = @.BUnitID
GROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
--ELSE
IF @.BUnitId = 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, '' As TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate))
GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
END
END
Adhoc vs PROC
Hi Guru,
Hi Silaphet,
Run this:
>
Run this:
>
select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')
>
If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.
>
It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
When I ran my adhoc script below it generated only 45000 reads or 4
seconds but when I wrapped it into procedure it took about two minutes
or millions of reads. The parameters calling both adhoc and proc are
indeed the same. I'm pretty 99.9% sure that the proc does not recompile
because I don't mix up between DDL and DML, no temp tables or any thing
to cause proc to recompile. The big difference is adhoc used index scan
for 45% but proc used bookmark lookup for 75%. Why it's so difference
since they both returned the same results?
Please help...
Silaphet,
Below is my code,
DECLARE @.Modevarchar(10),
@.UserIDvarchar(36),
@.FromDatesmalldatetime,
@.ToDatesmalldatetime,
@.Insttinyint,
@.LocationIDsmallint,
@.BunitIDtinyint,
@.TeamIDint
SET @.Mode='TEAM'
SET @.UserID=''
SET @.FromDate='Dec 1 2006 12:00AM'
SET @.ToDate='Dec 31 2006 12:00AM'
SET @.Inst=28
SET @.LocationID=0
SET @.BunitID=2
SET @.TeamID=805
--IF @.Mode = 'TEAM'
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active,
TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0
END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1
ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND
(DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As
PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped RIGHT OUTER JOIN
dbo.MyTeamsRpt ON
vw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerId
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =
dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (MyTeamID = @.TeamID) AND ((StartDate
BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate
AND @.ToDate))
GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,
Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1
ELSE 0 END)>0 Or Active = 1
ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,
Active
END
IF @.Mode = 'RM'
BEGIN
IF @.BUnitId 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''
As TeamName, OffBUnitDesc As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0
END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1
ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND
(DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As
PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =
dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID)
AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate
BETWEEN @.FromDate AND @.ToDate)) AND OffBUnitID = @.BUnitID
GROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,
OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1
ELSE 0 END)>0 Or Active = 1
ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,
OfficerName, Active
END
--ELSE
IF @.BUnitId = 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''
As TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0
END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1
ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND
(DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As
PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =
dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID)
AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate
BETWEEN @.FromDate AND @.ToDate))
GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1
ELSE 0 END)>0 Or Active = 1
ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
END
ENDOn 4 Jan 2007 07:12:58 -0800, kmounkhaty@.yahoo.com wrote:
Quote:
Originally Posted by
>Hi Guru,
>
>When I ran my adhoc script below it generated only 45000 reads or 4
>seconds but when I wrapped it into procedure it took about two minutes
>or millions of reads. The parameters calling both adhoc and proc are
>indeed the same. I'm pretty 99.9% sure that the proc does not recompile
>because I don't mix up between DDL and DML, no temp tables or any thing
>to cause proc to recompile. The big difference is adhoc used index scan
>for 45% but proc used bookmark lookup for 75%. Why it's so difference
>since they both returned the same results?
>
>Please help...
Hi Silaphet,
You might suffer from parameter sniffing. Google for this term to find
out what it is and how you can try to deal with it.
You should also consider creating three procedures for the three
versions of the SELECT statement, and change your current stored proc
into a simple IF ELSE tree to call either one of them. This way, each
stored proc can get an execution plan that is otimized for its
parameters.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:
Quote:
Originally Posted by
When I ran my adhoc script below it generated only 45000 reads or 4
seconds but when I wrapped it into procedure it took about two minutes
or millions of reads. The parameters calling both adhoc and proc are
indeed the same. I'm pretty 99.9% sure that the proc does not recompile
because I don't mix up between DDL and DML, no temp tables or any thing
to cause proc to recompile. The big difference is adhoc used index scan
for 45% but proc used bookmark lookup for 75%. Why it's so difference
since they both returned the same results?
Run this:
select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')
If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.
It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||First off, I think I see a bug:
"(StartDate BETWEEN @.FromDate-365 AND @.ToDate-365)"
What happens on leap years?
It's impossible to tell what the performance of this query is going to
be like without knowing how all of the tables and views are structured.
I'd imagine that your biggest issue is going to be with the range of
dates that you're dealing with, but there's no way to know for sure
without knowing the table structure, view definitions, and statistical
distribution of data in the tables.
My advice: If it's not a proc that's being used a lot during the day,
try defining it with the RECOMPILE option (see docs) so that the
procedure gets re-optimized each time you run it.
-Dave
Erland Sommarskog wrote:
Quote:
Originally Posted by
kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:
Quote:
Originally Posted by
When I ran my adhoc script below it generated only 45000 reads or 4
seconds but when I wrapped it into procedure it took about two minutes
or millions of reads. The parameters calling both adhoc and proc are
indeed the same. I'm pretty 99.9% sure that the proc does not recompile
because I don't mix up between DDL and DML, no temp tables or any thing
to cause proc to recompile. The big difference is adhoc used index scan
for 45% but proc used bookmark lookup for 75%. Why it's so difference
since they both returned the same results?
>
Run this:
>
select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')
>
If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.
>
It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Ad-hoc reporting
We have a web application using SQL Server 2000, and we'd like to add ad-hoc
reporting. Here are the options we thought of so far:
1. Use SQL Server 2000 Reporting Services - but we're not sure how "ad-hoc"
we can get using SSRS. Has anyone tried?
2. Use a 3rd party reporting software package. Does anyone know of any?
3. Build a custom .NET ad-hoc app that is fine tuned for this application.
This is likely the most costly.
The goal is to have the users build and save report templates, and the be
able to select any combination of fields in the relational schema to include
in the reports, and select on any fields as well.
Does anyone have any other suggestions?
Thank you!!My suggestion is to investigate Report Builder which comes with RS 2005. It
is an ad hoc reporting solution that came out with RS 2005. This is
non-trivial to create on your own and from a cost perspective you will be
better off paying for the upgrade. Plus RS 2005 is much faster for Excel and
PDF output, has multi-select parameters, end user sorting and date picker.
Note that as long as you pay the license you can upgrade just RS and leave
your database at 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:uUTBa8qrHHA.5028@.TK2MSFTNGP05.phx.gbl...
> We have a web application using SQL Server 2000, and we'd like to add
> ad-hoc
> reporting. Here are the options we thought of so far:
> 1. Use SQL Server 2000 Reporting Services - but we're not sure how
> "ad-hoc"
> we can get using SSRS. Has anyone tried?
> 2. Use a 3rd party reporting software package. Does anyone know of any?
> 3. Build a custom .NET ad-hoc app that is fine tuned for this application.
> This is likely the most costly.
> The goal is to have the users build and save report templates, and the be
> able to select any combination of fields in the relational schema to
> include
> in the reports, and select on any fields as well.
> Does anyone have any other suggestions?
> Thank you!!
>
reporting. Here are the options we thought of so far:
1. Use SQL Server 2000 Reporting Services - but we're not sure how "ad-hoc"
we can get using SSRS. Has anyone tried?
2. Use a 3rd party reporting software package. Does anyone know of any?
3. Build a custom .NET ad-hoc app that is fine tuned for this application.
This is likely the most costly.
The goal is to have the users build and save report templates, and the be
able to select any combination of fields in the relational schema to include
in the reports, and select on any fields as well.
Does anyone have any other suggestions?
Thank you!!My suggestion is to investigate Report Builder which comes with RS 2005. It
is an ad hoc reporting solution that came out with RS 2005. This is
non-trivial to create on your own and from a cost perspective you will be
better off paying for the upgrade. Plus RS 2005 is much faster for Excel and
PDF output, has multi-select parameters, end user sorting and date picker.
Note that as long as you pay the license you can upgrade just RS and leave
your database at 2000.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:uUTBa8qrHHA.5028@.TK2MSFTNGP05.phx.gbl...
> We have a web application using SQL Server 2000, and we'd like to add
> ad-hoc
> reporting. Here are the options we thought of so far:
> 1. Use SQL Server 2000 Reporting Services - but we're not sure how
> "ad-hoc"
> we can get using SSRS. Has anyone tried?
> 2. Use a 3rd party reporting software package. Does anyone know of any?
> 3. Build a custom .NET ad-hoc app that is fine tuned for this application.
> This is likely the most costly.
> The goal is to have the users build and save report templates, and the be
> able to select any combination of fields in the relational schema to
> include
> in the reports, and select on any fields as well.
> Does anyone have any other suggestions?
> Thank you!!
>
Ad-hoc reporting memory consumption
Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working. We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)? Thanks. Alberto.
Hi Alberto,
We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.
|||Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.
HTH.
Alberto
Ad-hoc reporting memory consumption
Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working.
We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)?
Thanks.
Alberto.
Hi Alberto,
We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.
|||Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.
HTH.
Alberto
Ad-Hoc Reporting for users in RS 2005 with Visual Studio 2005
My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
Services 2005, and during those demos, the end user development environonment
was demoed. We are currently using SQL 2000 RS for a full set of Financial
reports for our 800+ users and would like to make available report writing to
our power users.
Are there any links that anybody knows of that explains setting up the
end-user VS2005 environment? Is there a place to start at in the SQL help
file that explains how users should be configured to use Visual Studio and
the Security implications?
Thanks in advance.RS comes with two ways to create reports. Report Builder and Report
Designer. The designer is essentially the same. Report Builder is a whole
new thing and is for power users. It does not use VS. I suggest reading up
on Report Builder. It is a smart client application (one click install).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
news:7C593508-C325-4B66-AE9F-84396A9AD123@.microsoft.com...
> My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
> Services 2005, and during those demos, the end user development
> environonment
> was demoed. We are currently using SQL 2000 RS for a full set of
> Financial
> reports for our 800+ users and would like to make available report writing
> to
> our power users.
> Are there any links that anybody knows of that explains setting up the
> end-user VS2005 environment? Is there a place to start at in the SQL help
> file that explains how users should be configured to use Visual Studio and
> the Security implications?
> Thanks in advance.|||Do you know of any good websites for Report Builder/ad hoc reporting?
--
K. Thomas
"Bruce L-C [MVP]" wrote:
> RS comes with two ways to create reports. Report Builder and Report
> Designer. The designer is essentially the same. Report Builder is a whole
> new thing and is for power users. It does not use VS. I suggest reading up
> on Report Builder. It is a smart client application (one click install).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
> news:7C593508-C325-4B66-AE9F-84396A9AD123@.microsoft.com...
> > My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
> > Services 2005, and during those demos, the end user development
> > environonment
> > was demoed. We are currently using SQL 2000 RS for a full set of
> > Financial
> > reports for our 800+ users and would like to make available report writing
> > to
> > our power users.
> >
> > Are there any links that anybody knows of that explains setting up the
> > end-user VS2005 environment? Is there a place to start at in the SQL help
> > file that explains how users should be configured to use Visual Studio and
> > the Security implications?
> >
> > Thanks in advance.
>
>
Services 2005, and during those demos, the end user development environonment
was demoed. We are currently using SQL 2000 RS for a full set of Financial
reports for our 800+ users and would like to make available report writing to
our power users.
Are there any links that anybody knows of that explains setting up the
end-user VS2005 environment? Is there a place to start at in the SQL help
file that explains how users should be configured to use Visual Studio and
the Security implications?
Thanks in advance.RS comes with two ways to create reports. Report Builder and Report
Designer. The designer is essentially the same. Report Builder is a whole
new thing and is for power users. It does not use VS. I suggest reading up
on Report Builder. It is a smart client application (one click install).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
news:7C593508-C325-4B66-AE9F-84396A9AD123@.microsoft.com...
> My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
> Services 2005, and during those demos, the end user development
> environonment
> was demoed. We are currently using SQL 2000 RS for a full set of
> Financial
> reports for our 800+ users and would like to make available report writing
> to
> our power users.
> Are there any links that anybody knows of that explains setting up the
> end-user VS2005 environment? Is there a place to start at in the SQL help
> file that explains how users should be configured to use Visual Studio and
> the Security implications?
> Thanks in advance.|||Do you know of any good websites for Report Builder/ad hoc reporting?
--
K. Thomas
"Bruce L-C [MVP]" wrote:
> RS comes with two ways to create reports. Report Builder and Report
> Designer. The designer is essentially the same. Report Builder is a whole
> new thing and is for power users. It does not use VS. I suggest reading up
> on Report Builder. It is a smart client application (one click install).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alec Hardy" <AlecHardy@.discussions.microsoft.com> wrote in message
> news:7C593508-C325-4B66-AE9F-84396A9AD123@.microsoft.com...
> > My Supervisor and I attended a couple of Microsoft demos of SQL Reporting
> > Services 2005, and during those demos, the end user development
> > environonment
> > was demoed. We are currently using SQL 2000 RS for a full set of
> > Financial
> > reports for our 800+ users and would like to make available report writing
> > to
> > our power users.
> >
> > Are there any links that anybody knows of that explains setting up the
> > end-user VS2005 environment? Is there a place to start at in the SQL help
> > file that explains how users should be configured to use Visual Studio and
> > the Security implications?
> >
> > Thanks in advance.
>
>
AdHoc reporting against SQL 2K w/meta data?
Okay... We have a SQL2K database that has about 500 tables or so. It is normalized to a reasonable level and enforces all relationships with PK/FKs, not triggers. Hence, for a database-minded person it is fairly easy to read (as easy as a 500+ table database can be!).
Our users need adhoc query capabilities. Our report writer is simply overwhelmed. He doesn't need to be spending time writing a report that is intended to be run once.
I expect the best alternative would be to use some sort of adhoc reporting tool that is based off meta data. We (the DBAs) could be responsible for maintaining the meta data and STILL have a manhour savings over developing all these reports.
Here's the catch... We are on a TIGHT budget (aerospace industry is still reeling a bit). Is anyone using a product or aware of a product that might be just the ticket for us? We have been investigating a product by LogiXML called LGX AdHoc (http://www.logixml.com/products/AdHoc/adhoc.htm). Looks promising. Anyone use or familar with it?Can you describe what it does (I didn't find their eval)? I am using ActiveReport from DataDynamics, but it does require some knowledge and time to spend designing reports.sql
Our users need adhoc query capabilities. Our report writer is simply overwhelmed. He doesn't need to be spending time writing a report that is intended to be run once.
I expect the best alternative would be to use some sort of adhoc reporting tool that is based off meta data. We (the DBAs) could be responsible for maintaining the meta data and STILL have a manhour savings over developing all these reports.
Here's the catch... We are on a TIGHT budget (aerospace industry is still reeling a bit). Is anyone using a product or aware of a product that might be just the ticket for us? We have been investigating a product by LogiXML called LGX AdHoc (http://www.logixml.com/products/AdHoc/adhoc.htm). Looks promising. Anyone use or familar with it?Can you describe what it does (I didn't find their eval)? I am using ActiveReport from DataDynamics, but it does require some knowledge and time to spend designing reports.sql
Tuesday, March 27, 2012
Ad-Hoc Reporting
Hi,
Can i know which version of Reporting services supports Ad-hoc reporting.
Reporting Services 2005. More information on the specific functionality for the various editions can be found here: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx
-- Robert
Ad-hoc reporting
Is the ad-hoc reporting available for SQL Server 2005 reporting
services a web based environment or is it an application that has to be
installed?
Thanks
Robertits a clickonce application.
this required that the user have the .Net Framework 2.0 installed locally.
then the reportbuilder application is downloaded automatically without any
install process.
but its not a webbased application. its a client/server application which
use webservices calls to the server to execute andrender queries.
"rhoenig" <rhoenig@.charter.net> wrote in message
news:1158956048.392781.78140@.m7g2000cwm.googlegroups.com...
> Is the ad-hoc reporting available for SQL Server 2005 reporting
> services a web based environment or is it an application that has to be
> installed?
> Thanks
> Robert
>
services a web based environment or is it an application that has to be
installed?
Thanks
Robertits a clickonce application.
this required that the user have the .Net Framework 2.0 installed locally.
then the reportbuilder application is downloaded automatically without any
install process.
but its not a webbased application. its a client/server application which
use webservices calls to the server to execute andrender queries.
"rhoenig" <rhoenig@.charter.net> wrote in message
news:1158956048.392781.78140@.m7g2000cwm.googlegroups.com...
> Is the ad-hoc reporting available for SQL Server 2005 reporting
> services a web based environment or is it an application that has to be
> installed?
> Thanks
> Robert
>
Adhoc Reporting
Couple of Questions regarding Reporting Services
1) Does SQL Server Reporting Services support adhoc reporting like dynamic
generation of report files rather than basing it on a template rdl
2) How long does it take to do a manual conversion of Crystal report with
say sub reports and say 30 formula fields and 4 paramters?
Thanks in Advance,
RdjeethRdjeeth,
1) Version 1.0 of RS doesn't support ad-hoc reporting features. However, the
next version of RS will come with a Report Builder which will support
comprehensive ad-hoc functionality. I've seen a short demo of the Report
Builder and I can say only good words about it. Meanwhile, you can evaluate
third-party products, such as the Cizer Quick Query.
2) Time will be in reverse proportion to your RS skills. As a side note,
instead of subreports you should gravitate towards using side-by-side
regions for performance reasons.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"RDJEETH" <RDJEETH@.discussions.microsoft.com> wrote in message
news:1243B393-52ED-4CD4-B61A-F6C129B65912@.microsoft.com...
> Couple of Questions regarding Reporting Services
> 1) Does SQL Server Reporting Services support adhoc reporting like dynamic
> generation of report files rather than basing it on a template rdl
> 2) How long does it take to do a manual conversion of Crystal report with
> say sub reports and say 30 formula fields and 4 paramters?
> Thanks in Advance,
> Rdjeeth
1) Does SQL Server Reporting Services support adhoc reporting like dynamic
generation of report files rather than basing it on a template rdl
2) How long does it take to do a manual conversion of Crystal report with
say sub reports and say 30 formula fields and 4 paramters?
Thanks in Advance,
RdjeethRdjeeth,
1) Version 1.0 of RS doesn't support ad-hoc reporting features. However, the
next version of RS will come with a Report Builder which will support
comprehensive ad-hoc functionality. I've seen a short demo of the Report
Builder and I can say only good words about it. Meanwhile, you can evaluate
third-party products, such as the Cizer Quick Query.
2) Time will be in reverse proportion to your RS skills. As a side note,
instead of subreports you should gravitate towards using side-by-side
regions for performance reasons.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"RDJEETH" <RDJEETH@.discussions.microsoft.com> wrote in message
news:1243B393-52ED-4CD4-B61A-F6C129B65912@.microsoft.com...
> Couple of Questions regarding Reporting Services
> 1) Does SQL Server Reporting Services support adhoc reporting like dynamic
> generation of report files rather than basing it on a template rdl
> 2) How long does it take to do a manual conversion of Crystal report with
> say sub reports and say 30 formula fields and 4 paramters?
> Thanks in Advance,
> Rdjeeth
Adhoc report with join tables on
Does anyone know if user has the capability to join two entities with
additional field which is not a predefined primary key or foreign key at the
design time? User want to run the report by join tables with certain fields
on the fly. I have not found a way to allow them to do that unless they have
Microsoft visual studio 2005 or SQL server business intelligence development
studio installed. Is SQL server reporting service a right candidate to serve
user's request? Does anyone know any other tool with this capability?
Thanks!1. You can do this if you define the sql statement dynamically and allow the
user parameter choices that provide the ability to specify the tables and
joins expressions. (for how to define the sql statement dynamically,
building it up as an expression, just treat the command as an expression
like you would the expression to display in a text box. IOW, start it with
an = sign and build up the string or invoke a code function)
However, I don't think it's really the best way.
2. Does the user have access and understanding to create views on the
server? It might be best to define the report based on a view, and have the
report basically remain ignorant of the joins and table information. The
report would have a single parameter -- the name of the view to invoke --
and would send that information to a stored procedure which would validate
that the view exists and has appropriate columns, and then run the view or
error-handle as described in choice #3 below.
3. If the user does not have that ability or access, I think I would build
this report to run a stored procedure, passing the parameter information as
described in #1, and have the stored procedure built and execute the sql. I
could do better validation in the stored procedure (for example, validate
that the tables and fields chosen by the user actually exist, assuming these
elements cannot be a dropdown in the report interface). I would have the
sproc send back a default data set of one record with every item showing
appropriate error text (or something) if I couldn't handle it another way.
Basically I think choice #2 is the right way to go here and if the user
doesn't have that ability and access I'm wondering whether that user should
be specifying this information at all...
>L<
"Daisy" <diyfan@.msnews.group.post> wrote in message
news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> Does anyone know if user has the capability to join two entities with
> additional field which is not a predefined primary key or foreign key at
> the
> design time? User want to run the report by join tables with certain
> fields
> on the fly. I have not found a way to allow them to do that unless they
> have
> Microsoft visual studio 2005 or SQL server business intelligence
> development
> studio installed. Is SQL server reporting service a right candidate to
> serve
> user's request? Does anyone know any other tool with this capability?
> Thanks!|||Thank you very much for the idea! I had built the model with report builder
to let user chose any fields from the tables given for the report. Now user
wanted to join the table not based on the primary key field that specified in
the design time. They wanted to join the table by certain non key fields at
the run time. I was stucked. I was only thinking use report builder model to
let user do this. And the report model could not change the key field (join
relation) on the fly. I have not found anything online or in MSDN library
telling me how to define the key at run time. As you suggested by using the
reprot designer and the sql stored procedure it is feasible to achieve the
task. Hope I did not misunderstood your post. If so please let me know.
Thanks!
"Lisa Slater Nicholls" wrote:
> 1. You can do this if you define the sql statement dynamically and allow the
> user parameter choices that provide the ability to specify the tables and
> joins expressions. (for how to define the sql statement dynamically,
> building it up as an expression, just treat the command as an expression
> like you would the expression to display in a text box. IOW, start it with
> an = sign and build up the string or invoke a code function)
> However, I don't think it's really the best way.
> 2. Does the user have access and understanding to create views on the
> server? It might be best to define the report based on a view, and have the
> report basically remain ignorant of the joins and table information. The
> report would have a single parameter -- the name of the view to invoke --
> and would send that information to a stored procedure which would validate
> that the view exists and has appropriate columns, and then run the view or
> error-handle as described in choice #3 below.
> 3. If the user does not have that ability or access, I think I would build
> this report to run a stored procedure, passing the parameter information as
> described in #1, and have the stored procedure built and execute the sql. I
> could do better validation in the stored procedure (for example, validate
> that the tables and fields chosen by the user actually exist, assuming these
> elements cannot be a dropdown in the report interface). I would have the
> sproc send back a default data set of one record with every item showing
> appropriate error text (or something) if I couldn't handle it another way.
> Basically I think choice #2 is the right way to go here and if the user
> doesn't have that ability and access I'm wondering whether that user should
> be specifying this information at all...
> >L<
>
> "Daisy" <diyfan@.msnews.group.post> wrote in message
> news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> > Does anyone know if user has the capability to join two entities with
> > additional field which is not a predefined primary key or foreign key at
> > the
> > design time? User want to run the report by join tables with certain
> > fields
> > on the fly. I have not found a way to allow them to do that unless they
> > have
> > Microsoft visual studio 2005 or SQL server business intelligence
> > development
> > studio installed. Is SQL server reporting service a right candidate to
> > serve
> > user's request? Does anyone know any other tool with this capability?
> >
> > Thanks!
>
additional field which is not a predefined primary key or foreign key at the
design time? User want to run the report by join tables with certain fields
on the fly. I have not found a way to allow them to do that unless they have
Microsoft visual studio 2005 or SQL server business intelligence development
studio installed. Is SQL server reporting service a right candidate to serve
user's request? Does anyone know any other tool with this capability?
Thanks!1. You can do this if you define the sql statement dynamically and allow the
user parameter choices that provide the ability to specify the tables and
joins expressions. (for how to define the sql statement dynamically,
building it up as an expression, just treat the command as an expression
like you would the expression to display in a text box. IOW, start it with
an = sign and build up the string or invoke a code function)
However, I don't think it's really the best way.
2. Does the user have access and understanding to create views on the
server? It might be best to define the report based on a view, and have the
report basically remain ignorant of the joins and table information. The
report would have a single parameter -- the name of the view to invoke --
and would send that information to a stored procedure which would validate
that the view exists and has appropriate columns, and then run the view or
error-handle as described in choice #3 below.
3. If the user does not have that ability or access, I think I would build
this report to run a stored procedure, passing the parameter information as
described in #1, and have the stored procedure built and execute the sql. I
could do better validation in the stored procedure (for example, validate
that the tables and fields chosen by the user actually exist, assuming these
elements cannot be a dropdown in the report interface). I would have the
sproc send back a default data set of one record with every item showing
appropriate error text (or something) if I couldn't handle it another way.
Basically I think choice #2 is the right way to go here and if the user
doesn't have that ability and access I'm wondering whether that user should
be specifying this information at all...
>L<
"Daisy" <diyfan@.msnews.group.post> wrote in message
news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> Does anyone know if user has the capability to join two entities with
> additional field which is not a predefined primary key or foreign key at
> the
> design time? User want to run the report by join tables with certain
> fields
> on the fly. I have not found a way to allow them to do that unless they
> have
> Microsoft visual studio 2005 or SQL server business intelligence
> development
> studio installed. Is SQL server reporting service a right candidate to
> serve
> user's request? Does anyone know any other tool with this capability?
> Thanks!|||Thank you very much for the idea! I had built the model with report builder
to let user chose any fields from the tables given for the report. Now user
wanted to join the table not based on the primary key field that specified in
the design time. They wanted to join the table by certain non key fields at
the run time. I was stucked. I was only thinking use report builder model to
let user do this. And the report model could not change the key field (join
relation) on the fly. I have not found anything online or in MSDN library
telling me how to define the key at run time. As you suggested by using the
reprot designer and the sql stored procedure it is feasible to achieve the
task. Hope I did not misunderstood your post. If so please let me know.
Thanks!
"Lisa Slater Nicholls" wrote:
> 1. You can do this if you define the sql statement dynamically and allow the
> user parameter choices that provide the ability to specify the tables and
> joins expressions. (for how to define the sql statement dynamically,
> building it up as an expression, just treat the command as an expression
> like you would the expression to display in a text box. IOW, start it with
> an = sign and build up the string or invoke a code function)
> However, I don't think it's really the best way.
> 2. Does the user have access and understanding to create views on the
> server? It might be best to define the report based on a view, and have the
> report basically remain ignorant of the joins and table information. The
> report would have a single parameter -- the name of the view to invoke --
> and would send that information to a stored procedure which would validate
> that the view exists and has appropriate columns, and then run the view or
> error-handle as described in choice #3 below.
> 3. If the user does not have that ability or access, I think I would build
> this report to run a stored procedure, passing the parameter information as
> described in #1, and have the stored procedure built and execute the sql. I
> could do better validation in the stored procedure (for example, validate
> that the tables and fields chosen by the user actually exist, assuming these
> elements cannot be a dropdown in the report interface). I would have the
> sproc send back a default data set of one record with every item showing
> appropriate error text (or something) if I couldn't handle it another way.
> Basically I think choice #2 is the right way to go here and if the user
> doesn't have that ability and access I'm wondering whether that user should
> be specifying this information at all...
> >L<
>
> "Daisy" <diyfan@.msnews.group.post> wrote in message
> news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> > Does anyone know if user has the capability to join two entities with
> > additional field which is not a predefined primary key or foreign key at
> > the
> > design time? User want to run the report by join tables with certain
> > fields
> > on the fly. I have not found a way to allow them to do that unless they
> > have
> > Microsoft visual studio 2005 or SQL server business intelligence
> > development
> > studio installed. Is SQL server reporting service a right candidate to
> > serve
> > user's request? Does anyone know any other tool with this capability?
> >
> > Thanks!
>
Ad-hoc Report Generation Based on the Input Query!
Hi all,
I want to generate the ad-hoc report using SQL Reporting Services 2000 or SQL Reporting Services 2005. But the Database Server will be SQL Server 2000. So, Can I send the query at run-time and then attach the data source to the actual report. In this case there will not be any columns etc. at design time with the value field mentioned in it. I know this is not the requirement for which this report tool is developed for. But still if this is possible through some other way round then pls. let me know. Actually we want one generic report which will satisfy all the tables, with diff. conditions mentioned in the select clause and where clause. So, is this possible with this reporting tool? Is there any way out where we want to come up with only one such report by manipulating the datasource of the report at run-time? Pls. let me know.
Thanks & Regards,
Dipali.
There may be a better way to do this in 2005 but the way I acheived dynamic columns in 2000 was to have a maximum number of columns (say 20) that take the value Column01, Column02....Column20 and make sure your data source returns the values under those names. Then you can also pass in the column names as a parameter. That's the only way I could come up with for producing a completely dynamic report.sqlAdhoc Query Tool for Sql Server
Hi !!
We are developing an application where we need a Query tool which allows customer to do Ad-Hoc or random query.. something similar to lets sayhttp://salebyowner.com/advancedSearch.php
We have few more options than this on which customer can do search.
I don't think dynamic query in C# code or something like that is going to help me. Am I right? Do we have to use any type of query tool or something for doing this?Hi love_luv,
Your example link shows a reasonably sophisticated query form written in PHP. But that doesn't mean PHP is a better database query tool than C# (or any other language for that matter).
PHP is just an application layer over top of some database. That database requires a completely different query language known as SQL (that has no direct link to PHP, C# or any other language).
The programmer in your example is simply using PHP to dyanmically costruct a SQL query statement (in text form) and passing it to their database to retrieve a result. C# (i.e., ASP.NET) can do that too.
In fact, with C# you can utilizeparameterized SQL queries for cleaner and safer SQL construction and execution.
The real trick behind sophisticated queries, of the type you're hoping to achieve, is a good familiarity with the SQL language (of the type native to your database brand,e.g., MS SQL Server uses a form of SQL known as Transact-SQL (a.k.a. TSql), and the knowledge to transform the user input from a webpage into said SQL.
Hope that helps.|||know, I understand that, its sophisticated query. I thought do we need to get some tool like Cognos etc etc for doing such query? More than parameterized query, I think its Dynamic or Adhoc query.|||You don't "need" a query tool at all, if you are familiar with SQL - regardless of how complex a query is. Visual Studio also query tools built in.
A special query tool or not, sophisticated or simple requirements - it all still comes down to a SQL statement to get your database results.
I found query tools useful once-upon-a-time for learning SQL, but I generally don't use them in my day-to-day programming now. You might find a tool useful to get an example of what your SQL text should look like, but once you have that then you use a language like C# to dynamically build the SQL text.
As an aside, "nearly" all database queries are dynamic (or adhoc) in nature. And using a parameterized query is just an optional format to make your dynamic (i.e., adhoc) queries easier to build (and safer to execute).
Hope that helps.|||So mean to say such a complex query is Dynamic Query written in C# code?
We really don't need any Commercial Query tool for Client end? We can use Web Forms to generate such query?
In couple of projects we implemented complex dynamic query, and that made us think if we need any tool or what? In our dynamic query, most of the time (99%) Select clause returns fix number of columns however, From Clause has base table and based on Where Conditions it may or may not join with other tables in data base.
In our version of dynamic query we builded WHERE clause in C#.
But how can we implement Join and WHERE using some proper structure.
Thanks|||
We are developing an application where we need a Query tool which allows customer to do Ad-Hoc or random query.. something similar to lets sayhttp://salebyowner.com/advancedSearch.php
We have few more options than this on which customer can do search.
I don't think dynamic query in C# code or something like that is going to help me. Am I right? Do we have to use any type of query tool or something for doing this?Hi love_luv,
Your example link shows a reasonably sophisticated query form written in PHP. But that doesn't mean PHP is a better database query tool than C# (or any other language for that matter).
PHP is just an application layer over top of some database. That database requires a completely different query language known as SQL (that has no direct link to PHP, C# or any other language).
The programmer in your example is simply using PHP to dyanmically costruct a SQL query statement (in text form) and passing it to their database to retrieve a result. C# (i.e., ASP.NET) can do that too.
In fact, with C# you can utilizeparameterized SQL queries for cleaner and safer SQL construction and execution.
The real trick behind sophisticated queries, of the type you're hoping to achieve, is a good familiarity with the SQL language (of the type native to your database brand,e.g., MS SQL Server uses a form of SQL known as Transact-SQL (a.k.a. TSql), and the knowledge to transform the user input from a webpage into said SQL.
Hope that helps.|||know, I understand that, its sophisticated query. I thought do we need to get some tool like Cognos etc etc for doing such query? More than parameterized query, I think its Dynamic or Adhoc query.|||You don't "need" a query tool at all, if you are familiar with SQL - regardless of how complex a query is. Visual Studio also query tools built in.
A special query tool or not, sophisticated or simple requirements - it all still comes down to a SQL statement to get your database results.
I found query tools useful once-upon-a-time for learning SQL, but I generally don't use them in my day-to-day programming now. You might find a tool useful to get an example of what your SQL text should look like, but once you have that then you use a language like C# to dynamically build the SQL text.
As an aside, "nearly" all database queries are dynamic (or adhoc) in nature. And using a parameterized query is just an optional format to make your dynamic (i.e., adhoc) queries easier to build (and safer to execute).
Hope that helps.|||So mean to say such a complex query is Dynamic Query written in C# code?
We really don't need any Commercial Query tool for Client end? We can use Web Forms to generate such query?
In couple of projects we implemented complex dynamic query, and that made us think if we need any tool or what? In our dynamic query, most of the time (99%) Select clause returns fix number of columns however, From Clause has base table and based on Where Conditions it may or may not join with other tables in data base.
In our version of dynamic query we builded WHERE clause in C#.
But how can we implement Join and WHERE using some proper structure.
Thanks|||
How one can handle dynamic SQL which has to perform JOIN with other tables.
Example I showed in my first post say has 2 - 3 tables:
PropertyListings and PropertyFeatures, PropertyLotFeatures
PropertyListings table gives me basic information I need to display.
PropertyFeatures and PropertyLotFeatures has information I need for advance search. I don't know which table to use and Join till user selects some features. based on Features selected for advance search I may have to Join:
1) PropertyListings with PropertyFeatures
2) PropertyListings with PropertyLotFeatures
3) PropertyListings with propertyFeatures and PropertyLotFeatures
I know once can right such query in C#, but is that how they do it or there is a better way? Am I missing something?
Let me give an Example :
How about when there is Join Present. How whould you build Dynamic SQL? Do you need Dynamic SQL or there is a Better Way?
For Eg.
Table1 = Customers
Table2 = Interests
Table3 = Customers_Interests
Table4 = Activities
Table5 = Customers_Activities
Table3 holds Customers Interests.
Table5 holds Customers Activities
Lets say Customers Table has Data: C1, C2, C3, C4
Interests Table has Data: I1, I2, I3, I4
Cusomters_Interests has Data:
C1, I1
C1, I3
C1, I4
C2, I2
C2, I4
C3, I3
Activities Table has Data: A1, A2, A3, A4
Customers_Activities has Data:
C1, A1
C1, A4
C2, A3
C4, A1
Now lets say on my webpage I represent Interests and Activities as CheckBoxes.
Case 1:
I select Interests I1, I3, I4 but No Activities Selected. In this case my query has to join Customers, Interests and Customers_Interests Table
So query will be: Give me list of Customers with Interests I1, I3, I4
Case 2:
If I select Activities A1, A3 and No Interests, query whas to join Customers, Activities and Customers_Activities Table
Case 2:
If I select Interests I1 and Activities A1, in this case Query has to join Customers, Interests, Customers_Interests, Activities and Customers_Activities tables(all tables)
How would you write Query?
Looking for help!!!!!!!!!!!!!!!!!!!!!!!!!!!
ad-hoc query printing
Based on the tutorial in
http://msdn2.microsoft.com/en-us/library/ms170667.aspx
We have a prototype that works with our custom data extension in Reporting
Services 2005 such that we can generate a report based on a given query text.
Right now in order to view a report, we always publish the generated report
to a report server instance. Our question is whether we can do it without
using report server.
For example, does ReportViewer control (the local processing mode) help?
Our goal is to create a temporary report on the fly given a query such that
the user can print or export the report, which means that this temporary
report will be discarded as soon as the user is done with it. We would like
to know if this is possible.
Thanks in advance!Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to whether it is
possible to create a temporary report for print only. If I have
misunderstood your concern, please feel free to point it out.
Unfortunately, Reporting Services do not have such funtionality to do so. I
wonder why do you need a Report on fly?
I have an interesting experience before that might give you some ideas. The
data source in my project was a remote linked server that need a lot of
time executing the complex query (cost about 5 minutes each time). When
perform the Email-Delivery Subscription to 10 or more different users, the
query will be executed 10 or more 5-minutes (around for 1 hours to complete
the email delivery subscription), which is obviously unacceptedable. Hence,
I will use DTS to transfer the data from linked server to local server
(cost 5 minutes) and then do the email-delivery subscription on local
tables, which is pretty cheap.
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Oops, sorry that I think I missed something
Here is an interesting article that shows VS 2005's new ReportViewer
control and its built-in Report Designer enable smart-client and Web page
designers to lay out, format, embed, export, and print interactive reports
without running a SQL Server Report Server.
Build Client-Side Reports Easily
http://msforums.ph/forums/91285/ShowPost.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your help. I found this walkthrough sample (similar to the one
you quoted below) from MSDN2 as well
http://msdn2.microsoft.com/en-us/library/ms251724.aspx
This is useful tutorial for us. However, I need to generate such report on
the fly ... that is, the dataset and datatable etc will be generated at
run-time. Is this even possible?
In addition, is there any tutorial on generating RDLC programmatically?
Thanks
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> Oops, sorry that I think I missed something
> Here is an interesting article that shows VS 2005's new ReportViewer
> control and its built-in Report Designer enable smart-client and Web page
> designers to lay out, format, embed, export, and print interactive reports
> without running a SQL Server Report Server.
> Build Client-Side Reports Easily
> http://msforums.ph/forums/91285/ShowPost.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Actually, I just found out about the LocalReport class from
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.localreport.aspx
Do you know of any example of how to use it? Say I already have a dataset
or a datatable or a collection of business objects (can't seem to find more
about what exactly a business object is).
Thanks in advance!
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks for your help. I found this walkthrough sample (similar to the one
> you quoted below) from MSDN2 as well
> http://msdn2.microsoft.com/en-us/library/ms251724.aspx
> This is useful tutorial for us. However, I need to generate such report on
> the fly ... that is, the dataset and datatable etc will be generated at
> run-time. Is this even possible?
> In addition, is there any tutorial on generating RDLC programmatically?
> Thanks
> Jenny
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > Oops, sorry that I think I missed something
> >
> > Here is an interesting article that shows VS 2005's new ReportViewer
> > control and its built-in Report Designer enable smart-client and Web page
> > designers to lay out, format, embed, export, and print interactive reports
> > without running a SQL Server Report Server.
> >
> > Build Client-Side Reports Easily
> > http://msforums.ph/forums/91285/ShowPost.aspx
> >
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >|||Hi,
To create a RDLC file, you may refer the article below
Creating Client Report Definition (.rdlc) Files
http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
Converting RDL and RDLC Files
http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
If you encounter any difficulties in the walkthrough, please let me know
the detail step you meet the error.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Michael for the links. I did take a look at both and did try some
walkthrough tutorials. They are great and now I understand more about
ReportViewer controls in general.
However, my problem is still not resolved. I guess I can rephrase it a bit
to let you understand it more:
(1) I need to generate the RDLC file programmatically because I don't know
the report fields at design time
(2) The same applies to the report data source, and I have a way to run a
query to get a System.Data.DataTable object which I can use as the report
data source, but again this depends on the search query, which I don't know
until run-time
I have attempted to modify the walkthrough from
http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
But instead of creating the rdlc file using the UI, I created the RDLC file
based on the tutorial from
http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
Again, I just renamed this file using the *.rdlc extension instead of *.rdl
and save it somewhere in my local system.
As I mentioned above, given a query, I can create a System.Data.DataTable on
the fly and modified the sample code such that I commented out the followings
from Form1.Designer.cs:
//this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
//this.reportViewer1.LocalReport.ReportEmbeddedResource ="BusinessObject.Report1.rdlc";
and added the following lines to Form1_Load() just before the line
this.reportViewer1.RefreshReport();
this.reportViewer1.LocalReport.DataSources.Add(new
ReportDataSource("Sales", datatable));
this.reportViewer1.LocalReport.ReportPath =@."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
When I ran the app, I got the following error:
"A data source instance has not been supplied for the data source 'DataSet1'"
First of all, am I in the wrong track? Can I actually programmatically
create an RDL file and simply renamed it as RDLC. What else do I need to do
programmatically (not through adding it to the project as indicated by the
walkthrough)?
Second of all, what am I missing? Is it possible to supply the DataTable
(report data source) programmatically?
Thanks a lot for reading my long post.
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> To create a RDLC file, you may refer the article below
> Creating Client Report Definition (.rdlc) Files
> http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> Converting RDL and RDLC Files
> http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> If you encounter any difficulties in the walkthrough, please let me know
> the detail step you meet the error.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Actually I've figured it out myself! :-)
When I added a new ReportDataSource object, I should have named it
'DataSet1' to associate that with my RDLC file.
Jenny
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks Michael for the links. I did take a look at both and did try some
> walkthrough tutorials. They are great and now I understand more about
> ReportViewer controls in general.
> However, my problem is still not resolved. I guess I can rephrase it a bit
> to let you understand it more:
> (1) I need to generate the RDLC file programmatically because I don't know
> the report fields at design time
> (2) The same applies to the report data source, and I have a way to run a
> query to get a System.Data.DataTable object which I can use as the report
> data source, but again this depends on the search query, which I don't know
> until run-time
> I have attempted to modify the walkthrough from
> http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
> But instead of creating the rdlc file using the UI, I created the RDLC file
> based on the tutorial from
> http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
> Again, I just renamed this file using the *.rdlc extension instead of *.rdl
> and save it somewhere in my local system.
> As I mentioned above, given a query, I can create a System.Data.DataTable on
> the fly and modified the sample code such that I commented out the followings
> from Form1.Designer.cs:
> //this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
> //this.reportViewer1.LocalReport.ReportEmbeddedResource => "BusinessObject.Report1.rdlc";
> and added the following lines to Form1_Load() just before the line
> this.reportViewer1.RefreshReport();
> this.reportViewer1.LocalReport.DataSources.Add(new
> ReportDataSource("Sales", datatable));
> this.reportViewer1.LocalReport.ReportPath => @."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
>
> When I ran the app, I got the following error:
> "A data source instance has not been supplied for the data source 'DataSet1'"
>
> First of all, am I in the wrong track? Can I actually programmatically
> create an RDL file and simply renamed it as RDLC. What else do I need to do
> programmatically (not through adding it to the project as indicated by the
> walkthrough)?
> Second of all, what am I missing? Is it possible to supply the DataTable
> (report data source) programmatically?
>
> Thanks a lot for reading my long post.
> Jenny
>
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > To create a RDLC file, you may refer the article below
> >
> > Creating Client Report Definition (.rdlc) Files
> > http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> >
> > Converting RDL and RDLC Files
> > http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> >
> > If you encounter any difficulties in the walkthrough, please let me know
> > the detail step you meet the error.
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hi Jenny,
Thanks for your prompt update and it's great to hear you have resovled it
now!! And thanks so much for sharing this information with us.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
http://msdn2.microsoft.com/en-us/library/ms170667.aspx
We have a prototype that works with our custom data extension in Reporting
Services 2005 such that we can generate a report based on a given query text.
Right now in order to view a report, we always publish the generated report
to a report server instance. Our question is whether we can do it without
using report server.
For example, does ReportViewer control (the local processing mode) help?
Our goal is to create a temporary report on the fly given a query such that
the user can print or export the report, which means that this temporary
report will be discarded as soon as the user is done with it. We would like
to know if this is possible.
Thanks in advance!Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to whether it is
possible to create a temporary report for print only. If I have
misunderstood your concern, please feel free to point it out.
Unfortunately, Reporting Services do not have such funtionality to do so. I
wonder why do you need a Report on fly?
I have an interesting experience before that might give you some ideas. The
data source in my project was a remote linked server that need a lot of
time executing the complex query (cost about 5 minutes each time). When
perform the Email-Delivery Subscription to 10 or more different users, the
query will be executed 10 or more 5-minutes (around for 1 hours to complete
the email delivery subscription), which is obviously unacceptedable. Hence,
I will use DTS to transfer the data from linked server to local server
(cost 5 minutes) and then do the email-delivery subscription on local
tables, which is pretty cheap.
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Oops, sorry that I think I missed something
Here is an interesting article that shows VS 2005's new ReportViewer
control and its built-in Report Designer enable smart-client and Web page
designers to lay out, format, embed, export, and print interactive reports
without running a SQL Server Report Server.
Build Client-Side Reports Easily
http://msforums.ph/forums/91285/ShowPost.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your help. I found this walkthrough sample (similar to the one
you quoted below) from MSDN2 as well
http://msdn2.microsoft.com/en-us/library/ms251724.aspx
This is useful tutorial for us. However, I need to generate such report on
the fly ... that is, the dataset and datatable etc will be generated at
run-time. Is this even possible?
In addition, is there any tutorial on generating RDLC programmatically?
Thanks
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> Oops, sorry that I think I missed something
> Here is an interesting article that shows VS 2005's new ReportViewer
> control and its built-in Report Designer enable smart-client and Web page
> designers to lay out, format, embed, export, and print interactive reports
> without running a SQL Server Report Server.
> Build Client-Side Reports Easily
> http://msforums.ph/forums/91285/ShowPost.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Actually, I just found out about the LocalReport class from
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.localreport.aspx
Do you know of any example of how to use it? Say I already have a dataset
or a datatable or a collection of business objects (can't seem to find more
about what exactly a business object is).
Thanks in advance!
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks for your help. I found this walkthrough sample (similar to the one
> you quoted below) from MSDN2 as well
> http://msdn2.microsoft.com/en-us/library/ms251724.aspx
> This is useful tutorial for us. However, I need to generate such report on
> the fly ... that is, the dataset and datatable etc will be generated at
> run-time. Is this even possible?
> In addition, is there any tutorial on generating RDLC programmatically?
> Thanks
> Jenny
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > Oops, sorry that I think I missed something
> >
> > Here is an interesting article that shows VS 2005's new ReportViewer
> > control and its built-in Report Designer enable smart-client and Web page
> > designers to lay out, format, embed, export, and print interactive reports
> > without running a SQL Server Report Server.
> >
> > Build Client-Side Reports Easily
> > http://msforums.ph/forums/91285/ShowPost.aspx
> >
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >|||Hi,
To create a RDLC file, you may refer the article below
Creating Client Report Definition (.rdlc) Files
http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
Converting RDL and RDLC Files
http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
If you encounter any difficulties in the walkthrough, please let me know
the detail step you meet the error.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Michael for the links. I did take a look at both and did try some
walkthrough tutorials. They are great and now I understand more about
ReportViewer controls in general.
However, my problem is still not resolved. I guess I can rephrase it a bit
to let you understand it more:
(1) I need to generate the RDLC file programmatically because I don't know
the report fields at design time
(2) The same applies to the report data source, and I have a way to run a
query to get a System.Data.DataTable object which I can use as the report
data source, but again this depends on the search query, which I don't know
until run-time
I have attempted to modify the walkthrough from
http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
But instead of creating the rdlc file using the UI, I created the RDLC file
based on the tutorial from
http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
Again, I just renamed this file using the *.rdlc extension instead of *.rdl
and save it somewhere in my local system.
As I mentioned above, given a query, I can create a System.Data.DataTable on
the fly and modified the sample code such that I commented out the followings
from Form1.Designer.cs:
//this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
//this.reportViewer1.LocalReport.ReportEmbeddedResource ="BusinessObject.Report1.rdlc";
and added the following lines to Form1_Load() just before the line
this.reportViewer1.RefreshReport();
this.reportViewer1.LocalReport.DataSources.Add(new
ReportDataSource("Sales", datatable));
this.reportViewer1.LocalReport.ReportPath =@."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
When I ran the app, I got the following error:
"A data source instance has not been supplied for the data source 'DataSet1'"
First of all, am I in the wrong track? Can I actually programmatically
create an RDL file and simply renamed it as RDLC. What else do I need to do
programmatically (not through adding it to the project as indicated by the
walkthrough)?
Second of all, what am I missing? Is it possible to supply the DataTable
(report data source) programmatically?
Thanks a lot for reading my long post.
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> To create a RDLC file, you may refer the article below
> Creating Client Report Definition (.rdlc) Files
> http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> Converting RDL and RDLC Files
> http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> If you encounter any difficulties in the walkthrough, please let me know
> the detail step you meet the error.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Actually I've figured it out myself! :-)
When I added a new ReportDataSource object, I should have named it
'DataSet1' to associate that with my RDLC file.
Jenny
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks Michael for the links. I did take a look at both and did try some
> walkthrough tutorials. They are great and now I understand more about
> ReportViewer controls in general.
> However, my problem is still not resolved. I guess I can rephrase it a bit
> to let you understand it more:
> (1) I need to generate the RDLC file programmatically because I don't know
> the report fields at design time
> (2) The same applies to the report data source, and I have a way to run a
> query to get a System.Data.DataTable object which I can use as the report
> data source, but again this depends on the search query, which I don't know
> until run-time
> I have attempted to modify the walkthrough from
> http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
> But instead of creating the rdlc file using the UI, I created the RDLC file
> based on the tutorial from
> http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
> Again, I just renamed this file using the *.rdlc extension instead of *.rdl
> and save it somewhere in my local system.
> As I mentioned above, given a query, I can create a System.Data.DataTable on
> the fly and modified the sample code such that I commented out the followings
> from Form1.Designer.cs:
> //this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
> //this.reportViewer1.LocalReport.ReportEmbeddedResource => "BusinessObject.Report1.rdlc";
> and added the following lines to Form1_Load() just before the line
> this.reportViewer1.RefreshReport();
> this.reportViewer1.LocalReport.DataSources.Add(new
> ReportDataSource("Sales", datatable));
> this.reportViewer1.LocalReport.ReportPath => @."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
>
> When I ran the app, I got the following error:
> "A data source instance has not been supplied for the data source 'DataSet1'"
>
> First of all, am I in the wrong track? Can I actually programmatically
> create an RDL file and simply renamed it as RDLC. What else do I need to do
> programmatically (not through adding it to the project as indicated by the
> walkthrough)?
> Second of all, what am I missing? Is it possible to supply the DataTable
> (report data source) programmatically?
>
> Thanks a lot for reading my long post.
> Jenny
>
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > To create a RDLC file, you may refer the article below
> >
> > Creating Client Report Definition (.rdlc) Files
> > http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> >
> > Converting RDL and RDLC Files
> > http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> >
> > If you encounter any difficulties in the walkthrough, please let me know
> > the detail step you meet the error.
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hi Jenny,
Thanks for your prompt update and it's great to hear you have resovled it
now!! And thanks so much for sharing this information with us.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)