Friday, March 30, 2012
Modify Data Source Or CataLog On Data Source
10 different sources of data. The data structures are identical at all 10
sources, but they contain different data. You could think of it as each
source of data represents a customer and I want to use a shared set of
reports (maintained in one and only one place) to present data against these
10 different sources. I would like to be able to either modify a reports
data source or the catalog contained within a given data source at report
execution time. I can store in a session variable the name of the catalog or
data source for which the report should be executed against, but I need a
method to modify this information prior to execution...but modify it only in
memory on the web server so as not to physically save it back to the report
server. Physically saving it back to the report server would create data
contention issues and should be unecessary. I've been unable to find a
method that allows this, however. It seems all the web service methods I've
found in this area involve actually saving the new datasource information on
the server as opposed to in memory at render time. Any ideas?Two techniques that people use is to pass the database you want to use as a
parameter and pass that through to a stored procedure that then does an exec
select @.SQL = 'select * from ' + @.DB + '.dbo.sometable'
exec (@.SQL)
You can also do something similar in the query definition by using the
generic query designer and setting it to an expression.
= "select * from " & @.DB & ".dbo.sometable"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"OLAPMonkey" <OLAPMonkey@.discussions.microsoft.com> wrote in message
news:104E7130-A005-4F0E-8A24-6F70261870C6@.microsoft.com...
> I have a set of 20 reports. I want to be able to point these 20 reports
at
> 10 different sources of data. The data structures are identical at all 10
> sources, but they contain different data. You could think of it as each
> source of data represents a customer and I want to use a shared set of
> reports (maintained in one and only one place) to present data against
these
> 10 different sources. I would like to be able to either modify a reports
> data source or the catalog contained within a given data source at report
> execution time. I can store in a session variable the name of the catalog
or
> data source for which the report should be executed against, but I need a
> method to modify this information prior to execution...but modify it only
in
> memory on the web server so as not to physically save it back to the
report
> server. Physically saving it back to the report server would create data
> contention issues and should be unecessary. I've been unable to find a
> method that allows this, however. It seems all the web service methods
I've
> found in this area involve actually saving the new datasource information
on
> the server as opposed to in memory at render time. Any ideas?|||Unfortunately, I'm reporting against Analysis Services so I cannot adjust the
database on the fly via MDX in a similar fashion to what you've outlined here
with SQL. Any other ideas?
"Bruce L-C [MVP]" wrote:
> Two techniques that people use is to pass the database you want to use as a
> parameter and pass that through to a stored procedure that then does an exec
> select @.SQL = 'select * from ' + @.DB + '.dbo.sometable'
> exec (@.SQL)
> You can also do something similar in the query definition by using the
> generic query designer and setting it to an expression.
> = "select * from " & @.DB & ".dbo.sometable"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "OLAPMonkey" <OLAPMonkey@.discussions.microsoft.com> wrote in message
> news:104E7130-A005-4F0E-8A24-6F70261870C6@.microsoft.com...
> > I have a set of 20 reports. I want to be able to point these 20 reports
> at
> > 10 different sources of data. The data structures are identical at all 10
> > sources, but they contain different data. You could think of it as each
> > source of data represents a customer and I want to use a shared set of
> > reports (maintained in one and only one place) to present data against
> these
> > 10 different sources. I would like to be able to either modify a reports
> > data source or the catalog contained within a given data source at report
> > execution time. I can store in a session variable the name of the catalog
> or
> > data source for which the report should be executed against, but I need a
> > method to modify this information prior to execution...but modify it only
> in
> > memory on the web server so as not to physically save it back to the
> report
> > server. Physically saving it back to the report server would create data
> > contention issues and should be unecessary. I've been unable to find a
> > method that allows this, however. It seems all the web service methods
> I've
> > found in this area involve actually saving the new datasource information
> on
> > the server as opposed to in memory at render time. Any ideas?
>
>|||Nope, sorry.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"OLAPMonkey" <OLAPMonkey@.discussions.microsoft.com> wrote in message
news:AFF50CB4-124F-4644-81DB-1E223D90E17A@.microsoft.com...
> Unfortunately, I'm reporting against Analysis Services so I cannot adjust
the
> database on the fly via MDX in a similar fashion to what you've outlined
here
> with SQL. Any other ideas?
>
> "Bruce L-C [MVP]" wrote:
> > Two techniques that people use is to pass the database you want to use
as a
> > parameter and pass that through to a stored procedure that then does an
exec
> >
> > select @.SQL = 'select * from ' + @.DB + '.dbo.sometable'
> > exec (@.SQL)
> >
> > You can also do something similar in the query definition by using the
> > generic query designer and setting it to an expression.
> > = "select * from " & @.DB & ".dbo.sometable"
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "OLAPMonkey" <OLAPMonkey@.discussions.microsoft.com> wrote in message
> > news:104E7130-A005-4F0E-8A24-6F70261870C6@.microsoft.com...
> > > I have a set of 20 reports. I want to be able to point these 20
reports
> > at
> > > 10 different sources of data. The data structures are identical at
all 10
> > > sources, but they contain different data. You could think of it as
each
> > > source of data represents a customer and I want to use a shared set of
> > > reports (maintained in one and only one place) to present data against
> > these
> > > 10 different sources. I would like to be able to either modify a
reports
> > > data source or the catalog contained within a given data source at
report
> > > execution time. I can store in a session variable the name of the
catalog
> > or
> > > data source for which the report should be executed against, but I
need a
> > > method to modify this information prior to execution...but modify it
only
> > in
> > > memory on the web server so as not to physically save it back to the
> > report
> > > server. Physically saving it back to the report server would create
data
> > > contention issues and should be unecessary. I've been unable to find
a
> > > method that allows this, however. It seems all the web service
methods
> > I've
> > > found in this area involve actually saving the new datasource
information
> > on
> > > the server as opposed to in memory at render time. Any ideas?
> >
> >
> >sql
Wednesday, March 28, 2012
Model Question
on it be affected? Or, once created, are these reports independent?
ThanksYes,
--
Regards,
Raja
â'Smile is a curve that sets everything straight"
"TomT" wrote:
> Does anyone know if a change is made to a model, will reports created based
> on it be affected? Or, once created, are these reports independent?
> Thanks|||Raja, thanks for your reply. Do you mean yes, they will be affected, or yes
they are independent?
"Raja" wrote:
> Yes,
> --
> Regards,
> Raja
> â'Smile is a curve that sets everything straight"
>
> "TomT" wrote:
> > Does anyone know if a change is made to a model, will reports created based
> > on it be affected? Or, once created, are these reports independent?
> >
> > Thanks|||Hello Tom,
I think Raja means that the report will be affected.
You could refer the article:
Creating a Report Model Project
http://msdn2.microsoft.com/en-us/ms156285.aspx
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Ok, thanks very much.
"Wei Lu [MSFT]" wrote:
> Hello Tom,
> I think Raja means that the report will be affected.
> You could refer the article:
> Creating a Report Model Project
> http://msdn2.microsoft.com/en-us/ms156285.aspx
> Thanks!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
Monday, March 19, 2012
Missing: Have you seen me? Reportviewer toolbar button
Hi all,
I've implemented some reports with the asp.net2 reportviewer component. But it seems that the Print button has gone missing... The ShowPrintButton property is set to true on the control, but it's nowhere to be found on the component when rendered... and it's not shown in design mode too...
How do I enable it, I've put all the dll's I would need in my bin folder of my project, but doesn't the print feature need the active x component to work too? Where would I find that control, and how do I set it up?
Thanks
See these links, hope they're useful:http://forums.asp.net/thread/1058375.aspx
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=22277&SiteID=1
Missing Values
I have been writing some queries and reports using SQL (with Crystal and ASP.NET), and have a problem. Something I often need to do is find the number of records for each month, so there is a date field and a currency field, and I need to sum all the currency values for each month.
Firstly, to get a group for each month of each year I am using ...GROUP BY Month(DateField), Year(DateField)
is this correct or is there a better way?
The more problematic thing is, where there are no records for certain month, of course this month does not show up in the query results. This is a problem when displaying results in a table/chart. Is there any way to force each month to appear (with a 0 in the SUMed field)?
Any advice would be most, most welcome,
Thanksyou could define a table say tblmonth and define a join, which will allways retrieve the a month even if there are no entries for that month|||I'll have a go at that, thanks
any idea what join that would be?|||I think it would be a left join (Outer joins allow select all from this side and show any matching on the other).
Give it a go and if what you get is silly switch to right join as there is a small chance I got it wrong.|||you'll need a table of months, or use an integers table to generate them
see Selecting all months even if they're missing (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid526288_tax285649,00.html) (site registration may be required, but it's free)|||Thanks for your help on this, I seem to have it working now.
I have a similar problem with a report I am writing in crystal reports. I am trying to create a group calendar from Outlook using a crosstab, but not all dates show up (if noone has an appointment booked on that day).
What I need is to do is get a datasource with a list of days which I can do a join on to force all dates to appear. The problem is that the dates from Outlook are just dates but other applications such as access, excel use date/time fields and so the join does not work! Does anyone know where I can get a data source of just dates to use in the join?|||a data source of just dates? yeah, you can generate it from an integers table
Missing Value Grouping
I need to build a results grid that reports totals or zero for an attribute
SELECT ClaimType, VendorNumber,
COUNT (ClaimNumber) AS ClaimCount
FROM ClaimHeader
GROUP BY VendorNumber, ClaimType
ORDER BY VendorNumber ASC;
THe underlying data has two claim types (UB, and HCFA). Some vendor numbers have only UB claims or only HCFA claims. I want my totals to show a zero for those vendor numbers. Example
VendorNumber ClaimType ClaimCount
234 UB 21
234 HCFA 12
235 UB 0
235 HCFA 15
My current group by doesn't give me the 3rd row in my example. How do I change my query to add those rows that have no claims as a zero. Put another way, every vendorNumber has to report a HCFA total and a UB total, even if they're zero.
Thanks.This is flat-out "brute force" to get the job done using only what you've given us... There is almost certainly a better way.SELECT v.VendorNumber, c.ClaimType, (SELECT Coalesce(Count(*), 0)
FROM ClaimHeader AS ch
WHERE ch.VendorNumber = v.VendorNumber
AND ch.ClaimType = c.ClaimType) AS ClaimCount
FROM (SELECT DISTINCT VendorNumber
FROM ClaimHeader) AS v
CROSS JOIN (SELECT DISTINCT ClaimType
FROM ClaimHeader) AS c-PatP|||Is something else I could provide to help with my question? This might b closer but i'm still missing the zeros for providers with not HCFA or UB claims.
SELECT t.PeriodID, p.ProviderName, p.AltProviderID_1,
t.FormTypeID,
ClaimCount = CASE WHEN SUM(ClaimCount) IS NULL THEN 0 ELSE SUM(ClaimCount) END
FROM dvTATClaim t
INNER JOIN dimProvider p
ON t.ProviderID = p.ProviderID|||Sure...provide the DDL of the table
CREATE TABLE myTable...
Some Sample Data
INSERT INTO myTable(
SELECT 'data' UNION ALL
SELECT 'data' UNION ALL
ect
And what the results should look like...
You could probably use a stop watch to time how long it takes after you post that for your answer...|||Are there other tables that have:
1. Every possible VendorNumber
2. Every possible ClaimType
Also, did the previous query I provided give you the results that you want?
-PatP
Monday, March 12, 2012
Missing toggle images in matrix report
I have two Matrix reports located in seperate folders. The reports work fine, the toggle(+/-) images show up correctly. But when I add role security to the folders to limit access, the toggle images no longer show up, just the missing image(Red X). Any ideas on what I may be missing?
Thanks, Burl
Ok, seems the problem is with the <authorization> element in the web.config file. Changed it form <deny users="*"> to <deny users="?"> and my toggle images started working. Seems to be a security problem for the builtin resource file on the matrix report.
Friday, March 9, 2012
Missing ReportViewer WebControl
I'm trying to integrate reports with my .NET 1.1 application (using VS2003).
I've installed SQL Server Reporting Services 05 Standard Edition, yet I do
not see the ReportViewer control in the normal installation folder - in
fact, the only folder installed under Samples is Databases, containing the
sample tutorial databases.
Trying to patch the installation didnt obviously reveal where the file would
be installed from.
Any thoughts?
ThanksI just found this:
http://www.microsoft.com/downloads/details.aspx?familyid=8a166cac-758d-45c8-b637-dd7726e61367&displaylang=en
Which I *assume* contains my controls, but its not compatible with .NET 1.1.
So I'll rephrase my question :)
I have a .NET app with some forms that I'd like to provide direct links to
their Reporting Services counterparts in PDF format. I need to pass values
to the stored procedure parameters for each respective report, however. This
is the part I am unsure of.
I do not want the user to be prompted for values, because several of the
procedures contain values that the user will not know. Instead I was hoping
to pass these either within the report querystring, but I am not sure how to
do this. I thought the ReportViewer control would do the trick but it
appears it wont. I cannot convert the app to 2.0 because a significant
amount of time has already been invested in code and fixing compatibility
issues is not within our timeline.
Can anyone please provide me with a direction? Thank you.
"Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
news:eZ5szc$YGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Hello:
> I'm trying to integrate reports with my .NET 1.1 application (using
> VS2003).
> I've installed SQL Server Reporting Services 05 Standard Edition, yet I do
> not see the ReportViewer control in the normal installation folder - in
> fact, the only folder installed under Samples is Databases, containing the
> sample tutorial databases.
> Trying to patch the installation didnt obviously reveal where the file
> would be installed from.
> Any thoughts?
> Thanks
>|||You have two options. One is to use webservices. You get a stream back with
the format in PDF. You pass it all the parameters. The other option is to
use URL integration. You could embed the IE web control and give it the
appropriate URL. The URL string can specify rendering format as well as the
parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
news:u2Nu8h$YGHA.3328@.TK2MSFTNGP02.phx.gbl...
>I just found this:
> http://www.microsoft.com/downloads/details.aspx?familyid=8a166cac-758d-45c8-b637-dd7726e61367&displaylang=en
> Which I *assume* contains my controls, but its not compatible with .NET
> 1.1.
> So I'll rephrase my question :)
> I have a .NET app with some forms that I'd like to provide direct links to
> their Reporting Services counterparts in PDF format. I need to pass values
> to the stored procedure parameters for each respective report, however.
> This is the part I am unsure of.
> I do not want the user to be prompted for values, because several of the
> procedures contain values that the user will not know. Instead I was
> hoping to pass these either within the report querystring, but I am not
> sure how to do this. I thought the ReportViewer control would do the trick
> but it appears it wont. I cannot convert the app to 2.0 because a
> significant amount of time has already been invested in code and fixing
> compatibility issues is not within our timeline.
> Can anyone please provide me with a direction? Thank you.
> "Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
> news:eZ5szc$YGHA.3936@.TK2MSFTNGP05.phx.gbl...
>> Hello:
>> I'm trying to integrate reports with my .NET 1.1 application (using
>> VS2003).
>> I've installed SQL Server Reporting Services 05 Standard Edition, yet I
>> do not see the ReportViewer control in the normal installation folder -
>> in fact, the only folder installed under Samples is Databases, containing
>> the sample tutorial databases.
>> Trying to patch the installation didnt obviously reveal where the file
>> would be installed from.
>> Any thoughts?
>> Thanks
>>
>|||Thanks Bruce. Do you have any resources you can recommend for using the
Webservice option? I've found a lot on basic stuff but thats about it.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ubDgtn$YGHA.4760@.TK2MSFTNGP03.phx.gbl...
> You have two options. One is to use webservices. You get a stream back
> with the format in PDF. You pass it all the parameters. The other option
> is to use URL integration. You could embed the IE web control and give it
> the appropriate URL. The URL string can specify rendering format as well
> as the parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
> news:u2Nu8h$YGHA.3328@.TK2MSFTNGP02.phx.gbl...
>>I just found this:
>> http://www.microsoft.com/downloads/details.aspx?familyid=8a166cac-758d-45c8-b637-dd7726e61367&displaylang=en
>> Which I *assume* contains my controls, but its not compatible with .NET
>> 1.1.
>> So I'll rephrase my question :)
>> I have a .NET app with some forms that I'd like to provide direct links
>> to their Reporting Services counterparts in PDF format. I need to pass
>> values to the stored procedure parameters for each respective report,
>> however. This is the part I am unsure of.
>> I do not want the user to be prompted for values, because several of the
>> procedures contain values that the user will not know. Instead I was
>> hoping to pass these either within the report querystring, but I am not
>> sure how to do this. I thought the ReportViewer control would do the
>> trick but it appears it wont. I cannot convert the app to 2.0 because a
>> significant amount of time has already been invested in code and fixing
>> compatibility issues is not within our timeline.
>> Can anyone please provide me with a direction? Thank you.
>> "Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
>> news:eZ5szc$YGHA.3936@.TK2MSFTNGP05.phx.gbl...
>> Hello:
>> I'm trying to integrate reports with my .NET 1.1 application (using
>> VS2003).
>> I've installed SQL Server Reporting Services 05 Standard Edition, yet I
>> do not see the ReportViewer control in the normal installation folder -
>> in fact, the only folder installed under Samples is Databases,
>> containing the sample tutorial databases.
>> Trying to patch the installation didnt obviously reveal where the file
>> would be installed from.
>> Any thoughts?
>> Thanks
>>
>>
>
Saturday, February 25, 2012
Missing New Data Driven Subscriptions
have come across fine but I don't see the schedules that were set up for them.
Also, when I try to create a 'New Data-driven subscription' I don't see this
option; I used this feature in 2000. Is there some setting I need to change
somewhere?
ThanksOn Oct 4, 6:59 pm, Don <D...@.discussions.microsoft.com> wrote:
> Hello, I just migrated my reporting services to 2005 from 2000. My reports
> have come across fine but I don't see the schedules that were set up for them.
> Also, when I try to create a 'New Data-driven subscription' I don't see this
> option; I used this feature in 2000. Is there some setting I need to change
> somewhere?
> Thanks
If you do not have the SQL Server 2005 Enterprise Edition, this will
be the reason that they are not available (reference:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||So it was a feature in the regular version of 2000 but limited to Enterprise
in 2005
"EMartinez" wrote:
> On Oct 4, 6:59 pm, Don <D...@.discussions.microsoft.com> wrote:
> > Hello, I just migrated my reporting services to 2005 from 2000. My reports
> > have come across fine but I don't see the schedules that were set up for them.
> >
> > Also, when I try to create a 'New Data-driven subscription' I don't see this
> > option; I used this feature in 2000. Is there some setting I need to change
> > somewhere?
> >
> > Thanks
>
> If you do not have the SQL Server 2005 Enterprise Edition, this will
> be the reason that they are not available (reference:
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
> ). Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
Missing Last Row in Excel Output
Sometimes when I export to Excel I lose the last row. My reports are very
simple and do not include anything other than a table. I can export it one
day and all of the rows will be included and the next day I will lose the
last row. This is causing confusion among my end users because many of them
also receive a pdf document with the same information; however the pdf is
always correct.
Would someone from Microsoft please let me know if this is a known bug
because I can see nothing wrong with my report.
Thank you,
TimTim,
I am experiencing the same behaviour. Did you ever found out what the
issue was? If so, please share.
One of my report has 161 rows (including headers). When I export this
report to Excel, the last row is missing. Export to other formats work
fine. If I limit the number of rows in the report to 160, all of them
show up in the exported excel. Is there a limit on number of rows
exported on a tab to excel? How do I reset it?
Thanks|||Try adding an additional footer row to the table you are trying to export.
In the first cell enter a value of =" ". This isn't a fix but at least will
you will be able export the table with all of your data.
Tim
"kkaps" wrote:
> Tim,
> I am experiencing the same behaviour. Did you ever found out what the
> issue was? If so, please share.
> One of my report has 161 rows (including headers). When I export this
> report to Excel, the last row is missing. Export to other formats work
> fine. If I limit the number of rows in the report to 160, all of them
> show up in the exported excel. Is there a limit on number of rows
> exported on a tab to excel? How do I reset it?
> Thanks
>|||I had the same issue with a report that contains a matrix object...the
last row of matrix data was being dropped upon Export to Excel. I
simply put a small, empty textbox object directly below my matrix
object...this fixed the problem!
Missing Header Footer in
Hey all-
We have a number of reports that have headers and footers in HTML and PDF, but when exporting to Excel the headers and footers disappear.
Has anyone else seen this happen? Any ideas on how to re-enable the headers?
Thanks in advance,
Tristan
Tristan, did you find a way to do this? I am in a very similar position where the footers are not displayed.Thanks,
AJ
Monday, February 20, 2012
Missing expansion images on Matrix Report
I've created some Matrix reports which work just fine, I'm able to drill down and the expansion images(+/-) appear correctly.
However, after setting Role security on the folder containing the reports(this is an asp.net application), the reports still function but the images for the (+/-) are missing, just getting the missing image icon.
Any ideas?
Thanks, Burl
Ok, seems the problem is with the <authorization> element in the web.config file. Changed it form <deny users="*"> to <deny users="?"> and my toggle images started working. Seems to be a security problem for the builtin resource file on the matrix report.
Missing embedded images in Reporting Services
Any help will be much appreciated.
Hi Eduard,
Any luck resolving this issue? We are having the same problem and have not been able to find a solution. Any help would be appreciated.
Thanks,
Joel
|||Do the images show up when exported to PDF?
You might want to try setting UseSessionCookies to false in the ConfigurationInfo table of the ReportServer db.