Reporting Services :: Add Two Different Custom Calculated Fields From Two Different Datasets In Matrix

May 22, 2015

I am not sure if this is possible or not, we have two datasets and each one has an additional calculated field which breaks the value JobBilledExVAT into three years.

So it reports for example how much we billed in May 2015, 2014 and 2013.

I have got the table to display this when reporting on one dataset by grouping by year

The datasets both look similar to this 

SELECT
  Practice.ibvSalesByJob.JobBilledExVAT
  ,Practice.ibvSalesByJob.[Year]
FROM
  Practice.ibvSalesByJob
  INNER JOIN Practice.idvJobType

[Code] ...

The calculated field is 

=Switch(Fields!Year.Value = Parameters!Year.Value ,"15", Fields!Year.Value = Parameters!Pre1Year.Value ,"14", Fields!Year.Value = Parameters!Pre2Year.Value ,"13")
The field I am looking to add together is below
=Sum(Fields!JobBilledExVAT.Value)

If I do the below on the dataset associated with the matrix it displays the sum for each year correctly, I would however like to add the Sum(Fields!JobBilledExVAT.Value) from a different dataset (ABS1M) for the relevant year.

I tried this

=Sum(Fields!JobBilledExVAT.Value) + Sum(Fields!JobBilledExVAT.Value, "ABS1M")

However this adds 2015, 14 and 13 from ABS1M and doesn't add them individually if that makes sense? With the calculated field it only shows Year under the main dataset heading not 15, 14 and 13 separately.

I was think of using LOOKUP however I don't know how this would work as 15, 14 and 13 are not displayed under the dataset.

View 5 Replies


ADVERTISEMENT

Reporting Services :: Find Row Sum On Calculated Fields?

Jul 20, 2015

I have a simple report with a row grouping on teh Date and record_id from an sql query. I would like to find the row total.

The expression I have on the row is:

=IIF(Max(Fields!closing.Value) > 0, Microsoft.VisualBasic.Interaction.IIF(Max(Fields!opening.Value) > 0, IIF(Max(Fields!closing.Value) > Max(Fields!opening.Value), Max(Fields!closing.Value) - Max(Fields!opening.Value), Nothing), Nothing), Nothing)

View 2 Replies View Related

Reporting Services :: SSRS Matrix - Add A Column In A Matrix With A Variance

Aug 6, 2015

I got the following code to add a column in a matrix with a variance:

IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing,
(
(Fields!Amount.Value)
/Previous(sum(Fields!Amount.Value))
)
)

This code works fine, except that the first row of the matrix shows an #error

This happens with each matrix where I use this expression. A warning emerges:

rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.

Attempted to divide by zero.

The strange thing is that the part

Fields!year.Value=First(Fields!year.Value,"Category")
should prevent an error and I expect it to show 'nothing'

An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)

As you can see, all other 2013 rows show a blank cell, except the first row.

View 3 Replies View Related

Reporting Services And Linking Datasets

Nov 20, 2007



Hello All

Here is my scenario
1. I have to use a ODBC driver which does not support sub query. So I am forced to use two datasets and link them together.
2. I cannot use Sub-Report because the sum from dataset 2 will be used in calculation in Dataset1
3. Also SubReport cannot interact with Main report
4. We Re-created the report in Crystal XI and we can have two datasets, link them and Subreport value can be accessed in Main report
5. We tested Custom method to get this value for every record in Dataset 1 and it is taking too much time.
6. I could not beleive we cannot link two datasets in RS 2005. I like to use RS 2005 but this limitation is forcing us to use Crystal
6. Before we make a decision I thought to post my question here. Is there anyway to link two datasets?

All your help will be greatly appreciated.

View 2 Replies View Related

Reporting Services :: How To Use 2 Datasets In One Tablix

Oct 28, 2015

I have one tablix and this tablix has 2 parts(chart and table with numbers). Table with numbers uses Dataset1 and for chart I created another, 2nd dataset. How to get information from second dataset?

View 4 Replies View Related

Using Reporting Services To Compare Two Datasets

Feb 15, 2007

Hi,

I need to compare two datasets on the same server and then display if there is a difference in some fields for the two datasets.How can I do that? Is there an example somewhere that I can use ?

View 2 Replies View Related

Joining Two Datasets In Reporting Services.

May 8, 2007

I am using SQL Reporting Services 2000.



Is there some way I can connect two datasets? Main issue is that I have to connect two tables from different databases and then display them in a report. Since these tables reside in different databases therefore I have one dataset for each table. While displaying the report I need to join both these datasets and display a report with data from both the tables.



View 1 Replies View Related

Reporting Services :: Overall Sum For Each User When Using Union All To Join Two Datasets

Aug 5, 2015

Struggling to come up with an overall sum for each individual user when using the below code

SELECT
Practice.ibvStaffCategorisation.StaffId
,Practice.ibvStaffTotalsCL2Y.Period
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes

[Code] ....

As I am using union all it shows two values for each user, the problem is I am getting the data via lookup like below

=Lookup(Fields!StaffId.Value, Fields!StaffId.Value, Fields!Sum_ChargeableAmount.Value, "ABSTimeRollingY")

I don't think I can use a Sum within a lookup so at the moment it is only bringing through the first value for each user but I would like it to bring through both.

Is there a way to do this within the dataset or via the query in the table?

View 2 Replies View Related

Reporting Services : Report Using Two Datasets From Two SSAS Cubes

Mar 5, 2008

Hi,

I would like to build an SSRS report from two different cubes (SSAS 2005). These cubes are contained in two different SSAS databases.

To do this, i created a dataset for each cube throught MDX expressions.
These datasets use common parameters defined in my report.
Thus, i try to integrate in my report chart datas from the two datasets, but my chart only target one dataset.

Is it possible to create a kind of dataset view to join my two datasets ?
Or is it possible to join the cubes from two different SSAS databases in an MDX expression to put all my datas in a single dataset ?

thx for your help.

Zohir

View 3 Replies View Related

Reporting Services :: Accessing Data From 2 Datasets In A Tablix?

Aug 26, 2015

I have to create tablix report using data from 2 datasets. The data source for one of the dataset, DataSet1 is Microsoft SQL Server and the data source for the other dataset, DataSet2 is Excel.

I have to pic 3 columns from DataSet1 and one column from DataSet2.

I tried using a lookup function Expression:

=Lookup(Fields!ProgrammerName.Value,Fields!ProgrammerName.Value,Fields!Skill.Value, "DataSet2")

The above expression is throwing #Error on that column in the output.what should I do?

View 5 Replies View Related

MDX Calculated Members With Reporting Services

Oct 3, 2006

Hi all,

I have an analysis services 2005 cube with a lot of Calculated Members (for example I have a "Sales Volume in kg" and a "Sales Volume in squaremeters (m²)" and a calculated member "Sales Volume in g/m²" with (kg * 1000 / m²), and so on. This Measures should be shown with a hierarchical Product Dimension in which I can drilldown from a level1 to a level 4.

Sales Volume (g/m²)
--------------------------------------------------------------------------------
Level1
Level2
Level3
Level4
Level4
Level2
Level3
Level2

I can select them all with a MDX Query in the Data-part of reporting services. In the layout-part, I have a matrix-control which should show the values in the above form. The value-part of the matrix has the form "sum(field)". I know, it's not correct, I believe I should use "AVG(field)".

When I use non-calculated fields, all works ok. With calculated fields, I get the error

[rsAggregateOfMixedDataTypes] The Value expression for the textbox €˜textbox3€™ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.

It's the same with sum and avg (as the error message tells me). But how can I use such fields in reporting services? My users wants a report with the product hierarchy vertical and the salews volume g/m² horizontal so that the values are correct on every level the drill in. In Excel, OWC or other analysis clients, it's no problem to do this, but I can't find a way, doing this in reporting services.

Thanks for any help
Hans

View 3 Replies View Related

Reporting Services :: How To Create 2 Conditions From 2 Datasets In Row Visibility SSRS

Apr 29, 2015

In ssrs, I want to create an expression for the row visibility. But, the expression will contain 2 conditions from different 2 datasets (DealStarts & RowofTrendingVisibility). I have applied a solution from online, but got an error message is 

"The Visibility.Hidden expression for the tablix ‘Tablix9’ contains an error: [BC30451] Name 'launchdate' is not declared. "

I think that there is a minor issue in my syntax.

=iif ((Last(MonthName("DealStarts"))=monthname(month(today())) or launchdate ("RowofTrendingVisibility")<Parameters!StartDate.Value),true, false)

View 3 Replies View Related

Reporting Services :: Tabular Report Datasets - Grouping On Column

Jun 23, 2015

I have table which has 5 columns(col1,col2,col3,col4,col5) very simple and some data .

I would like to create a tabular  report  datasets on the report like shown below with grouping on col1

like
col1  some static text and dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
col1 some static & dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------

Note I need to keep all the tables on the same page and when exported in excel they should come on same page sheet.

View 2 Replies View Related

Reporting Services :: Using Multiple Datasets In A Table - Outer Join In SSRS

Jun 17, 2015

I want to use multiple datasets in a table and wants to do the full outer join on the two datasets in the same table. For example, my two datasets are:

I want to display a ssrs table like:

Both the datasets are coming from different sources. So I cannot integrate them at sql query level.

View 4 Replies View Related

Reporting Services :: Displaying Sum Of Current Month And Year To Date Without Using Two Separate Datasets?

Jun 4, 2015

I am trying to use one dataset rather than two and was hoping to then filter the data via a table or matrix.

This is my dataset 

SELECT
Practice.ibvStaffCategorisation.StaffId
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableAmount) AS Sum_ChargeableAmount
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableAmount) AS Sum_NonChargeableAmount
,Practice.ibvStaffTotalsCL2Y.Period

[code]....

I would like to display two rows of data for each StaffId one representing the current period and the other all periods to date so the table would look something like below.

StaffId | Non Chargeable Time | Chargeable Time

CJJ | 0:20 | 4:20
     | 4:50 | 19:20
JN | 0:05 | 5:30
     | 1:30 | 25:30

The above shows two separate StaffId figures the first line for each shows non chargeable and chargeable for the current period and the second line a total of all periods in that year.

I have managed to get the first row to display only figures from the current period by using a filter however it also applies the same filter to the second row in the group. I have also tried to group the rows but am drawing a blank.

View 6 Replies View Related

Reporting Services :: How To Export 2 Datasets To 2 Excel Sheets In Single Report SSRS

Jun 21, 2015

I have two different dataset in one Report, Each Dataset result is binded to a different Table component.

When I export as Excel ,I am getting all this in One Excel sheet.

I need this in separate excel, as dataset1 in excel1 and dataset2 in excel2.

View 3 Replies View Related

Reporting Services :: Count Of Lookup Expression Column In Report With Multiple Datasets?

Sep 27, 2015

My report has two data sets that hold inventory from two different departments.    

ds_DeptA and ds_DeptB

I have a table, that pulls the DeptB status of DeptA record and displays it. This returns empty when the lookup fails to make a match, which is fine.  Typically means DeptB does not have the record yet.   I need to count these empty (null) feilds and populate it in a Text box outside of the table.
 
I just can't figure out the syntax with multiple datasets. I can't use the lookup expression as part of the count expression since the count expression is not contained in a table that has a dataset. 

table: ds_DeptA
fields:
ID
Name 
date_set_to_DeptB
<<Expr>> =Lookup(Fields!ID.Value,Fields!DeptA_ID.Value,Fields!DeptB_Status.Value, "ds_DeptB")

View 3 Replies View Related

Reporting Services :: Change Visibility Of Textbox (Tablix) Based On Data From Two Datasets?

Jun 11, 2010

I have two charts which are based on their own separate datasets.  One chart shows data aggregated by MONTH, the other chart shows data aggregated by WEEK.  I have put both of these charts inside a rectangle and set their visibility based on whether or not there is data returned by their respective datasets.  This part is working just fine.

However, I have added a tablix to the report, deleted all columns and rows leaving only one remaining row/column so it looks like a textbox.  This tablix is sitting on top of the two charts.  The problem i'm having is that when both charts return no data and they do not show up in the report, the tablix still remains visible. I need to set the visibility but based on a condition between the two different datasets behind each chart.

Here's what I need:  If ONE of the charts returns data, then I need the tablix to be visible.  If neither chart returns data, then I need the tablix to be invisible.  I'm trying to write an IIF statement comparing the two datasets but I get inscope errors.  Isn't it possible to compare values which are contained in two separate datasets in SSRS?

Seems like something like the following logic should work:

IIF(CountRows("Monthly_DataSet") > 0 OR CountRows("Weekly_DataSet") > 0 , FALSE, TRUE)

Obviously the above expression will return syntax errors, but you'll get the point as to how i'm thinking. How can I set the visibility of the tablix based on these two charts?

View 4 Replies View Related

Reporting Services :: SSRS 2008 R2 / Grouping Multiple Tables (tablix) With Different Datasets

Mar 27, 2012

We have a report that was created in SSRS 2008 R2 that has 3 tables with different datasets that share a common ID that I want to use to group them.

If we run the report passing only a single value for the grouped parameter then the report works perfectly.  What we need is for this report to allow multiple values to be selected for this parameter and for the report to run as if the user had selected each value one at a time and run the report with page breaks in between.  Currently, when we pass multiple selected values for the grouping parameter the report displays all values for table 1, then all values for table 2, then all values for table three as below:

Table 1:

Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...
....

Table 3:

Detail rows for Group Param Value 1 ...
Detail rows for Group Param Value 2 ...
Detail rows for Group Param Value 3 ...

But we want it to render like this:

Table 1:
Detail rows for Group Param Value 1...
Table 2:
Detail rows for Group Param Value 1...
Table 3:
Detail rows for Group Param Value 1...

[code]....

The page breaks are needed so that when the report is exported to excel each individual report (by group param) will be on its own uniquely named tab.The report must export cleanly to excel and currently does for the single value passed.

View 5 Replies View Related

Reporting Services :: Calculated Field To Capture +/- 28 Days From Date

Sep 18, 2015

I need to create a calculated field that will give me 'yes' or 'no' if a date field is within +/- 28 days of another date field.

So for example if a.date = 1/1/15 and b.date is 30/3/15 the calculated field will say 'no'; if the b.date was 10/1/15 it would say yes.

Similarly, if the b.date was 1/11/14 it would say 'no'; if it was 10/12/14 it would say 'yes'.

View 2 Replies View Related

Reporting Services :: Sorting On Calculated Report Items Columns

Jun 21, 2015

I need to sort my tablix report where I have several calculated columns like:

=ReportItems!Textbox47.value+ReportItems!Textbox48.value..

Now I would like to sort these by using the Interactive sort functions - but I have seen elsewhere that this is not possible..(I'm also getting an error when trying..)Is there not a way that I can bypass this (using Code function or similar) ? The datasource for the data is a OLAP cube

View 3 Replies View Related

Reporting Services :: Adding Calculated Column To Series Group In The Chart?

Jun 11, 2015

I want a report that displays selected year quantity sales and previous year sales quantity and their quantity difference.

i also want to display a chart like I added year to the series group but i don't know how to add difference to the series group.

View 5 Replies View Related

Reporting Services Matrix Help

Apr 1, 2008

I'm looking for help on how to do something like this in Reporting Services. I'm using a Matrix, but always messes up and doesn't put the values where I want them to be.

Thanks alot!

View 5 Replies View Related

Reporting Services :: 2 Dataset Tables In One Matrix?

Dec 2, 2015

I am having difficulties with what seems to be a common issue. I want to produce a trial balance. The first row of the tablix would include fields: AccountNo, Description and startingbalance from a dataset called tb. Below that, I would like to draw from a dataset called Entries with multiple rows having fields: AccountNo, Desc1, Desc2, Debit, Credit and Balance.     Balance in the Entries table is already computed from the starting balance and the entries above.  There are many AccountNo's with corresponding entries.  

How do I do this?

View 4 Replies View Related

Reporting Services :: Add Sum - Calculating Average In A Matrix

Jun 9, 2015

I am having trouble with calculating AVG in a matrix. I have done the subtotal but I would like to know how to calculate the Average. I would also like AVA below the Total.  

Link : [URL] ....

I have also added an image oh how the matrix looks like.

View 5 Replies View Related

Reporting Services && Custom Assemblies

Mar 2, 2004

Hi everyone,

I am testing reporting services custom assembly feature, but have problems.

I have referenced my dll, put my class name and instantiated it...

when i try and use a method within my instantiated class, it says "file or assembly name was not found".


Any ideas?

View 2 Replies View Related

Reporting Services Custom Code

Jun 6, 2007

I created a class library which uses a web reference
My SSRS report uses the class through its custom code
I copied the dll & dll.config of the class library to the VS2005 private assembllies and the report works
I copied the dll & dd.config to the reportserver bin and gave the assembly full trust by adding the necessary code group
I even added a code group the give the dll.config file full trust as well
Yet on Repost server, the report does not work
Any ideas
I suspect it is a permission problem

View 4 Replies View Related

Custom Authorization In Reporting Services

Dec 4, 2006

For custom authorization, I am overwriting the CheckAccess method.

But how do I know what I am trying to access in CheckAccess method? I need to know what report the check access method is trying to execute for our requirements.

How do I get that?

View 1 Replies View Related

Custom Authorization In Reporting Services

Oct 3, 2007

I need to implement custom security in reporting services. I have followed the standard example here:

http://technet.microsoft.com/en-us/library/ms160724.aspx



I got authentication to work without too many problems, and it authenticates by querying my custom table. I want to also maintain my authorizations in the same manner. I want to be able to define in my own tables that user test1 has permission to view reports 1, 3, 5, and 8. User test2 can view reports 1,3,5,8, and 9. User test3 can view 1, 2, and 4. Etc.

The authorization extension, contained in my version of Authorization.cs is where I expected to be able to do this. I wanted to be able to write code that queries my own tables and says, "does this user have permission to view this item?" Getting the this user portion is easy as is obvious in the many checkAccess methods. But being able to tell what item I am currently dealing with has proved impossible from the context of Authorization.cs, which implements IAuthorizationExtension. Knowing what type of item I am dealing with is obvious, but I need to know something that uniquely identifies the exact item I am dealing with.

How can I determine in checkAccess or any of the other available methods what "thing" they are looking at? What can I examine to determine that for instance this particular authorization request is against "Report 1"? It seems that if I was allowed to completely override Authentication to use my tables, I should be able to override Authorization to do the same. Is this possible or am I missing something?


Can anyone help? Thanks.

View 2 Replies View Related

Reporting Services Matrix Reports Repeating Row Labels

Jan 30, 2007

Is it possible to repeat the row labels for matrix reports where there are multiple groups. So if the matrix report rows are department,division,employee then have all three labels show up on each row not just at the breaks. Cannot seem to find a way to do this. thanks

View 1 Replies View Related

Reporting Services Matrix Static And Dynamic Columns

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

Reporting Services :: Add Subtotals To SSRS Matrix Report?

Oct 7, 2015

Seems simple.  People working in departments with different types of hours (regular, overtime, vacation... thus the grid which pivots hours in rows to the appropriate columns).  I want to subtotal by department, but whenever I try to add totals after to that group, I just get a grand total.  No subtotals.  Why?

View 8 Replies View Related

Reporting Services :: Grouped Matrix Data Not Exporting To CSV

May 12, 2015

I am trying to export report to .CSV , but I am unable to export grouped data, instead to grouped data , it exporting detail data,for example my grouped data in matrix is as below , I need to export below data to .csv 

column1 colum2    year           amount
1             1          2011             $1.0
               2          2012             $2.0
               3          2013             $3.0
                           2014             $4.0

but, I am getting like below-

column1    column2    year           amount
1                  1           2011             $1.0
1                  2           2012             $2.0
1                  3           2013             $3.0
1                  3            2014             $4.0

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved