topic Re: Headline of the report based on database table in SAS Visual Analytics

Headline of the report based on database table

filippo_kow — Wed, 27 Jul 2022 12:01:17 GMT

Hi guys,

I am a new VA user and I have a question related to creating headline of the report.

I have a table with values for headline, which looks like this:

name_of_report	date_of_report	created_by
Sample report	2022-07-10	Joe Black

Then, I use multidimentional table to present data in the following way:

Name of report	Sample report
Date of report	2022-07-10
Created by	Joe Black

(I put name_of_report, date_of_report and created_by into "Columns" of that multidimensional table). But unfortunately it looks that I need to add some measure (like counts), because when I delete that measure the table looks like 'original' table (three columns with values). Is there a way to solve this issue?

Thanks in advance!

Filip

Re: Headline of the report based on database table

Stu_SAS — Wed, 27 Jul 2022 13:17:54 GMT

Hey @filippo_kow! One way to get around this is by hard-coding your name of your report and who made it into a text box, but you can use dynamic values for the date. An easy way to do that is to extract each date value from your table, then set each aggregation to average. For example:

Year: year('date'n)

Month: month('date'n)

Day: dayofmonth('date'n)

In your dynamic text box, you can add each value by selecting them as Measure roles:

Sample Report create by Joe Black.
Last Updated: {year}-{month}-{day}

The date will dynamically change as your data changes.

Re: Headline of the report based on database table

filippo_kow — Wed, 27 Jul 2022 13:34:24 GMT

Hi @Stu_SAS!

Thanks for the info 🙂
In general I don't want to calculate data dynamicaly (like date of report), but instead of that I would like to use data from a database table. The flow is that I am running job that recalculates data that should be visible in headline, then I am loading it into Viya and I would like to present the values in the headline of report.
Any further help will be appreciated 🙂

Thanks!
Filip

Re: Headline of the report based on database table

filippo_kow — Wed, 27 Jul 2022 13:36:33 GMT

So it should work in the following way:
Name: <select name_of_report from my_table where accounting_period="&parameter">
User: <select user_name from my_table where accounting_period="&parameter">
Date: <select date from my_table where accounting_period="&parameter">

Re: Headline of the report based on database table

Madelyn_SAS — Wed, 27 Jul 2022 15:55:18 GMT

How will those values be chosen? Via controls? or a link from another object? Also, what version are you using?

Re: Headline of the report based on database table

filippo_kow — Thu, 28 Jul 2022 08:19:18 GMT

Hi @Madelyn_SAS ,

I am using version 8.5.1 (SAS Viya V.03.05) and values are chosen from drop-down list.

In general it looks in the following way:

I have a drop-down list with DATE values ('2022-05', '2022-06', '2022-07'), listing table with current report (when I select '2022-05' I print values for May, when I select '2022-06' I print values for June and so on), and I need to create headline with values from another table:

when I select '2022-05' I should print
Report for: '2022-05'
Report name: Some sample title
when I select '2022-06' I should print:
Report for: '2022-06'
Report name: Another sample title

For headline I am using multidimentional table and it looks quite good. But unfortunately there is automaticaly added measure with counts, and when I delete this measue the headline looks like typical database table.

Here is a sample "data model":

TABLE WITH VALUES FOR HEADLINE
DATE	NAME_OF_REPORT	VERSION_OF_REPORT
2022-06	Report One	v.6
2022-07	Report Two	v.6_1

TABLE WITH VALUES
DATE	SOME_CATEGORY	SOME_MESURE
2022-06	OneTwo	423
2022-06	ThreeFour	241
2022-06	FiveSix	543
2022-06	SevenEight	42
2022-07	NineTen	543
2022-07	ElevenTwelve	656

Report, when I choose 2022-06 from drop down list:

Name of report	Report One
Version of report	v.6

DATE	SOME_CATEGORY	SOME_MESURE
2022-06	OneTwo	423
2022-06	ThreeFour	241
2022-06	FiveSix	543
2022-06	SevenEight	42

Report, when I choose 2022-07 from drop down list:

Name of report	Report Two
Version of report	v.6_1

DATE	SOME_CATEGORY	SOME_MESURE
2022-07	NineTen	543
2022-07	ElevenTwelve	656

Regards,

Filip

Re: Headline of the report based on database table

filippo_kow — Fri, 29 Jul 2022 13:53:42 GMT

Hi guys,

Anyone has some idea 🙂 ?

Thanks,
Filip

Re: Headline of the report based on database table

filippo_kow — Sun, 07 Aug 2022 14:05:12 GMT

Hi @Stu_SAS ,

Thanks a lot for your reply. I have very basic question 😉

You wrote that I should extract each date value from my table and then add it to dynamic text box. And my quesiton is: where exactly should I do that 🙂 ? I mean, let's assume that I have a table with date values, and where exactly in VA I can extract date from my table?

Thanks in advance,

Filip

Re: Headline of the report based on database table

Stu_SAS — Mon, 08 Aug 2022 15:38:35 GMT

Hey @filippo_kow! In VA this would be a new calculated item. You'll do this by going to the Data tab and selecting "New data item," then "Calculated Item" from the drop-down menu. You can then get your individual day/month/year values by using the functions shown in my previous post. You will create one data item for each part of the date (e.g. one for day, one for month, and one for year).