Dynamic Time On Columns With Reporting Services
May 17, 2005
Hi all!
If I have some mdx I'm using in reporting services like this:
select
{ [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6] } on columns,
{A_list_of_measures } on rows
from ACD_Calls
The column names are unique to the day of month- which means when I use a table to display this in reporting services, the field names change dynamically when the date parameters change which means the table stops working.
I'll post this in reporting services too but I thought maybe I could alias the column names in mdx shielding the reporting services table from changes in dates.
What do you think? Would a matrix be more flexible in this case?
Richard
View 7 Replies
ADVERTISEMENT
Apr 22, 2015
I am using sql 2012 SSRS.
My requirement : I have Database which has got 100 tables.I need to create SSRS report which display top 10 records from the table within that database.
User will select The table name as parameter in the report. the number of column in each table will vary .
I am using the below statement in stored proc, and calling same from ssrs report
exec(
'select top 10 * from ['+@datbasename+'] .[app].'+@tablename+'')
View 6 Replies
View Related
Jun 15, 2007
Hi every one,
Could you please anyone tell me how to have static and dynamic columns in a matrix?
We can add static columns by right clicking detail text box and add column. But it will repeat for each column group. I need static columns in the dynamic columns level itself.
Thanks in advance!
View 3 Replies
View Related
Aug 21, 2015
I am trying to use SSRS to allow users to select any one or several columns from a set of cascading parameters which will then do a "data dump" of the contents of the selected columns for "export".I know how to do cascading parameters, but I am having problems coming up with a way of using the parameters to dynamically build a matrix which has as its columns the list selected in the parameters. I need the user to choose WHICH columns will be displayed. There could be 1, 2, 3, up to 50 columns.
View 2 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
Oct 18, 2007
Hi all,
I have a problem making columns dynamic in a report. I' m using Microsoft.net reporting. I was using a flag to determine wheather a column should be made visible or hidden using
IIF(instr(Fields!numrows.value="a")>0,True,false) in the column I want to make dynamic. I do this by setting this expression in the visibility property of the column.
I have 7 columns which are to be made dynamic so I used the same condition in all the columns with a different string to compare ("a","b","c" etc) . I got the columns to be visible dynamically but the condition was not working properly so, I shifted from fields to report parameters and used IIF(parameters!fieldsDisplay1.value=true,true,false). I can get this to work. The problem I have now is if user selects col1 ,col3,col 5 among(col1,col2,col3,col4,col5,col6,col7) then the report shows up like this.... col1, ,col3, ,col5. Meaning the alternate columns are empty because I' m hiding the visibility when I use the above expressions. I want the report to show all columns one after the other example col1,col3,col5 so that the report doesn't have any empty columns.
I found a link online http://www.c-sharpcorner.com/UploadFile/uditsingh/CR1111022006055359AM/CR11.aspx which explains the same in crystal reports, Can anyone tell me if this is possible using Microsoft reporting? If yes, how do I accomplish this?
Any help would be appreciated.
Thanks
Mythili
View 6 Replies
View Related
Apr 30, 2015
we have problems with our SQL Reporting Service 2012 (SSRS) server . We have setup Kerberos delegation between SSRS and the database server (SQL Server Always-on cluster) so users are authenticated down to the database. The issue occurs from time to time that SSRS loses the ability to delegate the user credentials to the database. At this point in time the Report Server logs contain rejected database connections because of ANONYMOUS logon. After restarting SSRS the problem is gone.
View 2 Replies
View Related
Sep 14, 2015
At the moment I already added the DataSet I'm gonna be using. I have 2 date parameters Start Time and End Time.
What I would like to do for the report is to only pull results greater than 48 hours to the report
How can I accomplish this?
View 4 Replies
View Related
Jun 19, 2015
We've got a requirement to build the real time report. user can browse report at any point of time and need to see the latest data(stock market) in the report.
I've few options down...
1. Directly point to OLTP database as source and write stored procedure to show result set.
2. Replicate the database and write the SP's to reports. To avoid pointing directly to OLTP db.
3. To build the datawarehouse with dim & facts to show it in reports. I prefer this as a standard method, but this would have some latency depending on trasaction load which will differ from the requirement.
View 3 Replies
View Related
Dec 3, 2006
I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -
I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:
"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"
Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).
Here's what my query looks like -
SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]
Any ideas?
Thanks,
Arjun
View 8 Replies
View Related
Aug 31, 2015
I will be receiving a CSV daily where columns within the file will change. The column order and number of columns can change daily. I need a way to read in the header from the csv and create a flat file connection that reflects the columns listed in the header.
Is there an easy way to do this using a script task? I have already read the header into a table but I have been unable to create the dynamic file connection.
View 4 Replies
View Related
Jul 16, 2015
I am new to SSIS and C#. In SQL Server 2008 I am importing data from a .csv file. Now I have the columns dynamic. They can be around 22 columns (some times more or less). I created a staging table with 25 columns and import data into it. In essence each flat file that I import has different number of columns. They are all properly formatted only. My task is to import all the rows from a .csv flat file including the headers. I want to put this in a job so I can import multiple files into the table daily.
So inside a for each loop I have a data flow task within which I have a script component. I came up(research online) with the C# code below but I get error:Index was outside the bounds of the array.I tried to find the cause using MessageBox and I found it is reading the first line and the index is going outside the bounds of the array after the first line.
My File1Conn is the flat file connection instead I want to read it directly from a variable User::FileName
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Windows.Forms;
using System.IO;
[code]....
View 8 Replies
View Related
Apr 22, 2007
Hi
I've a stored procedure returns a table with dynamic SQL.
Columns of the returned table is dynamic.
How to make a report for this table?
Thanks.
View 2 Replies
View Related
Sep 11, 2015
We have a requirement to produce adhoc Excel reports with a standardized header page with a disclaimer attached. We want to be able to feed in a SQL Statement, or a table with the resultset from a SQL Statement and have SSIS populate an existing blank Excel workbook, which the disclaimer attached. The use of xp_cmdshell is not an option.I've spent a lot of time looking for solutions on the web and it seems though its not possible - although many articles are 3-5 years old. Before I throw in the towel, I just wanted to get feedback from this group if it still is not possible in the latest versions of SQLServer and SSIS, or to ask if there are any other 3rd party solutions that can do this today.
View 5 Replies
View Related
Jun 18, 2007
Hi,
I have a dynamic SQL statement like
'Select * from Table name where month=@month' ..Exec(SQLStatement)
Dynamic SQL does not recognizes @month which is a reporting services multi select parameter...An suggestions???
View 6 Replies
View Related
Apr 1, 2008
Hi,
i want to display images on the Reporting services, Images resides on the same machine but we want to set the image path dynamically from dataset fields. please suggest me best possible approach. this is windows application and all the data with image path is in a single dataset.
thanks,
Nitin Sood
View 5 Replies
View Related
Jul 30, 2015
I am in between of creating a dynamic SSSIS package which will run for multiple zones having different source connection.My source is in Oracle.I am having 3 DFT with the 3 different source tables.I want to create a package with above DFT dynamically so that my single package can run for the entire zone with dynamically source connection change.I have created a Master table which stores the zone source connection string and zone name. I have 2 different connection.so if in future any new zones come so only newly zone details need to be add in master table without opening the package.
View 3 Replies
View Related
May 7, 2004
Hi, all:
Kind of new to reporting services. I've been playing around with SQL Reporting Services and was wondering if anyone knows how to populate the fields from a dataset in the Report Designer from a stored procedure that uses dynamic SQL. I've had success with non-dynamic stored procedures and inline queries, but am unable to generate fields when the sp contains dynamic SQL. I've tried defining the fields manually, but when I execute the report I receive errors that the fields are undefined.
Any help would be greatly appreciated!
Thanks
View 2 Replies
View Related
Jun 29, 2007
I have a requirement to create dynamic reports for my client.
once i create these reports then the user will choose columns of there choice.
so the columns may belong to multiple tables.
Now the report should get generated with the layout etc. is it possible.
since our project is totally on the webserver(webbased.)
please if you can provide me with any links with dynamic report creation wizards.
and also we only use Stored procedures via database.
which is best is writing the entire queries right behind the layouit or calling the entire logic via Stored procedure. i am a bit confused. this is my first project working on reports itself.
Thank you all for the helpful information.
View 1 Replies
View Related
Apr 17, 2008
Hello,
I have a matrix with a title column. My matrix has a column that is expanded in 2 with data changes
as followings:
MY TITLE1 MY TITLE2
YEAR 2007 YEAR 2008 } matrix
matrix Column column } matrix
How can I change de title (MY TITLE2) differently according to the changing value of the dynamic data column ?
My title2 has to be located above the YEAR 2008 and has to be different from MY TITLE 1.
Thank you for your help.
Best Regards,
Hirondelle.
View 2 Replies
View Related
May 21, 2015
I have a SP like this:
ALTER PROCEDURE [dbo].[ReportGateWay]
(
@ISO bigint= 0,
@Gateway bigint= 0
)
AS
BEGIN
DECLARE @SQL nvarchar(max)
[Code] ....
I need to invoke this in SSRS. The problem is that when on creating dataset for this, I get an error which read:
You must have atleast one field for your dataset
What can be done in this case?
Here is the SQL Fiddle: [URL] ....
View 16 Replies
View Related
Dec 19, 2005
Hi all,
I am currently working on some reports where I need to display images dynamically.
there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.
for example,
if the range is between
80% - 100% smily face.
60% - 80% normal face
40% - 60% sad face.
Can any one help in approaching this.
Initially I worked with only static embeded images.
It also helps me in solving another problem.
I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.
Thanks in advance.
waiting for an early help as it is very urgent for me.
Regards,
Ramesh P
View 5 Replies
View Related
Aug 5, 2015
I am attempting to create a subscription that will send a report whose name would have been formatted to the following: ReportName_MMDDYYYY.
I know this can be done by creating a Windows File Share subscription type, but in this case, I would need to use E-Mail.
In this scenario, the user will receive the email and included, the file formatted as described above.
Is there a way to get it done ?
View 3 Replies
View Related
Sep 9, 2015
I need to put a dynamic table (the amount of rows I control) into a static container (with a fixed size).
View 2 Replies
View Related
Feb 14, 2008
Will the width property of a column be able to be set at run-time (i.e. ability to set the width of a column to an expression) in SQL Reporting Services 2008? One problem I have seen is the inability to set the width of columns in a report when you you have several columns whose order is determined based on parameters.
Lets say I have 2 columns in a report and two fields from the database which will populate those columns: 1 field that is varchar(5) and one which is varchar(50). If I do not know which column each field will go to until runtime how am I supposed to adjust the column width? Letting the column width be set by an expression would solve everything as I could pass the width in to the report. As it stands now, I would have to set each column's width to accomodate a field of type varchar(50) in order to avoid wrapping of data.
You could say just create 2 reports, one for each case, but what if I had 15 fields? 20?how to dynamically set the width of a column? Will the width of a column be able to be set at run-time in SQL Server Reporting Services 2008 via expression or some other means?
View 39 Replies
View Related
Nov 19, 2015
We run 2014 enterprise. I have 3 series being charted on a line (with dots) chart. I know what the series are but to ssrs they are dynamic. One of them really needs a secondary y axis because its a percentage. Is there a way to do this? From what I read if I was to somehow make them less dynamic, then ssrs could treat this one differently. Also, what if I want data point values to show only on this plot but not the other two?
View 4 Replies
View Related
Sep 21, 2015
I have created an SSRS report containing a bar chart. The Y-axis contains some application names which are displayed based on a certain criteria. Hence sometimes there are only 2 values in y-axis while sometimes it may range upto 20 items. I have tried a lot but have been unsuccessful in getting a dynamic y-axis which changes with the number of items present.
View 2 Replies
View Related
Aug 18, 2015
I am working on SSRS column chart which has one series that is quantity
I have added a stripline based on Quantity value > 500
Now I want my column to have 2 colors.
for portion above quantity 500 = RED
column part below quantity 500 = GREEN
How that can be done
View 11 Replies
View Related
Oct 16, 2015
I have the following result set that I am putting into a SSRS 2012 Matrix:
RowNum RowLabel Val Title
1.00 Advance 10000.0000 TestTitle1
4.00 List Price 18.0000 TestTitle1
5.00 Units Shipped 20000 TestTitle1
6.00 Return Units -8125 TestTitle1
7.00 Net Sales Units 11875 TestTitle1
8.00 Return % 45.0%/10.0% TestTitle1
*Note: The data in Val for 'Return %' is a text field - informational only and necessary.
When setting it up such that the Columns are Title, and the Rows are RowLabel, I get the following:
TestTitle1
View 3 Replies
View Related
Sep 30, 2015
I have a user table with Label and value fields where i would like to control the display of the text boxes based on the values selected in my user table.Can we adjust the text box positions dynamically based on the user table values.
Ex: Table
Label1 Field1 Label2 Field2 Label3 Field3
ID 100 Dept Sales
Report Design :
Label1: ID Field1:100
Label2: Field2:
Label3:Dept Field3:Sales
Expected Result :
Label1: ID Field1:100
Label3:Dept Field3:Sales
In my table i don't have values for Label 2 & Field 2 , can we adjust the spacing conditionally to be utilized by Label3 & Field3 in SSRS.
Note : Above mentioned data is just for an example and in my actual report i can have more than 3 columns and report is looking ugly with all the spacing if i don't have data in all the fields.
View 9 Replies
View Related
Aug 7, 2015
I have a tablix that looks like this...
30 Jul
31 Jul
01 Aug
02 Aug
03 Aug
04 Aug
05 Aug
Region1
0.00
0.00
0.00
0.00
100.00
100.00
0.00
[code]....
I would like to sort the last column which is a dynamic date (always the most recent from the last 7 days) from 0 to 100.
View 11 Replies
View Related
Oct 22, 2015
Is there any possibility to have the Defaultvalue of a Parameter in Bold fonts and rest of it as Default parametervalue=Defaultvalue ,"Bold","Default".So for example in drop down of parameter if the C is the current value it would look like this:
ParameteValue:
A
B
C
D
View 3 Replies
View Related
Nov 7, 2007
Firstly sorry if this already exists but I can't find it the answer anywhere.
I'm running Visual Studio 2005 Reporting Service. I have a SQL query which is reutrning a count of second which I need to display in HHH:MMS format.
integer value = 144322 which is displaying as 16:05:22 or 02/01/1900 16::05
the end result needs to be 40:05:22
It is quite visable what SQL is doing but I am having big issues around how to reach the result.
Any help much appreciated.
SUM(Call_Length) AS Total_Talk_secs,
DATEADD(second, SUM(Call_Length), '00:00:00') AS Total_Talk_Time,
View 4 Replies
View Related