Wednesday, March 21, 2012
Dynamicaly add charts to report
per chart. The number of processes is not known at the design time and is
defined by the result of the underlying report query, so I do not know how
many chart objects to place on the report at design time. Is it possible to
add charts dynamically to the report during the run time?
Any hint would be greatly appreciated.
Thanks.Maybe this will help!
I create a group (detail) line and I add a chart to this group by defining a
chart in one of the text boxes...
hen do the normal things with a chart - but the chart is automatically
GROUPED for you based upon the results of your query (in line or T-SQL)
"Simon Gold" wrote:
> I need display some processes (lines in a chart) in the report. One process
> per chart. The number of processes is not known at the design time and is
> defined by the result of the underlying report query, so I do not know how
> many chart objects to place on the report at design time. Is it possible to
> add charts dynamically to the report during the run time?
> Any hint would be greatly appreciated.
> Thanks.
>
Sunday, March 11, 2012
Dynamically define the height of a chart
I have a "Stacked Bar" chart and it has a certain ammount of lines depending on its data source. Sometimes if there are too many lines, only every other label shows up for that line on the left hand side (See image).
What I need:
Basically I need a solution that fixes my current situation. My first thouts were to dynamically size the height of the chart, but I haven't had much luck doing that. Also, I have tried to find properties for the char item that might let it grow.
If anyone has any suggestions, or know where I can get more information on this, I would appreciate it very much. Thanks in advance.
It looks like you have just a list of items to show in the chart.
Here is one approach:
* Add a list to the report, put the chart inside the list
* Add a (detail) group to the list with the following grouping expression:
=Int((RowNumber(Nothing)-1)/15)
This should result in the desired grouping of 15 items per (repeating) list instance.
Another approach is to define multiple charts of different sizes and use the Visibility.Hidden property on the chart to dynamically hide all charts but one. Note: you can use =CountRows("DatasetName") to determine the number of rows in a particular dataset and hide one chart e.g. if the total number of dataset rows is greater than 20: Visibility.Hidden property setting: =CountRows("DatasetName") > 20
-- Robert
|||Thanks for the reply. This seems like a good solution, however other problems were present that I didn't notice before. If you look at the x-axis of my graph it too does not display the labels correctly. My boss would like me to now explore using image creation on the fly to possibly fix this problem. Do you think that using a list would fix my problem with the x-axis?|||You didn't explain why you think the x-axis labels are displayed incorrectly - but if the problem is that lots of data is shown in one chart and the x-axis looks crowded, splitting the underlying dataset into multiple charts will help.
-- Robert
Friday, February 24, 2012
dynamic text
In Reporting Services, is there a chance to make the text dynamic?
For example, sometimes I want it to show 10 lines and sometimes 20.
Is it possible?Can you be a little more specific? Nearly everything is expression based and hence can be made dynamic.|||I have a report about SaleContracts.
In the SaleContract Text area, the customer (company) definition will be shown and for example one company's definition is 20 character length and another is 50 character length.I use this definition more than once in the contract text.So the text changes from contract to contract.And so this text may be 20 line or 30 line length.
If I keep the textbox's size small, some of the text couldn't be shown when the company definition string contains 30 characters(for example).And if I keep the textbox's size large, there is a blank space and this is not a good view.Therefore I want the text to be dynamic.
Thanks in advance!|||Text boxes have 2 properties CanGrow and CanShrink that can be used to automatically size the textbox based on it's content. Bear in mind that these will only grow or shrink vertically i.e. height, the width is fixed.
Sunday, February 19, 2012
Dynamic SQL stops responding with no error message
SP (sp_BuildPayments) which has among several lines of code one specific lin
e
that cancel the process with no error message. The line is a dynamic SQL tha
t
follows below:
execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
' from [PRM].[dbo].[' + @.TableName + ']' +
' where datepart(month, Event_Date) = ' + @.Event_Month +
' and datepart(year, Event_Date) = ' + @.Event_Year )
I've tried a lot of things with no sucess to find out the problem. Acctually
the code stop running in this line but @.@.error is 0. Does anyone know how to
solve this?
Thanks in advance.
Cheers.
JorgeJorge Luis Ribeiro wrote:
> On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls
> another SP (sp_BuildPayments) which has among several lines of code
> one specific line that cancel the process with no error message. The
> line is a dynamic SQL that follows below:
> execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
> ' set ' + @.Account_Field_Name + ' = ' +
> @.Account_Value + ' from [PRM].[dbo].[' + @.TableName +
> ']' + ' where datepart(month, Event_Date) = ' +
> @.Event_Month + ' and datepart(year, Event_Date) = ' +
> @.Event_Year )
> I've tried a lot of things with no sucess to find out the problem.
> Acctually the code stop running in this line but @.@.error is 0. Does
> anyone know how to solve this?
> Thanks in advance.
> Cheers.
> Jorge
Do the users have UPDATE rights on the underlying table? The update is
going to require a table scan every time because you do not have and
SARGable clauses in the query. It's possible you are running into a
locking/blocking issue. You should be able to see the error value
outside the EXEC, unless there is a trigger on the table which is
resetting the value. Do you have a trigger on the table?
David Gugick
Imceda Software
www.imceda.com|||Hi
Print ('update [PRM].[dbo].[' + @.TableName + '] ' +
' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
' from [PRM].[dbo].[' + @.TableName + ']' +
' where datepart(month, Event_Date) = ' + @.Event_Month +
' and datepart(year, Event_Date) = ' + @.Event_Year )
Run the output in QA and see if it does the job.
"Jorge Luis Ribeiro" <Jorge Luis Ribeiro@.discussions.microsoft.com> wrote in
message news:B259EFD8-FECF-4652-844B-1AF42FE3F697@.microsoft.com...
> On SQL Server 2000, I have a SP (sp_CloseBusinessAcount) that calls
another
> SP (sp_BuildPayments) which has among several lines of code one specific
line
> that cancel the process with no error message. The line is a dynamic SQL
that
> follows below:
> execute ('update [PRM].[dbo].[' + @.TableName + '] ' +
> ' set ' + @.Account_Field_Name + ' = ' + @.Account_Value +
> ' from [PRM].[dbo].[' + @.TableName + ']' +
> ' where datepart(month, Event_Date) = ' + @.Event_Month +
> ' and datepart(year, Event_Date) = ' + @.Event_Year )
> I've tried a lot of things with no sucess to find out the problem.
Acctually
> the code stop running in this line but @.@.error is 0. Does anyone know how
to
> solve this?
> Thanks in advance.
> Cheers.
> Jorge|||Actually there is a trigger that calls the sp_CloseBusinessAccount that call
s
the other one.
I've tried to check the @.@.error in both procedures with no sucess.
As you mentioned the lock issue, this table @.TableName is a table that has
been created by the same procedure I intend to update.
"David Gugick" wrote:
> Jorge Luis Ribeiro wrote:
> Do the users have UPDATE rights on the underlying table? The update is
> going to require a table scan every time because you do not have and
> SARGable clauses in the query. It's possible you are running into a
> locking/blocking issue. You should be able to see the error value
> outside the EXEC, unless there is a trigger on the table which is
> resetting the value. Do you have a trigger on the table?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
Wednesday, February 15, 2012
Dynamic SQL in procedure compiles but does not run
We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.
Well, it is parsing all statements regardless of the instructions from the parameters. Anyone know of any tricks to get around this?I'm goint to try removing the IF statements for the lines of coverage and just do something like:
case @.lob when 'WC' then 'WC' else 'GL' end + 'CCLM1'
I suspect this will work just fine.|||I found the cause. I had hard coded the wrong line of coverage in an area of the procedure so that explains why it was looking for the opposite tables! I'm still going to do the case statement above. Since I've got it in one proc I should cut down what was four queries to two!|||...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"|||[QUOTE=DBA-ONE]We have standard report for each line. Since the reports call a stored proceudure I've written it with dynamic SQL in an attept to only have one stored procedure. The idea is that I will pass the procedure the line of coverage and the corrects set of statements will run. I made them dynamic SQL because I figured it would puke when trying to compile and hoped it would not check if a table exist until runtime due to the iff statement.
QUOTE]
But you ignore the fact that the optimizer will recompile your Dynamic SQL
** every **
time it executes, so you are trading writing four efficient procs running well for one extremely inefficient proc that runs poorly.:(|||That is actually fine. Because these reports are not run every second of the day so the overhead on the server is much more favorable then having to change several procedures and statements. I'd much rather update one procedure, one file and have to put that one file in a single location. See, we support Oracle and SQL Server so for each procedure I need one for comp, one liability, and one each of those for SQL and Oracle. Now do you see why I'm willing to give up a little on the server end to make the maintinence easier? When we roll something out it can be an enourmous pain having to put all these files in different places.|||By the way, I test the old version to the new. While the optimizer may have more work to do there was no difference to speak of in the time to execute. Not bad for my sloppy procedure huh?|||Recompilation expense is minimal and only becomes an issue when a statement might be executed hundreds of times each minute.|||Clients may execute these once a day. Usually at month end.|||...and the mess that you leave behind I usually call "why don't you just shoot me, right now?"
At least it doesn't happen too often!