Finance | Project Operations, Human Resources, ...

Calculate % Growth YOY on SSRS

(0) Share

Report

Posted on by On-Hand Inventory I...

120

Dear Sir,

I would like to make comparison report comparing on Calculate % Growth YOY on SSRS in matrix for specific Number of years and I am working in Microsoft Dynamic AX 2012 , and this my Data Set contains as the following

Data Source Dynamics AX

Data Source Type Report Data Provider

Query SELECT * FROM JFLFSalesYEARMONTHMATRIXDP.JFLFSalesYearMonthTmp

And I am Trying to make my SSRS Report as the following

Customer Name	Item Name	2018	2019	% Growth
Smith	Mattresses	100	70	70 %
Jocob	Bed	100	72	72%

and I have added a Parameter in the parameter folder called a Year parameter and specify values and I have specify a value as 2019.

and I have Right-click Year group > Group Properties > Visibility. and I Choose Show or hide base on an expression.

and Used this expression:

=iif(instr(join(Parameters!Year.Value,","),Fields!Year.Value)=0 ,true,false)

But I am getting this error When I Buld The Report

Error 1 : Field token Parameters!SalesYear.Value is invalid. Could not find the identifier.

How can I solve this error

Waiting for valuable answer

Best Regards

Jamil Alshaibani

My Email : Jamil_Alshaibani@yahoo.com

All responses (6)

Answers (0)

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Calculate % Growth YOY on SSRS

Hi Jamil,

You can get started here: docs.microsoft.com/.../configure-analysis-services

Coding this in an SSRS report appears at least to me very rigid and static and I believe that SSAS is the better option.

Best regards,

Ludwig
On-Hand Inventory I... 120 on at

Like (0)

Report

RE: Calculate % Growth YOY on SSRS

Dear friends

I Got this output for my report Calculate % Growth YOY on SSRS which is not what I want

my requirement is to get specific summary data for specific period of date in this example I give the screenshots for the input and the output

only what I want just Sales of April 2018 and April 2019

and this my class script

public void processReport()
{
int number;
str s;
TransDate fromDate,toDate;
JFLFSalesYEARMONTHMATRIXContract CustInvoiceDC;
Query query;
QueryRun queryRun;
QueryBuildDataSource queryBuildDataSource,qbds,queryBuildDataSource1 ;
QueryBuildRange queryBuildRange,contractQbr2;
QueryBuildRange receiptDateRequestedFilter ;
CustInvoiceJour queryCustInvoiceJour;
CustInvoiceTrans _custInvoiceTrans;
InventDim _inventDim;
InventTable _inventTable;
// InventLocation _inventLocation;
// InventSite _inventSite;
InventItemGroupItem _inventItemGroupItem;
query = new query(queryStr("JFLFSalesYEARMONTHMATRIX"));
CustInvoiceDC = this.parmDataContract() as JFLFSalesYEARMONTHMATRIXContract;
query = this.parmQuery();
fromDate = CustInvoiceDC.parmFromDate();
toDate = CustInvoiceDC.parmToDate();
queryBuildDataSource = query.dataSourceTable(tableNum(CustInvoiceJour));
receiptDateRequestedFilter = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(CustInvoiceJour)),fieldNum(CustInvoiceJour,InvoiceDate));
receiptDateRequestedFilter.value(SysQuery::range(fromDate,toDate));
queryRun = new QueryRun(query);
ttsBegin;
while(queryRun.next())
{
jFLFSalesYearMonthTmp.clear();
queryCustInvoiceJour = queryRun.get(tableNum(CustInvoiceJour));
_custInvoiceTrans = queryRun.get(tableNum(CustInvoiceTrans));
_inventDim = queryRun.get(tableNum(InventDim));
_inventTable = queryRun.get(tableNum(InventTable));
// _inventLocation = queryRun.get(tableNum(InventLocation));
// _inventSite = queryRun.get(tableNum(InventSite));
_inventItemGroupItem = queryRun.get(tableNum(InventItemGroupItem));

jFLFSalesYearMonthTmp.InvoiceAmount = queryCustInvoiceJour.InvoiceAmount;
jFLFSalesYearMonthTmp.InvoiceDate = queryCustInvoiceJour.InvoiceDate;
jFLFSalesYearMonthTmp.SalesYear = year(queryCustInvoiceJour.InvoiceDate);
// jFLFSalesYearMonthTmp.SaleMonthName = mthName(mthOfYr(queryCustInvoiceJour.InvoiceDate)); // mthName(mthOfYr(systemdateget()))
s =mthName(mthOfYr(queryCustInvoiceJour.InvoiceDate));
jFLFSalesYearMonthTmp.SaleMName = s;
jFLFSalesYearMonthTmp.SalesMonth = mthOfYr(queryCustInvoiceJour.InvoiceDate);
jFLFSalesYearMonthTmp.InvoicingName = queryCustInvoiceJour.InvoicingName;
jFLFSalesYearMonthTmp.OrderAccount = queryCustInvoiceJour.OrderAccount;
jFLFSalesYearMonthTmp.costValue = queryCustInvoiceJour.costValue();
jFLFSalesYearMonthTmp.InventQty = _custInvoiceTrans.InventQty;
jFLFSalesYearMonthTmp.ItemId = _custInvoiceTrans.ItemId;
jFLFSalesYearMonthTmp.LineAmount = _custInvoiceTrans.LineAmount;
jFLFSalesYearMonthTmp.SalesPrice = _custInvoiceTrans.SalesPrice;
jFLFSalesYearMonthTmp.itemName = _custInvoiceTrans.itemName();
jFLFSalesYearMonthTmp.modVolumeM3 = _custInvoiceTrans.modVolumeM3();
jFLFSalesYearMonthTmp.configId = _inventDim.configId;
jFLFSalesYearMonthTmp.InventColorId = _inventDim.InventColorId;
jFLFSalesYearMonthTmp.InventSizeId = _inventDim.InventSizeId;
jFLFSalesYearMonthTmp.NameAlias = _inventTable.NameAlias;
// jFLFSalesYearMonthTmp.InventLocationId = _inventLocation.InventLocationId;
// jFLFSalesYearMonthTmp.RegionName = _inventLocation.Name;
// jFLFSalesYearMonthTmp.SiteId = _inventSite.SiteId;
// jFLFSalesYearMonthTmp.BranshName = _inventSite.Name;
jFLFSalesYearMonthTmp.ItemGroupId = _inventItemGroupItem.ItemGroupId;

jFLFSalesYearMonthTmp.insert();

}
ttsCommit;
}

Where I have gone wrong in my Class script

would you please guide me,waiting for your valuable answer.

Best Regards

Jamil Alshaibani
On-Hand Inventory I... 120 on at

Like (0)

Report

RE: Calculate % Growth YOY on SSRS

Dear Sire Dr. Ludwig Reinhard

Thank you very much for your suggestion , but I do not have any knowledge about this a SSAS (BI topic) can give me any site that I can learn about this .

Best regards

Jamil Alshaibani
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

RE: Calculate % Growth YOY on SSRS

Hi,

why do you try to do that in SSRS?

Isn't this a SSAS (BI topic)?

Don't you need deep drill down possibilities?

Best regards,

Ludwig
On-Hand Inventory I... 120 on at

Like (0)

Report

RE: Calculate % Growth YOY on SSRS

Hi MATTGUO

Thank you very much for your response , actually this my processReport()

Class contains

public void processReport()

{

int number;

str s;

TransDate fromDate,toDate;

JFLFSalesYEARMONTHMATRIXContract CustInvoiceDC;

Query query;

QueryRun queryRun;

QueryBuildDataSource queryBuildDataSource,qbds,queryBuildDataSource1 ;

QueryBuildRange queryBuildRange,contractQbr2;

QueryBuildRange receiptDateRequestedFilter ;

CustInvoiceJour queryCustInvoiceJour;

CustInvoiceTrans _custInvoiceTrans;

InventDim _inventDim;

InventTable _inventTable;

InventLocation _inventLocation;

InventSite _inventSite;

query = new query(queryStr("JFLFSalesYEARMONTHMATRIX"));

CustInvoiceDC = this.parmDataContract() as JFLFSalesYEARMONTHMATRIXContract;

query = this.parmQuery();

fromDate = CustInvoiceDC.parmFromDate();

toDate = CustInvoiceDC.parmToDate();

queryBuildDataSource = query.dataSourceTable(tableNum(CustInvoiceJour));

receiptDateRequestedFilter = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(CustInvoiceJour)),fieldNum(CustInvoiceJour,InvoiceDate));

receiptDateRequestedFilter.value(SysQuery::range(fromDate,toDate));

queryRun = new QueryRun(query);

ttsBegin;

while(queryRun.next())

{

jFLFSalesYearMonthTmp.clear();

queryCustInvoiceJour = queryRun.get(tableNum(CustInvoiceJour));

_custInvoiceTrans = queryRun.get(tableNum(CustInvoiceTrans));

_inventDim = queryRun.get(tableNum(InventDim));

_inventTable = queryRun.get(tableNum(InventTable));

_inventLocation = queryRun.get(tableNum(InventLocation));

_inventSite = queryRun.get(tableNum(InventSite));

jFLFSalesYearMonthTmp.InvoiceAmount = queryCustInvoiceJour.InvoiceAmount;

jFLFSalesYearMonthTmp.InvoiceDate = queryCustInvoiceJour.InvoiceDate;

jFLFSalesYearMonthTmp.SalesYear = year(queryCustInvoiceJour.InvoiceDate);

// jFLFSalesYearMonthTmp.SaleMonthName = mthName(mthOfYr(queryCustInvoiceJour.InvoiceDate)); // mthName(mthOfYr(systemdateget()))

s =mthName(mthOfYr(queryCustInvoiceJour.InvoiceDate));

jFLFSalesYearMonthTmp.SaleMName = s;

jFLFSalesYearMonthTmp.SalesMonth = mthOfYr(queryCustInvoiceJour.InvoiceDate);

jFLFSalesYearMonthTmp.InvoicingName = queryCustInvoiceJour.InvoicingName;

jFLFSalesYearMonthTmp.OrderAccount = queryCustInvoiceJour.OrderAccount;

jFLFSalesYearMonthTmp.costValue = queryCustInvoiceJour.costValue();

jFLFSalesYearMonthTmp.InventQty = _custInvoiceTrans.InventQty;

jFLFSalesYearMonthTmp.ItemId = _custInvoiceTrans.ItemId;

jFLFSalesYearMonthTmp.LineAmount = _custInvoiceTrans.LineAmount;

jFLFSalesYearMonthTmp.SalesPrice = _custInvoiceTrans.SalesPrice;

jFLFSalesYearMonthTmp.itemName = _custInvoiceTrans.itemName();

jFLFSalesYearMonthTmp.modVolumeM3 = _custInvoiceTrans.modVolumeM3();

jFLFSalesYearMonthTmp.configId = _inventDim.configId;

jFLFSalesYearMonthTmp.InventColorId = _inventDim.InventColorId;

jFLFSalesYearMonthTmp.InventSizeId = _inventDim.InventSizeId;

jFLFSalesYearMonthTmp.NameAlias = _inventTable.NameAlias;

jFLFSalesYearMonthTmp.InventLocationId = _inventLocation.InventLocationId;

jFLFSalesYearMonthTmp.RegionName = _inventLocation.Name;

jFLFSalesYearMonthTmp.SiteId = _inventSite.SiteId;

jFLFSalesYearMonthTmp.BranshName = _inventSite.Name;

jFLFSalesYearMonthTmp.insert();

}

ttsCommit;

}

so where do I have to do I have to it, and how to do it, just what I want the report to display only comparisons between specific years according the user input, can you please help.

Best regards

Jamil Alshaibani
MATTGUO 22,306 on at

Like (0)

Report

RE: Calculate % Growth YOY on SSRS

Hi Jamil,

You are using a query for lookup of parameter and a DP for actual data.

And you will also get this error if you have one dataset that references a data provider, and another that references a query.

Please try to code them into DP class.